How To Get The DDL For An Oracle User With All Roles And Privileges?

Sometimes we need to create a user similar to another user or we need to recreate a user in another database.In those situations,Oracle supplied package DBMS_METADATA can be used to generate the user creation DDL.

>> To generate the User creation script

SQL> SELECT dbms_metadata.get_ddl(‘USER’,’TEST’) FROM dual;

DBMS_METADATA.GET_DDL(‘USER’,’TEST’)

——————————————————————————–

CREATE USER “TEST” IDENTIFIED BY VALUES ‘S:0905BF2E1482098330B134F45FD369’

DEFAULT TABLESPACE “USERS01”

TEMPORARY TABLESPACE “TEMP”

PROFILE “APP_USER”

>> To generate the script for granted roles

SQL> SELECT DBMS_METADATA.GET_GRANTED_DDL(‘ROLE_GRANT’,’TEST’) from dual;

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

——————————————————————————-

GRANT “DBA” TO “TEST”

>> To generate the script for system privileges

SQL> select DBMS_METADATA.GET_GRANTED_DDL(‘SYSTEM_GRANT’,’TEST’) from dual;

DBMS_METADATA.GET_GRANTED_DDL(‘SYSTEM_GRANT’,’TEST’)

——————————————————————————–

GRANT UPDATE ANY TABLE TO “TEST”

>> To generate the script for object privileges

SQL> SELECT DBMS_METADATA.GET_GRANTED_DDL(‘OBJECT_GRANT’,’TEST’) from dual;

DBMS_METADATA.GET_GRANTED_DDL(‘OBJECT_GRANT’,’TEST’)

——————————————————————————–

GRANT SELECT ON “USER1″.”TEST_CHECK” TO “TEST”

>> To generate the script for tablespace quota

SQL> select dbms_metadata.get_granted_ddl( ‘TABLESPACE_QUOTA’, ‘TEST’) from dual;

DBMS_METADATA.GET_GRANTED_DDL(‘TABLESPACE_QUOTA’,’TEST’)

——————————————————————————–

DECLARE

TEMP_COUNT NUMBER;

SQLSTR VARCHAR2(200);

BEGIN

SQLSTR := ‘ALTER USER “TEST” QUOTA 10737418240 ON “USERS01″‘;

EXECUTE IMMEDIATE SQLSTR;

EXCEPTION

WHEN OTHERS THEN

IF SQLCODE = -30041 THEN

SQLSTR := ‘SELECT COUNT(*) FROM USER_TABLESPACES

WHERE TABLESPACE_NAME = ”USERS01” AND CONTENTS

= ”TEMPORARY”’;

EXECUTE IMMEDIATE SQLSTR INTO TEMP_COUNT;

IF TEMP_COUNT = 1 THEN RETURN;

ELSE RAISE;

END IF;

ELSE

RAISE;

END IF;

END;

>> Below given query can be used to generate the consolidated script with all details.

set long 999

set head off

set pages 200

SELECT dbms_metadata.get_ddl(‘USER’,’TEST’) FROM dual

UNION ALL

SELECT DBMS_METADATA.GET_GRANTED_DDL(‘ROLE_GRANT’,’TEST’) from dual

UNION ALL

select DBMS_METADATA.GET_GRANTED_DDL(‘SYSTEM_GRANT’,’TEST’) from dual

UNION ALL

SELECT DBMS_METADATA.GET_GRANTED_DDL(‘OBJECT_GRANT’,’TEST’) from dual

UNION ALL

select dbms_metadata.get_granted_ddl( ‘TABLESPACE_QUOTA’, ‘TEST’) from dual

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

Article by : Anju