How to get DDL for roles – oracle

set long 20000 longchunksize 20000 pagesize 0 linesize 1000 feedback off verify off trimspool on
 column ddl format a1000

 dbms_metadata.set_transform_param (dbms_metadata.session_transform, 'SQLTERMINATOR', true);
 dbms_metadata.set_transform_param (dbms_metadata.session_transform, 'PRETTY', true);

variable v_role VARCHAR2(30);

exec :v_role := upper('&1');

select dbms_metadata.get_ddl('ROLE', r.role) AS ddl
 from dba_roles r
 where r.role = :v_role
 union all
 select dbms_metadata.get_granted_ddl('ROLE_GRANT', rp.grantee) AS ddl
 from dba_role_privs rp
 where rp.grantee = :v_role
 and rownum = 1
 union all
 select dbms_metadata.get_granted_ddl('SYSTEM_GRANT', sp.grantee) AS ddl
 from dba_sys_privs sp
 where sp.grantee = :v_role
 and rownum = 1
 union all
 select dbms_metadata.get_granted_ddl('OBJECT_GRANT', tp.grantee) AS ddl
 from dba_tab_privs tp
 where tp.grantee = :v_role
 and rownum = 1

Discover more from Raja M Naveed

Subscribe now to keep reading and get access to the full archive.

Continue reading