ContextQMD
Back to Materialize

REVOKE PRIVILEGE

doc/user/content/sql/revoke-privilege.md

1236.5 KB
Original Source

REVOKE revokes privileges from a database object. The PUBLIC pseudo-role can be used to indicate that the privileges should be revoked from all roles (including roles that might not exist yet).

Syntax

{{< note >}}

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

{{</note>}}

{{< tabs >}}

<!-- ============ CLUSTER syntax ============== -->

{{< tab "Cluster" >}}

For specific cluster(s):

mzsql
REVOKE <USAGE | CREATE | ALL [PRIVILEGES]> [, ... ]
ON CLUSTER <name> [, ...]
FROM <role_name> [, ... ]
;

For all clusters:

mzsql
REVOKE <USAGE | CREATE | ALL [PRIVILEGES]> [, ... ]
ON ALL CLUSTERS
FROM <role_name> [, ... ]
;

{{</ tab >}}

<!-- ================== Connection syntax ====================== -->

{{< tab "Connection">}}

For specific connection(s):

mzsql
REVOKE <USAGE | ALL [PRIVILEGES]>
ON CONNECTION <name> [, ...]
FROM <role_name> [, ... ];

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

mzsql
REVOKE <USAGE | ALL [PRIVILEGES]>
ON ALL CONNECTIONS
 [ IN <SCHEMA | DATABASE> <name> [, <name> ...] ]
FROM <role_name> [, ... ];

{{</ tab >}}

<!-- ================== Database syntax ===================== -->

{{< tab "Database">}}

For specific database(s):

mzsql
REVOKE <USAGE | CREATE | ALL [PRIVILEGES]> [, ... ]
ON DATABASE <name> [, ...]
FROM <role_name> [, ... ];

For all database:

mzsql
REVOKE <USAGE | CREATE | ALL [PRIVILEGES]> [, ... ]
ON ALL DATABASES
FROM <role_name> [, ... ];

{{</ tab >}}

<!-- =============== Materialized view syntax =================== -->

{{< tab "Materialized view/view/source">}}

{{< note >}} {{% include-headless "/headless/rbac-cloud/privilege-for-views-mat-views" %}} {{</ note >}}

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

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

{{</ tab >}}

<!-- ==================== Schema syntax ===================== -->

{{< tab "Schema">}}

For specific schema(s):

mzsql
REVOKE <USAGE | CREATE | ALL [PRIVILEGES]> [, ... ]
ON SCHEMA <name> [, ...]
FROM <role_name> [, ... ];

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

mzsql
REVOKE <USAGE | CREATE | ALL [PRIVILEGES]> [, ... ]
ON ALL SCHEMAS [IN DATABASE <name> [, <name> ...]]
FROM <role_name> [, ... ];

{{</ tab >}}

<!-- ==================== Secret syntax ===================== -->

{{< tab "Secret">}}

For specific secret(s):

mzsql
REVOKE <USAGE | ALL [PRIVILEGES]> [, ... ]
ON SECRET <name> [, ...]
FROM <role_name> [, ... ];

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

mzsql
REVOKE <USAGE | ALL [PRIVILEGES]> [, ... ]
ON ALL SECRET [IN DATABASE <name> [, <name> ...]]
FROM <role_name> [, ... ];

{{</ tab >}}

<!-- ==================== System syntax ===================== -->

{{< tab "System">}}

mzsql
REVOKE <CREATEROLE | CREATEDB | CREATECLUSTER | CREATENETWORKPOLICY | ALL [PRIVILEGES]> [, ... ]
ON SYSTEM
FROM <role_name> [, ... ];

{{</ tab >}}

<!-- ==================== Type syntax ======================= -->

{{< tab "Type">}}

For specific view(s):

mzsql
REVOKE <USAGE | ALL [PRIVILEGES]>
ON TYPE <name> [, <name> ...]
FROM <role_name> [, ... ];

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

mzsql
REVOKE <USAGE | ALL [PRIVILEGES]>
ON ALL TYPES
  [ IN <SCHEMA|DATABASE> <name> [, <name> ...] ]
FROM <role_name> [, ... ];

{{</ tab >}}

<!-- ======================= Table syntax ===================== -->

{{< tab "Table">}}

For specific table(s):

mzsql
REVOKE <SELECT | INSERT | UPDATE | DELETE | ALL [PRIVILEGES]> [, ...]
ON [TABLE] <name> [, <name> ...]
FROM <role_name> [, ... ];

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

{{< note >}}

{{% include-headless "/headless/rbac-cloud/grant-privilege-all-tables" %}}

{{</ note >}}

mzsql
REVOKE <SELECT | INSERT | UPDATE | DELETE | ALL [PRIVILEGES]> [, ...]
ON ALL TABLES
  [ IN <SCHEMA|DATABASE> <name> [, <name> ...] ]
FROM <role_name> [, ... ];

{{</ tab >}}

{{</ tabs >}}

Details

Applicable privileges to revoke

{{< tabs >}} {{< tab "By Privilege" >}} {{< yaml-table data="rbac/privileges_objects" >}} {{</ tab >}} {{< tab "By Object" >}} {{< yaml-table data="rbac/object_privileges" >}} {{</ tab >}} {{</ tabs >}}

Privileges

The privileges required to execute this statement are:

{{% include-headless "/headless/sql-command-privileges/revoke-privilege" %}}

Examples

mzsql
REVOKE SELECT ON mv FROM joe, mike;
mzsql
REVOKE USAGE, CREATE ON DATABASE materialize FROM joe;
mzsql
REVOKE ALL ON CLUSTER dev FROM joe;
mzsql
REVOKE CREATEDB ON SYSTEM FROM joe;

Useful views