Querying the Data Dictionary for Privileges in Oracle AI Database 26ai

Granting a privilege is only half the job. The other half is being able to answer, with certainty rather than a guess, exactly what a given user or role can actually do right now. Oracle's data dictionary holds that answer in a family of views built around a consistent naming pattern, and this lesson works through that family from the classic core outward to what current releases have added around it.

A basic query against DBA_SYS_PRIVS retrieves the system privileges granted directly to a user or role:

SELECT grantee, privilege, admin_option
FROM   dba_sys_privs
WHERE  grantee = 'USERNAME';

GRANTEE is the user or role holding the privilege. PRIVILEGE is the specific system privilege granted. ADMIN_OPTION indicates whether that grantee can pass the privilege along to others, YES if so, NO if not. Adjust the WHERE clause for a specific user or role, or drop it entirely to see every system privilege granted in the database at once.

The view's actual column definitions are worth knowing precisely rather than assuming from an old reference, since one detail has genuinely changed over the years:

Name         Null?    Type
-----------------------------
GRANTEE      NOT NULL VARCHAR2(128)
PRIVILEGE    NOT NULL VARCHAR2(40)
ADMIN_OPTION          VARCHAR2(3)

GRANTEE is 128 characters wide, not the 30 characters that older Oracle material sometimes shows. This reflects Oracle's identifier length extension from 30 to 128 bytes, introduced some years ago now and fully current in Oracle AI Database 26ai; usernames and role names can run considerably longer than they once could, and this column has to be wide enough to hold them. PRIVILEGE stays at 40 characters and ADMIN_OPTION at 3, both unchanged, since privilege names come from Oracle's own fixed vocabulary rather than anything a user names themselves.

Current releases add two further columns beyond the original three, both tied directly to the fact that every Oracle AI Database 26ai database is a multitenant container database:

• COMMON indicates whether the privilege was granted commonly, using CONTAINER=ALL, or granted locally within a single PDB.
• INHERITED indicates whether the grant was inherited from another container rather than made directly in this one.

Neither question has an obvious answer without checking these columns specifically, and in an environment managing several PDBs, that distinction matters considerably.

Querying for a specific privilege shows exactly who has it and who can grant it onward:

SQL> SELECT * FROM dba_sys_privs
  2  WHERE privilege = 'CREATE TABLESPACE';

GRANTEE              PRIVILEGE            ADM
-------------------- -------------------- ---
CTXSYS               CREATE TABLESPACE    NO
DBA                  CREATE TABLESPACE    YES
IMP_FULL_DATABASE    CREATE TABLESPACE    NO
JEFF                 CREATE TABLESPACE    YES
JENNY                CREATE TABLESPACE    YES
ASHLEY               CREATE TABLESPACE    NO

7 rows selected.

DBA, JEFF, and JENNY hold CREATE TABLESPACE with the admin option and can grant it to others; the remaining grantees hold the privilege itself but cannot pass it along. If these names look familiar, they should: Jeff, Jenny, and Ashley are the same three users from the WITH ADMIN OPTION diagram in the previous lesson, though this particular query happens to be checking a different privilege than the one granted there. A query like this one is exactly how you would confirm, in your own database, whether a delegation chain like the one covered in that lesson actually produced the admin-option pattern you intended.

DBA_SYS_PRIVS is the starting point, not the whole picture. Current Oracle releases organize privilege visibility along two independent dimensions worth understanding together: whose privileges you're looking at, and what kind of privilege you're looking for.

The first dimension has three levels. USER_ views are automatically scoped to whoever is running the query and need no special access at all. DBA_ views show every grantee in the database but require catalog-level access, SELECT ANY DICTIONARY, SELECT_CATALOG_ROLE, or SYSDBA. SESSION_ views show something different again: not everything a user has been granted, but specifically what is usable right now, in the current session, after accounting for which roles happen to be enabled at this moment.

The second dimension is the kind of privilege: classic system privileges, the newer schema-level privileges, object privileges, roles, and column-level grants each have their own dedicated views. Laid out together, the pattern becomes easy to hold in memory:

The combined system-plus-schema views deserve a direct callout: DBA_SYS_PRIVS_ALL and its USER_ and SESSION_ siblings are confirmed, explicitly, as new to Oracle AI Database 26ai, not a rebranding of something older. Oracle's own reference states plainly that this view "is available starting with Oracle AI Database 26ai." Before this release, checking a user's complete privilege picture, system privileges and schema-level privileges together, meant querying two separate view families and combining the results yourself; the _ALL views do that combination for you in one query.

