Home » RDBMS Server » Server Administration » SQL for generating roles (Oracle 11.2.0.2.0,Linux 2.6)
SQL for generating roles [message #572900] Tue, 18 December 2012 08:33 Go to next message
Jack14
Messages: 497
Registered: December 2011
Location: INDIA
Senior Member
Hi,

I am looking for the below one.

SQL for generating roles needs to be spooled by automated script.

Thank you
Re: SQL for generating roles [message #572902 is a reply to message #572900] Tue, 18 December 2012 08:43 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
Please read and follow the forum guidelines, to enable us to help you:

http://www.orafaq.com/forum/t/88153/0/

DDL to CREATE ROLL should reside now in application code repository.
How did the ROLEs get into the DB in the first place?

Re: SQL for generating roles [message #572903 is a reply to message #572900] Tue, 18 December 2012 08:48 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
First step:
select dbms_metadata.get_ddl('ROLE',role) from dba_roles ;


Try to improve it and come back if you can't.

Regards
Michel
Re: SQL for generating roles [message #574915 is a reply to message #572903] Wed, 16 January 2013 13:49 Go to previous messageGo to next message
alan.kendall@nfl.com
Messages: 163
Registered: June 2012
Location: Culver City, California
Senior Member
I use the following to create application roles
ECSCDAS1S > @cr8_all_existing_roles.sql

'CREATEROLE'||ROLE||';'
-------------------------------------------
create role PV_WR_ROLE;
create role LIVE_RO_ROLE;
create role LIVE_WR_ROLE;
create role LIVE_WR_ROLE2;
create role PV_RO_ROLE;
create role FEEDS_BATCH_ROLE;
create role SHARED_WR_ROLE;
create role SHARED_RO_ROLE;

8 rows selected.

ECSCDAS1S > list
  1  select 'create role '||role||';' from dba_roles
  2  where role not in (
  3  'CONNECT',
  4  'RESOURCE',
  5  'DBA',
  6  'SELECT_CATALOG_ROLE',
  7  'EXECUTE_CATALOG_ROLE',
  8  'DELETE_CATALOG_ROLE',
  9  'EXP_FULL_DATABASE',
 10  'IMP_FULL_DATABASE',
 11  'LOGSTDBY_ADMINISTRATOR',
 12  'DBFS_ROLE',
 13  'AQ_ADMINISTRATOR_ROLE',
 14  'AQ_USER_ROLE',
 15  'DATAPUMP_EXP_FULL_DATABASE',
 16  'DATAPUMP_IMP_FULL_DATABASE',
 17  'ADM_PARALLEL_EXECUTE_TASK',
 18  'GATHER_SYSTEM_STATISTICS',
 19  'JAVA_DEPLOY',
 20  'RECOVERY_CATALOG_OWNER',
 21  'SCHEDULER_ADMIN',
 22  'HS_ADMIN_SELECT_ROLE',
 23  'HS_ADMIN_EXECUTE_ROLE',
 24  'HS_ADMIN_ROLE',
 25  'GLOBAL_AQ_USER_ROLE',
 26  'OEM_ADVISOR',
 27  'OEM_MONITOR',
 28  'WM_ADMIN_ROLE',
 29  'JAVAUSERPRIV',
 30  'JAVAIDPRIV',
 31  'JAVASYSPRIV',
 32  'JAVADEBUGPRIV',
 33  'EJBCLIENT',
 34  'JMXSERVER',
 35  'JAVA_ADMIN',
 36  'CTXAPP',
 37  'XDBADMIN',
 38  'XDB_SET_INVOKER',
 39  'AUTHENTICATEDUSER',
 40  'XDB_WEBSERVICES',
 41  'XDB_WEBSERVICES_WITH_PUBLIC',
 42  'XDB_WEBSERVICES_OVER_HTTP',
 43  'OLAP_DBA',
 44  'ORDADMIN',
 45  'OLAP_XS_ADMIN',
 46  'CWM_USER',
 47  'OLAP_USER',
 48  'SPATIAL_WFS_ADMIN',
 49  'WFS_USR_ROLE',
 50  'SPATIAL_CSW_ADMIN',
 51  'CSW_USR_ROLE',
 52  'MGMT_USER',
 53  'APEX_ADMINISTRATOR_ROLE',
 54  'OWB$CLIENT',
 55  'OWB_DESIGNCENTER_VIEW',
 56  'XDBWEBSERVICES',
 57  'OLAPI_TRACE_USER',
 58  'PLUSTRACE',
 59  'OWB_USER'
 60* )
Re: SQL for generating roles [message #574917 is a reply to message #574915] Wed, 16 January 2013 13:52 Go to previous message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Which is valid ONLY for not authenticated roles and so NOT for secure application roles.
Better use DBMS_METADATA then you are sure to have no error and to follow the evolution of Oracle with the versions.

Regards
Michel

[Updated on: Wed, 16 January 2013 13:52]

Report message to a moderator

Previous Topic: Data Block Corruption even after repaired
Next Topic: Find current shared pool usage of my program
Goto Forum:
  


Current Time: Thu Mar 28 18:07:15 CDT 2024