Product Documentation

Appearance

All platforms

GRANT ​

Grant privileges on database objects to specific roles and grant membership in roles.

You can grant privileges at the instance level (ON SYSTEM) and on the following lower-level objects. (ON SYSTEM means that the privileges are granted to users and roles within a specific data warehouse instance.)

  • Virtual compute clusters (ON CLUSTER)
  • Databases (ON DATABASE)
  • Schemas (ON SCHEMA)
  • Tables, views, and columns in tables (ON TABLE)
  • Sequences (ON SEQUENCE)
  • Stored procedures (ON PROCEDURE)
  • Encryption keys (ON KEY)
  • Users and roles (ON ROLE)
  • External storage, location, and format objects (ON EXTERNAL)

Note that newly granted privileges are added to any that have already been granted.

Access privileges (ACLs) are logged in sys.database. For a complete list of ACLs and their abbreviations, see Abbreviations for ACLs.

Here is a complete syntax summary for the GRANT command, followed by definitions of some common parameters. See the subsequent sections for details about each ON variant.

GRANT { { SELECT | INSERT | UPDATE | DELETE | TRUNCATE | REFERENCES }
   [, ...] | ALL [ PRIVILEGES ] }
   ON { [ TABLE ] table_name [, ...]
        | ALL TABLES IN SCHEMA schema_name [, ...] }
   TO role_specification [, ...] [ WITH GRANT OPTION ]

GRANT { { SELECT | INSERT | UPDATE | REFERENCES } ( column_name [, ...] )
   [, ...] | ALL [ PRIVILEGES ] ( column_name [, ...] ) }
   ON [ TABLE | VIEW ] table_name | view_name [, ...]
   TO role_specification [, ...] [ WITH GRANT OPTION ]

GRANT { { CREATE | CONNECT | TEMPORARY | TEMP | 
   EXPLAIN QUERY | TRACE QUERY | VIEW QUERY TEXT |  
   ALTER ANY SCHEMA | DROP ANY SCHEMA |
   BACKUP | RESTORE | BULK LOAD | CONTROL } 
   [, ...] | ALL [ PRIVILEGES ] }
   ON DATABASE database_name [, ...]
   TO role_specification [, ...] [ WITH GRANT OPTION ]

GRANT { EXECUTE | ALL [ PRIVILEGES ] }
   ON { PROCEDURE procedure_name ( [ [ argmode ] [ arg_name ] arg_type [, ...] ] ) 
   [, ...] | ALL PROCEDURES IN SCHEMA schema_name [, ...] }
   TO role_specification [, ...] [ WITH GRANT OPTION ]

GRANT { { CREATE | USAGE } [, ...] | ALL [ PRIVILEGES ] }
   ON SCHEMA schema_name [, ...]
   TO role_specification [, ...] [ WITH GRANT OPTION ]

GRANT { { USAGE | SELECT | UPDATE }
   [, ...] | ALL [ PRIVILEGES ] }
   ON { SEQUENCE sequence_name [, ...] | ALL SEQUENCES IN SCHEMA schema_name [, ...] }
   TO role_specification [, ...] [ WITH GRANT OPTION ]

GRANT { { ENCRYPT | DECRYPT | HMAC } 
   [, ...] | ALL [ PRIVILEGES ] } 
   ON KEY key_name 
   TO { role_specification } [, ...] [ WITH GRANT OPTION ]

GRANT { { CREATE ROLE | ALTER ANY ROLE | DROP ANY ROLE | VIEW ROLE |
   CREATE DATABASE | ALTER ANY DATABASE | DROP ANY DATABASE | BACKUP ANY DATABASE | RESTORE ANY DATABASE | 
   TRACE QUERY | VIEW QUERY TEXT | EXPLAIN QUERY |
   CONTROL ANY SESSION | CONTROL LDAP }
   [, ...] | ALL [ PRIVILEGES ] }
   ON SYSTEM
   TO { role_specification } [, ...] [ WITH GRANT OPTION ]

GRANT { { ALTER ROLE | DROP ROLE | CONTROL }
   [, ...] | ALL [ PRIVILEGES ] }
   ON ROLE role_specification [, ...] 
   TO role_specification [, ...] [ WITH GRANT OPTION ]

where role_specification can be:

   role_name
  | PUBLIC
  | CURRENT_USER
  | SESSION_USER

GRANT role_name [, ...] TO role_name [, ...] [ WITH ADMIN OPTION ]

Common Parameters (All GRANT Commands) ​

ALL PRIVILEGES

Grant all of the available privileges on the object. Individual privileges may be later revoked from a user with ALL PRIVILEGES. CONTROL (ON DATABASE or ROLE) is not part of ALL.

Note: GRANT ALL does not equate to superuser or table owner privileges.

See also Granting Schema-Level Privileges to Members of a Role.

role_specification

Name of an existing role or one of the following:

  • PUBLIC: an implicit group that always includes all roles. PUBLIC means that privileges are granted to both existing roles and new roles when they are created. A given role accrues all of the following privileges:

    • Privileges granted directly
    • Privileges granted to roles that the role in question is a member of
    • Privileges granted to PUBLIC Yellowbrick grants default CONNECT and CREATE TEMP TABLE privileges for databases to PUBLIC. No default privileges are granted to PUBLIC on tables, columns, or schemas.

  • CURRENT_USER

  • SESSION_USER

WITH GRANT OPTION

WITH GRANT OPTION means that the receiving role can grant the same privilege to other roles. You cannot grant options to PUBLIC. Granting privileges to the owner of a table, schema, or database (the owner is typically the user who created the object) is not necessary. Owners accrue all privileges by default (but may revoke some of them for security reasons).

WITH ADMIN OPTION

Privilege to grant and revoke membership in the role to others.

Object Owners and Creators ​

The ability to drop and alter objects is not subject to GRANT actions. Owners have these privileges by default, and you cannot grant or revoke them. On the other hand, you can grant or revoke membership in the role that owns an object. Also note that the owner of an object implicitly has all grant options for that object.

An object owner can revoke both default and granted privileges. If you want to revoke privileges, for security purposes it is a best practice to do so within the same transaction that creates the object. In this way, you do not allow a window of time in which another user can access the object.

Note that object ownership can be transferred, but the creator of an object is persistent.