A representative query against the combined view, checking everything SCOTT holds at once:

SELECT grantee, privilege, schema, admin_option
FROM   dba_sys_privs_all
WHERE  grantee = 'SCOTT';

The SCHEMA column is the key to reading this output correctly: it is NULL for an ordinary system privilege, since those apply database-wide rather than to one schema, and populated with a schema name for a schema-level grant such as SELECT ANY TABLE ON SCHEMA HR. A single result set now shows both kinds of privilege side by side, distinguished by whether SCHEMA is empty or not.

The COMMON and INHERITED columns answer two related but distinct questions, and seeing them side by side helps keep the distinction straight. Consider a common user granted a privilege in the CDB root, then examined from within one specific PDB:

SELECT grantee, privilege, common, inherited
FROM   dba_sys_privs
WHERE  grantee = 'C##ADMIN';

GRANTEE      PRIVILEGE          COMMON  INHERITED
------------ ------------------ ------- ---------
C##ADMIN     CREATE SESSION     YES     YES

COMMON answers where the grant originated: YES here means it was made with CONTAINER=ALL, applying across the entire container rather than one PDB specifically. INHERITED answers something different: whether the row you are looking at, in this specific PDB, reflects a grant made directly here or one flowing down from the root. A grant can be common without being inherited, if you happen to be querying from the root itself where the grant actually originated, and understanding which container you were connected to when you ran the query is just as important as the query's results.

USER_SYS_PRIVS and DBA_SYS_PRIVS both show what has been granted, which is not always the same as what is currently usable. A privilege granted to a user through a role is only active while that role is enabled in the current session; SESSION_PRIVS answers the narrower, often more practical question of what a session can actually do at this exact moment:

SELECT privilege FROM session_privs ORDER BY privilege;

-- Including schema privileges
SELECT privilege, schema FROM session_privs_all ORDER BY privilege, schema;

SESSION_ROLES answers the equivalent question for roles: which roles are enabled right now, not which roles have ever been granted.

A worked example makes the SCHEMA column easier to reason about than the description alone. Suppose bob has been granted SELECT ANY TABLE ON SCHEMA hr, a schema-level privilege, alongside an ordinary CREATE SESSION system privilege:

SELECT grantee, privilege, schema, admin_option
FROM   dba_sys_privs_all
WHERE  grantee = 'BOB';

GRANTEE    PRIVILEGE           SCHEMA    ADMIN_OPTION
---------- ------------------- --------- ------------
BOB        CREATE SESSION                NO
BOB        SELECT ANY TABLE    HR        NO

Both rows describe genuinely different things wearing similar-looking names: the first is a database-wide capability with no schema attached at all, and the second is scoped entirely to HR. Querying DBA_SYS_PRIVS alone, the pre-26ai view, would have shown only the first row; the schema-level grant would have been invisible without a separate query against DBA_SCHEMA_PRIVS. The combined view exists specifically to close that gap.

A common, genuinely confusing situation: a privilege query shows a user holds exactly the privilege they need, yet the operation still fails. This is almost always a gap between what has been granted and what is currently enabled, and it is worth working through the views in a specific order rather than guessing.

Start with DBA_SYS_PRIVS or DBA_ROLE_PRIVS to confirm the privilege genuinely reaches the user at all, whether directly or through a role:

SELECT granted_role, admin_option, default_role
FROM   dba_role_privs
WHERE  grantee = 'APP_USER';

The DEFAULT_ROLE column is exactly where this kind of mystery often gets solved. DEFAULT_ROLE set to YES means the role activates automatically the moment the user connects, with nothing further required. DEFAULT_ROLE set to NO means the opposite: the user genuinely holds the role, but it sits inactive until something in the session explicitly enables it with SET ROLE. A privilege granted only through a non-default role is real, and DBA_SYS_PRIVS or DBA_ROLE_PRIVS will show it, but it does the user no good at all until that role is turned on.

