GRANT <privileges> … TO APPLICATION ROLE

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

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

Variations:

GRANT OWNERSHIP , REVOKE <privileges> … FROM APPLICATION ROLE

语法

GRANT {
        { schemaPrivileges         | ALL [ PRIVILEGES ] } ON SCHEMA <schema_name>
        | { schemaObjectPrivileges | ALL [ PRIVILEGES ] } ON { <object_type> <object_name> | ALL <object_type_plural> IN { DATABASE <db_name> | SCHEMA <schema_name> }
        | { schemaObjectPrivileges | ALL [ PRIVILEGES ] } ON FUTURE <object_type_plural> IN SCHEMA <schema_name>
      }
    TO APPLICATION ROLE <name> [ WITH GRANT OPTION ]

其中：

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 { AGGREGATION | MASKING | PACKAGES | PASSWORD | PROJECTION | 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

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

可选参数

ON FUTURE

Specifies that privileges are granted on new (i.e. future) schema objects of a specified type rather than existing objects. Future grants can be revoked at any time using REVOKE <privileges> … FROM APPLICATION ROLE with the ON FUTURE keywords; any privileges granted on existing objects are retained. For more information about future grants, see Future Grants on Schema Objects in this topic.

WITH GRANT OPTION

如果指定，则允许收件人应用程序角色向其他应用程序角色授予权限。

默认：无值，这意味着收件人应用程序角色无法向其他应用程序角色授予权限。

Note

The WITH GRANT OPTION clause does not support the IMPORTED PRIVILEGES privilege. For more information, refer to Granting privileges on an imported database.

使用说明

You must use an application role to grant and revoke privileges on objects in an application.

This command has different restrictions depending on whether you are the application provider or consumer.

The application consumer cannot do the following with respect to an application role:

  • Grant or revoke object privileges with respect to an application role.
  • Grant an application role to a database or share, or revoke an application role from a database or share.
  • Grant an application role to same application or a different application, or revoke an application role from the same application or a different application.

These items apply the application provider with respect to an application role.

  • To grant the OWNERSHIP privilege on an object or all objects of a specified type in a schema to an application role, transferring ownership of the object from one application role to another application role, use the GRANT OWNERSHIP command.
  • 可以在单个 GRANT 语句中为同一对象类型指定多个权限，每个权限用逗号分隔。

然而，只有由执行 GRANT 命令的应用程序角色持有和可授权的权限才被实际授权给目标应用程序角色。对于任何无法授予的权限，将返回一条警告消息。

  • Privileges granted to a particular application role are automatically inherited by any other application roles to which the application role is granted, as well as any other higher-level application roles within the role hierarchy.

    For more details, see Overview of Access Control.

  • 在托管访问架构中：

    • 对对象的 OWNERSHIP 权限只能转让给架构所有者的下级角色。
    • 对于暂存区：
      • USAGE 仅适用于外部暂存区。
      • 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, refer to CREATE STAGE and Access Control Requirements (in this topic).

  • When granting privileges on an individual UDF or stored procedure, you must specify the data types of the arguments, if any, using the syntax shown below:

    <udf_or_stored_procedure_name> ( [ <arg_data_type> [ , ... ] ] )

    Snowflake uses argument data types to resolve UDFs and stored procedures that have the same name within a schema. For more information, refer Overloading procedures and functions.

访问控制要求

  • 此命令只能在应用程序内部执行。

  • Privileges can only be granted or revoked on objects owned by the application. To determine these objects, use the SHOW OBJECTS command:

    SHOW OBJECTS OWNED BY APPLICATION myapp;

  • 关于托管访问架构：

    • In managed access schemas (i.e. schemas created using the CREATE SCHEMA … WITH MANAGED ACCESS syntax), object owners lose the ability to make grant decisions.

以下角色可以授予对托管访问架构中对象的权限：

  • 应用程序角色，因为该角色是架构所有者（即拥有架构 OWNERSHIP 权限的角色）。
  • 继承应用程序角色的角色。
  • 具有全局 MANAGE GRANTS 权限的角色可以授予架构中对象的权限。

拥有全局 MANAGE GRANTS 权限的角色可以授予当前（授予者）角色附加权限。

对架构对象的未来授权

当对架构中的对象分配未来授权时（即使用 ON FUTURE 关键字时），这些部分中的备注适用。

注意事项

  • When future grants are defined on the same object type for a schema, the schema-level grants take precedence over the database level grants, and the database level grants are ignored. This behavior applies to privileges on future objects granted to one application role or different application roles.

约束与限制

  • 最多允许一次对每种安全对象类型的 OWNERSHIP 权限的未来授权。
  • Future grants cannot be defined on objects of the following types:

    • Compute pool

    • External function

    • Image repository

    • Organization profile

    • Policy objects:

      • Aggregation policy
      • Join policy
      • Masking policy
      • Packages policy
      • Projection policy
      • Row access policy
      • Session policy
      • Storage lifecycle policy

    • Snapshot

    • Tag

  • A future grant of the OWNERSHIP privilege on objects of a specified type in a database do not apply to new objects in a managed access schema.

  • 以下限制适用于对托管访问架构中对象的未来授权：

    • A future grant of the OWNERSHIP privilege on objects can only be applied to a subordinate role of the schema owner (i.e. the role that has the OWNERSHIP privilege on the schema).
    • Before ownership of a managed access schema can be transferred to a different role, all open future grants of the OWNERSHIP privilege must be revoked using REVOKE <privileges> … FROM ROLE with the ON FUTURE keywords.

  • 重命名或交换表时，未来授权不适用。

  • 支持对指定暂存区的未来授权，但有以下限制：

    • 如果没有 READ 权限，则无法指定 WRITE 权限。
    • 如果 READ 权限存在，则无法撤销 WRITE 权限。
    • 对于内部暂存区，只有具有 READ 或 WRITE 权限的未来授权才能物化。
    • 对于外部暂存区，只有具有 USAGE 权限的未来授权才能物化。

  • In a managed access schema, the application role and a role with the global MANAGE GRANTS privilege can grant privileges on future objects in the managed access schema.

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

示例

将对视图的 SELECT 权限授予应用程序角色：

GRANT SELECT ON VIEW data.views.credit_usage
  TO APPLICATION ROLE app_snowflake_credits;