Revoking System Privileges in Oracle

In Oracle, you remove a user’s capabilities by issuing the REVOKE statement. Revokes take effect immediately—even if the user is currently connected, so commands that rely on a removed privilege will start failing at once.

REVOKE CREATE USER, CREATE SESSION FROM jeff;

REVOKE RESOURCE FROM app_runtime;

REVOKE EXECUTE ANY PROCEDURE FROM reporting_svc;

REVOKE CONNECT FROM PUBLIC;  

Revoke system privileges and/or roles

REVOKE { system_privilege | role }
     [, { system_privilege | role } ]...
  FROM { user | role | PUBLIC }
     [, { user | role | PUBLIC } ]...
[ CONTAINER = { CURRENT | ALL } ]  -- CDB environments (common users/roles)

Grant refresher (for comparison)

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

• Immediate effect: The user’s active session loses the ability to perform actions requiring the revoked privilege.
• Cascading behavior: If a privilege (or role) was granted WITH ADMIN OPTION (or WITH GRANT OPTION for object privileges) and you revoke it from the grantor, Oracle also revokes it from downstream grantees (cascade).
• Objects remain: Revoking privileges or roles does not drop the user or the objects they own.

• Manage common users/roles from the CDB$ROOT and use CONTAINER=ALL when appropriate.
• Manage local users/roles while connected to the target PDB; use the default CONTAINER=CURRENT.

Revoke a risky system privilege

REVOKE DROP ANY TABLE FROM dev_tooling;

Remove a role you no longer want a user to have

REVOKE DBA FROM temp_admin;

Stop general access granted through PUBLIC

REVOKE CREATE SESSION FROM PUBLIC;  

• Prefer roles over direct privileges: Grant users roles; revoke by removing the role.
• Use least privilege: Grant only what is needed; periodically audit with DBA_TAB_PRIVS, DBA_SYS_PRIVS, DBA_ROLE_PRIVS.
• Stage changes in non-prod first: Revokes can be immediately disruptive.
• Audit critical privileges: Enable Unified Auditing to track use of powerful privileges (e.g., ALTER SYSTEM, DROP ANY TABLE).

Revoking everything does not remove the account. To delete a user and deal with owned objects:

DROP USER username CASCADE;

• Without CASCADE, the drop fails if the user still owns objects.
• With CASCADE, Oracle drops the user, their objects, and dependent constraints; views/procedures that reference those objects become invalid.