REVOKE <privileges>¶

Removes one or more privileges on a securable object from a role or database role. The privileges that can be revoked are object-specific.

Roles:

The privileges that can be revoked from roles are grouped into the following categories:

Global privileges
Privileges for account objects (resource monitors, virtual warehouses, and databases)
Privileges for schemas
Privileges for schema objects (tables, views, stages, file formats, UDFs, and sequences)

Database roles:

The privileges that can be revoked from database roles are grouped into the following categories:

Privileges for the database that contains the database role.
Privileges for schemas in the database that contains the database role.
Privileges for schema objects (tables, views, stages, file formats, UDFs, and sequences) in the database that contains the database role.

See also:

GRANT <privileges> , GRANT OWNERSHIP

REVOKE <privilege> … FROM SHARE

Syntax¶

Account roles:

REVOKE [ GRANT OPTION FOR ]
    {
       { globalPrivileges         | ALL [ PRIVILEGES ] } ON ACCOUNT
     | { accountObjectPrivileges  | ALL [ PRIVILEGES ] } ON { RESOURCE MONITOR | WAREHOUSE | COMPUTE POOL | DATABASE | INTEGRATION | CONNECTION | FAILOVER GROUP | REPLICATION GROUP | EXTERNAL VOLUME } <object_name>
     | { schemaPrivileges         | ALL [ PRIVILEGES ] } ON { SCHEMA <schema_name> | ALL SCHEMAS IN DATABASE <db_name> }
     | { schemaPrivileges         | ALL [ PRIVILEGES ] } ON { FUTURE SCHEMAS IN DATABASE <db_name> }
     | { schemaObjectPrivileges   | ALL [ PRIVILEGES ] } ON { <object_type> <object_name> | ALL <object_type_plural> IN SCHEMA <schema_name> }
     | { schemaObjectPrivileges   | ALL [ PRIVILEGES ] } ON FUTURE <object_type_plural> IN { DATABASE <db_name> | SCHEMA <schema_name> }
    }
  FROM [ ROLE ] <role_name> [ RESTRICT | CASCADE ]

Copy

Database roles:

REVOKE [ GRANT OPTION FOR ]
    {
       { CREATE SCHEMA | MODIFY | MONITOR | USAGE } [ , ... ] } ON DATABASE <object_name>
       { globalPrivileges         | ALL [ PRIVILEGES ] } ON ACCOUNT
     | { accountObjectPrivileges  | ALL [ PRIVILEGES ] } ON { RESOURCE MONITOR | WAREHOUSE | COMPUTE POOL | DATABASE | INTEGRATION | EXTERNAL VOLUME } <object_name>
     | { schemaPrivileges         | ALL [ PRIVILEGES ] } ON { SCHEMA <schema_name> | ALL SCHEMAS IN DATABASE <db_name> }
     | { schemaPrivileges         | ALL [ PRIVILEGES ] } ON { FUTURE SCHEMAS IN DATABASE <db_name> }
     | { schemaObjectPrivileges   | ALL [ PRIVILEGES ] } ON { <object_type> <object_name> | ALL <object_type_plural> IN SCHEMA <schema_name> }
     | { schemaObjectPrivileges   | ALL [ PRIVILEGES ] } ON FUTURE <object_type_plural> IN { DATABASE <db_name> | SCHEMA <schema_name> }
    }
  FROM DATABASE ROLE <database_role_name> [ RESTRICT | CASCADE ]

Copy

Where:

