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
Object | Privileges |
---|---|
CLUSTER
|
|
CONNECTION
|
|
DATABASE
|
|
MATERIALIZED VIEW
|
|
SCHEMA
|
|
SECRET
|
|
SOURCE
|
|
SYSTEM
|
|
TABLE
|
|
TYPE
|
|
VIEW
|
|
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
mz_internal.mz_show_system_privileges
mz_internal.mz_show_my_system_privileges
mz_internal.mz_show_cluster_privileges
mz_internal.mz_show_my_cluster_privileges
mz_internal.mz_show_database_privileges
mz_internal.mz_show_my_database_privileges
mz_internal.mz_show_schema_privileges
mz_internal.mz_show_my_schema_privileges
mz_internal.mz_show_object_privileges
mz_internal.mz_show_my_object_privileges
mz_internal.mz_show_all_privileges
mz_internal.mz_show_all_my_privileges