SESSION_PRIVS: Privileges Enabled Right Now

This module has now covered granting a privilege, delegating the authority to grant it further, and querying the full data dictionary to see everything that has ever been granted to anyone. This lesson narrows the focus deliberately, to a single, practical question: not what has been granted in the database's entire history, but what is actually usable in this one session, right now, at this exact moment. That distinction turns out to matter a great deal more in practice than it might first appear.

SESSION_PRIVS lists the system privileges currently usable in your session. That phrasing is precise for a reason: this view answers "what can I actually do right now," which turns out to be a genuinely different question from "what have I been granted," and conflating the two is the single most common misunderstanding this lesson needs to clear up.

SESSION_PRIVS returns information about you specifically; it only makes sense to query it for the session you're actually running it from, since there is no GRANTEE column to filter by someone else:

SELECT * FROM session_privs;

The view has exactly one column, named PRIVILEGE. Older material sometimes shows this as PRIVILEGES, plural; that is not the actual column name. Its definition:

Column      Datatype       Null?     Description
----------- -------------- --------- ------------------------
PRIVILEGE   VARCHAR2(40)   NOT NULL  Name of the privilege

SESSION_PRIVS describes the system privileges currently available to the user, drawing from both direct grants and whatever roles happen to be enabled in the session at the moment the query runs. That second source, enabled roles, is exactly where this view earns its keep, and exactly where it can also mislead you if you don't understand it precisely.

Rather than an arbitrary list of privileges, it's worth working through Oracle's own official example for this exact view, since it demonstrates the role-enablement behavior far more precisely than a static list ever could. Assume the following setup already exists:

CREATE ROLE security_admin IDENTIFIED BY password;

GRANT CREATE PROFILE, ALTER PROFILE, DROP PROFILE,
    CREATE ROLE, DROP ANY ROLE, GRANT ANY ROLE, AUDIT ANY,
    AUDIT SYSTEM, CREATE USER, BECOME USER, ALTER USER, DROP USER
    TO security_admin WITH ADMIN OPTION;

GRANT security_admin, CREATE SESSION TO swilliams;

User swilliams now holds the security_admin role, carrying a substantial set of administrative privileges, plus a direct CREATE SESSION grant of her own.

First, confirm which roles are actually enabled right now with SESSION_ROLES, a companion view worth introducing alongside SESSION_PRIVS since the two are documented together and answer closely related questions:

SELECT * FROM session_roles;

If swilliams has security_admin enabled in this session, the result is:

ROLE
------------------------------
SECURITY_ADMIN

Now query SESSION_PRIVS in that same session:

SELECT * FROM session_privs;

PRIVILEGE
----------------------------------------
AUDIT SYSTEM
CREATE SESSION
CREATE USER
BECOME USER
ALTER USER
DROP USER
CREATE ROLE
DROP ANY ROLE
GRANT ANY ROLE
AUDIT ANY
CREATE PROFILE
ALTER PROFILE
DROP PROFILE

CREATE SESSION appears because it was granted directly. Everything else in this list traces back to the security_admin role being currently enabled, not to any direct grant on swilliams's own account.

This is the detail that makes SESSION_PRIVS genuinely different from a simple "what have I been granted" view, and it is worth stating precisely rather than approximately: if the security_admin role is disabled for swilliams, SESSION_ROLES returns no rows at all, and SESSION_PRIVS returns only a single row, CREATE SESSION. Every privilege that traced back to the role simply disappears from the result the moment the role itself is no longer enabled, even though swilliams still holds that role in the sense that DBA_ROLE_PRIVS and USER_ROLE_PRIVS would still show it granted to her. Nothing has been revoked; the privileges granted through the role are simply not currently active in this session.

This is precisely why SESSION_PRIVS is the right view for answering "can I do this right now" and the wrong view for answering "what have I ever been granted." A privilege can be completely real, fully granted, and entirely invisible to SESSION_PRIVS at the same time, if the role carrying it isn't currently switched on.

