SQL GRANT and REVOKE Permissions

Controlling access to your database goes far beyond creating users and roles. The real power lies in deciding exactly what each user or role is allowed to do and on which specific objects. This is where the GRANT and REVOKE statements come into play. Together, they form the backbone of SQL GRANT REVOKE permission management, giving you fine-grained control over who can read, modify, or manage your data.

This guide covers everything you need to confidently manage permissions in SQL: granting and revoking privileges at the table level, restricting access down to individual columns, applying permissions across entire schemas, and avoiding the most common pitfalls along the way. All examples are practical and applicable to major database systems like PostgreSQL, MySQL, SQL Server, and Oracle.

How SQL Permissions Work

SQL follows a whitelist model for permissions. By default, a newly created user has no access to any data. Every permission must be explicitly granted. This design ensures that no user accidentally ends up with more access than intended.

The two core statements are:

• GRANT adds permissions, allowing a user or role to perform specific actions.
• REVOKE removes permissions, taking away previously granted access.

Permissions can be applied at multiple levels of granularity:

Level	What It Controls
Database	Connection, creation of schemas
Schema	Usage, creation of objects within a schema
Table	SELECT, INSERT, UPDATE, DELETE on entire tables
Column	SELECT, INSERT, UPDATE on specific columns only

Think of permissions as layers. You can grant broad access at the schema level and then fine-tune restrictions at the table or column level depending on your security requirements.

GRANT: Adding Permissions

The GRANT statement gives a user or role permission to perform one or more operations on a database object.

Basic Syntax

GRANT privilege_type [, privilege_type, ...]
ON object_name
TO user_or_role;

Granting Table-Level Permissions

The most common use case is granting specific privileges on a table.

Grant read access:

GRANT SELECT ON employees TO alice;

Now alice can run SELECT queries against the employees table, but she cannot insert, update, or delete rows.

Grant multiple privileges at once:

GRANT SELECT, INSERT ON employees TO alice;

This allows alice to both read and add new rows to the employees table.

Grant full data manipulation privileges:

GRANT SELECT, INSERT, UPDATE, DELETE ON employees TO data_editor;

Available Privilege Types

Different database systems support slightly different privilege names, but the most common ones are universal:

Privilege	What It Allows
SELECT	Read rows from a table or view
INSERT	Add new rows to a table
UPDATE	Modify existing rows in a table
DELETE	Remove rows from a table
TRUNCATE	Remove all rows from a table (PostgreSQL)
REFERENCES	Create foreign keys referencing a table
TRIGGER	Create triggers on a table
ALL PRIVILEGES	Every available privilege on the object

Granting to Roles vs. Users

You can grant permissions directly to a user or to a role. Granting to a role is almost always the better practice because it keeps your permission management centralized and reusable.

-- Grant to a role (recommended)
CREATE ROLE reporting_team;
GRANT SELECT ON employees TO reporting_team;
GRANT SELECT ON departments TO reporting_team;
GRANT SELECT ON salaries TO reporting_team;

-- Then assign the role to users
GRANT reporting_team TO alice;
GRANT reporting_team TO bob;
GRANT reporting_team TO charlie;

If you later need to add access to a new table for the entire reporting team, you update the role once:

GRANT SELECT ON projects TO reporting_team;
-- All three users instantly gain access

GRANT ALL PRIVILEGES

The ALL PRIVILEGES shorthand grants every applicable permission on the specified object.

GRANT ALL PRIVILEGES ON employees TO db_admin;

This gives db_admin full control over the employees table: SELECT, INSERT, UPDATE, DELETE, and any other table-level privilege supported by your database system.

warning

Use ALL PRIVILEGES sparingly. In production environments, it is almost always better to grant only the specific privileges a user or role actually needs. The principle of least privilege exists to reduce the blast radius if an account is compromised or misused.

WITH GRANT OPTION

By default, a user who receives a privilege cannot pass that privilege to other users. The WITH GRANT OPTION clause changes this behavior.

GRANT SELECT ON employees TO team_lead WITH GRANT OPTION;

Now team_lead can grant SELECT on employees to other users:

-- Connected as team_lead
GRANT SELECT ON employees TO new_intern;
-- Success: team_lead is allowed to delegate this privilege

Without WITH GRANT OPTION:

GRANT SELECT ON departments TO team_lead;

