dba_role_privs: List Oracle Roles Granted to a User

calendar Jan 4, 2024 / · 10 min read · Privileges User dba_role_privs user_role_privs role_role_privs role_sys_privs session_roles  ·
Share on: linkedin copy

dba_role_privs: List Oracle Roles Granted to a User

Purpose

Which roles does an Oracle user hold, and can that user pass them on to others? The view DBA_ROLE_PRIVS answers both questions. It describes the roles granted to all users and roles in the database. One row is one grant. The query below filters that view for one account and returns the role name and the ADMIN OPTION flag for each grant.

This is the first check in most access reviews. An auditor asks what a user can do. A role is the usual answer, because privileges are normally grouped into roles and the roles are then granted to users. Before you can read the privileges, you need the list of roles. This query gives you that list.

You can also use it when a user reports a permission problem. If a role is missing from the list, the grant is missing. If the role is present, the problem is somewhere else, such as a role that is not enabled in the session. The sections below cover that case as well.

Code

1select grantee
2, granted_role
3, admin_option
4from dba_role_privs
5where grantee like upper('&username')
6/

Code Breakdown

The columns

  • grantee is the name of the user or role that receives the grant. The column holds roles as well as users, because a role can be granted to another role.
  • granted_role is the name of the granted role.
  • admin_option shows whether the grant was made with the ADMIN OPTION. The value is YES or NO.

The view

  • dba_role_privs describes the roles granted to all users and roles in the database. A related view, USER_ROLE_PRIVS, describes the roles granted to the current user. Use the DBA view when you review another account. Use the USER view when you only need your own list.

The filter

  • where grantee like upper('&username') limits the rows to one account. Replace &username with the account name when your client asks for it.
  • upper() is there because Oracle interprets nonquoted identifiers as uppercase. A user created as scott is stored as SCOTT, so the filter must be uppercase to match it.
  • like lets you add a wildcard. A value such as APP% returns the grants of every account whose name starts with APP. A value with no wildcard behaves like an equals test.
  • The closing / ends the statement for a client that reads a script file.

The query above is the base. These views answer the next questions. Each one is described on its reference page.

 1-- Roles granted to the current user only
 2select username, granted_role, admin_option, default_role
 3from user_role_privs;
 4
 5-- Roles that are granted to other roles
 6select role, granted_role, admin_option
 7from role_role_privs
 8where role like upper('&role_name');
 9
10-- System privileges granted to a role
11select role, privilege, admin_option
12from role_sys_privs
13where role like upper('&role_name');
14
15-- Roles that are enabled right now in this session
16select role
17from session_roles;

ROLE_ROLE_PRIVS describes the roles granted to other roles. ROLE_SYS_PRIVS describes system privileges granted to roles. Both views give information only about roles to which the current user has access. SESSION_ROLES describes the roles that are currently enabled to the user.

Key Points

  • The view lists direct grants. DBA_ROLE_PRIVS lists roles directly granted to users and roles. If a user holds role A, and role A holds role B, the row for B has role A as its grantee, not the user. To see B for that user, follow the chain with ROLE_ROLE_PRIVS.
  • ADMIN_OPTION is a power to pass the role on. The column shows whether the grant was made with the ADMIN OPTION (YES) or not (NO). A grantee with the ADMIN OPTION can grant the role to another user or role, unless the role is a GLOBAL role. The grantee can also revoke the role from another user or role.
  • DEFAULT_ROLE tells you if the role starts active. The column shows whether the role is designated as a DEFAULT ROLE for the user. When a user logs on, the database enables all privileges granted explicitly to the user and all privileges in the user's default roles. A role that is not a default role stays off until the user enables it.
  • Multitenant adds columns. DBA_ROLE_PRIVS also has COMMON and INHERITED columns. COMMON is YES if the role was granted commonly (CONTAINER=ALL was used). INHERITED shows whether the role grant was inherited from another container.
  • Case matters for quoted names. Quoted identifiers are case sensitive. If an account was created with a quoted lowercase name, upper() will not match it. Remove upper() and use the exact name in that case.

Insights and Best Practices

Read the ADMIN OPTION column first

In an access review, the YES rows deserve the most attention. A grantee with the ADMIN OPTION on a role can give that role to others. That is a way to spread access without the security team knowing. Filter on admin_option = 'YES' to get that list on its own.

