Generate DDL for the User including grants in Oracle

Get the DDL for the User present in the Oracle database

Following is the script will provide you all the grants, profile , permission, tablespace quote of user:
Script:

set long 20000
set longchunksize 20000
set pagesize 0
set linesize 1000
set trimspool on
set column ddl format a1000
set feedback off
set verify off
--Add a semicolon at the end of each statement
execute dbms_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'SQLTERMINATOR',true);
--Generate the DDL for User you enter
select dbms_metadata.get_ddl('USER', u.username) AS ddl
from dba_users u
where u.username = '&&v_username'
union all
select dbms_metadata.get_granted_ddl('TABLESPACE_QUOTA', tq.username) AS ddl
from dba_ts_quotas tq
where tq.username = '&&v_username' and rownum = 1
union all
select dbms_metadata.get_granted_ddl('ROLE_GRANT', rp.grantee) AS ddl
from dba_role_privs rp
where rp.grantee = '&&v_username'
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_username'
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_username'
and rownum = 1
union all
select dbms_metadata.get_granted_ddl('DEFAULT_ROLE', rp.grantee) AS ddl
from dba_role_privs rp
where rp.grantee = '&&v_username'
and rp.default_role = 'YES'
and rownum = 1
union all
select to_clob('/* Start profile creation script in case they are missing') AS ddl
from dba_users u
where u.username = '&&v_username'
and u.profile='DEFAULT'
and rownum = 1
union all
select dbms_metadata.get_ddl('PROFILE', u.profile) AS ddl
from dba_users u
where u.username = '&&v_username'
and u.profile='DEFAULT'
union all
select to_clob('End profile creation script */') AS ddl
from dba_users u
where u.username = '&&v_username'
and u.profile='DEFAULT'
and rownum = 1
/

OUTPUT:

Enter value for v_username: SYSTEM


