REVOKE

Introduced or updated: v1.2.955

Revokes privileges, roles, and ownership of a specific database object. This includes:

• Revoking privileges from roles.
• Removing roles from users or other roles.

See also:

• GRANT
• SHOW GRANTS
• REVOKE FROM SHARE for revoking a Share's provider database/table grants.

After changing privileges or roles with REVOKE, run SYSTEM FLUSH PRIVILEGES to broadcast the updates to every query node immediately.

Syntax

Revoking Privileges

REVOKE { 
        schemaObjectPrivileges | ALL [ PRIVILEGES ] ON <privileges_level>
       }
FROM ROLE <role_name>

Where:

schemaObjectPrivileges ::=
-- For TABLE
  { SELECT | INSERT }
  
-- For SCHEMA
  { CREATE | DROP | ALTER }

-- For database or global scope only
  { ATTACH TABLE }
  
-- For USER
  { CREATE USER }
  
-- For ROLE
  { CREATE ROLE}

-- For STAGE
  { READ, WRITE }

-- For UDF
  { USAGE }

-- For INTEGRATION
  { USAGE }

-- For MASKING POLICY (account-level privileges)
  { CREATE MASKING POLICY | APPLY MASKING POLICY }

-- For ROW ACCESS POLICY (account-level privileges)
  { CREATE ROW ACCESS POLICY | APPLY ROW ACCESS POLICY }

privileges_level ::=
    *.*
  | db_name.*
  | db_name.tbl_name
  | STAGE <stage_name>
  | UDF <udf_name>
  | INTEGRATION <integration_name>
  | MASKING POLICY <policy_name>
  | ROW ACCESS POLICY <policy_name>

Revoking Materialized View Privileges

Introduced or updated: Unreleased

REVOKE SELECT ON MATERIALIZED VIEW [<database_name>.]<view_name> FROM ROLE <role_name>;
REVOKE ALL [PRIVILEGES] ON MATERIALIZED VIEW [<database_name>.]<view_name> FROM ROLE <role_name>;

Use the explicit MATERIALIZED VIEW object type and an existing materialized view name. Bare table syntax, regular views, and wildcard targets are rejected. ALL removes all privileges on this object, including legacy non-SELECT grants from older versions. It does not remove database-level, global, or other-role grants that can still authorize reads. To remove all read access, check those effective grants as well. See Materialized Views.

Revoking Integration Privileges

REVOKE USAGE ON INTEGRATION <integration_name> FROM ROLE <role_name>;
REVOKE ALL [ PRIVILEGES ] ON INTEGRATION <integration_name> FROM ROLE <role_name>;

This removes permission to reference the integration in future UDF definitions. It does not disable existing UDFs or revoke UDF invocation rights. To block existing integration-bound calls, disable the integration or change its rules. Policy changes propagate asynchronously.

Revoke unwanted integration grants before dropping the object. Grants remain name-based after a drop. See External Access Integrations.

Revoking ATTACH TABLE Privileges

Revoke ATTACH TABLE from a role or user at the scope where it was granted:

REVOKE ATTACH TABLE ON <db_name>.* FROM ROLE <role_name>;
REVOKE ATTACH TABLE ON *.* FROM ROLE <role_name>;
REVOKE ATTACH TABLE ON <db_name>.* FROM USER <user_name>;
REVOKE ATTACH TABLE ON *.* FROM USER <user_name>;

Table-level revocation is not supported. Revoking this privilege does not revoke CREATE or remove existing attached tables. Attaching another table fails if the user no longer has an applicable ATTACH TABLE grant, even if the user still has CREATE, SUPER, or database ownership.

Revoking Masking Policy Privileges

REVOKE APPLY ON MASKING POLICY <policy_name> FROM ROLE <role_name>
REVOKE ALL [ PRIVILEGES ] ON MASKING POLICY <policy_name> FROM ROLE <role_name>
REVOKE OWNERSHIP ON MASKING POLICY <policy_name> FROM ROLE '<role_name>'

Use these forms to remove access to individual masking policies. Global CREATE MASKING POLICY and APPLY MASKING POLICY privileges are revoked using the standard syntax with ON *.*.

Revoking Row Access Policy Privileges

