LLI Wiki — My Digital Knowledge Base

Oracle: copy a user and its grants between servers

Extract the DDL of a user, its roles, privileges and quotas with DBMS_METADATA.

To recreate an Oracle user on another server with its password (hash), roles and grants, extract the matching DDL with DBMS_METADATA.

Extract a user's DDL

Replace APP_USER with the name of the user to copy:

select dbms_metadata.get_ddl('USER', 'APP_USER') FROM DUAL;
SELECT DBMS_METADATA.GET_GRANTED_DDL('ROLE_GRANT','APP_USER') FROM DUAL;
SELECT DBMS_METADATA.GET_GRANTED_DDL('SYSTEM_GRANT','APP_USER') FROM DUAL;
SELECT DBMS_METADATA.GET_GRANTED_DDL('OBJECT_GRANT','APP_USER') FROM DUAL;
SELECT DBMS_METADATA.GET_GRANTED_DDL('TABLESPACE_QUOTA', 'APP_USER') FROM dual;
Query Content
GET_DDL('USER', ...) User creation (with the password as a hash)
ROLE_GRANT Granted roles
SYSTEM_GRANT System privileges
OBJECT_GRANT Object privileges
TABLESPACE_QUOTA Tablespace quotas

Then run the resulting scripts on the target server.

Mastodon LLIMastodon SocialFosstodon