6

In Oracle, if you describe the DBA_ROLES view, you only get three columns:

SQL> describe dba_roles;
 Name                      Null     Type
------------------------------------------------
 ROLE                      NOT NULL VARCHAR2(30)
 PASSWORD_REQUIRED                  VARCHAR2(8)
 AUTHENTICATION_TYPE                VARCHAR2(11)

There is no column that could tell me whether or not a given role is one of the built-in roles the RDBMS comes with, like SET_CATALOG_ROLE or a user-created role like LEMONADE_STAND_MANAGER.

  • How can I get a list of all user-created roles (excluding all built-in roles)?
4

A list of predefined roles in Oracle 12c can be found in the Security Guide. Also in Oracle 12c the view dba_roles has a column ORACLE_MAINTAINED. A 'Y' in this column tells you that this is a role created by the Oracle installation scripts. For Oracle versions before 12c this column does not exist. So for Oracle version lower than 12c you have to check the manuals or search the internet for lists of Oracle predefined roles.

Note

There is a dictionary table SYS.USER$ that contains all roles and users. This view is not visible to non dba users.

select user#,type#,name from user$ order by user#;

lists all users and roles ordered by their internal Iduser#. If typeis 0 the item is a role. Most of the time these IDs will be created in a sequential manner, so that roles and user created during installation have a low Id. But I found 12c databases with a lot of Oracle created roles with a high Id. Also if you upgrade a database then there will be roles created by Oracle scripts that have a higher Id than user created roles. So the user# is not a reliable criterion.

1

See installed roles here http://www.morganslibrary.org/reference/roles.html

Create a table with the installed roles. Then select from dba_roles where not exists from the table you created.

-1

Unlike schema objects, roles aren't contained in any schema objects. Therefore, there is no "role owner". As an example, you can create a role (given the user has create role privilege), alongside with example tables, indexes... If you then drop the user, nothing will happen to the role, and only schema objects will be dropped.

Reference: http://docs.oracle.com/cd/B28359_01/network.111/b28531/authorization.htm#BABCADIB

  • Is there a way to tell the built-in roles (the ones that come with the RDBMS) from any other role? – Tulains Córdova Aug 3 '15 at 19:17
  • there is a note on Metalink describing list of all Oracle's build-in usernames and roles. – ibre5041 Aug 3 '15 at 20:03
  • 1
    I don't see how this post answers the question of the OP. – miracle173 Aug 4 '15 at 3:49
-1

The following built in roles install with Oracle 12c when a shell database is created:

SELECT ROLE FROM dba_roles 
WHERE ROLE IN ('ADM_PARALLEL_EXECUTE_TASK','APPLICATION_TRACE_VIEWER','AQ_ADMINISTRATOR_ROLE','AQ_USER_ROLE','AUDIT_ADMIN','AUDIT_VIEWER','AUTHENTICATEDUSER','CAPTURE_ADMIN','CDB_DBA','CONNECT','
CSW_USR_ROLE','CTXAPP','DATAPATCH_ROLE','DATAPUMP_EXP_FULL_DATABASE','DATAPUMP_IMP_FULL_DATABASE','DBA','DBFS_ROLE','DBJAVASCRIPT','DBMS_MDX_INTERNAL','
DV_ACCTMGR','DV_ADMIN','DV_AUDIT_CLEANUP','DV_DATAPUMP_NETWORK_LINK','DV_GOLDENGATE_ADMIN','DV_GOLDENGATE_REDO_ACCESS','DV_MONITOR','DV_OWNER','DV_PATCH_ADMIN','
DV_POLICY_OWNER','DV_PUBLIC','DV_REALM_OWNER','DV_REALM_RESOURCE','DV_SECANALYST','DV_STREAMS_ADMIN','DV_XSTREAM_ADMIN','EJBCLIENT','EM_EXPRESS_ALL','EM_EXPRESS_BASIC','EXECUTE_CATALOG_ROLE','
EXP_FULL_DATABASE','GATHER_SYSTEM_STATISTICS','GDS_CATALOG_SELECT','GGSYS_ROLE','GLOBAL_AQ_USER_ROLE','GSMADMIN_ROLE','GSMUSER_ROLE','GSM_POOLADMIN_ROLE','HS_ADMIN_EXECUTE_ROLE','
HS_ADMIN_ROLE','HS_ADMIN_SELECT_ROLE','IMP_FULL_DATABASE','JAVADEBUGPRIV','JAVAIDPRIV','JAVASYSPRIV','JAVAUSERPRIV','JAVA_ADMIN','JAVA_DEPLOY','JMXSERVER','LBAC_DBA','LOGSTDBY_ADMINISTRATOR','
OEM_ADVISOR','OEM_MONITOR','OLAP_DBA','OLAP_USER','OLAP_XS_ADMIN','OPTIMIZER_PROCESSING_RATE','ORDADMIN','PDB_DBA','PROVISIONER','RDFCTX_ADMIN','RECOVERY_CATALOG_OWNER','RECOVERY_CATALOG_OWNER_VPD','
RECOVERY_CATALOG_USER','RESOURCE','SCHEDULER_ADMIN','SELECT_CATALOG_ROLE','SODA_APP','SPATIAL_CSW_ADMIN','SYSUMF_ROLE','WM_ADMIN_ROLE','XDBADMIN','XDB_SET_INVOKER','XDB_WEBSERVICES','
XDB_WEBSERVICES_OVER_HTTP','XDB_WEBSERVICES_WITH_PUBLIC','XS_CACHE_ADMIN','XS_CONNECT','XS_NAMESPACE_ADMIN','XS_SESSION_ADMIN')
  • 1
    Welcome on the DBA SE! I tried to format your post, but probably it won't be enough. Please, try using round sentences to answer the question on the top. – peterh says reinstate Monica Apr 4 at 17:44
  • Looks like SELECT is a bit redundant, since you already have the list of rows. – mustaccio Apr 4 at 17:54

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.