-- Connected as team_lead
GRANT SELECT ON departments TO new_intern;
-- ERROR: permission denied for table departments

caution

WITH GRANT OPTION creates a chain of trust. If you revoke the privilege from team_lead, the privileges they delegated to others may also be revoked (this behavior is called cascading revoke and varies by database system). Use this feature carefully and document who has grant options.

GRANT Examples Across Database Systems

PostgreSQL:

GRANT SELECT, INSERT ON employees TO app_service;
GRANT USAGE ON SEQUENCE employees_id_seq TO app_service;

In PostgreSQL, if a table has an auto-incrementing column using a sequence, you must also grant USAGE on the sequence for INSERT to work properly.

MySQL:

GRANT SELECT, INSERT ON company_db.employees TO 'app_service'@'localhost';

MySQL requires you to specify the database with the table name and the user's host.

SQL Server:

USE company_db;
GRANT SELECT, INSERT ON dbo.employees TO app_service;

SQL Server uses the schema.table format (commonly dbo as the default schema).

Oracle:

GRANT SELECT, INSERT ON hr.employees TO app_service;

Oracle uses the owner.table format.

REVOKE: Removing Permissions

The REVOKE statement removes previously granted privileges from a user or role. Its syntax mirrors GRANT.

Basic Syntax

REVOKE privilege_type [, privilege_type, ...]
ON object_name
FROM user_or_role;

Examples

Revoke a single privilege:

REVOKE INSERT ON employees FROM alice;

alice can no longer insert rows into employees, but any other privileges she has (like SELECT) remain intact.

Revoke multiple privileges:

REVOKE INSERT, UPDATE, DELETE ON employees FROM alice;

Revoke all privileges:

REVOKE ALL PRIVILEGES ON employees FROM alice;

REVOKE Across Database Systems

PostgreSQL:

REVOKE SELECT ON employees FROM reporting_team;

MySQL:

REVOKE SELECT ON company_db.employees FROM 'alice'@'localhost';

SQL Server:

REVOKE SELECT ON dbo.employees FROM alice;

Oracle:

REVOKE SELECT ON hr.employees FROM alice;

Common Mistake: Revoking From a User When the Privilege Came From a Role

A frequent source of confusion is attempting to revoke a privilege directly from a user when that user actually receives the privilege through a role.

Scenario:

-- Setup
CREATE ROLE read_only;
GRANT SELECT ON employees TO read_only;

CREATE USER alice WITH PASSWORD 'Alice!2024';
GRANT read_only TO alice;

Wrong approach:

REVOKE SELECT ON employees FROM alice;

This revokes any direct SELECT grant on employees from alice, but she still has SELECT through the read_only role. She can still query the table.

Correct approach (option 1): Revoke the role from the user:

REVOKE read_only FROM alice;

Correct approach (option 2): Revoke the privilege from the role:

REVOKE SELECT ON employees FROM read_only;
-- This affects ALL users who have the read_only role

Choose the approach based on whether you want to affect just one user or everyone with that role.

Verifying Current Permissions

After granting or revoking permissions, you should verify the current state.

PostgreSQL:

-- Check table-level privileges
SELECT grantee, privilege_type, table_name
FROM information_schema.table_privileges
WHERE table_name = 'employees';

Output:

  grantee   | privilege_type | table_name
------------+----------------+------------
 data_editor| SELECT         | employees
 data_editor| INSERT         | employees
 data_editor| UPDATE         | employees
 alice       | SELECT         | employees

MySQL:

SHOW GRANTS FOR 'alice'@'localhost';

Output:

+--------------------------------------------------------------+
| Grants for alice@localhost                                    |
+--------------------------------------------------------------+
| GRANT USAGE ON *.* TO `alice`@`localhost`                    |
| GRANT SELECT ON `company_db`.`employees` TO `alice`@`localhost` |
+--------------------------------------------------------------+

SQL Server:

SELECT 
    dp.name AS principal_name,
    o.name AS object_name,
    p.permission_name,
    p.state_desc
FROM sys.database_permissions p
JOIN sys.objects o ON p.major_id = o.object_id
JOIN sys.database_principals dp ON p.grantee_principal_id = dp.principal_id
WHERE o.name = 'employees';

Column-Level Permissions

Sometimes granting access to an entire table is too broad. You might want a user to see employee names and departments but not their salaries or personal information. Column-level permissions solve this.

Syntax

