RE: [Q] way to re-gernerate ROLE privilege ?

From: Freeman, Donald <dofreeman_at_state.pa.us>
Date: Mon, 9 May 2005 15:37:20 -0400
Message-ID: <51327ABA927BEF4B96590554CEA7832C25D48B@enhbgpri05.pa.lcl>

This will get you started.

SELECT 'CREATE ROLE '||role||' NOT IDENTIFIED;' FROM DBA_ROLES WHERE ROLE NOT IN
('CONNECT',
'RESOURCE',
'DBA',
'SELECT_CATALOG_ROLE',
'EXECUTE_CATALOG_ROLE',
'DELETE_CATALOG_ROLE',
'EXP_FULL_DATABASE',
'IMP_FULL_DATABASE',
'RECOVERY_CATALOG_OWNER',
'GATHER_SYSTEM_STATISTICS',
'LOGSTDBY_ADMINISTRATOR',
'AQ_ADMINISTRATOR_ROLE',
'AQ_USER_ROLE',
'OEM_MONITOR',
'HS_ADMIN_ROLE',
'WM_ADMIN_ROLE');
SELECT 'GRANT '||privilege||' ON '||owner||'.'||table_name||' TO =
'||grantee||';'

FROM dba_tab_privs where owner not in ('SYS','SYSTEM','WMSYS') ORDER BY privilege
/

SELECT 'GRANT '||privilege||' TO '||grantee||';' FROM dba_sys_privs where grantee not in ('SYS',
'SYSTEM',
'CSMIG',
'WMSYS',
'EXP_FULL_DATABASE',
'DBA',
'IMP_FULL_DATABASE',
'EXECUTE_CATALOG_ROLE',
'SELECT_CATALOG_ROLE',
'OUTLN',
'OEM_MONITOR',
'LOGSTDBY_ADMINISTRATOR',
'CONNECT',
'RECOVERY_CATALOG_OWNER',
'AQ_ADMINISTRATOR_ROLE',
'RESOURCE')

ORDER BY privilege
/

Don Freeman
Database Administrator 1
Pennsylvania Dept of Health
Bureau of Information Technology
717-783-8095 Ext 337

-----Original Message-----
From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org]On Behalf Of dba1 mcc Sent: Monday, May 09, 2005 3:16 PM
To: oracle-l_at_freelists.org
Subject: [Q] way to re-gernerate ROLE privilege ?

We have a ORACLE role with 50 different object privileges grant to it. Does there has way to generate a script which I can bring this script to another database and re-create it?

Thanks.

        =09

__________________________________=20

Do you Yahoo!?=20
Yahoo! Mail - Helps protect you from nasty viruses.=20 http://promotions.yahoo.com/new_mail

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

Received on Mon May 09 2005 - 15:41:48 CDT