Re: Export user DDL and privileges?
- From: Denis <denis.sun@xxxxxxxxx>
- To: Chen Zhou <oracle.unknowns@xxxxxxxxx>
- Date: Wed, 25 Jul 2012 17:28:32 -0700 (PDT)
If your purpose is to construct a create user script with all privs, roles etc,
please check if the following script helps. It won't give you neat commands,
some edits needed to apply it to other database.
rem script: user_cr_ddl.sql
rem Purpose: generate create user script with privs
rem
rem Usage: user_cr_ddl <username>
rem
rem Note:
rem If ORA-31608 encountered, it means the user does not
rem have grants in that category. Edit the spooled script
rem as ncessary
rem
rem
SET LINESIZE 200
SET PAGESIZE 0 FEEDBACK off VERIFY off
-- SET TRIMSPOOL on
SET LONG 1000000
-- COLUMN ddl_string FORMAT A100 WORD_WRAP
EXECUTE
DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'PRETTY',true);
EXECUTE
DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'SQLTERMINATOR',true);
COLUMN ddl FORMAT A4000
define username=&&1
spool &username._cr_ddl.sql
SELECT DBMS_METADATA.GET_DDL('USER', upper('&username') ) DDL
FROM dual;
prompt -- Role
SELECT DBMS_METADATA.GET_GRANTED_DDL('ROLE_GRANT', upper('&username')) DDL
from dual
where exists ( select 1 from dba_role_privs where grantee=upper('&username') )
;
prompt -- Sys priv
SELECT DBMS_METADATA.GET_GRANTED_DDL('SYSTEM_GRANT', upper('&username')) DDL
FROM dual
where exists ( select 1 from dba_sys_privs where grantee=upper('&username') )
;
prompt -- Object priv
SELECT DBMS_METADATA.GET_GRANTED_DDL('OBJECT_GRANT', upper('&username')) DDL
FROM dual
where exists ( select 1 from dba_tab_privs where grantee=upper('&username') )
;
prompt -- tablespace quota
SELECT DBMS_METADATA.GET_GRANTED_DDL('TABLESPACE_QUOTA',upper('&username')) DDL
from dual
where exists ( select 1 from dba_ts_quotas where username=upper('&username') )
;
spool off
- Denis
--
//www.freelists.org/webpage/oracle-l
Other related posts:
