How to Grant All Privileges to a User in Oracle
In Oracle, granting “all privileges” to a user means giving them full access to system privileges or object privileges. Let’s break it down:
Granting All System Privileges
If you want to grant a user all system privileges without assigning the powerful DBA role, use the ALL PRIVILEGES keyword.
However, ALL PRIVILEGES does not include:
- SELECT ANY DICTIONARY
- ALTER DATABASE LINK
- ALTER PUBLIC DATABASE LINK
Example: Granting All System Privileges
To grant all system privileges to a user named W3BUDDY, run:
GRANT ALL PRIVILEGES TO HR;After executing this command, the user will have 234 system privileges, including:
- Creating, altering, and dropping tables, views, and indexes.
- Managing users, roles, and profiles.
- Administering database resources.
- Executing any procedure, function, or package.
To verify the granted privileges:
set pagesize 10000;
SELECT PRIVILEGE FROM DBA_SYS_PRIVS WHERE GRANTEE = 'HR' ORDER BY 1;Granting All Object Privileges
To grant all privileges on a specific object (e.g., a table or procedure), use:
GRANT ALL ON <SCHEMA>.<OBJECT> TO <GRANTEE>;Privileges granted vary based on the object type:
- For a Table: Includes SELECT, INSERT, UPDATE, DELETE, ALTER, INDEX, etc.
- For a Procedure, Function, or Package: Includes EXECUTE and DEBUG.
For revoking all privileges, check our separate guide: “How to Revoke All Privileges from a User in Oracle“.
Final Thoughts
Granting all privileges gives a user extensive control over the database, so use it wisely. Always ensure security by only granting necessary privileges.