GRANT privilege_type (column1, column2, ...)
ON table_name
TO user_or_role;

Practical Example

Consider an employees table with these columns:

CREATE TABLE employees (
    id SERIAL PRIMARY KEY,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    email VARCHAR(100),
    department VARCHAR(50),
    salary DECIMAL(10, 2),
    ssn VARCHAR(11)
);

The salary and ssn columns contain sensitive data. You want the support team to see basic employee information but not financial or personal details.

-- Grant SELECT on only the non-sensitive columns
GRANT SELECT (id, first_name, last_name, email, department) 
ON employees 
TO support_team;

Testing the permissions:

-- Connected as a member of support_team
SELECT id, first_name, department FROM employees;

Output:

 id | first_name | department
----+------------+------------
  1 | Emma       | Engineering
  2 | Frank      | Marketing

-- Trying to access a restricted column
SELECT id, first_name, salary FROM employees;
-- ERROR: permission denied for column "salary" of relation "employees"

-- SELECT * also fails because it includes restricted columns
SELECT * FROM employees;
-- ERROR: permission denied for column "salary" of relation "employees"

Column-Level INSERT and UPDATE

You can also restrict which columns a user can write to.

-- Allow updating only the department and email, not salary
GRANT UPDATE (department, email) ON employees TO hr_assistant;

Testing:

-- Connected as hr_assistant
UPDATE employees SET department = 'Sales' WHERE id = 1;
-- Success

UPDATE employees SET salary = 999999 WHERE id = 1;
-- ERROR: permission denied for column "salary" of relation "employees"

Column-Level Permissions Across Database Systems

PostgreSQL:

GRANT SELECT (first_name, last_name, department) ON employees TO viewer;

Full support for column-level SELECT, INSERT, UPDATE, and REFERENCES.

MySQL:

GRANT SELECT (first_name, last_name, department) ON company_db.employees TO 'viewer'@'%';

MySQL supports column-level SELECT, INSERT, and UPDATE.

SQL Server:

GRANT SELECT ON dbo.employees (first_name, last_name, department) TO viewer;

SQL Server also supports DENY at the column level, which explicitly blocks access even if another permission would allow it.

-- Grant SELECT on the whole table but deny access to sensitive columns
GRANT SELECT ON dbo.employees TO hr_assistant;
DENY SELECT ON dbo.employees (salary, ssn) TO hr_assistant;

This is a powerful pattern: grant broad access first, then deny specific columns. It is unique to SQL Server.

Oracle:

Oracle does not natively support column-level SELECT restrictions through GRANT. Instead, you achieve the same result using views:

CREATE VIEW employees_public AS
SELECT id, first_name, last_name, email, department
FROM employees;

GRANT SELECT ON employees_public TO support_team;

Views as an Alternative to Column-Level Permissions

Even in databases that support column-level permissions natively, using views is often a cleaner approach. A view acts as a virtual table that exposes only the columns (and rows) you choose. This works consistently across all database systems.

CREATE VIEW employee_directory AS
SELECT id, first_name, last_name, department
FROM employees;

GRANT SELECT ON employee_directory TO public_access;

Revoking Column-Level Permissions

Revoking follows the same column-specific syntax:

REVOKE SELECT (email) ON employees FROM support_team;

After this, support_team can still select id, first_name, last_name, and department, but no longer email.

Schema-Level Permissions

While table-level and column-level permissions give you precision, schema-level permissions let you manage access at a broader scope. A schema is a logical container that groups tables, views, functions, and other database objects. Granting permissions at the schema level applies to all objects within that schema, reducing the number of individual GRANT statements you need.

Why Schema-Level Permissions Matter

Imagine a database with 50 tables organized into schemas:

company_db
├── public          (general tables)
├── hr              (human resources data)
├── finance         (financial records)
└── analytics       (reporting tables)

Instead of granting SELECT on each of the 50 tables individually, you can grant access at the schema level:

GRANT SELECT ON ALL TABLES IN SCHEMA analytics TO reporting_team;

One statement covers every table in the analytics schema.

Schema-Level Permissions in PostgreSQL

PostgreSQL has the most explicit schema permission model. There are two layers you must configure:

Layer 1: USAGE on the schema

Before a user can access any object in a schema, they need USAGE permission on the schema itself. Think of it as permission to "enter" the schema.

GRANT USAGE ON SCHEMA hr TO hr_manager;