SESSION_PRIVS is scoped narrowly to system privileges, and it's worth knowing exactly where to look for everything it deliberately leaves out:

The distinction between object privileges and system privileges is worth restating plainly, since it trips people up constantly: holding CREATE TABLE in SESSION_PRIVS says nothing at all about whether you can SELECT from a specific table someone else owns. Object access is an entirely separate grant, tracked in an entirely separate set of views, covered in the previous lesson's tour of the full privilege dictionary.

ROLE_TAB_PRIVS in particular deserves a moment of its own attention, since its scope is easy to misjudge. It describes table privileges granted specifically to roles, not to users directly, and it reports information only about roles the current user actually has enabled, not every role in the database. This makes it a genuinely useful companion to SESSION_ROLES: once you know which roles are active in your session, ROLE_TAB_PRIVS is where you go to see exactly what object-level access those specific roles carry, rather than working backward from a table you're curious about and hoping to guess which role might be responsible for your access to it.

Current Oracle releases add SESSION_PRIVS_ALL alongside the classic SESSION_PRIVS, covering system privileges and schema-level privileges together in a single view:

SELECT privilege, schema FROM session_privs_all;

The SCHEMA column works the same way here as it does throughout the rest of this project's coverage of the newer _ALL views: it is null for an ordinary system privilege, and populated with a schema name for a schema-level grant currently active in the session. Querying SESSION_PRIVS_ALL instead of the classic SESSION_PRIVS is the right move the moment you need to know about schema-level access alongside system privileges in one pass, rather than checking two views separately and combining the results yourself.

There is one scenario worth understanding in real depth, because it produces exactly the kind of confusing failure this lesson exists to help you diagnose: a privilege shows up in SESSION_PRIVS for your interactive session, yet a stored procedure you run still fails with an insufficient-privileges error. This isn't a bug or an inconsistency; it's Oracle's role behavior working precisely as designed, once you know the rule.

How roles behave inside a PL/SQL block depends entirely on whether that block runs with definer's rights or invoker's rights:

• Named blocks with definer's rights, the default for stored procedures, functions, and triggers, disable every role entirely. Roles play no part in privilege checking inside such a block, and you cannot even issue a SET ROLE statement from within one. If a definer's-rights procedure itself queries SESSION_ROLES, the query returns no rows at all, regardless of what roles are enabled in the session that called it.
• Named blocks with invoker's rights, and anonymous PL/SQL blocks, behave the way you'd naturally expect: whatever roles are currently enabled in the calling session are used for privilege checking inside the block too, the same roles SESSION_PRIVS and SESSION_ROLES would already show you.

The practical consequence is significant: a definer's-rights procedure can only use privileges granted directly to its owner, never privileges the owner holds only through a role. If security_admin's procedures need CREATE USER, that privilege has to be granted to security_admin directly, not merely inherited through some other role, or any definer's-rights procedure security_admin writes that needs it will fail regardless of what SESSION_PRIVS shows for the interactive session. This is exactly the kind of gotcha worth checking first the moment a procedure fails with a privilege error despite the account clearly having the privilege interactively: check whether the failing code runs with definer's rights, and if so, check whether the privilege in question was actually granted directly rather than through a role.

The practical consequence is significant: a definer's-rights procedure can only use privileges granted directly to its owner, never privileges the owner holds only through a role. If security_admin's procedures need CREATE USER, that privilege has to be granted to security_admin directly, not merely inherited through some other role, or any definer's-rights procedure security_admin writes that needs it will fail regardless of what SESSION_PRIVS shows for the interactive session. This is exactly the kind of gotcha worth checking first the moment a procedure fails with a privilege error despite the account clearly having the privilege interactively: check whether the failing code runs with definer's rights, and if so, check whether the privilege in question was actually granted directly rather than through a role.

