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 |
|
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 #574915 is a reply to message #572903] |
Wed, 16 January 2013 13:49 |
|
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 |
|
Michel Cadot
Messages: 68729 Registered: March 2007 Location: Saint-Maur, France, https...
|
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
|
|
|
Goto Forum:
Current Time: Sun Jan 12 23:20:18 CST 2025
|