Oracle data dictionary, tell built-in roles apart from custom roles

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)?

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.

StackExchange Database Administrators Q#108883, answer score: 4