globalPrivileges ::=
  {
      CREATE {
          ACCOUNT | APPLICATION | APPLICATION PACKAGE | COMPUTE POOL | DATA EXCHANGE LISTING
          | DATABASE | FAILOVER GROUP | INTEGRATION | NETWORK POLICY | EXTERNAL VOLUME
          | REPLICATION GROUP | ROLE | SHARE
       | USER | WAREHOUSE
      }
      | ATTACH POLICY | AUDIT | BIND SERVICE ENDPOINT
      | APPLY {
         { AGGREGATION | AUTHENTICATION | MASKING | PACKAGES | PASSWORD
           | PROJECTION | ROW ACCESS | SESSION } POLICY
         | TAG }
      | EXECUTE { ALERT | DATA METRIC FUNCTION | MANAGED ALERT | MANAGED TASK | TASK }
      | IMPORT SHARE
      | MANAGE { ACCOUNT SUPPORT CASES | EVENT SHARING | GRANTS | LISTING AUTO FULFILLMENT | ORGANIZATION SUPPORT CASES | USER SUPPORT CASES | WAREHOUSES }
      | MODIFY { LOG LEVEL | TRACE LEVEL | SESSION LOG LEVEL | SESSION TRACE LEVEL }
      | MONITOR { EXECUTION | SECURITY | USAGE }
      | OVERRIDE SHARE RESTRICTIONS | PURCHASE DATA EXCHANGE LISTING | RESOLVE ALL
      | READ SESSION
  }
  [ , ... ]

Copy

accountObjectPrivileges ::=
-- For COMPUTE POOL
   { MODIFY | MONITOR | OPERATE | USAGE } [ , ... ]
-- For CONNECTION
   { FAILOVER } [ , ... ]
-- For DATABASE
   { APPLYBUDGET | CREATE { DATABASE ROLE | SCHEMA }
   | IMPORTED PRIVILEGES | MODIFY | MONITOR | USAGE } [ , ... ]
-- For EXTERNAL VOLUME
   { USAGE } [ , ... ]
-- For FAILOVER GROUP
   { FAILOVER | MODIFY | MONITOR | REPLICATE } [ , ... ]
-- For INTEGRATION
   { USAGE | USE_ANY_ROLE } [ , ... ]
-- For REPLICATION GROUP
   { MODIFY | MONITOR | REPLICATE } [ , ... ]
-- For RESOURCE MONITOR
   { MODIFY | MONITOR } [ , ... ]
-- For USER
   { MONITOR } [ , ... ]
-- For WAREHOUSE
   { APPLYBUDGET | MODIFY | MONITOR | USAGE | OPERATE } [ , ... ]

Copy

schemaPrivileges ::=

    ADD SEARCH OPTIMIZATION | APPLYBUDGET
  | CREATE {
        ALERT | CORTEX SEARCH SERVICE | DATA METRIC FUNCTION | DATASET
      | EVENT TABLE | FILE FORMAT | FUNCTION
      | { GIT | IMAGE } REPOSITORY
      | MODEL | NETWORK RULE | NOTEBOOK | PIPE | PROCEDURE
      | { AGGREGATION | AUTHENTICATION | MASKING | PACKAGES
         | PASSWORD | PRIVACY | PROJECTION | ROW ACCESS | SESSION } POLICY
      | SECRET | SEQUENCE | SERVICE | SNAPSHOT | STAGE | STREAM | STREAMLIT
      | SNOWFLAKE.CORE.BUDGET
      | SNOWFLAKE.DATA_PRIVACY.CUSTOM_CLASSIFIER
      | SNOWFLAKE.ML.ANOMALY_DETECTION | SNOWFLAKE.ML.CLASSIFICATION
         | SNOWFLAKE.ML.FORECAST | SNOWFLAKE.ML.TOP_INSIGHTS
      | SNOWFLAKE.ML.DOCUMENT_INTELLIGENCE
      | [ { DYNAMIC | EXTERNAL | ICEBERG } ] TABLE
      | TAG | TASK | [ MATERIALIZED ] VIEW
      }
   | MODIFY | MONITOR | USAGE
   [ , ... ]

Copy