This is exactly the gap SESSION_PRIVS and SESSION_ROLES are built to expose. If a privilege appears in DBA_ROLE_PRIVS but the corresponding role is missing from SESSION_ROLES for that connected session, the mystery is solved: the privilege exists, but nothing has switched it on for this particular connection. The fix is either enabling the role explicitly within the session, or reconsidering whether that role should have been a default role in the first place.

Object privileges, access to a specific table, view, or other object rather than a database-wide capability, live in a parallel set of views: DBA_TAB_PRIVS for any user, USER_TAB_PRIVS scoped to the current user. Column-level grants, narrower still, have their own views:

SELECT * FROM user_col_privs;
SELECT * FROM dba_col_privs WHERE grantee = 'SCOTT';

Two further views worth knowing about: ALL_TAB_PRIVS and ALL_COL_PRIVS show whatever object and column grants the current user can actually see, meaning objects they own themselves or objects someone else has granted them access to, a genuinely different scope than either the USER_ or DBA_ prefix implies.

With this many related views, it helps to know how to search for one rather than memorize the entire family. The DICTIONARY view itself lists every dictionary view in the database along with a short description, and can be searched directly:

SELECT table_name, comments
FROM   dictionary
WHERE  table_name LIKE '%PRIV%'
   OR  comments   LIKE '%privilege%'
ORDER BY table_name;

This is worth running the first time you need a privilege-related view whose exact name you're not certain of, rather than guessing at a name and hoping it exists.

A role confers a whole group of system, object, and other role privileges on whoever holds it. Users granted a role inherit everything granted to that role in turn, and roles can optionally be password-protected, meaning a user might hold a role without being able to use it in every session unless they authenticate for it specifically.

A role exists purely to administer privileges as a group; it serves no other purpose. Privileges are granted to the role, and the role itself is then granted to other roles or directly to users, who inherit everything the role carries. Creating one is straightforward:

CREATE ROLE appl_dba;

Optionally, a role can require authentication before it can be enabled, using the IDENTIFIED BY clause at creation time. This pattern shows up most often inside applications, where the application itself controls exactly when a user's role becomes active rather than leaving it active for the entire session by default:

SET ROLE appl_dba IDENTIFIED BY seekwrit;

DBA_ROLE_PRIVS lists every role privilege granted in the database, the direct counterpart to DBA_SYS_PRIVS for roles rather than system privileges, and follows the same USER_/DBA_/SESSION_ pattern as everything else covered in this lesson. Its DEFAULT_ROLE column, already covered above in the troubleshooting section, is worth remembering as one of the more consequential single columns in this entire family of views: it is frequently the actual answer to "why doesn't this privilege work," long before the underlying grant itself is ever in question.

Worth deciding deliberately when creating a role, rather than leaving to Oracle's default behavior: whether it should be a default role at all. A role granted with DEFAULT ROLE behavior activates automatically at connection, which is convenient but means every privilege it carries is live for the entire session without exception. A role that requires SET ROLE explicitly, particularly one requiring authentication via IDENTIFIED BY, forces a deliberate, visible activation step, which is exactly the kind of friction worth keeping for a role carrying genuinely sensitive privileges, even though it makes everyday use marginally less convenient.

Between DBA_SYS_PRIVS for the classic system privilege picture, the newer _ALL views for system and schema privileges combined, the schema-specific views for 26ai's schema-level grants, and the object, column, and role views rounding out the rest, you now have a complete toolkit for answering exactly what any user or role in your database can actually do, rather than relying on memory of what you think you granted.

DBA_SYS_PRIVS remains the starting point for any question about who holds a system privilege, with GRANTEE now wide enough to hold current, longer identifier names, and with COMMON and INHERITED reflecting the reality that every query runs against one container in a multitenant database, not the database as a whole. The newer _ALL views, confirmed as genuinely new in Oracle AI Database 26ai, close a real gap by showing system and schema privileges together rather than requiring two separate queries and a manual comparison. USER_ views answer what you yourself hold; DBA_ views answer the same question for anyone, at the cost of needing catalog access; SESSION_ views answer the narrower, often more practically useful question of what is actually active right now. Object privileges, column-level grants, and role assignments each follow this same pattern in their own dedicated views. Taken together, this family of views turns "I think this user can do that" into something you can actually confirm with a single, precise query, which is exactly the habit worth building before troubleshooting turns into guesswork.

Click the Exercise link below to practice querying the DBA_SYS_PRIVS view.
DBA System Privileges - Exercise