Visualizza wikitesto di 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.