Categories:

User & Security DDL (Access Control)

REVOKE <privileges> … FROM ROLE

Removes one or more privileges on a securable object from a role. The privileges that can be revoked are object-specific and 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).

For more details about roles and securable objects, see Access Control in Snowflake.

See also:

GRANT <privileges> … TO ROLE , GRANT OWNERSHIP

REVOKE <privilege> … FROM SHARE

In this Topic:

Syntax

REVOKE [ GRANT OPTION FOR ]
    {
       { globalPrivileges         | ALL [ PRIVILEGES ] } ON ACCOUNT
     | { accountObjectPrivileges  | ALL [ PRIVILEGES ] } ON { RESOURCE MONITOR | WAREHOUSE | DATABASE | INTEGRATION } <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 ]

Where:

globalPrivileges ::=
  { { CREATE { ROLE | USER | WAREHOUSE | DATABASE | INTEGRATION } } | APPLY MASKING POLICY | EXECUTE TASK | MANAGE GRANTS | MONITOR { EXECUTION | USAGE }  } [ , ... ]
accountObjectPrivileges ::=
-- For RESOURCE MONITOR
  { MODIFY | MONITOR } [ , ... ]
-- For WAREHOUSE
  { MODIFY | MONITOR | USAGE | OPERATE } [ , ... ]
-- For DATABASE
  { MODIFY | MONITOR | USAGE | CREATE SCHEMA | IMPORTED PRIVILEGES } [ , ... ]
-- For INTEGRATION
  { USAGE | USE_ANY_ROLE } [ , ... ]
schemaPrivileges ::=
  { MODIFY | MONITOR | USAGE | CREATE { TABLE | EXTERNAL TABLE | VIEW | MATERIALIZED VIEW | MASKING POLICY | FILE FORMAT | STAGE | PIPE | STREAM | TASK | FUNCTION | PROCEDURE | SEQUENCE } } [ , ... ]
schemaObjectPrivileges ::=
-- For TABLE
  { SELECT | INSERT | UPDATE | DELETE | TRUNCATE | REFERENCES } [ , ... ]
-- For VIEW
    SELECT
-- For MATERIALIZED VIEW
    SELECT
-- For internal STAGE
    READ [ , WRITE ]
-- For external STAGE
    USAGE
-- For FILE FORMAT, PROCEDURE, FUNCTION (UDF or external function), or SEQUENCE
    USAGE
-- For STREAM
    SELECT
-- For TASK
   { MONITOR | OPERATE } [ , ... ]
-- For MASKING POLICY
    APPLY

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 objects):

TABLE | EXTERNAL TABLE | VIEW | MATERIALIZED VIEW | STAGE | FILE FORMAT | FUNCTION | PROCEDURE | SEQUENCE | STREAM | TASK

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).

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

Granting privileges on individual objects

In general, either an object owner (i.e. the role with the OWNERSHIP privilege on the object) or a role with the global MANAGE GRANTS privilege can revoke privileges on the object from other roles.

In managed access schemas (i.e. schemas created using the CREATE SCHEMA … WITH MANAGED ACCESS syntax), object owners lose the ability to make grant and revoke decisions. Only 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 objects in the schema.

Defining grants on future objects of a specified type

Database level

The global MANAGE GRANTS privilege is required to grant or revoke privileges on future objects in a database. Only the SECURITYADMIN and ACCOUNTADMIN system roles 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), only the schema owner (i.e. the role with the OWNERSHIP privilege on the schema) can grant or revoke privileges on future objects in the schema.

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

Usage Notes

  • 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.

  • 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 Access Control in Snowflake.

  • For databases, the IMPORTED PRIVILEGES privilege only applies to shared databases (i.e. databases created from a share). For more details, see Data Consumers.

  • 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 Overview of UDFs.

  • 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.

Examples

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;

Same as previous example, but also disallow the analyst role to grant the privilege to other roles:

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

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;

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;

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 Overview of UDFs.

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;

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;