Copy User

These are two simple methods to create a copy of a user on another with the same permissions and privileges.

i) Using the system views

— REPLACE FROMUSER BY USER TO COPY
— REPLACE TOUSER BY NEW USER
— CREATE USER
SELECT ‘CREATE USER TOUSER IDENTIFIED ‘ ||
DECODE (PASSWORD,
NULL, ‘EXTERNALLY’,
‘ BY VALUES ‘ || ”” || PASSWORD || ””
) || CHR(10) ||
‘DEFAULT TABLESPACE ‘ || DEFAULT_TABLESPACE || CHR(10) ||
‘TEMPORARY TABLESPACE ‘ || TEMPORARY_TABLESPACE || CHR(10) ||
‘PROFILE ‘ || PROFILE || CHR(10) ||
‘/’
FROM DBA_USERS
WHERE USERNAME = ‘FROMUSER’

SELECT ‘GRANT ‘ || PRIVILEGE || ‘ TO TOUSER ;’ FROM DBA_SYS_PRIVS WHERE GRANTEE = ‘FROMUSER’;
SELECT ‘GRANT ‘ || GRANTED_ROLE || ‘ TO TOUSER ;’ FROM DBA_ROLE_PRIVS WHERE GRANTEE = ‘FROMUSER’;
SELECT ‘GRANT ‘ || PRIVILEGE || ‘ ON ‘ || OWNER || ‘.’ || TABLE_NAME || ‘ TO TOUSER ;’
FROM DBA_TAB_PRIVS WHERE GRANTEE = ‘FROMUSER’;

ii) Using the DBMS_METADATA package

— GENERATE DDL SCRIPT, AFTER THAT CHANGE FROMUSER TO THE NEW USER

SELECT DBMS_METADATA.GET_DDL (‘USER’, ‘FROMUSER’) FROM DUAL;
SELECT DBMS_METADATA.GET_GRANTED_DDL (‘ROLE_GRANT’,’FROMUSER’) FROM DUAL;
SELECT DBMS_METADATA.GET_GRANTED_DDL (‘SYSTEM_GRANT’,’FROMUSER’) FROM DUAL;
SELECT DBMS_METADATA.GET_GRANTED_DDL (‘OBJECT_GRANT’,’FROMUSER’) FROM DUAL;
SELECT DBMS_METADATA.GET_granted_DDL (‘TABLESPACE_QUOTA’, ‘FROMUSER’) FROM dual;

HTH – Antonio NAVARRO

Advertisements

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 )

Twitter picture

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

Facebook photo

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

Google+ photo

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

Connecting to %s