Oracle: How to give all privileges of one user to another?

Question

I have 2 user A and B. They all have some privileges of their own. Now, i want to give all A's privileges to B. Is there any way (instantly,...) to do that but write a script to give that grant for each user (by select from USER_SYS_PRIVS - i mean when i have a lot of privileges, it seems impossible to be done!?)? Thanks in advance!

Answer 1

You can try something like this:

Get all privileges from AAA

 SELECT DBMS_METADATA.GET_GRANTED_DDL('ROLE_GRANT','AAA') FROM DUAL;

 SELECT DBMS_METADATA.GET_GRANTED_DDL('SYSTEM_GRANT','AAA') FROM DUAL;

 SELECT DBMS_METADATA.GET_GRANTED_DDL('OBJECT_GRANT','AAA') FROM DUAL;

Change the DDL commands with the user 'BBB' and execute.

Or

expdp userid=system directory=DATA_PUMP_DIR dumpfile=AAA.dmp schemas=AAA

impdp userid=system directory=DATA_PUMP_DIR dumpfile=AAA.dmp