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.