Revoking Privileges and Roles in Oracle AI Database 26ai

REVOKE removes a capability you previously granted, and it takes effect immediately, even against a user who is currently connected. Commands relying on the removed privilege start failing right away, not at the user's next login. That immediacy is exactly why revoking safely means understanding what you're about to do before you do it, not discovering the consequences afterward.

REVOKE CREATE USER, CREATE SESSION FROM jeff;
REVOKE RESOURCE FROM app_runtime;
REVOKE EXECUTE ANY PROCEDURE FROM reporting_svc;
REVOKE CONNECT FROM PUBLIC;

That last one deserves a pause before you ever run it for real: revoking from PUBLIC affects every user who relied on that public grant, not just the account you had in mind.

Revoking system privileges and roles:

REVOKE { system_privilege | role }
    [, { system_privilege | role } ]...
FROM { user | role | PUBLIC }
    [, { user | role | PUBLIC } ]...
[ CONTAINER = { CURRENT | ALL } ];

For comparison, the matching GRANT syntax:

GRANT { system_privilege | role }
    [, { system_privilege | role } ]...
TO { user | role | PUBLIC }
    [, { user | role | PUBLIC } ]...
[ WITH ADMIN OPTION ]
[ CONTAINER = { CURRENT | ALL } ];

Revoking blindly, based on memory of what you think was granted, is how safe cleanups turn into outages. Before touching a real account, check what is actually there across every category a privilege or role could belong to:

-- System privileges
SELECT privilege, admin_option, common, inherited
FROM   dba_sys_privs
WHERE  grantee = 'SCOTT';

-- Schema privileges
SELECT privilege, schema, admin_option, common
FROM   dba_schema_privs
WHERE  grantee = 'SCOTT';

-- Roles
SELECT granted_role, admin_option, default_role, common
FROM   dba_role_privs
WHERE  grantee = 'SCOTT';

-- Object privileges
SELECT owner, table_name, privilege, grantable, grantor
FROM   dba_tab_privs
WHERE  grantee = 'SCOTT';

-- Column privileges
SELECT owner, table_name, column_name, privilege, grantable
FROM   dba_col_privs
WHERE  grantee = 'SCOTT';

The COMMON and INHERITED columns matter here specifically because every 26ai database is a multitenant container database: a grant made with CONTAINER=ALL has to be revoked the same way, and querying these columns first tells you which approach the grant actually needs before you attempt anything.

Anyone holding ADMIN OPTION on a specific privilege or role, or holding the broader GRANT ANY PRIVILEGE or GRANT ANY ROLE, can revoke it, and critically, the revoker does not have to be the original grantor. One notable exception: a user cannot revoke a role from themselves.

Schema privileges follow a parallel but distinct authorization path: you can revoke one if you own the schema, hold that schema privilege with ADMIN OPTION, or hold GRANT ANY SCHEMA PRIVILEGE.

REVOKE SELECT ANY TABLE ON SCHEMA hr FROM scott;
REVOKE INSERT ANY TABLE ON SCHEMA hr FROM app_role;

Object privileges work differently again. You can revoke an object privilege you personally granted, or revoke on the owner's behalf if you hold GRANT ANY OBJECT PRIVILEGE. What you cannot do is revoke a grant someone else made using their own WITH GRANT OPTION directly from the person they granted it to; instead, you revoke from the original grantor, and the revoke cascades down from there automatically.

REVOKE SELECT, INSERT ON hr.employees FROM scott;
REVOKE ALL ON hr.employees FROM scott;
REVOKE SELECT ON hr.employees FROM PUBLIC;

This is where "safe" genuinely earns its keep. Cascade behavior differs meaningfully by what you're revoking, and assuming the wrong direction is the single most common way a revoke produces a surprise:

The REFERENCES case is worth a concrete example, since dropping constraints is exactly the kind of side effect worth confirming before it happens rather than after:

REVOKE REFERENCES ON hr.employees FROM oe CASCADE CONSTRAINTS;

This drops any foreign-key constraints oe created using that REFERENCES privilege. Check what those constraints actually are first:

SELECT owner, constraint_name, table_name, r_owner, r_constraint_name
FROM   dba_constraints
WHERE  constraint_type = 'R'
AND    r_owner = 'HR'
AND    r_constraint_name IN (
         SELECT constraint_name FROM dba_constraints
         WHERE owner = 'HR' AND table_name = 'EMPLOYEES'
       );

Beyond users and roles, current Oracle releases also support code-based access control: granting or revoking a role directly on a specific package, procedure, or function, rather than on the user who owns it. This lets a role apply only while that specific program unit is executing, distinct from the privileges the owning user holds everywhere else:

REVOKE clerk_admin FROM PACKAGE psmith.checkstats_pkg;
REVOKE ALL FROM FUNCTION hr.func2;

A related, more specialized mechanism worth knowing about: INHERIT PRIVILEGES controls whether an invoker's-rights procedure owned by one user can execute using another user's privileges when that second user calls it. Revoking this specifically closes that inheritance path without touching either user's own direct privileges:

REVOKE INHERIT PRIVILEGES ON USER jward FROM ebrown;
REVOKE INHERIT PRIVILEGES ON USER jward FROM PUBLIC;

1. Do not revoke from Oracle-maintained accounts such as SYS, SYSTEM, AUDSYS, or SYSRAC, the account specifically used to manage Oracle Real Application Clusters, or from Oracle-supplied roles, unless Oracle Support has specifically directed you to.
2. Match common and local scope exactly. If COMMON shows YES in the relevant DBA_*_PRIVS view, revoke it in the root with CONTAINER=ALL; a common grant genuinely cannot be revoked locally, confirmed directly in Oracle's own syntax reference.
3. You cannot revoke only ADMIN OPTION or only GRANT OPTION on their own. Revoke the privilege entirely, then grant it again without the option:

   REVOKE CREATE USER FROM security_admin;
   GRANT  CREATE USER TO  security_admin;
   
   REVOKE SELECT ON hr.employees FROM app_owner;
   GRANT  SELECT ON hr.employees TO  app_owner;

   There is no REVOKE ADMIN OPTION clause; this two-step sequence is the only supported way.
4. Clean up default roles after revoking a role. Removing a role from a user doesn't automatically update which roles activate by default at connection:

   ALTER USER scott DEFAULT ROLE ALL EXCEPT accts_rec;
   -- or
   ALTER USER scott DEFAULT ROLE NONE;

5. Test in a non-production clone first. Revokes are immediately disruptive by nature; generate the revoke script, run it in test, and keep a matching regrant script on hand in case you need to reverse it.
6. Watch definer's-rights objects specifically. Revoke a privilege from a view or procedure's owner, and that object can go invalid, cutting off everyone who uses it, even users who still hold a perfectly valid grant on the object itself.
7. Prefer revoking a role over peeling privileges off individual users. One role revocation is far easier to reason about, and to reverse, than tracking down every user who happened to receive a privilege directly.

Since every Oracle AI Database 26ai database is a multitenant container database, scope matching isn't optional: a local grant is revoked in that specific PDB with CONTAINER=CURRENT, the default, and a common grant is revoked from the root with CONTAINER=ALL. A single common user can genuinely hold a mix of both, a common role alongside local object grants made separately in one PDB, so check COMMON and INHERITED before assuming either scope applies uniformly.

REVOKE CREATE SESSION FROM c##app CONTAINER = ALL;      -- common
REVOKE SELECT ON hr.emp FROM c##app CONTAINER = CURRENT; -- local

SELECT privilege FROM dba_sys_privs WHERE grantee = 'SCOTT';
SELECT granted_role FROM dba_role_privs WHERE grantee = 'SCOTT';
SELECT privilege, schema FROM dba_schema_privs WHERE grantee = 'SCOTT';
SELECT owner, table_name, privilege FROM dba_tab_privs WHERE grantee = 'SCOTT';

Have the affected account reconnect, or re-enable roles explicitly, and rerun whatever critical path you're concerned about. An existing session keeps whatever roles were already enabled until it re-enables roles or reconnects entirely; the revoke is real and immediate at the data dictionary level, but a session's currently active privilege set, covered in the previous lesson on SESSION_PRIVS, doesn't necessarily refresh until that session does something to trigger it.

Revoking everything a user holds does not remove the account itself. To remove the account and decide what happens to whatever it owns, DROP USER is the right tool, covered in depth earlier in this course:

DROP USER username CASCADE;

Without CASCADE, the drop fails outright if the user still owns any objects. With CASCADE, Oracle drops the user along with their objects and any dependent foreign-key constraints elsewhere, while views and procedures elsewhere that merely reference those objects become invalid rather than being dropped themselves, exactly the CASCADE behavior covered in full detail in this course's user-management module.

• Prefer roles over direct privileges wherever practical; grant a role, and revoke by removing the role.
• Apply least privilege consistently, and audit periodically through DBA_TAB_PRIVS, DBA_SYS_PRIVS, DBA_SCHEMA_PRIVS, and DBA_ROLE_PRIVS rather than relying on memory of what was granted months ago.
• Stage disruptive changes in non-production first.
• Audit the use of genuinely powerful privileges through unified auditing, the only auditing mechanism current Oracle releases support for new configuration, particularly for privileges like ALTER SYSTEM and DROP ANY TABLE.