SQL GRANT

How do I grant privileges in SQL?

Assigns specific privileges or roles on database objects to users, roles, or PUBLIC.

Welcome to the Galaxy, Guardian!
You'll be receiving a confirmation email

Follow us on twitter :)

Oops! Something went wrong while submitting the form.

Compatible dialects for SQL GRANT: PostgreSQL, MySQL, SQL Server, Oracle, Snowflake, Redshift, DB2, MariaDB, Teradata

SQL GRANT Full Explanation

The SQL GRANT statement is part of Data Control Language (DCL). It authorizes a user, role, or the special keyword PUBLIC to perform actions such as SELECT, INSERT, UPDATE, DELETE, EXECUTE, or USAGE on database objects (tables, views, schemas, functions, procedures, etc.). GRANT can also bestow entire roles. When used with WITH GRANT OPTION, the recipient can further delegate the same privileges to others. Privileges take effect immediately without requiring COMMIT but persist until explicitly revoked with REVOKE. GRANT must be executed by an object owner, a role with ADMIN OPTION, or a superuser, and the exact set of allowable privileges varies by object type and SQL dialect.

SQL GRANT Syntax

GRANT privilege_list
ON object_name
TO grantee_list
[WITH GRANT OPTION];

SQL GRANT Parameters

- privilege_list (string) - Comma-separated privileges such as SELECT, INSERT, UPDATE, DELETE, ALL, EXECUTE, USAGE
- object_name (identifier) - Target table, view, sequence, function, procedure, schema, or database
- grantee_list (identifier) - One or more user names, role names, or the keyword PUBLIC
- WITH GRANT OPTION (keyword) - Allows each grantee to grant the same privileges to others

Example Queries Using SQL GRANT

-- Grant read-only access on a table to a role
GRANT SELECT ON public.users TO analyst_role;

-- Grant full DML rights on a table to a user with delegation rights
GRANT SELECT, INSERT, UPDATE, DELETE ON sales.orders TO alice WITH GRANT OPTION;

-- Grant a role to another role
GRANT data_analyst TO marketing_team;

-- Grant USAGE on a schema
GRANT USAGE ON SCHEMA analytics TO bob;

Expected Output Using SQL GRANT

Statement succeeds silently
System catalogs are updated so that specified grantees gain the listed privileges immediately
Subsequent access checks reference these new entries

Use Cases with SQL GRANT

Onboard new analysts with read-only table access
Delegate write privileges to application roles
Allow a senior engineer to sub-grant privileges via WITH GRANT OPTION
Grant EXECUTE on stored procedures to an API service account
Implement least-privilege security models aligned with compliance standards

Common Mistakes with SQL GRANT

Omitting the schema qualifier and granting on the wrong object
Using incorrect privilege names for the object type (e.g., EXECUTE on a table)
Forgetting WITH GRANT OPTION when delegation is needed
Assuming GRANT is transactional and will roll back on error (it commits instantly in most systems)
Attempting to grant privileges without ownership or proper ADMIN rights

First Introduced In

SQL-92

Frequently Asked Questions

What privileges can I grant?

Common privileges include SELECT, INSERT, UPDATE, DELETE, REFERENCES, EXECUTE, USAGE, and ALL. Availability depends on the object type and database vendor.

Who can execute a GRANT statement?

Usually the object owner, a role with ADMIN OPTION on the object, or a superuser. Regular users without sufficient rights will receive a permission-denied error.

How do I let someone delegate privileges?

Add WITH GRANT OPTION to the GRANT statement. This empowers the recipient to pass along the same rights to others.

Does GRANT require a transaction commit?

No. Most databases apply GRANT immediately. Rolling back the surrounding transaction will not undo the privilege change; you must issue REVOKE explicitly.