Revoke All Privileges from a User in Oracle
Granting excessive privileges to a user can pose security risks. If a user has been mistakenly granted too many privileges, it is important to revoke them to enforce the principle of least privilege. This guide explains how to remove different types of privileges from an Oracle user.
Revoking All System Privileges
To check the system privileges granted to a user, run:
SELECT COUNT(*) FROM DBA_SYS_PRIVS WHERE GRANTEE = 'W3BUDDY';If the user has unnecessary privileges, you can revoke them using:
REVOKE ALL PRIVILEGES FROM W3BUDDY;This command removes all system privileges from the user.
If the user still requires specific privileges, you can grant them back selectively:
GRANT CREATE SESSION, CREATE TABLE, CREATE VIEW, CREATE PROCEDURE, CREATE TYPE TO W3BUDDY;Revoking All Object Privileges
Object privileges control access to tables, views, and other database objects. To revoke all object privileges from a user, first generate the necessary revoke statements:
SELECT 'REVOKE ALL ON ' || OWNER || '.' || TABLE_NAME || ' FROM ' || GRANTEE || ' CASCADE CONSTRAINTS;'
FROM DBA_TAB_PRIVS WHERE GRANTEE = 'W3BUDDY' ORDER BY 1;This query produces statements similar to:
REVOKE ALL ON HR.EMPLOYEES FROM W3BUDDY CASCADE CONSTRAINTS;
REVOKE ALL ON HR.LOCATIONS FROM W3BUDDY CASCADE CONSTRAINTS;Execute these statements to revoke object privileges selectively.
Revoking All Roles
Roles bundle multiple system and object privileges. To find the roles assigned to a user, use:
SELECT 'REVOKE ' || GRANTED_ROLE || ' FROM ' || GRANTEE || ';'
FROM DBA_ROLE_PRIVS WHERE GRANTEE = 'W3BUDDY' ORDER BY 1;This query generates statements like:
REVOKE NEW_ROLE_NAME FROM W3BUDDY;
REVOKE ANOTHER_ROLE_NAME FROM W3BUDDY;
REVOKE THIRD_ROLE_NAME FROM W3BUDDY;Execute the relevant statements to remove unnecessary roles.
Conclusion
Revoking excessive privileges helps secure your database by ensuring users only have the permissions they need. Always review privileges periodically to maintain database security.
