How to export users in Oracle with its roles and system privileges using expdp?

Question

I am trying to export a schema/user in Oracle with its roles and system privileges. I don't want to export any data or any table. I have tried to export users using the following command.

expdp system/system@gisdblocal include=user DIRECTORY = TestBack  
logfile=test12.log DUMPFILE=test12.dmp SCHEMAS=test_replication

When I import this in other database or in the same database with a different name i.e

impdp system/system@gisdblocal DIRECTORY = TestBack  DUMPFILE = test12.dmp   
SCHEMAS = test_replication REMAP_SCHEMA = 
test_replication:test_replication_copy

the user or schema is created but it has not been granted any role or system privileges.

I am doing this because I have created a backup of a schema using the user that did not have the required rights DATAPUMP_IMP_FULL_DATABASE or DATAPUMP_EXP_FULL_DATABASE. When I restore that backup in another database, it says the user does not exist. Therefore, I am thinking to create a user with the same privileges first and then restore the backup.

User creation and privilege granting are really the kind of thing which should be scripted and stashed in a source control repository. — APC, Sep 18 '19 at 11:25
What I do when I'm refreshing an entire schema from one DB to another, is drop all of the target schema objects, but not the user, prior to the import. — Adam vonNieda, Sep 18 '19 at 13:24

score 13 · Accepted Answer · answered Sep 18 '19 at 13:07

Using SQL...

SELECT dbms_metadata.get_ddl('USER', :name)
  FROM dual
UNION ALL
SELECT dbms_metadata.get_granted_ddl('ROLE_GRANT', grantee)
  FROM dba_role_privs
 WHERE grantee = :name
   AND ROWNUM = 1
UNION ALL
SELECT dbms_metadata.get_granted_ddl('DEFAULT_ROLE', grantee)
  FROM dba_role_privs
 WHERE grantee = :name
   AND ROWNUM = 1
UNION ALL
SELECT dbms_metadata.get_granted_ddl('SYSTEM_GRANT', grantee)
  FROM dba_sys_privs          sp,
       system_privilege_map   spm
 WHERE sp.grantee = :name
   AND sp.privilege = spm.name
   AND spm.property <> 1
   AND ROWNUM = 1
UNION ALL
SELECT dbms_metadata.get_granted_ddl('OBJECT_GRANT', grantee)
  FROM dba_tab_privs
 WHERE grantee = :name
   AND ROWNUM = 1
UNION ALL
SELECT dbms_metadata.get_granted_ddl('TABLESPACE_QUOTA', username)
  FROM dba_ts_quotas
 WHERE username = :name
   AND ROWNUM = 1

:name being...a bind variable for the USER you want to re-create.

I have been an Oracle DBA since Version 5 of the database (mid-1980's). This is the most clever way to get user information from database-to-database I have seen. Wow. Nice job. And thanks for sharing. — Ron, May 08 '20 at 14:58
it's the SQL we use behind the covers when using SQL Developer to manage users in the DBA > Security panel — thatjeffsmith, May 08 '20 at 15:16

Kamner Chen · Answer 2 · 2023-07-14T10:40:30.357

0

I found that some user privileges in dba_tab_privs but not in the above SQL results.

The default value of parameter OBJECT_COUNT is 10,000.

reset object_count=>3000000 get all the rows.

set longchunksize 3000  
set long 2000000000   
set lines 1000 
SELECT DBMS_METADATA.GET_GRANTED_DDL('OBJECT_GRANT',GRANTEE, object_count=>3000000)
FROM dba_tab_privs;

edited Jul 14 '23 at 10:40

answered Jul 14 '23 at 09:41

Kamner Chen

1
2

score 0 · Answer 3 · answered Aug 03 '23 at 12:30

I generally use the below, (replace XXXXX with your username)

set feedback off pages 0 long 90000 serveroutput on

SELECT DBMS_METADATA.GET_DDL('USER',username) from DBA_USERS where username='XXXXX';

SELECT DBMS_METADATA.GET_GRANTED_DDL('SYSTEM_GRANT',grantee) from DBA_SYS_PRIVS where grantee='XXXXX';

SELECT DBMS_METADATA.GET_GRANTED_DDL('ROLE_GRANT',grantee) from DBA_ROLE_PRIVS where grantee='XXXXX';

SELECT DBMS_METADATA.GET_GRANTED_DDL('OBJECT_GRANT',grantee) from DBA_TAB_PRIVS where grantee='XXXXX';

Hope it helps for you !!

How to export users in Oracle with its roles and system privileges using expdp?

3 Answers3