ALTER USER "SYSTEM" IDENTIFIED BY VALUES 'S:4200BB8AD50E2E684D0740528FD1A348C9C30CAE5569FFA61752717412E7;T:458398FB4D29590DA463E66C768806AAAD78E081D3580570D0589D193ED34959A6D82EE278EB26F643C3604F16D15583E7F52BC9687EEC5938322F70F932275B36E95CB96DBC5574594D0B3872464B28'
TEMPORARY TABLESPACE "TEMP";
GRANT "DBA" TO "SYSTEM";
GRANT "AQ_ADMINISTRATOR_ROLE" TO "SYSTEM" WITH ADMIN OPTION;
GRANT GLOBAL QUERY REWRITE TO "SYSTEM";
GRANT CREATE MATERIALIZED VIEW TO "SYSTEM";
GRANT SELECT ANY TABLE TO "SYSTEM";
GRANT CREATE TABLE TO "SYSTEM";
GRANT UNLIMITED TABLESPACE TO "SYSTEM";
GRANT USE ON "SYS"."ORA$BASE" TO "SYSTEM" WITH GRANT OPTION;
GRANT SELECT ON "SYS"."LOGMNR_GTCS_SUPPORT" TO "SYSTEM";
GRANT EXECUTE ON "SYS"."DBMS_ALERT" TO "SYSTEM";
GRANT EXECUTE ON "SYS"."DBMS_SYS_ERROR" TO "SYSTEM";
GRANT EXECUTE ON "SYS"."DBMS_RULE_EXIMP" TO "SYSTEM";
GRANT EXECUTE ON "SYS"."DBMS_AQ" TO "SYSTEM";
GRANT EXECUTE ON "SYS"."DBMS_AQADM" TO "SYSTEM";
GRANT EXECUTE ON "SYS"."DBMS_AQ_IMPORT_INTERNAL" TO "SYSTEM";
GRANT EXECUTE ON "SYS"."DBMS_AQELM" TO "SYSTEM";
GRANT EXECUTE ON "SYS"."DBMS_TRANSFORM_EXIMP" TO "SYSTEM";
GRANT EXECUTE ON "SYS"."DBMS_SYSTEM" TO "SYSTEM";
GRANT EXECUTE ON "SYS"."DBMS_LOGMNR_INTERNAL" TO "SYSTEM";
GRANT EXECUTE ON "SYS"."LOGMNR_DICT_CACHE" TO "SYSTEM";
GRANT EXECUTE ON "SYS"."LOGMNR_GET_GT_PROTOCOL" TO "SYSTEM";
GRANT EXECUTE ON "SYS"."LOGMNR_GTLO3" TO "SYSTEM";
GRANT EXECUTE ON "SYS"."SYS_GROUP" TO "SYSTEM";
GRANT DELETE ON "XDB"."XDB$TSETMAP" TO "SYSTEM";
GRANT DELETE ON "XDB"."XDB$H_INDEX" TO "SYSTEM";
GRANT INSERT ON "XDB"."XDB$H_INDEX" TO "SYSTEM";
GRANT SELECT ON "XDB"."XDB$H_INDEX" TO "SYSTEM";
GRANT UPDATE ON "XDB"."XDB$H_INDEX" TO "SYSTEM";
GRANT INSERT ON "XDB"."XDB$D_LINK" TO "SYSTEM";
GRANT SELECT ON "XDB"."XDB$D_LINK" TO "SYSTEM";
GRANT UPDATE ON "XDB"."XDB$D_LINK" TO "SYSTEM";
GRANT DELETE ON "XDB"."XDB$RESOURCE" TO "SYSTEM";
GRANT INSERT ON "XDB"."XDB$RESOURCE" TO "SYSTEM";
GRANT SELECT ON "XDB"."XDB$RESOURCE" TO "SYSTEM";
GRANT UPDATE ON "XDB"."XDB$RESOURCE" TO "SYSTEM";
GRANT DELETE ON "XDB"."XDB$NLOCKS" TO "SYSTEM";
GRANT INSERT ON "XDB"."XDB$NLOCKS" TO "SYSTEM";
GRANT SELECT ON "XDB"."XDB$NLOCKS" TO "SYSTEM";
GRANT UPDATE ON "XDB"."XDB$NLOCKS" TO "SYSTEM";
GRANT EXECUTE ON "SYS"."CHECK_UPGRADE" TO "SYSTEM";
GRANT DELETE ON "XDB"."X$PT3HBHABL22E8LPPKKO8O9CW9I2F" TO "SYSTEM";
GRANT INSERT ON "XDB"."X$PT3HBHABL22E8LPPKKO8O9CW9I2F" TO "SYSTEM";
GRANT SELECT ON "XDB"."X$PT3HBHABL22E8LPPKKO8O9CW9I2F" TO "SYSTEM";
GRANT UPDATE ON "XDB"."X$PT3HBHABL22E8LPPKKO8O9CW9I2F" TO "SYSTEM";
GRANT DELETE ON "XDB"."XDB$ACL" TO "SYSTEM";
GRANT INSERT ON "XDB"."XDB$ACL" TO "SYSTEM";
GRANT SELECT ON "XDB"."XDB$ACL" TO "SYSTEM";
GRANT UPDATE ON "XDB"."XDB$ACL" TO "SYSTEM";
GRANT DELETE ON "XDB"."XDB$RESCONFIG" TO "SYSTEM";
GRANT INSERT ON "XDB"."XDB$RESCONFIG" TO "SYSTEM";
GRANT SELECT ON "XDB"."XDB$CONFIG" TO "SYSTEM";
GRANT UPDATE ON "XDB"."XDB$CONFIG" TO "SYSTEM";
ALTER USER "SYSTEM" DEFAULT ROLE ALL;
/* Start profile creation script in case they are missing
ALTER PROFILE "DEFAULT"
LIMIT
COMPOSITE_LIMIT UNLIMITED
SESSIONS_PER_USER UNLIMITED
CPU_PER_SESSION UNLIMITED
CPU_PER_CALL UNLIMITED
LOGICAL_READS_PER_SESSION UNLIMITED
LOGICAL_READS_PER_CALL UNLIMITED
IDLE_TIME UNLIMITED
CONNECT_TIME UNLIMITED
PRIVATE_SGA UNLIMITED
FAILED_LOGIN_ATTEMPTS 10
PASSWORD_LIFE_TIME UNLIMITED
PASSWORD_REUSE_TIME UNLIMITED
PASSWORD_REUSE_MAX UNLIMITED
PASSWORD_VERIFY_FUNCTION NULL
PASSWORD_LOCK_TIME 86400/86400
PASSWORD_GRACE_TIME 604800/86400
INACTIVE_ACCOUNT_TIME UNLIMITED ;
End profile creation script */

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.