Access Control Privileges

This topic describes the privileges that are available in the Snowflake access control model. Privileges are granted to roles, and roles are granted to users, to specify the operations that the users can perform on objects in the system.

In this Topic:

All Privileges (Alphabetical)

The following privileges are available in the Snowflake access control model. The meaning of each privilege varies depending on the object type to which it is applied, and not all objects support all privileges:

Privilege

Object Type

Description

ALL [ PRIVILEGES ]

All

Grants all the privileges for the specified object type.

CREATE <object_type>

Global , Database , Schema

Grants ability to create an object of <object_type> (e.g. CREATE TABLE grants the ability to create a table within a schema).

CREATE ACCOUNT

Global

Grants ability to create managed accounts; currently applies only to data providers creating reader accounts for sharing data with consumers.

CREATE SHARE

Global

Grants ability to create shares; applies to data providers for sharing data with other accounts.

DELETE

Table

Grants ability to execute a DELETE command on the table.

IMPORT SHARE

Global

Grants ability to view shares shared with your account and create databases from the shares; applies to data consumers.

IMPORTED PRIVILEGES

Table

Grants ability to enable roles other than the owning role to access a shared database; applies only to shared databases.

INSERT

Table

Grants ability to execute an INSERT command on the table.

MANAGE GRANTS

Global

Grants ability to grant or revoke privileges on any object as if the invoking role were the owner of the object.

MODIFY

Resource Monitor , Warehouse , Database , Schema

Grants ability to change the settings or properties of an object (e.g. on a virtual warehouse, provides the ability to change the size of a virtual warehouse).

MONITOR

Resource Monitor , Warehouse , Database , Schema

Grants ability to see details within an object (e.g. queries and usage within a warehouse).

MONITOR USAGE

Global

Grants ability to monitor account-level usage and historical information for databases and warehouses; for more details, see Enabling Non-Account Administrators to Monitor Usage and Billing History. Additionally grants ability to view managed accounts using SHOW MANAGED ACCOUNTS.

OPERATE

Warehouse

Grants ability to start, stop, suspend, or resume a virtual warehouse.

OWNERSHIP

All

Grants ability to delete, alter, and grant or revoke access to an object. Required to rename an object. OWNERSHIP is a special privilege on an object that is automatically granted to the role that created the object, but can also be transferred using the GRANT OWNERSHIP command to a different role by the owning role (or any role with the MANAGE GRANTS privilege).

REFERENCES

Table

Grants ability to reference a table as the unique/primary key table for a foreign key constraint.

SELECT

Table , External table, View, Stream

Grants ability to execute a SELECT statement on the table/view.

TRUNCATE

Table

Grants ability to execute a TRUNCATE TABLE command on the table.

UPDATE

Table

Grants ability to execute an UPDATE command on the table.

USAGE

Warehouse , Database , Schema

Grants ability to execute a USE <object> command on the object; also grants ability to execute a SHOW <objects> command on the objects within a database or schema; however, a contained object is only listed in the output if the executing role also has at least one privilege on the object.

The remaining sections in this topic describe the specific privileges available for each type of object and their usage.

Global Privileges

Privilege

Usage

CREATE USER

Enables creating a new user.

CREATE ROLE

Enables creating a new role.

MANAGE GRANTS

Enables modifying grants on objects for which the role is not the owner.

CREATE WAREHOUSE

Enables creating a new virtual warehouse.

CREATE DATABASE

Enables creating a new database or create a clone of an existing database.

CREATE SHARE

Enables a data provider to create a new share. For more details, see Granting CREATE SHARE and IMPORT SHARE to Other Roles.

IMPORT SHARE

Enables a data consumer to view shares shared with their account and create databases from the shares. For more details, see Granting CREATE SHARE and IMPORT SHARE to Other Roles.

CREATE ACCOUNT

Enables a data provider to create a new managed account (i.e. reader account). For more details, see Managing Reader Accounts.

MONITOR USAGE

