Retrive all

Generate user DDL Posted on 2011/01/26 by Cyrille

The oracle DBA can use these 2 scripts to generate DDL statements for a user with their roles, system and object privileges.

On the other side, with datapump (impdp) you can use the parameter sqlfile=My_file.sql you can easily get DDL from dumpfile: http://www.oracle-scripts.net/standard-datapump-use/

For Oracle >=10:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 clear screen accept uname prompt 'Enter User Name : ' accept outfile prompt ' Output filename : '

spool &&outfile..gen

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);
  DBMS_METADATA.set_transform_param (DBMS_METADATA.session_transform, 'PRETTY', true);

END; /

SELECT dbms_metadata.get_ddl('USER','&&uname') FROM dual; SELECT DBMS_METADATA.GET_GRANTED_DDL('SYSTEM_GRANT','&&uname') from dual; SELECT DBMS_METADATA.GET_GRANTED_DDL('ROLE_GRANT','&&uname') from dual; SELECT DBMS_METADATA.GET_GRANTED_DDL('OBJECT_GRANT','&&uname') from dual;

spool off More information for this package in the official Oracle 12c documentation : http://docs.oracle.com/cd/E16655_01/appdev.121/e17602/d_metada.htm#ARPLS026

For Oracle <10 (runs well too with 10g, 11g):

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 clear screen

accept uname prompt 'Enter User Name : ' accept outfile prompt ' Output filename : '

col username noprint col lne newline

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

spool &&outfile..gen

prompt -- generate user ddl SELECT username, 'CREATE USER '||username||' '||

      DECODE(password, 'EXTERNAL', 'IDENTIFIED EXTERNALLY',
             'IDENTIFIED BY VALUES ||password|| ') lne,
      'DEFAULT TABLESPACE '||default_tablespace lne,
      'TEMPORARY TABLESPACE '||temporary_tablespace||';' lne
 FROM DBA_USERS
WHERE USERNAME LIKE UPPER('%&&uname%')
   OR UPPER('&&uname') IS NULL
ORDER BY USERNAME;

SELECT username, 'ALTER USER '||username||' QUOTA '||

      DECODE(MAX_BYTES, -1, 'UNLIMITED', TO_CHAR(ROUND(MAX_BYTES/1024))||'K')
      ||' ON '||tablespace_name||';' lne
 FROM DBA_TS_QUOTAS
WHERE USERNAME LIKE UPPER('%&&uname%')
   OR UPPER('&&uname') IS NULL
ORDER BY USERNAME;

col grantee noprint

select grantee, granted_role granted_priv,

      'GRANT '||granted_role||' to '||grantee||
      DECODE(ADMIN_OPTION, 'Y', ' WITH ADMIN OPTION;', ';')
 from dba_role_privs
where grantee like upper('%&&uname%')
        UNION

select grantee, privilege granted_priv,

      'GRANT '||privilege||' to '||grantee||
      DECODE(ADMIN_OPTION, 'Y', ' WITH ADMIN OPTION;', ';')
 from dba_sys_privs
where grantee like upper('%&&uname%')
order by 1, 2;

spool off This entry was posted in Datapump, Security by Cyrille. Bookmark the permalink.