How to list all the roles existing in Oracle database?

How to List All Roles in an Oracle Database

Discover the various methods to identify and inspect all roles defined within your Oracle database, including system and custom roles, using SQL queries and data dictionary views.

Understanding the roles configured in an Oracle database is crucial for security auditing, privilege management, and troubleshooting. Roles are named collections of privileges that can be granted to users or other roles, simplifying the management of permissions. This article will guide you through different SQL queries to list all available roles, distinguish between system-defined and user-defined roles, and examine their granted privileges.

Listing All Roles in the Database

Oracle stores metadata about all database objects, including roles, in its data dictionary. The DBA_ROLES view is the primary source for retrieving information about all roles existing in the database. This view is accessible to users with appropriate privileges, typically database administrators.

SELECT role,
       password_required
FROM   dba_roles
ORDER BY role;

Query to list all roles and their password requirement status.

Understanding Role Types: System vs. User-Defined

Oracle databases come with a set of predefined system roles (e.g., CONNECT, RESOURCE, DBA, EXP_FULL_DATABASE, IMP_FULL_DATABASE) that provide common sets of privileges. Users can also create their own custom roles to group specific privileges relevant to their applications or organizational structure. While DBA_ROLES lists all of them, you can often infer system roles by their common names or by checking if they are created by SYS.

flowchart TD
    A[Start: Query DBA_ROLES] --> B{Role Name Starts with 'C', 'R', 'DBA', etc.?}
    B -- Yes --> C[System-Defined Role]
    B -- No --> D[User-Defined Role]
    C --> E[End]
    D --> E[End]

Decision flow for identifying system-defined vs. user-defined roles.

Inspecting Privileges Granted to Roles

Once you have a list of roles, you might want to see what privileges each role grants. Oracle provides several data dictionary views for this purpose:

• DBA_ROLE_PRIVS: Shows which roles are granted to other roles or users.
• ROLE_SYS_PRIVS: Lists system privileges granted to roles.
• ROLE_TAB_PRIVS: Lists table privileges (object privileges) granted to roles.

SELECT grantee,
       granted_role,
       admin_option,
       delegate_option
FROM   dba_role_privs
WHERE  grantee = 'YOUR_ROLE_NAME';

SELECT role,
       privilege,
       admin_option
FROM   role_sys_privs
WHERE  role = 'YOUR_ROLE_NAME';

SELECT role,
       owner,
       table_name,
       privilege
FROM   role_tab_privs
WHERE  role = 'YOUR_ROLE_NAME';

Queries to inspect roles granted to a role, system privileges, and object privileges granted to a specific role.