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.