Data Control Language (DCL)

This section documents standard SQL (ISO/IEC 9075-2 (SQL/Foundation)) functionality only. Vendor-specific extensions or behavior of any particular database management system are intentionally left out. In the US, the standard is also distributed via ANSI’s webstore (search "ISO/IEC 9075"). This content was generated with the assistance of AI and should be verified against your target DBMS’s own documentation before relying on it in production.

Data Control Language covers the standard SQL statements that manage access to database objects: granting and revoking privileges, and setting session/transaction characteristics. This page covers GRANT, REVOKE, and SET as defined by the SQL standard. For the authoritative (if less readable) specification, see ISO/IEC 9075-2 (SQL/Foundation), the part of the standard that defines GRANT, REVOKE, and SET.

GRANT

GRANT gives one or more privileges on a database object (typically a table, view, or schema) to a role or user (in the standard, a grantee, referred to as an "authorization identifier").

Syntax

GRANT { privilege [, privilege ] ... | ALL PRIVILEGES }
    ON [ TABLE ] object_name
    TO grantee [, grantee ] ...
    [ WITH GRANT OPTION ]

Privileges

SELECT — allows reading rows from the table or view. May be restricted to a column list, e.g. SELECT (column_name).
INSERT — allows adding new rows. May also be restricted to a column list.
UPDATE — allows modifying existing rows. May be restricted to a column list.
DELETE — allows removing rows.
REFERENCES — allows creating a foreign key constraint that references the object’s columns.
USAGE — allows use of a domain, character set, collation, translation, or sequence generator.
ALL PRIVILEGES — grants every privilege applicable to the object that the grantor holds with the grant option.

Granting to a role or user

The grantee may be a specific user, a role, or the keyword PUBLIC (every current and future user).

GRANT SELECT, INSERT, UPDATE
    ON employees
    TO clerk_role;

WITH GRANT OPTION

WITH GRANT OPTION allows the grantee to, in turn, grant the same privileges to other users or roles.

GRANT SELECT
    ON employees
    TO manager_role
    WITH GRANT OPTION;

Granting on a schema

Privileges may also be granted on an entire schema, using USAGE to allow reference to the schema’s objects.

GRANT USAGE
    ON SCHEMA hr
    TO clerk_role;

Granting all privileges

GRANT ALL PRIVILEGES
    ON employees
    TO admin_role
    WITH GRANT OPTION;

REVOKE

REVOKE removes one or more previously granted privileges from a role or user, mirroring the GRANT statement.

Syntax

REVOKE [ GRANT OPTION FOR ] { privilege [, privilege ] ... | ALL PRIVILEGES }
    ON [ TABLE ] object_name
    FROM grantee [, grantee ] ...
    { CASCADE | RESTRICT }

Parameters and options

privilege / ALL PRIVILEGES — the same privilege names accepted by GRANT (SELECT, INSERT, UPDATE, DELETE, REFERENCES, USAGE, or ALL PRIVILEGES).
GRANT OPTION FOR — revokes only the grantee’s ability to re-grant the privilege, leaving the privilege itself in place.
CASCADE — also revokes the privilege from any other grantee who received it, in turn, from the revoked grantee (i.e. privileges granted using WITH GRANT OPTION that depend on the one being revoked).
RESTRICT — rejects the REVOKE if it would leave a dependent privilege grant (one made using WITH GRANT OPTION) without an authorization to fall back on. The standard requires one of CASCADE or RESTRICT to be specified.

Examples

Revoking specific privileges:

REVOKE INSERT, UPDATE
    ON employees
    FROM clerk_role
    RESTRICT;

Revoking only the grant option, leaving the base privilege intact:

REVOKE GRANT OPTION FOR SELECT
    ON employees
    FROM manager_role
    CASCADE;

Revoking all privileges, cascading to any further grants that depended on them:

REVOKE ALL PRIVILEGES
    ON employees
    FROM admin_role
    CASCADE;

SET

The standard SET statement establishes characteristics that apply to the current session or the current transaction. This section covers the standard forms SET TRANSACTION, SET SESSION CHARACTERISTICS, and SET ROLE.

SET TRANSACTION

Sets the access mode, isolation level, or diagnostics size of the transaction that is about to begin. It must be the first statement of a transaction.

SET TRANSACTION
    { READ ONLY | READ WRITE }
    [ , ISOLATION LEVEL { READ UNCOMMITTED | READ COMMITTED | REPEATABLE READ | SERIALIZABLE } ]
    [ , DIAGNOSTICS SIZE number_of_conditions ]

READ ONLY / READ WRITE — whether the transaction may modify data.
ISOLATION LEVEL — the standard isolation levels, in increasing order of strictness: READ UNCOMMITTED, READ COMMITTED, REPEATABLE READ, SERIALIZABLE.
DIAGNOSTICS SIZE — the number of condition entries retained in the diagnostics area for this transaction.

SET TRANSACTION READ WRITE, ISOLATION LEVEL SERIALIZABLE;

SET SESSION CHARACTERISTICS

Sets the default transaction characteristics that will apply to every subsequent transaction started in the current session, until overridden by a SET SESSION CHARACTERISTICS statement or a per-transaction SET TRANSACTION statement.

SET SESSION CHARACTERISTICS AS TRANSACTION
    { READ ONLY | READ WRITE }
    [ , ISOLATION LEVEL { READ UNCOMMITTED | READ COMMITTED | REPEATABLE READ | SERIALIZABLE } ]

SET SESSION CHARACTERISTICS AS TRANSACTION READ ONLY, ISOLATION LEVEL READ COMMITTED;

SET ROLE

Changes the current authorization identifier’s active role for the remainder of the session (or until changed again), affecting which privileges are available to subsequent statements.

SET ROLE { role_name | NONE }

role_name — activates the named role. The current user must already be authorized to use this role.
NONE — deactivates any currently active role, reverting to the privileges of the current user alone.

SET ROLE clerk_role;

SET ROLE NONE;