Copying Oracle Users,Roles,Privileges from DBMS_METADATA.GET_DDL()

In my today's blog I am going to highlight the usage of  utility package called dbms_metadata that will easily display DDL and stored procedures directly from the data dictionary. Using this powerful utility, you can punch individual objects or an entire Oracle schema.

Sometimes you get an requirement to create a Y user as like of X user and provide all the priviliges and roles X user does have to Y user. In this case the DBMS_METADATA.GET_DDL() has the best utility to use rather than manually thinking about what sql commands you goona used to create Y user as like of X user.

So here we go.

USER CREATION DDL

First check what is the create statement which include the default tablespace and temporary tablespace and in which profile it is set to be.

Here X user: IBM  :)
and Y user: TCS  :)

set head off
set pages 0
set long 9999999

SELECT DBMS_METADATA.GET_DDL('USER',username) as script from DBA_USERS where username='&username';

 CREATE USER "IBM" IDENTIFIED BY VALUES 'S:8EF6C6807D8B18E063CE1AA5ACC6C4165BA0893EA4DE660394B2049C41;96556E541B38CFEC'
    DEFAULT TABLESPACE "USERS"
    TEMPORARY TABLESPACE "TEMP"

Now create user TCS user like above with default tablespace and temporary tablespace.

ROLE ASSIGNED TO X USER and provide the same roles to Y user

SELECT DBMS_METADATA.GET_granted_DDL('ROLE_GRANT',username) as script from DBA_USERS where username='&username';

 GRANT "CONNECT" TO "IBM"

 GRANT "RESOURCE" TO "IBM"

Get the system grants and object grants using function GET_GRANTED_DLL

 SELECT DBMS_METADATA.GET_granted_DDL('SYSTEM_GRANT',username) as script from DBA_USERS where username='&username';

SAMPLE OUTPUT

  GRANT ADMINISTER SQL TUNING SET TO "IBM"
 GRANT ALTER ANY SQL PROFILE TO "IBM"
GRANT DROP ANY SQL PROFILE TO "IBM"
GRANT SELECT ANY TRANSACTION TO "IBM"

>> To generate the script for object privileges

SQL> SELECT DBMS_METADATA.GET_GRANTED_DDL('OBJECT_GRANT','IBM') from dual;

>> To generate the script for tablespace quota

SQL> select dbms_metadata.get_granted_ddl( 'TABLESPACE_QUOTA', 'IBM') from dual;

  In a single command

Note : ORA-31608 and ORA-06512 will occur if there is no output ( no rows selected) for any of these statements.

-- This will generate the DDL for the user and add his objects,system and role grants

SELECT DBMS_METADATA.GET_DDL('USER',username) as script from DBA_USERS where username='&username'

UNION ALL

SELECT DBMS_METADATA.GET_GRANTED_DDL('SYSTEM_GRANT',grantee)as script from DBA_SYS_PRIVS where grantee='&username' and rownum=1

UNION ALL

SELECT DBMS_METADATA.GET_GRANTED_DDL('ROLE_GRANT',grantee)as script from DBA_ROLE_PRIVS where grantee='&username' and rownum=1

UNION ALL

SELECT DBMS_METADATA.GET_GRANTED_DDL('OBJECT_GRANT',grantee)as script from DBA_TAB_PRIVS where grantee='&username' and rownum=1;