It's worth being precise about why Oracle designed it this way, since the restriction can otherwise feel arbitrary. Definer's rights procedures execute with the privileges of whoever created them, not whoever calls them, which is exactly what makes them useful for controlled access: an application user with very limited direct privileges can still run a definer's-rights procedure that performs privileged operations on their behalf, as long as the procedure's owner holds the necessary privileges. If role-based privileges were honored inside such a procedure, a user could potentially gain broader access simply by having a role enabled at the moment they happened to invoke the procedure, an unstable and hard-to-audit basis for a security boundary. Disabling roles inside definer's-rights code removes that instability entirely: the procedure's privilege set becomes fixed and predictable, determined solely by what its owner was granted directly, not by whatever happened to be enabled in whoever called it.

This also explains why anonymous PL/SQL blocks and invoker's-rights procedures behave differently, and why that difference is deliberate rather than an oversight. An anonymous block, the kind you type directly at a SQL*Plus prompt inside a BEGIN...END, executes as an extension of your own session; there is no separate owner to speak of, so using your own currently enabled roles is the only sensible behavior. An invoker's-rights procedure makes the same choice deliberately, running with the privileges of whoever calls it rather than whoever wrote it, which is exactly the situation where honoring the caller's enabled roles is the correct, intended behavior rather than a security gap.

SESSION_ROLES, already introduced above alongside SESSION_PRIVS, deserves its own brief treatment given how tightly the two are linked. Its single column, ROLE, lists every role currently enabled for the session, the same set of roles SESSION_PRIVS is effectively summarizing into individual privileges. A role can be simultaneously enabled for one user and disabled for another at the very same time, since enablement is a per-session state, not a property of the role itself. Querying SESSION_ROLES first, before SESSION_PRIVS, is often the faster diagnostic step: if a role you expect is missing from SESSION_ROLES entirely, there's no need to comb through SESSION_PRIVS looking for privileges that role would have carried, since none of them can possibly be present.

Suppose swilliams also holds a schema-level privilege, SELECT ANY TABLE ON SCHEMA hr, alongside everything already granted through security_admin. Querying the classic SESSION_PRIVS would show only her system privileges, with the schema-level grant nowhere in the result at all. SESSION_PRIVS_ALL closes that gap:

SELECT privilege, schema FROM session_privs_all;

PRIVILEGE                    SCHEMA
----------------------------- --------
AUDIT SYSTEM
CREATE SESSION
CREATE USER
BECOME USER
ALTER USER
DROP USER
SELECT ANY TABLE              HR

Every row with a blank SCHEMA value is an ordinary system privilege, active session-wide. The one row with HR in the SCHEMA column is the schema-level grant, active only within that specific schema. A single query now answers a question that used to require checking two separate views and mentally merging the results.

SESSION_PRIVS answers one question well: which system privileges can I exercise at this exact moment. It does not answer, and was never meant to answer, the broader question of everything you have ever been granted, and it says nothing whatsoever about object-level access to a specific table, view, or other object. If a script or an application suddenly fails with a privilege error despite you being certain the underlying grant exists, work through the investigation in a specific order rather than guessing: confirm what SESSION_ROLES shows as currently enabled, confirm what SESSION_PRIVS shows as currently active, then compare that against what USER_ROLE_PRIVS or DBA_ROLE_PRIVS shows as actually granted. A gap between the two almost always means one of two things: either a role exists but isn't currently switched on, or the failing code runs with definer's rights and needs a direct grant rather than a role-derived one.

Between SESSION_PRIVS for the classic system-privilege picture, SESSION_PRIVS_ALL for system and schema privileges together, and SESSION_ROLES for the roles underlying both, you have a precise, reliable way to answer exactly what a session, or a piece of code running inside it, can actually do right now. That's a genuinely different and often more immediately useful question than the fuller historical picture DBA_SYS_PRIVS and DBA_ROLE_PRIVS provide about everything that has ever been granted, and knowing which of the two questions you're actually asking is most of the battle the next time something fails for reasons that aren't immediately obvious.