Layer 2: Permissions on objects within the schema

GRANT SELECT ON ALL TABLES IN SCHEMA hr TO hr_manager;

Both layers are required. This is a common source of confusion.

Common mistake in PostgreSQL:

-- Granting table permissions without schema USAGE
GRANT SELECT ON ALL TABLES IN SCHEMA hr TO hr_manager;

-- Connected as hr_manager
SELECT * FROM hr.employees;
-- ERROR: permission denied for schema hr

Correct approach:

GRANT USAGE ON SCHEMA hr TO hr_manager;
GRANT SELECT ON ALL TABLES IN SCHEMA hr TO hr_manager;

-- Connected as hr_manager
SELECT * FROM hr.employees;
-- Returns rows successfully

Default Privileges in PostgreSQL

A powerful PostgreSQL feature is default privileges. When you grant SELECT ON ALL TABLES IN SCHEMA hr, it applies only to tables that already exist. Tables created in the future will not automatically be covered.

ALTER DEFAULT PRIVILEGES solves this:

-- Any NEW table created in the hr schema will automatically
-- grant SELECT to hr_manager
ALTER DEFAULT PRIVILEGES IN SCHEMA hr
GRANT SELECT ON TABLES TO hr_manager;

This is essential for maintaining permissions as your schema evolves over time.

-- Full schema setup with default privileges
GRANT USAGE ON SCHEMA hr TO hr_manager;
GRANT SELECT ON ALL TABLES IN SCHEMA hr TO hr_manager;  -- existing tables
ALTER DEFAULT PRIVILEGES IN SCHEMA hr
GRANT SELECT ON TABLES TO hr_manager;                    -- future tables

info

ALTER DEFAULT PRIVILEGES applies to objects created by the user who runs the command. If multiple users create tables in the same schema, you may need to specify the owner:

ALTER DEFAULT PRIVILEGES FOR ROLE schema_owner IN SCHEMA hr
GRANT SELECT ON TABLES TO hr_manager;

Schema-Level Permissions in MySQL

MySQL uses databases as the equivalent of schemas. You grant permissions at the database level using the database.* syntax:

-- Grant read access to all tables in the hr database
GRANT SELECT ON hr.* TO 'hr_manager'@'%';

-- Grant read/write access to the analytics database
GRANT SELECT, INSERT, UPDATE, DELETE ON analytics.* TO 'data_team'@'%';

Schema-Level Permissions in SQL Server

SQL Server uses GRANT on the schema object directly:

-- Grant SELECT on all objects in the hr schema
GRANT SELECT ON SCHEMA::hr TO hr_manager;

-- Grant INSERT and UPDATE on the finance schema
GRANT INSERT, UPDATE ON SCHEMA::finance TO finance_team;

The SCHEMA:: prefix tells SQL Server you are targeting a schema, not a table.

Schema-Level Permissions in Oracle

Oracle grants schema-wide access through system privileges:

-- Allow the user to select from any table in any schema
GRANT SELECT ANY TABLE TO reporting_user;

For more controlled access, Oracle typically relies on granting permissions on individual objects or using roles combined with specific grants.

Revoking Schema-Level Permissions

-- PostgreSQL
REVOKE SELECT ON ALL TABLES IN SCHEMA hr FROM hr_manager;
REVOKE USAGE ON SCHEMA hr FROM hr_manager;

-- MySQL
REVOKE SELECT ON hr.* FROM 'hr_manager'@'%';

-- SQL Server
REVOKE SELECT ON SCHEMA::hr FROM hr_manager;

DENY: Explicitly Blocking Access (SQL Server)

SQL Server introduces a third permission statement beyond GRANT and REVOKE: DENY. While REVOKE simply removes a previously granted permission, DENY explicitly blocks access, overriding any GRANT that might apply through roles or other paths.

-- Grant SELECT on everything in the hr schema
GRANT SELECT ON SCHEMA::hr TO all_staff;

-- But explicitly block access to the salary table
DENY SELECT ON hr.salaries TO all_staff;

The precedence order in SQL Server is:

1. DENY always wins
2. GRANT applies if no DENY exists
3. No permission means no access

note

PostgreSQL, MySQL, and Oracle do not have a DENY statement. In those systems, you control access by simply not granting the unwanted privilege, or by using views to expose only the data you want users to see.

Practical Scenario: Full Permission Setup

