Tags

, ,

I found GET_GRANTED_DDL func which is very helpful while doing imp/exp – impdp/expdp operations or for any other user migrations. Check it out..

SELECT ‘select DBMS_METADATA.GET_GRANTED_DDL (”ROLE_GRANT”, ”’||USERNAME||”’) from dual;’
FROM DBA_USERS

‘SELECTDBMS_METADATA.GET_GRANTED_DDL(”ROLE_GRANT”,”’||USERNAME||”’)FROMDUAL;’

select DBMS_METADATA.GET_GRANTED_DDL (‘ROLE_GRANT’, ‘SYSTEM’) from dual;
select DBMS_METADATA.GET_GRANTED_DDL (‘ROLE_GRANT’, ‘SYS’) from dual;
select DBMS_METADATA.GET_GRANTED_DDL (‘ROLE_GRANT’, ‘SCOTT’) from dual;

select DBMS_METADATA.GET_GRANTED_DDL (‘ROLE_GRANT’, ‘ORACLE_OCM’) from dual;

DBMS_METADATA.GET_GRANTED_DDL(‘ROLE_GRANT’,’SYSTEM’)

GRANT “DBA” TO “SYSTEM” WITH ADMIN OPTION

GRANT “AQ_ADMINISTRATOR_ROLE” TO “SYSTEM” WITH ADMIN OPTION

GRANT “SALES_HISTORY_ROLE” TO “SYSTEM” WITH ADMIN OPTION

GRANT “MGMT_USER” TO “SYSTEM”

1 row selected.

DBMS_METADATA.GET_GRANTED_DDL(‘ROLE_GRANT’,’SYS’)

GRANT “CONNECT” TO “SYS” WITH ADMIN OPTION

GRANT “RESOURCE” TO “SYS” WITH ADMIN OPTION

GRANT “DBA” TO “SYS” WITH ADMIN OPTION

GRANT “SELECT_CATALOG_ROLE” TO “SYS” WITH ADMIN OPTION

GRANT “EXECUTE_CATALOG_ROLE” TO “SYS” WITH ADMIN OPTION

GRANT “DELETE_CATALOG_ROLE” TO “SYS” WITH ADMIN OPTION

GRANT “EXP_FULL_DATABASE” TO “SYS” WITH ADMIN OPTION

GRANT “IMP_FULL_DATABASE” TO “SYS” WITH ADMIN OPTION

GRANT “RECOVERY_CATALOG_OWNER” TO “SYS” WITH ADMIN OPTION

GRANT “GATHER_SYSTEM_STATISTICS” TO “SYS” WITH ADMIN OPTION

DBMS_METADATA.GET_GRANTED_DDL(‘ROLE_GRANT’,’SYS’)

GRANT “LOGSTDBY_ADMINISTRATOR” TO “SYS” WITH ADMIN OPTION

GRANT “AQ_ADMINISTRATOR_ROLE” TO “SYS” WITH ADMIN OPTION

GRANT “AQ_USER_ROLE” TO “SYS” WITH ADMIN OPTION

GRANT “OEM_MONITOR” TO “SYS” WITH ADMIN OPTION

GRANT “HS_ADMIN_ROLE” TO “SYS” WITH ADMIN OPTION

GRANT “JAVAUSERPRIV” TO “SYS” WITH ADMIN OPTION

GRANT “JAVAIDPRIV” TO “SYS” WITH ADMIN OPTION

GRANT “JAVASYSPRIV” TO “SYS” WITH ADMIN OPTION

GRANT “JAVADEBUGPRIV” TO “SYS” WITH ADMIN OPTION

GRANT “EJBCLIENT” TO “SYS” WITH ADMIN OPTION

GRANT “JAVA_ADMIN” TO “SYS” WITH ADMIN OPTION

DBMS_METADATA.GET_GRANTED_DDL(‘ROLE_GRANT’,’SYS’)

GRANT “JAVA_DEPLOY” TO “SYS” WITH ADMIN OPTION

GRANT “CTXAPP” TO “SYS” WITH ADMIN OPTION

GRANT “XDBADMIN” TO “SYS” WITH ADMIN OPTION

GRANT “AUTHENTICATEDUSER” TO “SYS” WITH ADMIN OPTION

GRANT “OLAP_DBA” TO “SYS” WITH ADMIN OPTION

GRANT “OLAP_USER” TO “SYS” WITH ADMIN OPTION

GRANT “SCHEDULER_ADMIN” TO “SYS” WITH ADMIN OPTION

GRANT “OEM_ADVISOR” TO “SYS” WITH ADMIN OPTION

GRANT “MGMT_USER” TO “SYS” WITH ADMIN OPTION

GRANT “CWM_USER” TO “SYS” WITH ADMIN OPTION

DBMS_METADATA.GET_GRANTED_DDL(‘ROLE_GRANT’,’SYS’)

GRANT “OLAPI_TRACE_USER” TO “SYS” WITH ADMIN OPTION

1 row selected.

DBMS_METADATA.GET_GRANTED_DDL(‘ROLE_GRANT’,’SCOTT’)

GRANT “CONNECT” TO “SCOTT”

GRANT “RESOURCE” TO “SCOTT”