SQL GRANT / REVOKE – Secure Your Database with Permission Control

Introduction – Why GRANT and REVOKE Matter

In multi-user database environments, not everyone should have unrestricted access. The GRANT and REVOKE statements in SQL are used to assign or revoke privileges to users or roles. This provides precise control over who can read, modify, or administer database objects like tables, views, or stored procedures.

Without proper permission management, your system is vulnerable to accidental data corruption, unauthorized access, and compliance violations.

In this guide, you’ll learn:

• What GRANT and REVOKE do
• Syntax and examples for common permissions
• How to manage user roles and access
• Platform-specific differences (MySQL, PostgreSQL, SQL Server)
• Best practices and common pitfalls

1. What Are SQL GRANT and REVOKE?

GRANT

Gives specific users or roles permission to access or modify database objects.

REVOKE

Removes previously granted privileges, effectively restricting access.

2. Common Privileges in SQL

3. GRANT Syntax & Examples

GRANT on Table (MySQL / PostgreSQL)

GRANT SELECT, INSERT ON employees TO 'jane'@'localhost';

Jane can now view and add rows to the employees table.

GRANT in SQL Server

GRANT SELECT, UPDATE ON dbo.employees TO Jane;

Use GO or run as a batch if combining multiple grants.

GRANT EXECUTE on Stored Procedure

GRANT EXECUTE ON get_salary TO 'jane'@'localhost';

4. REVOKE Syntax & Examples

REVOKE in MySQL / PostgreSQL

REVOKE SELECT ON employees FROM 'jane'@'localhost';

Jane can no longer view data from employees.

REVOKE in SQL Server

REVOKE SELECT ON dbo.employees FROM Jane;

Use DENY instead of REVOKE to explicitly forbid access, even if granted by a role.

5. Using Roles for Scalable Access Control

Rather than assigning permissions to individual users, it’s better to group privileges into roles.

PostgreSQL

CREATE ROLE readonly;
GRANT CONNECT ON DATABASE hrdb TO readonly;
GRANT USAGE, SELECT ON ALL TABLES IN SCHEMA public TO readonly;
GRANT readonly TO alice;

SQL Server

CREATE ROLE ReadOnlyRole;
GRANT SELECT ON dbo.employees TO ReadOnlyRole;
EXEC sp_addrolemember 'ReadOnlyRole', 'Alice';

6. Best Practices & Pitfalls

Summary – Recap & Real-World Impact

SQL GRANT and REVOKE are essential for securing your database system, enforcing access control, and meeting regulatory compliance requirements.

Key Takeaways:

• Use GRANT to allow and REVOKE to restrict user actions
• Prefer role-based access management
• Be specific with privileges, avoid ALL unless necessary

Real-World Relevance:
From financial systems to healthcare databases, permission control is vital to ensure data safety and accountability.

FAQ – SQL GRANT / REVOKE

What’s the difference between REVOKE and DENY in SQL Server?

REVOKE removes permission, DENY explicitly blocks it—even if granted elsewhere.

How can I view current permissions?

• PostgreSQL: Use \z in psql
• MySQL: Run SHOW GRANTS FOR 'user'@'host';
• SQL Server: Use fn_my_permissions() or SSMS

Can I revoke a GRANT given by another user?

Only if you have admin option or are a superuser/DBA.

Is it safe to use GRANT ALL?

Avoid it unless absolutely necessary—violates the principle of least privilege.