Let's build a complete permission structure for a company database with multiple teams.

-- =============================================
-- STEP 1: Create schemas
-- =============================================
CREATE SCHEMA hr;
CREATE SCHEMA finance;
CREATE SCHEMA public_data;

-- =============================================
-- STEP 2: Create roles for each access level
-- =============================================
CREATE ROLE hr_read;
CREATE ROLE hr_write;
CREATE ROLE finance_read;
CREATE ROLE finance_write;
CREATE ROLE public_read;

-- =============================================
-- STEP 3: Grant schema-level permissions to roles
-- =============================================

-- HR roles
GRANT USAGE ON SCHEMA hr TO hr_read, hr_write;
GRANT SELECT ON ALL TABLES IN SCHEMA hr TO hr_read;
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA hr TO hr_write;

-- Finance roles
GRANT USAGE ON SCHEMA finance TO finance_read, finance_write;
GRANT SELECT ON ALL TABLES IN SCHEMA finance TO finance_read;
GRANT SELECT, INSERT, UPDATE ON ALL TABLES IN SCHEMA finance TO finance_write;

-- Public data role
GRANT USAGE ON SCHEMA public_data TO public_read;
GRANT SELECT ON ALL TABLES IN SCHEMA public_data TO public_read;

-- =============================================
-- STEP 4: Set default privileges for future tables
-- =============================================
ALTER DEFAULT PRIVILEGES IN SCHEMA hr
  GRANT SELECT ON TABLES TO hr_read;
ALTER DEFAULT PRIVILEGES IN SCHEMA hr
  GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO hr_write;
ALTER DEFAULT PRIVILEGES IN SCHEMA finance
  GRANT SELECT ON TABLES TO finance_read;

-- =============================================
-- STEP 5: Create users and assign roles
-- =============================================
CREATE USER sarah WITH PASSWORD 'Sarah!HR2024';
GRANT hr_write TO sarah;
GRANT public_read TO sarah;

CREATE USER mike WITH PASSWORD 'Mike!Fin2024';
GRANT finance_read TO mike;
GRANT public_read TO mike;

CREATE USER lisa WITH PASSWORD 'Lisa!Analyst2024';
GRANT hr_read TO lisa;
GRANT finance_read TO lisa;
GRANT public_read TO lisa;

Result:

User	HR Schema	Finance Schema	Public Data Schema
Sarah	Full CRUD	No access	Read only
Mike	No access	Read only	Read only
Lisa	Read only	Read only	Read only

This structure is easy to maintain. When a new team member joins HR, you simply create their user and grant hr_write. No need to remember dozens of individual table grants.

Quick Reference

GRANT Syntax Patterns

-- Table-level
GRANT SELECT, INSERT ON table_name TO user_or_role;

-- Column-level
GRANT SELECT (col1, col2) ON table_name TO user_or_role;

-- Schema-level (PostgreSQL)
GRANT SELECT ON ALL TABLES IN SCHEMA schema_name TO user_or_role;

-- Schema-level (MySQL)
GRANT SELECT ON database_name.* TO 'user'@'host';

-- Schema-level (SQL Server)
GRANT SELECT ON SCHEMA::schema_name TO user_or_role;

-- With delegation ability
GRANT SELECT ON table_name TO user_or_role WITH GRANT OPTION;

REVOKE Syntax Patterns

-- Table-level
REVOKE INSERT ON table_name FROM user_or_role;

-- Column-level
REVOKE SELECT (col1) ON table_name FROM user_or_role;

-- Schema-level (PostgreSQL)
REVOKE SELECT ON ALL TABLES IN SCHEMA schema_name FROM user_or_role;

-- All privileges
REVOKE ALL PRIVILEGES ON table_name FROM user_or_role;

Conclusion

Mastering SQL GRANT REVOKE operations is essential for building secure, well-organized databases. The GRANT statement gives you the power to precisely define what each user or role can do, from broad schema-level access down to individual column restrictions. The REVOKE statement lets you cleanly remove access when it is no longer needed.

Start with schema-level permissions for broad access patterns, use table-level permissions when teams need access to specific tables, and reach for column-level permissions when sensitive data must be protected within a shared table. Always prefer granting to roles rather than individual users, and remember to set default privileges so that future objects are automatically covered.

With these tools and practices in place, you can confidently manage database access for teams of any size while keeping your data secure.