REVOKE APPLY ON ROW ACCESS POLICY <policy_name> FROM ROLE <role_name>
REVOKE ALL [ PRIVILEGES ] ON ROW ACCESS POLICY <policy_name> FROM ROLE <role_name>
REVOKE OWNERSHIP ON ROW ACCESS POLICY <policy_name> FROM ROLE '<role_name>'

Use these forms to revoke access to specific row access policies. Revoke global CREATE ROW ACCESS POLICY and APPLY ROW ACCESS POLICY privileges with the standard syntax against ON *.*.

Revoking Role

-- Revoke a role from a user
REVOKE ROLE <role_name> FROM <user_name>

-- Revoke a role from a role
REVOKE ROLE <role_name> FROM ROLE <role_name>

Examples

Example 1: Revoking Privileges from a Role

Create a role:

CREATE ROLE user1_role;

Grant the SELECT,INSERT privilege on all existing tables in the default database to the role user1_role:

GRANT SELECT,INSERT ON default.* TO ROLE user1_role;

SHOW GRANTS FOR ROLE user1_role;
+---------------------------------------------------------+
| Grants                                                  |
+---------------------------------------------------------+
| GRANT SELECT,INSERT ON 'default'.* TO ROLE 'user1_role' |
+---------------------------------------------------------+

Revoke INSERT privilege from role user1_role:

REVOKE INSERT ON default.* FROM ROLE user1_role;

SHOW GRANTS FOR ROLE user1_role;
+---------------------------------------------------+
| Grants                                            |
+---------------------------------------------------+
| GRANT SELECT ON 'default'.* TO 'user1_role'       |
+---------------------------------------------------+

Example 2: Revoking Privileges from Another Role

Grant the SELECT,INSERT privilege on all existing tables in the mydb database to the role role1:

Create role:

CREATE ROLE role1;

Grant privileges to the role:

GRANT SELECT,INSERT ON mydb.* TO ROLE role1;

Show the grants for the role:

SHOW GRANTS FOR ROLE role1;
+--------------------------------------------+
| Grants                                     |
+--------------------------------------------+
| GRANT SELECT,INSERT ON 'mydb'.* TO 'role1' |
+--------------------------------------------+

Revoke INSERT privilege from role role1:

REVOKE INSERT ON mydb.* FROM ROLE role1;

SHOW GRANTS FOR ROLE role1;
+-------------------------------------+
| Grants                              |
+-------------------------------------+
| GRANT SELECT ON 'mydb'.* TO 'role1' |
+-------------------------------------+

Example 3: Revoking a Role from a User

REVOKE ROLE role1 FROM USER user1;

SHOW GRANTS FOR user1;

:::tip Why does default_role still show after revoke? default_role is a user property, not a grant. REVOKE removes role membership but won't reset default_role. Here's what that looks like:

CREATE ROLE analyst;
CREATE USER bob IDENTIFIED BY 'password123' WITH DEFAULT_ROLE = 'analyst';
GRANT ROLE analyst TO bob;

DESC USER bob;
+------+----------+----------------------+--------------+---------+
| name | hostname | auth_type            | default_role | roles   |
+------+----------+----------------------+--------------+---------+
| bob  | %        | double_sha1_password | analyst      | analyst |
+------+----------+----------------------+--------------+---------+

REVOKE ROLE analyst FROM bob;

DESC USER bob;
+------+----------+----------------------+--------------+-------+
| name | hostname | auth_type            | default_role | roles |
+------+----------+----------------------+--------------+-------+
| bob  | %        | double_sha1_password | analyst      |       |
+------+----------+----------------------+--------------+-------+

Notice roles is now empty but default_role stays. The user no longer has analyst's privileges. To clean up, run ALTER USER bob WITH DEFAULT_ROLE = 'public'. :::

Example 4: Revoking Masking Policy Privileges

-- Remove per-policy access from a role
REVOKE APPLY ON MASKING POLICY email_mask FROM ROLE pii_readers;

-- Revoke the ability to create masking policies at the account level
REVOKE CREATE MASKING POLICY ON *.* FROM ROLE security_admin;

Example 5: Revoking Row Access Policy Privileges

-- Remove per-policy access from a role
REVOKE APPLY ON ROW ACCESS POLICY rap_region FROM ROLE apac_only;

-- Revoke the ability to create row access policies globally
REVOKE CREATE ROW ACCESS POLICY ON *.* FROM ROLE row_policy_admin;