REVOKE <privileges> … FROM APPLICATION ROLE¶

撤消应用程序角色对安全架构级对象的一项或多项访问权限。可撤消的权限是特定于对象的。

For more details about roles and securable objects, see Overview of Access Control.

Variations:

GRANT OWNERSHIP , GRANT <privileges> … TO APPLICATION ROLE

语法

账户角色：

REVOKE [ GRANT OPTION FOR ]
    {
    | { 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 APPLICATION ROLE <name> [ RESTRICT | CASCADE ]

其中：

schemaPrivileges ::=
  {
    ADD SEARCH OPTIMIZATION
    | CREATE {
        ALERT | EXTERNAL TABLE | FILE FORMAT | FUNCTION
        | IMAGE REPOSITORY | MATERIALIZED VIEW | PIPE | PROCEDURE
        | { AGGREGATION | MASKING | PASSWORD | PROJECTION | ROW ACCESS | SESSION } POLICY
        | SECRET | SEMANTIC VIEW | SEQUENCE | SERVICE | SNAPSHOT | STAGE | STREAM
        | TAG | TABLE | TASK | VIEW
      }
    | MODIFY | MONITOR | USAGE
  }
  [ , ... ]

schemaObjectPrivileges ::=
  -- For ALERT
     { MONITOR | OPERATE } [ , ... ]
  -- For DYNAMIC TABLE
     OPERATE, SELECT [ , ...]
  -- For EVENT TABLE
     { INSERT | SELECT } [ , ... ]
  -- For FILE FORMAT, FUNCTION (UDF or external function), PROCEDURE, SECRET, SEQUENCE, SNAPSHOT, or TYPE
     USAGE [ , ... ]
  -- For IMAGE REPOSITORY
     { READ, WRITE } [ , ... ]
  -- For MATERIALIZED VIEW
     { APPLYBUDGET | REFERENCES | SELECT } [ , ... ]
  -- For PIPE
     { APPLYBUDGET | MONITOR | OPERATE } [ , ... ]
  -- For { MASKING | PACKAGES | PASSWORD | ROW ACCESS | SESSION } POLICY or TAG
     APPLY [ , ... ]
  -- For SECRET
     READ, USAGE [ , ... ]
  -- For SEMANTIC VIEW
     REFERENCES [ , ... ]
  -- For SERVICE
     { MONITOR | OPERATE } [ , ... ]
  -- For external STAGE
     USAGE [ , ... ]
  -- For internal STAGE
     READ [ , WRITE ] [ , ... ]
  -- For STREAM
     SELECT [ , ... ]
  -- For TABLE
     { APPLYBUDGET | DELETE | EVOLVE SCHEMA | INSERT | REFERENCES | SELECT | TRUNCATE | UPDATE } [ , ... ]
  -- For TAG
     READ
  -- For TASK
     { APPLYBUDGET | MONITOR | OPERATE } [ , ... ]
  -- For VIEW
     { REFERENCES | SELECT } [ , ... ]

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

必填参数

object_name

为授予权限的对象指定标识符。

object_type

为架构级对象指定对象的类型。

• ALERT
• DYNAMIC TABLE
• EVENT TABLE
• EXTERNAL TABLE
• FILE FORMAT
• FUNCTION
• MASKING POLICY
• MATERIALIZED VIEW
• NETWORK RULE
• PACKAGES POLICY
• PASSWORD POLICY
• PIPE
• PROCEDURE
• ROW ACCESS POLICY
• SECRET
• SEMANTIC VIEW
• SESSION POLICY
• SEQUENCE
• STAGE
• STREAM
• TABLE
• TAG
• TASK
• TYPE
• VIEW

object_type_plural

Plural form of object_type (e.g. TABLES, VIEWS).

请注意，不允许对管道进行批量授权。

name

指定收件人应用程序角色（即授予权限的角色）的标识符。

可选参数

FUTURE

如果指定此参数，则仅移除对指定类型（例如表或视图）的新（即未来）架构对象的已授予权限，而不移除对现有对象的已授予权限。请注意，所有对现有对象的已授予权限都将保留。

RESTRICT | CASCADE

如果指定此参数，则会根据权限是否已重新授予其他应用程序角色，来确定撤消权限的操作是成功还是失败。

RESTRICT

如果撤消的权限已重新授予其他应用程序角色，则 REVOKE 命令失败。

CASCADE

如果撤消的权限已被重新授予，则 REVOKE 命令以递归方式撤消这些依赖性的授予。如果另一个授予者已将对某个对象的相同权限授予目标角色（并行授予），则该授予不受影响，并且目标角色将保留该权限。

Default: RESTRICT

安全要求

Revoking privileges on individual objects:

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

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

如果授予了对指定对象的多个权限实例，则仅会撤消由活跃授予者角色授予的实例。

• 该角色具有全局 MANAGE GRANTS 权限。

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

请注意，默认情况下，只有 SECURITYADMIN 系统角色及更高级别的角色才具有 MANAGE GRANTS 权限；不过，可以将该权限授予自定义角色。

以下角色可以撤消托管访问架构（即使用 CREATE SCHEMA …WITH MANAGED ACCESS 语法创建的架构）构中对象的权限：

• The application role because this role is the schema owner (i.e. has the OWNERSHIP privilege on the schema). (i.e. the role with the OWNERSHIP privilege on the schema)
• 具有全局 MANAGE GRANTS 权限的角色。

Revoking grants on future objects of a specified type:

在托管访问架构中，应用程序角色或具有全局 MANAGE GRANTS 权限的角色都可以撤消对架构中未来对象的权限。

在标准架构中，需要全局 MANAGE GRANTS 权限才能撤消对架构中未来对象的权限。

使用说明

• A privilege can be granted to an application 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.

  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.

• When revoking 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 more details, refer User-defined functions overview.

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

示例

撤消应用程序角色对视图的 SELECT 权限：

REVOKE SELECT ON VIEW data.views.credit_usage
  FROM APPLICATION ROLE app_snowflake_credits;