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

In this Topic:

Syntax

GRANT {  { globalPrivileges         | ALL [ PRIVILEGES ] } ON ACCOUNT
       | { accountObjectPrivileges  | ALL [ PRIVILEGES ] } ON { USER | 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 { DATABASE <db_name> | SCHEMA <schema_name> } }
       | { schemaObjectPrivileges   | ALL [ PRIVILEGES ] } ON FUTURE <object_type_plural> IN { DATABASE <db_name> | SCHEMA <schema_name> }
      }
  TO [ ROLE ] <role_name> [ WITH GRANT OPTION ]

Where:

globalPrivileges ::=
  {
     CREATE {
               ACCOUNT | DATA EXCHANGE LISTING | DATABASE | INTEGRATION
               | NETWORK POLICY | ROLE | SHARE | USER | WAREHOUSE
     }
     | APPLY MASKING POLICY | APPLY ROW ACCESS POLICY | APPLY SESSION POLICY | APPLY TAG | ATTACH POLICY
     | EXECUTE TASK | IMPORT SHARE | MANAGE GRANTS | MONITOR { EXECUTION | USAGE } | OVERRIDE SHARE RESTRICTIONS
  }
  [ , ... ]
accountObjectPrivileges ::=
-- For USER
  { MONITOR } [ , ... ]
-- 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 | ROW ACCESS POLICY | SESSION POLICY
                 | TAG | SEQUENCE | FUNCTION | PROCEDURE | FILE FORMAT | STAGE | PIPE | STREAM | TASK
              }
     | ADD SEARCH OPTIMIZATION
  }
  [ , ... ]
schemaObjectPrivileges ::=
-- For TABLE
  { SELECT | INSERT | UPDATE | DELETE | TRUNCATE | REFERENCES } [ , ... ]
-- For VIEW
  { SELECT | REFERENCES } [ , ... ]
-- For MATERIALIZED VIEW
  { SELECT | REFERENCES } [ , ... ]
-- For SEQUENCE, FUNCTION (UDF or external function), PROCEDURE, or FILE FORMAT
    USAGE
-- For internal STAGE
    READ [ , WRITE ]
-- For external STAGE
    USAGE
-- For PIPE
   { MONITOR | OPERATE } [ , ... ]
-- For STREAM
    SELECT
-- For TASK
   { MONITOR | OPERATE } [ , ... ]
-- For MASKING POLICY
    APPLY
-- For ROW ACCESS POLICY
    APPLY
-- For SESSION POLICY
    APPLY
-- For TAG
    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 granted.

object_type

Specifies the type of object (for schema objects):

TABLE | EXTERNAL TABLE | VIEW | MATERIALIZED VIEW | MASKING POLICY | ROW ACCESS POLICY | SESSION POLICY | SEQUENCE | FUNCTION | PROCEDURE | FILE FORMAT | STAGE | PIPE | STREAM | TASK

object_type_plural

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

Note that bulk grants on pipes are not allowed.

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) database or 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 Database or 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)

Note

The WITH GRANT OPTION parameter does not support the IMPORTED PRIVILEGES privilege. For more information, see Granting Privileges on a Shared Database.

Usage Notes

  • To grant the OWNERSHIP privilege on an object (or all objects of a specified type in a schema) to a role, transferring ownership of the object from one role to another role, use GRANT OWNERSHIP instead. The GRANT OWNERSHIP command has a different syntax.

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

  • 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 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 or stored procedures that have the same name within a schema. For more information, see:

    For examples, see Examples (in this topic) and Overloading Stored Procedure Names.

Access Control Requirements

Granting privileges on individual objects

In general, a role with any one of the following sets of privileges can grant privileges on an object to other roles:

  • The global MANAGE GRANTS privilege.

    Only the SECURITYADMIN and ACCOUNTADMIN system roles have the MANAGE GRANTS privilege; however, the privilege can be granted to custom roles.

  • The OWNERSHIP privilege on the object. When granting privileges on schema objects (e.g. tables and views), the role must also have the USAGE privilege on the parent database and schema.

  • If a privilege was granted to a role with the WITH GRANT OPTION parameter included in the GRANT <privileges> … TO ROLE statement, the role can grant the same privilege to 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 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 grant privileges on objects in the schema.

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

Defining grants on future objects of a specified type

Database level

The global MANAGE GRANTS privilege is required to grant 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), either the schema owner (i.e. the role with the OWNERSHIP privilege on the schema) or a role with the global MANAGE GRANTS privilege can grant privileges on future objects in the schema.

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

For more information about defining grants on future objects of a specified type, see Future Grants on Database or Schema Objects (in this topic).

Future Grants on Database or Schema Objects

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

For more information, see managed access schemas.

Considerations

  • When future grants are defined on the same object type for a database and a schema in the same database, 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 role or different roles.

    For example, the following statements grant different privileges on objects of the same type at the database and schema levels.

    Grant the SELECT privilege on all future schemas in database d1 to role r1:

    GRANT SELECT ON FUTURE TABLES IN DATABASE d1 TO ROLE r1;
    

    Grant the INSERT and DELETE privileges on all future tables in the d1.s1 schema to role r2.

    GRANT INSERT,DELETE ON FUTURE TABLES IN SCHEMA d1.s1 TO ROLE r2;
    

    The future grants assigned to the r1 role are ignored completely. When new table are created in schema d1.s1, only the future privileges defined on tables for the r2 role are granted.

  • Database level future grants apply to both regular and managed access schemas.

Restrictions / Limitations

  • Future grants cannot be defined on objects of the following types:

    • External function

    • Policy objects:

      • Masking policy

      • Row access policy

      • Session policy

    • Tag

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

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 in the mydb.myschema schema to the analyst role:

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

GRANT ALL PRIVILEGES ON FUNCTION mydb.myschema.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 usage privilege on a stored procedure in the mydb.myschema schema to the analyst role:

GRANT USAGE ON PROCEDURE mydb.myschema.myprocedure(number) TO ROLE analyst;

Note that stored procedure names (like UDF names) can be overloaded, so you must specify the data type of the arguments(s). For more details about overloading stored procedures, see Overloading of Names.

Grant the privilege to create materialized views in the specified schema:

GRANT CREATE MATERIALIZED VIEW ON SCHEMA mydb.myschema TO ROLE myrole;

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;

Grant the USAGE privilege on all future schemas in the mydb database to the role1 role:

use role accountadmin;

grant usage on future schemas in database mydb to role role1;
Back to top