Grants ability to monitor account-level usage and historical information for databases and warehouses; for more details, see Enabling Non-Account Administrators to Monitor Usage and Billing History. Additionally grants ability to view managed accounts using SHOW MANAGED ACCOUNTS.

ALL [ PRIVILEGES ]

Grants all global privileges.

User and Role Privileges

Privilege

Usage

OWNERSHIP

Grants full control over a user/role.

Resource Monitor Privileges

Privilege

Usage

MODIFY

Enables altering any properties of a resource monitor, such as changing the monthly credit quota.

MONITOR

Enables viewing a resource monitor.

Virtual Warehouse Privileges

Privilege

Usage

MODIFY

Enables altering any properties of a warehouse, including changing its size.

MONITOR

Enables viewing current and past queries executed on a warehouse as well as usage statistics on that warehouse.

OPERATE

Enables changing the state of a warehouse (stop, start, suspend, resume) as well as abort any executing queries.

USAGE

Enables using a virtual warehouse and, as a result, execute queries on the warehouse.

ALL [ PRIVILEGES ]

Grants all privileges, except OWNERSHIP, on the warehouse.

OWNERSHIP

Transfers ownership of a warehouse, which gives full control over the warehouse.

Integration Privileges

Note

Storage integrations only; other integration types (notification, security) have no object-level privileges.

Privilege

Usage

USAGE

Enables referencing the storage integration when creating a stage (using CREATE STAGE) or modifying a stage (using ALTER STAGE).

Database Privileges

Privilege

Usage

MODIFY

Enables altering any settings of a database.

MONITOR

Enables viewing details for a database.

USAGE

Enables using a database. Additional privileges are required to view or take actions on objects in a database.

CREATE SCHEMA

Enables creating a new schema in a database, including cloning a schema.

IMPORTED PRIVILEGES

Enables roles other than the owning role to access a shared database; applies only to shared databases.

ALL [ PRIVILEGES ]

Grants all privileges, except OWNERSHIP, on a database.

OWNERSHIP

Transfers ownership of a database, which grants full control over the database.

Note that changing the properties of a database, including comments, requires the OWNERSHIP privilege for the database.

Schema Privileges

Privilege

Usage

MODIFY

Enables altering any settings of a schema.

MONITOR

Enables viewing details for a schema.

USAGE

Enables using a schema, including executing SHOW SCHEMAS commands to list the schema details in a database.

To execute SHOW <objects> commands for objects (tables, views, stages, file formats, sequences, pipes, or functions) in the schema, a role must have at least one privilege granted on the object.

CREATE TABLE

Enables creating a new table in a schema, including cloning a table.

CREATE EXTERNAL TABLE

Enables creating a new external table in a schema.

CREATE VIEW

Enables creating a new view in a schema.

CREATE STAGE

Enables creating a new stage in a schema, including cloning a stage.

CREATE FILE FORMAT

Enables creating a new file format in a schema, including cloning a file format.

CREATE SEQUENCE

Enables creating a new sequence in a schema, including cloning a sequence.

CREATE FUNCTION

Enables creating a new UDF in a schema.

CREATE PIPE

Enables creating a new pipe in a schema.

CREATE STREAM

Enables creating a new stream in a schema, including cloning a stream.

CREATE TASK

Enables creating a new task in a schema, including cloning a task.

ALL [ PRIVILEGES ]

Grants all privileges, except OWNERSHIP, on a schema.

OWNERSHIP

Transfers ownership of a schema, which grants full control over the schema.

Note

  • Changing the properties of a schema, including comments, requires the OWNERSHIP privilege for the database.

  • Operating on a schema also requires the USAGE privilege on the parent database.

Table Privileges

Privilege

Usage

SELECT

Enables executing a SELECT statement on a table.

INSERT

Enables executing an INSERT command on a table. Also enables using the ALTER TABLE command with a RECLUSTER clause to manually recluster a table with a clustering key.

UPDATE

Enables executing an UPDATE command on a table.

TRUNCATE

