I bet you’re really wanting to hear about the other missing view — well here we are with part 2. The SYS.DBA_TAB_PRIVS_AE view seems a bit odd since it’s well documented that grants are not editioned in the database. That is true in a strict sense that a grant is not an object (versus a synonym, which is an object). A grant is more like a property of an object, if you delete the object, the grant also disappears (again, unlike a synonym). So though a grant does not have an edition associated with it, it does belong to an object and that object can be editioned.
If an object is newly created, it has no object grants. But if you do either an “alter compile” or “replace” (as in create or replace), then the grants are left from before. This applies across editions as well. If you instantiate an object in a child edition (with either compile or replace), it inherits all grants from the parent edition’s object. However, it does not maintain that relationship for future grants. After you instantiate an object in a child edition, it is de-coupled from the parent object and you need to maintain consistency of the grants between the editions (if that is important to you). I do this with a query on the SYS.DBA_TAB_PRIVS_AE_DH, the new DD view I will show you how to create, but first the SQL to fix any missing grants. This accepts 2 edition names and compares the grants for the objects in those 2 editions.
ACCEPT Edition1 CHAR PROMPT ‘First Edition Name: ‘
ACCEPT Edition2 CHAR PROMPT ‘Second Edition Name: ‘
PROMPT alter system set edition=&Edition1;;
SELECT ‘grant ‘||privilege||’ on ‘||owner||’.’||table_name||’ to ‘||grantee||’;’ FROM sys.dba_tab_privs_ae_dh WHERE edition_name = UPPER(‘&Edition2’)
MINUS
SELECT ‘grant ‘||privilege||’ on ‘||owner||’.’||table_name||’ to ‘||grantee||’;’ FROM sys.dba_tab_privs_ae_dh WHERE edition_name = UPPER(‘&Edition1’);
PROMPT
PROMPT alter system set edition=&Edition2;;
SELECT ‘grant ‘||privilege||’ on ‘||owner||’.’||table_name||’ to ‘||grantee||’;’ FROM sys.dba_tab_privs_ae_dh WHERE edition_name = UPPER(‘&Edition1’)
MINUS
SELECT ‘grant ‘||privilege||’ on ‘||owner||’.’||table_name||’ to ‘||grantee||’;’ FROM sys.dba_tab_privs_ae_dh WHERE edition_name = UPPER(‘&Edition2’);
Of course to run this, you will need to first create the sys.dba_tab_privs_ae_dh view like this:
create or replace view sys.dba_tab_privs_ae_dh
(grantee, owner, table_name, grantor, privilege, grantable, hierarchy, edition_name)
as
select ue.name, u.name, o.name, ur.name, tpm.name,
decode(mod(oa.option$,2), 1, ‘YES’, ‘NO’),
decode(bitand(oa.option$,2), 2, ‘YES’, ‘NO’), o.defining_edition
from sys.objauth$ oa, sys.”_ACTUAL_EDITION_OBJ” o, sys.user$ u, sys.user$ ur,
sys.user$ ue, table_privilege_map tpm
where oa.obj# = o.obj#
and oa.grantor# = ur.user#
and oa.grantee# = ue.user#
and oa.col# is null
and oa.privilege# = tpm.privilege
and u.user# = o.owner#;
comment on table SYS.dba_tab_privs_ae_dh is ‘All grants on objects in the database with edition’;
comment on column SYS.dba_tab_privs_ae_dh.GRANTEE is ‘User to whom access was granted’;
comment on column SYS.dba_tab_privs_ae_dh.OWNER is ‘Owner of the object’;
comment on column SYS.dba_tab_privs_ae_dh.TABLE_NAME is ‘Name of the object’;
comment on column SYS.dba_tab_privs_ae_dh.GRANTOR is ‘Name of the user who performed the grant’;
comment on column SYS.dba_tab_privs_ae_dh.PRIVILEGE is ‘Table Privilege’;
comment on column SYS.dba_tab_privs_ae_dh.GRANTABLE is ‘Privilege is grantable’;
comment on column SYS.dba_tab_privs_ae_dh.HIERARCHY is ‘Privilege is with hierarchy option’;
comment on column SYS.dba_tab_privs_ae_dh.edition_name is ‘Name of the edition in which the object is actual’;
create or replace public synonym dba_tab_privs_ae_dh for sys.dba_tab_privs_ae_dh;