My Oracle Support Banner

Permissions Lost On Version Enabled Table When Using DBMS_WM.BeginDDL (Doc ID 274122.1)

Last updated on APRIL 24, 2020

Applies to:

Workspace Manager - Version 9.2.0.1 and later
Information in this document applies to any platform.

Symptoms

 

Modifying the structure of a table which has been Version Enabled using WorkSpace Management, all the permissions on the tables and views created in WorkSpace management are lost.

The following select shows privileges granted the cola_marketing_bugdet table. The table is version enabled using Oracle Workspace Manager.

    SQL> select grantee, table_name, select_priv, insert_priv, delete_priv, update_priv
      2      from table_privileges
      3     where table_name like 'COLA%'
      4  ;
    
    GRANTEE                        TABLE_NAME                     S I D U
    ------------------------------ ------------------------------ - - - -
    SCOTT                          COLA_MARKETING_BUDGET          Y N N N
    SCOTT                          COLA_MARKETING_BUDGET_LT       N N N N
    SCOTT                          COLA_MARKETING_BUDGET_MW       Y N N N
    SCOTT                          COLA_MARKETING_BUDGET_CONF     Y N N N
    SCOTT                          COLA_MARKETING_BUDGET_DIFF     Y N N N
    SYS                            COLA_MARKETING_BUDGET_HIST     G N N N
    WM_ADMIN_ROLE                  COLA_MARKETING_BUDGET_HIST     Y N N N
    SCOTT                          COLA_MARKETING_BUDGET_LOCK     Y N N N
    
    8 rows selected.



now the following steps are performed to add a new column to the table:

       EXECUTE DBMS_WM.BeginDDL('COLA_MARKETING_BUDGET');
       ALTER TABLE cola_marketing_budget_lts ADD (comments VARCHAR2(100));
       DESCRIBE cola_marketing_budget_lts;
       EXECUTE DBMS_WM.CommitDDL('COLA_MARKETING_BUDGET');




displaying the granted privileges will show, that the previous granted privileges do not exist anymore:

    SQL> select grantee, table_name, select_priv, insert_priv, delete_priv, update_priv
      2      from table_privileges
      3     where table_name like 'COLA%'
      4  ;
    
    GRANTEE                        TABLE_NAME                     S I D U
    ------------------------------ ------------------------------ - - - -
    SCOTT                          COLA_MARKETING_BUDGET_LT       N N N N

Changes

 

Cause

To view full details, sign in with your My Oracle Support account.

Don't have a My Oracle Support account? Click to get started!


My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.