schemaObjectPrivileges ::=
  -- For ALERT
     { MONITOR | OPERATE } [ , ... ]
 -- For APPLICATION PACKAGE
     { ATTACH LISTING | DEVELOP | INSTALL | MANAGE VERSIONS | MANAGE RELEASES } [ , ... ]
  -- For DATA METRIC FUNCTION
     USAGE [ , ... ]
  -- For DYNAMIC TABLE
     MONITOR, OPERATE, SELECT [ , ...]
  -- For EVENT TABLE
     { APPLYBUDGET | DELETE | OWNERSHIP | REFERENCES | SELECT | TRUNCATE } [ , ... ]
  -- For FILE FORMAT, FUNCTION (UDF or external function), MODEL, PROCEDURE, SECRET, SEQUENCE, or SNAPSHOT
     USAGE [ , ... ]
  -- For GIT REPOSITORY
     { READ, WRITE } [ , ... ]
  -- For HYBRID TABLE
     { APPLYBUDGET | DELETE | INSERT | REFERENCES | SELECT | TRUNCATE | UPDATE } [ , ... ]
  -- For IMAGE REPOSITORY
     { READ, WRITE } [ , ... ]
  -- For ICEBERG TABLE
     { APPLYBUDGET | DELETE | INSERT | REFERENCES | SELECT | TRUNCATE | UPDATE } [ , ... ]
  -- For PIPE
     { APPLYBUDGET | MONITOR | OPERATE } [ , ... ]
  -- For { AGGREGATION | AUTHENTICATION | MASKING | PACKAGES | PASSWORD | PRIVACY | PROJECTION | ROW ACCESS | SESSION } POLICY or TAG
     APPLY [ , ... ]
  -- For SECRET
     READ, USAGE [ , ... ]
  -- For SERVICE
     { MONITOR | OPERATE } [ , ... ]
  -- For external STAGE
     USAGE [ , ... ]
  -- For internal STAGE
     READ [ , WRITE ] [ , ... ]
  -- For STREAM
     SELECT [ , ... ]
  -- For STREAMLIT
     USAGE [ , ... ]
  -- For TABLE
     { APPLYBUDGET | DELETE | EVOLVE SCHEMA | INSERT | REFERENCES | SELECT | TRUNCATE | UPDATE } [ , ... ]
  -- For TAG
     READ
  -- For TASK
     { APPLYBUDGET | MONITOR | OPERATE } [ , ... ]
  -- For VIEW
     { REFERENCES | SELECT } [ , ... ]
  -- For MATERIALIZED VIEW
     { APPLYBUDGET | REFERENCES | SELECT } [ , ... ]

Copy

For more details about the privileges supported for each object type, see Access control privileges.

Required parameters¶

object_name: Specifies the identifier for the object on which the privileges are revoked.

object_type

Specifies the type of object for schema-level objects.

AGGREGATION POLICY
ALERT
AUTHENTICATION POLICY
CORTEX SEARCH SERVICE
DATA METRIC FUNCTION
DYNAMIC TABLE
EVENT TABLE
EXTERNAL TABLE
FILE FORMAT
FUNCTION
GIT REPOSITORY
HYBRID TABLE
IMAGE REPOSITORY
ICEBERG TABLE
MASKING POLICY
MATERIALIZED VIEW
MODEL
NETWORK RULE
NOTEBOOK
PACKAGES POLICY
PASSWORD POLICY
PIPE
PRIVACY POLICY
PROCEDURE
PROJECTION POLICY
ROW ACCESS POLICY
SECRET
SERVICE
SESSION POLICY
SEQUENCE
SNAPSHOT
STAGE
STREAM
STREAMLIT
TABLE
TAG
TASK
VIEW

