Export users - roles & priv (draft)

 

https://stackoverflow.com/questions/57991488/how-to-export-users-in-oracle-with-its-roles-and-system-privileges-using-expdp

 set long 20000

 set lines 200

 set pages 666

 col DBMS_METADATA.GET_DDL('USER',:NAME) format a90

 variable name varchar2(50)

 exec :name:='CON29';

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

SQL> /

DBMS_METADATA.GET_DDL('USER',:NAME)

------------------------------------------------------------------------------------------

   CREATE USER "CON29" IDENTIFIED BY VALUES 'AbcDefGhi'

      DEFAULT TABLESPACE "USERS"

      TEMPORARY TABLESPACE "TEMPORARY"

.....etc

Run against the new database to create the user.

In AIX to run against multiple DBs e.g.

#!/usr/bin/env bash

set -A array XYZDBA 

## Set the env b4 running this

for i in "${array[@]}"

do

exit | sqlplus -silent / as sysdba << EOF

set head on

set feedback off

set long 30000

set lines 220

set pages 666

## col DBMS_METADATA.GET_DDL('USER',:NAME) format a220

## 

col DBMS_METADATA.GET_DDL('USER','XYZDBA') format a220

SELECT dbms_metadata.get_ddl('USER',  '$i')

  FROM dual......

(   same script as above )

EOF

done

 

This works too...

declare
data CLOB;
BEGIN
SELECT dbms_metadata.get_ddl('USER','SFS') into data from dual;
DBMS_XSLPROCESSOR.CLOB2FILE(data,'DIRECTORY**','create_user.sql');

END;

 

**DIRECTORY - change as approp. e.g. DATA_PUMP_DIR find from dba_directories

For the tablespaces for the above..

SQL> SET LONG 20000 LONGCHUNKSIZE 20000 PAGESIZE 0 LINESIZE 1000 FEEDBACK OFF VERIFY OFF TRIMSPOOL ON

BEGIN

   DBMS_METADATA.set_transform_param (DBMS_METADATA.session_transform, 'SQLTERMINATOR', true);

SQL> SQL>   2    3     DBMS_METADATA.set_transform_param (DBMS_METADATA.session_transform, 'PRETTY', true);

END;

/

SELECT DBMS_METADATA.get_ddl ('TABLESPACE', tablespace_name)

FROM   dba_tablespaces

WHERE  tablespace_name = DECODE(UPPER('&1'), 'ALL', tablespace_name, UPPER('&1'));

  4    5  SET PAGESIZE 14 LINESIZE 100 FEEDBACK ON VERIFY ON

SQL> SQL>   2    3  Enter value for 1: Enter value for 1: SQL>

SQL> /

Enter value for 1: NWLMEAVX

Enter value for 1: NWLMEAVX

  CREATE TABLESPACE "NWLMEAVX" DATAFILE etc......

You will need to alter to suit when going from ASM to Unix datafiles or vice-versa.

see https://oracle-base.com/dba/script?category=script_creation&file=tablespace_ddl.sq

l