3

I'd like to get ddls of all roles in the database using dbms_metadata package. Unfortunately dbms_metadata.get_granted_ddl fails with error when there are no grant (object, system or role type) for the role. That's why I have to check the presence of privileges in dba_tab_privs, dba_sys_privs and dba_role_privs views. However AQ_ADMINISTRATOR_ROLE role has system privileges the following statement fails. Checked on two databases.

sqlplus system/pass@db1

select dbms_metadata.get_granted_ddl('SYSTEM_GRANT', 'AQ_ADMINISTRATOR_ROLE')
  from dual
  where exists (select 1 from dba_sys_privs where grantee = 'AQ_ADMINISTRATOR_ROLE')
/

ORA-31608: specified object of type SYSTEM_GRANT not found ORA-06512: at "SYS.DBMS_METADATA", line 4018 ORA-06512: at "SYS.DBMS_METADATA", line 5991 ORA-06512: at line 1

select * from v$version
/

Oracle Database 11g Release 11.2.0.2.0 - 64bit Production PL/SQL
Release 11.2.0.2.0 - Production CORE    11.2.0.2.0  Production
pahariayogi
  • 1,073
  • 1
  • 7
  • 18
hotmori
  • 91
  • 2
  • 5
  • Code in title and body of question does not match. Title says 'get_granted_ddl' but description says 'get_dependent_ddl'. Need to get fixed. – pahariayogi Jul 31 '15 at 15:46

2 Answers2

2

Oracle returns that exception if there are no rows that match your parameters. There must be no SYSTEM_GRANT objects for QA_ADMINISTRATOR_ROLE

Nathan Voxland
  • 15,453
  • 2
  • 48
  • 64
  • Its a correct but in this case @hotmori is making incorrect use of 'dbms_metadata.get_granted_ddl' by passing role 'AQ_ADMINISTRATOR_ROLE' in place of schema user. I made a assumption and given some sample usage of 'dbms_metadata' usage. – pahariayogi Jul 31 '15 at 15:31
  • Grantees include both users and roles. Example: SELECT dbms_metadata.get_granted_ddl('SYSTEM_GRANT', 'DBA') FROM DUAL; I came to this question because AQ_ADMINISTRATOR_ROLE is the only role for which this doesn't work. – durette Nov 14 '17 at 13:49
0

This will return what you are looking for:

select dbms_metadata.GET_GRANTED_DDL('SYSTEM_GRANT')  from dual;

This will return something like:

GRANT CREATE JOB TO "SCOTT"

GRANT SELECT ANY DICTIONARY TO "SCOTT"

GRANT EXECUTE ANY TYPE TO "SCOTT"

I assume, you want to extract DDL for all privileges (roles/permissions) given to current schema user (when you say 'database').

If one wants to extract for a given user, use the following:

select dbms_metadata.GET_GRANTED_DDL('SYSTEM_GRANT', 'SCOTT')  from dual;

If one wants to extract privileges given on a OBJECT from current user to another schema user, use the following:-

select DBMS_METADATA.GET_DEPENDENT_DDL('OBJECT_GRANT','TEST_TABLE') from dual;
pahariayogi
  • 1,073
  • 1
  • 7
  • 18
  • 3
    DBMS_METADATA.GET_DEPENDENT_DDL will throw 'ORA-31608' exceptions if NO grants given on object 'TEST_TABLE' to other users. However, i am not sure if its a oracle bug or a intended behavior. I would have expected 'no rows returned' – pahariayogi Jul 31 '15 at 15:41