SQL GRANT / REVOKE (Permissions)

Add W3Buddy as a preferred source on Google

हि En

🔊 Listen

The GRANT and REVOKE statements are used to manage user access and privileges in SQL. These ensure users have only the permissions they need — nothing more.

🔐 Why Use GRANT / REVOKE?

To control who can do what in the database:

• Read data (SELECT)
• Modify data (INSERT, UPDATE, DELETE)
• Execute procedures
• Manage tables or users

🔹 GRANT – Give Permissions

-- Syntax
GRANT privilege [, ...] ON object TO user;

-- Example: Allow user to select and insert on employees table
GRANT SELECT, INSERT ON employees TO john;

You can also grant privileges with WITH GRANT OPTION to allow users to grant to others:

🔹 REVOKE – Remove Permissions

GRANT SELECT ON employees TO john WITH GRANT OPTION;

-- Syntax
REVOKE privilege [, ...] ON object FROM user;

-- Example: Remove insert permission
REVOKE INSERT ON employees FROM john;

This ensures the user can no longer perform those operations.

🔹 Common Privileges

🧠 Quick Recap

💡 Always apply the principle of least privilege for better database security.