GRANT <privileges> … TO APPLICATION ROLE¶

Syntax¶

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 ]

Where:

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 | 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, or SNAPSHOT
     USAGE [ , ... ]
  -- For IMAGE REPOSITORY
     { READ, WRITE } [ , ... ]
  -- For PIPE
     { APPLYBUDGET | MONITOR | OPERATE } [ , ... ]
  -- For { AGGREGATION | MASKING | PACKAGES | PASSWORD | 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 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 } [ , ... ]

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

object_type

Specifies the type of object for schema-level objects.

• 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

• SESSION POLICY

• SEQUENCE

• STAGE

• STREAM

• TABLE

• TAG

• TASK

• VIEW

object_type_plural

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

Note that bulk grants on pipes are not allowed.

name

Specifies the identifier for the recipient application role (i.e. the role to which the privileges are granted).

Optional parameters¶

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

If specified, allows the recipient application role to grant the privileges to other application roles.

Default: No value, which means the recipient application role cannot grant the privileges to other application roles.

Note

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

Usage notes¶

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.

• Multiple privileges can be specified for the same object type in a single GRANT statement with each privilege separated by commas.

  However, only privileges held and grantable by the application role executing the GRANT command are actually granted to the target application role. A warning message is returned for any privileges that could not be granted.

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

• In managed access schemas:

  • The OWNERSHIP privilege on objects can only be transferred to a subordinate role of the schema owner.

  • 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, 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> [ , ... ] ] )
  

  Copy

  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.

Access control requirements¶

• This command can only be executed from within the application.

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

  Copy

• Regarding managed access schemas:

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

    The following roles can grant privileges on objects in a managed access schema:

    • The application role because this role is the schema owner (i.e. the role with the OWNERSHIP privilege on the schema).

    • A role that inherits the application role.

    • A role with the global MANAGE GRANTS privilege can grant privileges on objects in the schema.

      A role that holds the global MANAGE GRANTS privilege can grant additional privileges to the current (grantor) role.

  • Refer to Future Grants on Schema Objects (in this topic) for the access control requirements of future grants in managed access schemas.

Future grants on schema objects¶

The notes in these sections apply when assigning future grants on objects in a schema (i.e. when using the ON FUTURE keywords).

Example¶

Grant the SELECT privilege on a view to an application role:

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