To remove the ADMIN OPTION, you cannot simply change one flag. To revoke the ADMIN OPTION on a system privilege or role from a user, you must revoke the privilege or role from the user altogether and then grant it to the user without the ADMIN OPTION. Plan the change so the user does not lose access at a bad time.

Check the default role setting when a user says a role does not work

A grant that exists is not always a role that is active. If you grant a role to a user, then the database makes the role available to the user. The user can immediately enable the role and exercise the privileges in the privilege domain of the role. The SET ROLE statement is the tool for that. A user or an application can use it any number of times to enable or disable the roles currently enabled for the session. SET ROLE NONE disables all roles for the current session, including the default role.

The support steps follow from this. First, query DBA_ROLE_PRIVS and check that the grant exists. Second, read DEFAULT_ROLE. Third, ask the user to run the SESSION_ROLES query and compare. If the grant is there but the role is not in SESSION_ROLES, the cause is the role setting in that session, not a missing grant.

Know when role grants are not made with GRANT

Some operating systems let you grant roles to Oracle Database users with the initialization parameter OS_ROLES. If you choose to grant roles to users through operating system facilities, then you cannot also grant roles to users with the GRANT statement. USER_ROLE_PRIVS has an OS_GRANTED column for this case. It shows whether the role was granted by the operating system (YES) or not (NO). It occurs if the OS_ROLES initialization parameter is true. If your site uses this setting, a list built only from GRANT history will be incomplete.

Follow the chain for a full picture

A role is only a container. The privileges are inside it. A complete review runs in this order:

  1. Run the base query to get the roles for the user.
  2. Run ROLE_ROLE_PRIVS for each role to find nested roles.
  3. Run ROLE_SYS_PRIVS for each role to see its system privileges.

DBA_SYS_PRIVS describes system privileges granted to users and roles, so it also shows privileges granted to the user directly, outside any role. Check it too. A user can hold a strong privilege with no role involved.

Separate Oracle-supplied roles from your own

DBA_ROLES describes all roles in the database. Its ORACLE_MAINTAINED column denotes whether the role was created, and is maintained, by Oracle-supplied scripts. A role with the value Y must not be changed in any way except by running an Oracle-supplied script. When you build a report for managers, join your grants to DBA_ROLES and filter on this column. The result separates roles your team owns from roles that ship with the product.

Review on a schedule

Run the query for privileged accounts on a fixed schedule, such as every quarter, and save the output. A saved list lets you show what changed between two reviews. Keep the list of YES rows for the ADMIN OPTION in the same file.

Multitenant note

In a container database, a role can be common or local. The name of a common role must be prefixed with C## or c##. The name of a local role must not carry that prefix. When a common role is granted to a local user, the privileges are limited to that specific user in that specific PDB. Check the COMMON column when you read grants in a PDB, so you know how each grant was made.

When to Use This Check

  • During a periodic access review or a security audit.
  • When a user asks why they can or cannot run a statement.
  • Before you drop a role, to see which accounts hold it.
  • Before you lock or drop a user, to record what that user could do.
  • After a staff change, to confirm that a new account has only the roles it needs.
  • When you hunt for accounts that can grant roles to others.
  • After a migration, to compare role grants on the old and new database.

Troubleshooting Common Issues

The query returns no rows for a user that exists. Check the name case. Nonquoted identifiers are not case sensitive and Oracle interprets them as uppercase. Quoted identifiers are case sensitive. If the account was created with a quoted mixed-case name, remove upper() and type the exact name. Also check the spelling of the wildcard pattern.

You see a role name but cannot find its privileges. The role may be granted to another role. Query ROLE_ROLE_PRIVS with that role as the role value and follow the chain. Remember that ROLE_ROLE_PRIVS and ROLE_SYS_PRIVS give information only about roles to which you have access.

The grant exists but the user still gets a privilege error. Look at DEFAULT_ROLE and at SESSION_ROLES from the user's own session. If the role is not a default role, the user must enable it with SET ROLE. If the user ran SET ROLE with a short list of roles, all roles not specified are disabled for the current session or until another SET ROLE statement is issued.

The query is refused. If your account cannot read DBA_ROLE_PRIVS, ask a DBA for access. As a fallback, use USER_ROLE_PRIVS, which describes the roles granted to the current user.

You cannot revoke the ADMIN OPTION alone. As noted above, the role must be revoked and then granted again without the ADMIN OPTION. Record the old grant before you run the revoke.

Related reading on this site. To list the accounts themselves, with status and tablespaces, see Oracle dba_users: List All User Account Information.

References

Posts in this series