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!	`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;