Categories:

User & Security DDL (Access Control)

GRANT <privileges> … TO ROLE

Grants one or more access privileges on a securable object to a role. The privileges that can be granted 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.

Variations:

GRANT OWNERSHIP , GRANT <privilege> … TO SHARE

See also:

REVOKE <privileges> … FROM ROLE

Syntax

GRANT {  { 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> }
       | { schemaObjectPrivileges  | ALL [ PRIVILEGES ] } ON { <object_type> <object_name> | ALL <object_type>S IN SCHEMA <schema_name> }
       | { schemaObjectPrivileges  | ALL [ PRIVILEGES ] } ON FUTURE <object_type>S IN SCHEMA <schema_name>
      }
  TO [ ROLE ] <role_name> [ WITH GRANT OPTION ]

Where:

globalPrivileges

globalPrivileges ::=
  { { CREATE { ROLE | USER | WAREHOUSE | DATABASE | INTEGRATION } } | MANAGE GRANTS | MONITOR USAGE } [ , ... ]

accountObjectPrivileges

accountObjectPrivileges ::=
-- For RESOURCE MONITOR
  { MODIFY | MONITOR } [ , ... ]
-- For WAREHOUSE
  { MODIFY | MONITOR | USAGE | OPERATE } [ , ... ]
-- For DATABASE
  { MODIFY | MONITOR | USAGE | CREATE SCHEMA | IMPORTED PRIVILEGES } [ , ... ]
-- For INTEGRATION
  { USAGE } [ , ... ]

schemaPrivileges

schemaPrivileges ::=
  { MODIFY | MONITOR | USAGE | CREATE { TABLE | VIEW | FILE FORMAT | STAGE | PIPE | STREAM | TASK | SEQUENCE | FUNCTION | PROCEDURE } } [ , ... ]

schemaObjectPrivileges

schemaObjectPrivileges ::=
-- For TABLE
  { SELECT | INSERT | UPDATE | DELETE | TRUNCATE | REFERENCES } [ , ... ]
-- For VIEW
    SELECT
-- For internal STAGE
    READ [ , WRITE ]
-- For external STAGE
    USAGE
-- For FILE FORMAT, UDF, or SEQUENCE
    USAGE

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

TABLE | VIEW | STAGE | FILE FORMAT | FUNCTION | PROCEDURE | SEQUENCE

role_name

Specifies the identifier for the recipient 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 (e.g. tables or views) rather than existing objects. Note that future grants can be revoked at any time using REVOKE <privileges> … FROM 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 role to grant the privileges to other roles.

Default: No value (the recipient role cannot grant the privileges to other roles)

Usage Notes

  • A security administrator (user with the SECURITYADMIN role) or another role with the MANAGE GRANTS privilege on the account can grant privileges on objects. The MANAGE GRANTS privilege is required to grant future grants on objects of a specified type in a schema. For more information about future grants, see Future Grants on Schema Objects in this topic.

    In addition:

    • In regular (non-managed) schemas, the object owner (i.e. the role with the OWNERSHIP privilege on the object) can grant privileges on the individual objects.

    • In managed access schemas, object owners lose the ability to make grant decisions. The schema owner (i.e. the role with the OWNERSHIP privilege on the schema) can grant privileges on objects in the schema, including future grants.

  • 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 ALL object_typeS in container 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, we recommend creating a shared role and using the role to create objects that are automatically accessible to all users who have been granted the role.

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

Future Grants on Schema Objects

The notes in this section apply when assigning future grants on objects in a schema; i.e. when using the ON FUTURE keywords.

Object Cloning

  • When a database is cloned, the schemas in the cloned database copy the future privileges from the source schemas. This maintains consistency with the regular object grants, in which the grants of the source object (i.e. database) are not copied to the clone, but the grants on all the children objects (i.e. schemas in the database) are copied to the clones.

  • When a schema is cloned, the future grants from the source schema are not copied to the clone.

  • When an object in a schema is cloned, any future grants defined for this object type in the schema are applied to the cloned object unless the COPY GRANTS option is specified in the CREATE <object> statement for the clone operation; in that case, the new object retains the access permissions of the original table and does not inherit any future grants for objects of that type.

Restrictions / Limitations

  • Future grants are not supported for:

    • Data sharing

    • Data replication

  • Future grants are supported on named stages with the following restrictions:

    • The WRITE privilege cannot be specified without the READ privilege.

    • The READ privilege cannot be revoked if the WRITE privilege is present.

    • For internal stages, only future grants with the READ or WRITE privilege are materialized.

    • For external stages, only future grants with the USAGE privileges are materialized.

  • Future grants are not applied when renaming or swapping a table.

  • No more than one future grant of the OWNERSHIP privilege is allowed on each securable object type.

  • In managed access schemas:

    • 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 is transferred to a different role, all open future grants must be revoked using REVOKE <privileges> … FROM ROLE with the ON FUTURE keywords.

Examples

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

GRANT OPERATE ON WAREHOUSE report_wh TO ROLE analyst;

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

GRANT OPERATE ON WAREHOUSE report_wh TO ROLE analyst WITH GRANT OPTION;

Grant the SELECT privilege on all existing tables in the mydb.myschema schema to the analyst role:

GRANT SELECT ON ALL TABLES IN SCHEMA mydb.myschema to ROLE analyst;

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

GRANT ALL PRIVILEGES ON FUNCTION add5(number) TO ROLE analyst;

GRANT ALL PRIVILEGES ON FUNCTION add5(string) TO 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.

Grant the SELECT and INSERT privileges on all future tables created in the mydb.myschema schema to the role1 role:

GRANT SELECT,INSERT ON FUTURE TABLES IN SCHEMA mydb.myschema
TO ROLE role1;