object_type_plural: Plural form of object_type (e.g. TABLES, VIEWS).
role_name: Specifies the identifier for the recipient role (i.e. the role from which the privileges are revoked).
database_role_name: Specifies the identifier for the recipient database role (i.e. the role from which the privileges are revoked). If the identifier is not fully qualified (in the form of db_name.database_role_name, the command looks for the database role in the current database for the session.

Optional parameters¶

GRANT OPTION FOR

If specified, removes the ability for the recipient role to grant the privileges to another role.

Default: No value

ON FUTURE

If specified, only removes privileges granted on new (i.e. future) schema objects of a specified type (e.g. tables or views) rather than existing objects. Note that any privileges granted on existing objects are retained.

RESTRICT | CASCADE

If specified, determines whether the revoke operation succeeds or fails for the privileges, based on the whether the privileges had been re-granted to another role.

RESTRICT: If the privilege being revoked has been re-granted to another role, the REVOKE command fails.
CASCADE: If the privilege being revoked has been re-granted, the REVOKE command recursively revokes these dependent grants. If the same privilege on an object has been granted to the target role by a different grantor (parallel grant), that grant is not affected and the target role retains the privilege.

Default: RESTRICT

Security requirements¶

Revoking privileges on individual objects:

An active role that meets either of the following criteria, or a higher role, can be used to revoke privileges on an object from other roles:

The role is identified as the grantor of the privilege in the GRANTED_BY column in the SHOW GRANTS output.

If multiple instances of a privilege have been granted on the specified object, only the instances granted by the active grantor role are revoked.
The role has the global MANAGE GRANTS privilege.

If multiple instances of a privilege have been granted on the specified object, all instances are revoked.

Note that only the SECURITYADMIN system role and higher have the MANAGE GRANTS privilege by default; however, the privilege can be granted to custom roles.

In managed access schemas (i.e. schemas created using the CREATE SCHEMA … WITH MANAGED ACCESS syntax), only the schema owner (i.e. the role with the OWNERSHIP privilege on the schema) or a role with the global MANAGE GRANTS privilege, or a higher role, can revoke privileges on objects in the schema.

Revoking grants on future objects of a specified type:

Database level

The global MANAGE GRANTS privilege is required to revoke privileges on future objects in a database. Only the SECURITYADMIN system role and higher have the MANAGE GRANTS privilege; however, the privilege can be granted to custom roles.

Schema level

In managed access schemas (i.e. schemas created using the CREATE SCHEMA … WITH MANAGED ACCESS syntax), either the schema owner (i.e. the role with the OWNERSHIP privilege on the schema) or a role with the global MANAGE GRANTS privilege can revoke privileges on future objects in the schema.

In standard schemas, the global MANAGE GRANTS privilege is required to revoke privileges on future objects in the schema.

Usage notes¶

Privileges cannot be granted or revoked directly on any class. You can, however, create an instance of a class and revoke instance roles from an account role. Revoke the CREATE <class_name> privilege on the schema to prevent a role from creating an instance of a class.
A privilege can be granted to a role multiple times by different grantors. A REVOKE <privilege> statement only revokes grants for which the active role, or a lower role in a hierarchy, is the grantor. Any additional grants of a specified privilege by other grantors are ignored.

Also note that a REVOKE <privilege> statement is successful even if no privileges are revoked. A REVOKE <privilege> statement only returns an error if a specified privilege has dependent grants and the CASCADE clause is omitted in the statement.
Multiple privileges can be specified for the same object type in a single GRANT statement (with each privilege separated by commas), or the special ALL [ PRIVILEGES ] keyword can be used to grant all applicable privileges to the specified object type. Note, however, that only privileges held and grantable by the role executing the GRANT command are actually granted to the target role. A warning message is returned for any privileges that could not be granted.

You cannot specify this keyword for tags.
Privileges granted to a particular role are automatically inherited by any other roles to which the role is granted, as well as any other higher-level roles within the role hierarchy. For more details, see Overview of Access Control.
For databases, the IMPORTED PRIVILEGES privilege only applies to shared databases (i.e. databases created from a share). For more details, see Consume imported data.
For schemas and objects in schemas, an option is provided to grant privileges on all objects of the same type within the container (i.e. database or schema). This is a convenience option; internally, the command is expanded into a series of individual GRANT commands on each object. Only objects that currently exist within the container are affected.

However, note that, in the Snowflake model, bulk granting of privileges is not a recommended practice. Instead, Snowflake recommends creating a shared role and using the role to create objects that are automatically accessible to all users who have been granted the role.
For stages:
- USAGE only applies to external stages.
- READ | WRITE only applies to internal stages. In addition, to grant the WRITE privilege on an internal stage, the READ privilege must first be granted on the stage.
For more details about external and internal stages, see CREATE STAGE.
When granting privileges on an individual UDF, you must specify the data types for the arguments, if any, for the UDF in the form of udf_name ( [ arg_data_type , ... ] ). This is required because Snowflake uses argument data types to resolve UDFs that have the same name within a schema. For an example, see Examples (in this topic). For more details, see User-defined functions overview.
When granting privileges on an individual stored procedure, you must specify the data types for the arguments, if any, for the procedure in the form of procedure_name ( [ arg_data_type , ... ] ). This is required because Snowflake uses argument data types to resolve stored procedures that have the same name within a schema.
Future grants: Revoking future grants only drops grants of privileges for future objects of a specified type. Any privileges granted on existing objects are retained.

For more information, see managed access schemas.

Examples¶

Roles¶

Revoke the necessary privileges to operate (i.e. suspend or resume) the report_wh warehouse from the analyst role:

REVOKE OPERATE ON WAREHOUSE report_wh FROM ROLE analyst;

Copy

Revoke only the grant option for the OPERATE privilege on the report_wh warehouse from the analyst role. The role retains the OPERATE privilege but can no longer grant the OPERATE privilege on the warehouse to other roles:

REVOKE GRANT OPTION FOR OPERATE ON WAREHOUSE report_wh FROM ROLE analyst;

Copy

Revoke the SELECT privilege on all existing tables in the mydb.myschema schema from the analyst role:

REVOKE SELECT ON ALL TABLES IN SCHEMA mydb.myschema from ROLE analyst;

Copy

Revoke all privileges on two UDFs (with the same name in the current schema) from the analyst role:

REVOKE ALL PRIVILEGES ON FUNCTION add5(number) FROM ROLE analyst;

REVOKE ALL PRIVILEGES ON FUNCTION add5(string) FROM ROLE analyst;
Copy
Note that the UDFs have different arguments, which is how Snowflake uniquely identifies UDFs with the same name. For more details about UDF naming, see User-defined functions overview.

Revoke all privileges on two stored procedures (with the same name in the current schema) from the analyst role:

REVOKE ALL PRIVILEGES ON PROCEDURE clean_schema(string) FROM ROLE analyst;

REVOKE ALL PRIVILEGES ON procedure clean_schema(string, string) FROM ROLE analyst;
Copy
Note that the two stored procedures have different arguments, which is how Snowflake uniquely identifies procedures with the same name.

Revoke the SELECT and INSERT privileges granted on all future tables created in the mydb.myschema schema from the role1 role:

REVOKE SELECT,INSERT ON FUTURE TABLES IN SCHEMA mydb.myschema
FROM ROLE role1;

Copy

Database roles¶

Revoke the SELECT privilege on all existing tables in the mydb.myschema schema from the mydb.dr1 database role:

REVOKE SELECT ON ALL TABLES IN SCHEMA mydb.myschema
  FROM DATABASE ROLE mydb.dr1;

Copy

Revoke all privileges on two UDFs (with the same name in the current schema) from the mydb.dr1 database role:

REVOKE ALL PRIVILEGES ON FUNCTION add5(number)
  FROM DATABASE ROLE mydb.dr1;

REVOKE ALL PRIVILEGES ON FUNCTION add5(string)
  FROM DATABASE ROLE mydb.dr1;
Copy
Note that the UDFs have different arguments, which is how Snowflake uniquely identifies UDFs with the same name. For more details about UDF naming, see User-defined functions overview.

Revoke all privileges on two stored procedures (with the same name in the current schema) from the mydb.dr1 database role:

REVOKE ALL PRIVILEGES ON PROCEDURE clean_schema(string)
  FROM DATABASE ROLE mydb.dr1;

REVOKE ALL PRIVILEGES ON procedure clean_schema(string, string)
  FROM DATABASE ROLE mydb.dr1;
Copy
Note that the two stored procedures have different arguments, which is how Snowflake uniquely identifies procedures with the same name.

Revoke the SELECT and INSERT privileges granted on all future tables created in the mydb.myschema schema from the mydb.dr1 database role:

REVOKE SELECT,INSERT ON FUTURE TABLES IN SCHEMA mydb.myschema
  FROM DATABASE ROLE mydb.dr1;

Copy