Script to extract ROLE DDL and dbms_metadata.get_granted_ddl bug for Oracle 23c

 

Script to extract ROLE DDL

set long 8000 
set longchunksize 2000

with role_name as 
  (select 'DBA' as name from dual)
select dbms_metadata.get_ddl('ROLE', r.role) AS ddl
from   dba_roles r, role_name
where  r.role = role_name.name
union all
select dbms_metadata.get_granted_ddl('ROLE_GRANT', rp.grantee) AS ddl
from   dba_role_privs rp, role_name
where  rp.grantee = role_name.name
and    rownum = 1
union all
select dbms_metadata.get_granted_ddl('SYSTEM_GRANT', sp.grantee) AS ddl
from   dba_sys_privs sp, role_name
where  sp.grantee = role_name.name
and    rownum = 1
union all
select dbms_metadata.get_granted_ddl('OBJECT_GRANT', tp.grantee) AS ddl
from   dba_tab_privs tp, role_name
where  tp.grantee = role_name.name
and    rownum = 1
/

Bug in Oracle 23c (23.2)

SQL> select banner_full from v$version;

BANNER_FULL
--------------------------------------------------------------------------------
Oracle Database 23c Free, Release 23.0.0.0.0 - Developer-Release
Version 23.2.0.0.0


SQL> select dbms_metadata.get_granted_ddl('SYSTEM_GRANT','CONNECT') from dual;

DBMS_METADATA.GET_GRANTED_DDL('SYSTEM_GRANT','CONNECT')
--------------------------------------------------------------------------------

  BEGIN NULL; END
  BEGIN NULL; END

There is no such funny bug in Oracle 21c (21.9)

SQL> select banner_full from v$version;

BANNER_FULL
--------------------------------------------------------------------------------
Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production
Version 21.9.0.0.0


SQL> select dbms_metadata.get_granted_ddl('SYSTEM_GRANT','CONNECT') from dual;

DBMS_METADATA.GET_GRANTED_DDL('SYSTEM_GRANT','CONNECT')
--------------------------------------------------------------------------------

  GRANT CREATE SESSION TO "CONNECT"
  GRANT SET CONTAINER TO "CONNECT"