Re: Export user DDL and privileges?

From: Denis <denis.sun_at_yahoo.com>
Date: Wed, 25 Jul 2012 17:28:32 -0700 (PDT)
Message-ID: <1343262512.75077.YahooMailNeo_at_web122003.mail.ne1.yahoo.com>

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

--
http://www.freelists.org/webpage/oracle-l

Received on Wed Jul 25 2012 - 19:28:32 CDT