Product Documentation

Appearance

GRANT ​

Grant privileges on database objects to specific roles and grant membership in roles. Also grant certain privileges at the system level.

You can grant privileges on the following objects:

  • Databases
  • Schemas
  • Tables
  • Columns in tables
  • Views
  • Sequences
  • Stored procedures
  • Encryption keys

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

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 | BACKUP | RESTORE } [, ...] | 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 | CREATE DATABASE | RESTORE ANY DATABASE } [, ...] | ALL [ PRIVILEGES ] }
   ON SYSTEM
   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.

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.

In This Section

Parent topic:SQL Commands