GRANT PRIVILEGE

GRANT PRIVILEGE grants privileges to database role(s).

Syntax

NOTE: The syntax supports the ALL [PRIVILEGES] shorthand to refer to all applicable privileges for the object type.

For specific cluster(s):

GRANT <USAGE | CREATE | ALL [PRIVILEGES]> [, ... ]
ON CLUSTER <name> [, ...]
TO <role_name> [, ... ];

For all clusters:

GRANT <USAGE | CREATE | ALL [PRIVILEGES]> [, ... ]
ON ALL CLUSTERS
TO <role_name> [, ... ];

For specific connection(s):

GRANT <USAGE | ALL [PRIVILEGES]>
ON CONNECTION <name> [, ...]
TO <role_name> [, ... ];

For all connections or all connections in specific schema(s) or in database(s):

GRANT <USAGE | ALL [PRIVILEGES]>
ON ALL CONNECTIONS
 [ IN <SCHEMA | DATABASE> <name> [, <name> ...] ]
TO <role_name> [, ... ];

For specific database(s):

GRANT <USAGE | CREATE | ALL [PRIVILEGES]> [, ... ]
ON DATABASE <name> [, ...]
TO <role_name> [, ... ];

For all database:

GRANT <USAGE | CREATE | ALL [PRIVILEGES]> [, ... ]
ON ALL DATABASES
TO <role_name> [, ... ];
NOTE: To read from a views or a materialized views, you must have SELECT privileges on the view/materialized views. That is, having SELECT privileges on the underlying objects defining the view/materialized view is insufficient.

For specific materialized view(s)/view(s)/source(s):

GRANT <SELECT | ALL [PRIVILEGES]>
ON [TABLE] <name> [, <name> ...] -- For PostgreSQL compatibility, if specifying type, use TABLE
TO <role_name> [, ... ];

For specific schema(s):

GRANT <USAGE | CREATE | ALL [PRIVILEGES]> [, ... ]
ON SCHEMA <name> [, ...]
TO <role_name> [, ... ];

For all schemas or all schemas in a specific database(s):

GRANT <USAGE | CREATE | ALL [PRIVILEGES]> [, ... ]
ON ALL SCHEMAS [IN DATABASE <name> [, <name> ...]]
TO <role_name> [, ... ];

For specific secret(s):

GRANT <USAGE | CREATE | ALL [PRIVILEGES]> [, ... ]
ON SECRET <name> [, ...]
TO <role_name> [, ... ];

For all secrets or all secrets in a specific database(s):

GRANT <USAGE | CREATE | ALL [PRIVILEGES]> [, ... ]
ON ALL SECRET [IN DATABASE <name> [, <name> ...]]
TO <role_name> [, ... ];
GRANT <CREATEROLE | CREATEDB | CREATECLUSTER | CREATENETWORKPOLICY | ALL [PRIVILEGES]> [, ... ]
ON SYSTEM
TO <role_name> [, ... ];

For specific view(s):

GRANT <USAGE | ALL [PRIVILEGES]>
ON TYPE <name> [, <name> ...]
TO <role_name> [, ... ];

For all types or all types in a specific schema(s) or in a specific database(s):

GRANT <USAGE | ALL [PRIVILEGES]>
ON ALL TYPES
  [ IN <SCHEMA|DATABASE> <name> [, <name> ...] ]
TO <role_name> [, ... ];

For specific table(s):

GRANT <SELECT | INSERT | UPDATE | DELETE | ALL [PRIVILEGES]> [, ...]
ON [TABLE] <name> [, <name> ...]
TO <role_name> [, ... ];

For all tables or all tables in a specific schema(s) or in a specific database(s):

NOTE: Granting privileges via ALL TABLES […] also applies to sources, views, and materialized views (for the applicable privileges).
GRANT <SELECT | INSERT | UPDATE | DELETE | ALL [PRIVILEGES]> [, ...]
ON ALL TABLES
  [ IN <SCHEMA|DATABASE> <name> [, <name> ...] ]
TO <role_name> [, ... ];

Details

Available privileges

Privilege Description Abbreviation Applies to
SELECT Permission to read rows from an object. r
  • MATERIALIZED VIEW
  • SOURCE
  • TABLE
  • VIEW
INSERT Permission to insert rows into an object. a
  • TABLE
UPDATE

Permission to modify rows in an object.

Modifying rows may also require SELECT if a read is needed to determine which rows to update.

w
  • TABLE
DELETE

Permission to delete rows from an object.

Deleting rows may also require SELECT if a read is needed to determine which rows to delete.

d
  • TABLE
CREATE Permission to create a new objects within the specified object. C
  • DATABASE
  • SCHEMA
  • CLUSTER
USAGE Permission to use or reference an object (e.g., schema/type lookup). U
  • CLUSTER
  • CONNECTION
  • DATABASE
  • SCHEMA
  • SECRET
  • TYPE
CREATEROLE

Permission to create/modify/delete roles and manage role memberships for any role in the system.

WARNING! Roles with the CREATEROLE privilege can obtain the privileges of any other role in the system by granting themselves that role. Avoid granting CREATEROLE unnecessarily.
R
  • SYSTEM
CREATEDB Permission to create new databases. B
  • SYSTEM
CREATECLUSTER Permission to create new clusters. N
  • SYSTEM
CREATENETWORKPOLICY Permission to create network policies to control access at the network layer. P
  • SYSTEM
Object Privileges
CLUSTER
  • USAGE
  • CREATE
CONNECTION
  • USAGE
DATABASE
  • USAGE
  • CREATE
MATERIALIZED VIEW
  • SELECT
SCHEMA
  • USAGE
  • CREATE
SECRET
  • USAGE
SOURCE
  • SELECT
SYSTEM
  • CREATEROLE
  • CREATEDB
  • CREATECLUSTER
TABLE
  • INSERT
  • SELECT
  • UPDATE
  • DELETE
TYPE
  • USAGE
VIEW
  • SELECT

Privileges

The privileges required to execute this statement are:

  • Ownership of affected objects.
  • USAGE privileges on the containing database if the affected object is a schema.
  • USAGE privileges on the containing schema if the affected object is namespaced by a schema.
  • superuser status if the privilege is a system privilege.

Examples

GRANT SELECT ON mv_quarterly_sales TO data_analysts, reporting;
GRANT USAGE, CREATE ON DATABASE materialize TO data_analysts;
GRANT ALL ON CLUSTER dev_cluster TO data_analysts, developers;
GRANT CREATEDB ON SYSTEM TO source_owners;

Useful views

Back to top ↑