REVOKE

Introduced or updated: v1.2.845

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

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

See also:

• GRANT
• SHOW GRANTS

Syntax

Revoking Privileges

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

Where:

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

-- For STAGE
  { READ, WRITE }

-- For UDF
  { USAGE }

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

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

Revoking Masking Policy Privileges

REVOKE APPLY ON MASKING POLICY <policy_name> FROM [ ROLE ] <grantee>
REVOKE ALL [ PRIVILEGES ] ON MASKING POLICY <policy_name> FROM [ ROLE ] <grantee>
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 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 User

Create a user:

CREATE USER user1 IDENTIFIED BY 'abc123';

Grant the SELECT,INSERT privilege on all existing tables in the default database to the user user1:

GRANT SELECT,INSERT ON default.* TO user1;

SHOW GRANTS FOR user1;
+---------------------------------------------------+
| Grants                                            |
+---------------------------------------------------+
| GRANT SELECT,INSERT ON 'default'.* TO 'user1'@'%' |
+---------------------------------------------------+

Revoke INSERT privilege from user user1:

REVOKE INSERT ON default.* FROM user1;

SHOW GRANTS FOR user1;
+--------------------------------------------+
| Grants                                     |
+--------------------------------------------+
| GRANT SELECT ON 'default'.* TO 'user1'@'%' |
+--------------------------------------------+

Example 2: Revoking Privileges from a 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;
+-----------------------------------------+
| Grants                                  |
+-----------------------------------------+
| GRANT ALL ON 'default'.* TO 'user1'@'%' |
| GRANT ALL ON *.* TO 'user1'@'%'         |
+-----------------------------------------+

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;