Enables executing a TRUNCATE TABLE command on a table.

DELETE

Enables executing a DELETE command on a table.

REFERENCES

Enables referencing a table as the unique/primary key table for a foreign key constraint.

ALL [ PRIVILEGES ]

Grants all privileges, except OWNERSHIP, on a table.

OWNERSHIP

Transfers ownership of a table, which grants full control over the table. Required to alter most properties a table, with the exception of reclustering.

Note

Operating on a table also requires the USAGE privilege on the parent database and schema.

External Table Privileges

Privilege

Usage

SELECT

Enables executing a SELECT statement on an external table.

ALL [ PRIVILEGES ]

Grants all privileges, except OWNERSHIP, on an external table.

OWNERSHIP

Transfers ownership of a view, which grants full control over the external table; required to refresh an external table.

Note

Operating on an external table also requires the USAGE privilege on the parent database and schema.

View Privileges

Privilege

Usage

SELECT

Enables executing a SELECT statement on a view.

ALL [ PRIVILEGES ]

Grants all privileges, except OWNERSHIP, on a view.

OWNERSHIP

Transfers ownership of a view, which grants full control over the view. Required to alter a view.

Note

A user who has SELECT privilege on a view does not also need SELECT privilege on the tables that the view uses. This means that you can use a view to give a role access to only a subset of a table. For example, you can create a view that accesses medical billing information but not medical diagnosis information in the same table, and you can then grant privileges on that view to the “accountant” role so that the accountants can look at the billing information without seeing the patient’s diagnosis.

Operating on a view also requires the USAGE privilege on the parent database and schema.

Stage Privileges

Privilege

Usage

USAGE

Enables using an external stage object in a SQL statement; not applicable to internal stages.

READ

Enables performing any operations that require reading from an internal stage (GET, LIST, COPY INTO <table>, etc.); not applicable to external stages.

WRITE

Enables performing any operations that require writing to an internal stage (PUT, REMOVE, COPY INTO <location>, etc.); not applicable for external stages.

ALL [ PRIVILEGES ]

Grants all applicable privileges, except OWNERSHIP, on the stage (internal or external).

OWNERSHIP

Transfers ownership of a stage, which grants full control over the stage.

Note

  • When granting both the READ and WRITE privileges for an internal stage, the READ privilege must be granted before or at the same time as the WRITE privilege.

  • When revoking both the READ and WRITE privileges for an internal stage, the WRITE privilege must be revoked before or at the same time as the READ privilege.

  • Operating on a stage also requires the USAGE privilege on the parent database and schema.

File Format Privileges

Privilege

Usage

USAGE

Enables utilizing a file format in a SQL statement.

ALL [ PRIVILEGES ]

Grants all privileges, except OWNERSHIP, on the file format.

OWNERSHIP

Transfers ownership of a file format, which grants full control over the file format. Required to alter a file format.

Note

Operating on file formats also requires the USAGE privilege on the parent database and schema.

Pipe Privileges

Privilege

Usage

OWNERSHIP

Transfers ownership of a pipe, which grants full control over the pipe; required for loading data using Snowpipe.

Note

Operating on pipes also requires the USAGE privilege on the parent database and schema.

Stream Privileges

Privilege

Usage

SELECT

Enables executing a SELECT statement on a stream.

OWNERSHIP

Transfers ownership of a stream, which grants full control over the stream.

ALL [ PRIVILEGES ]

Grants all privileges, except OWNERSHIP, on the stream.

Task Privileges

Privilege

Usage

OWNERSHIP

Transfers ownership of a task, which grants full control over the task.

Other Database Object Privileges (Sequences, UDFs)

Privilege

Usage

USAGE

Enables utilizing the specified object in a SQL statement.

ALL [ PRIVILEGES ]

Grants all privileges, except OWNERSHIP, on the specified object.

OWNERSHIP

Transfers ownership of the specified object, which grants full control over the object; required to alter the object.

Note

Operating on these other database objects also requires the USAGE privilege on the parent database and schema.