The SNOWFLAKE Database

All Snowflake accounts should have a database named SNOWFLAKE. It’s a shared database, using Snowflake’s secure data sharing.

If you set up your Snowflake account before the spring of 2019, you may need to import the shared database. If you don’t see the SNOWFLAKE database in your account and don’t see it as an inbound share, contact Snowflake Support.

Snowflake, Inc. (the company) unsurprisingly runs a number of its own Snowflake accounts. The SNOWFLAKE database in your account is an inbound share from the Snowflake, Inc. account running on your cloud provider and region.

Because the SNOWFLAKE database contains information on usage and metering, by default only the ACCOUNTADMIN role has privileges to select on the views.

Attempting Grants on the SNOWFLAKE Database

Without this background, it’s possible to conclude improperly that only the ACCOUNTADMIN can access the views in the SNOWFLAKE database. Here’s why:

-- A user may try something like this:
use role ACCOUNTADMIN;
grant select on "SNOWFLAKE"."ACCOUNT_USAGE"."QUERY_HISTORY" to role SYSADMIN;

This will result in the error Grant not executed: Insufficient privileges.

The ACCOUNTADMIN is the most powerful role in a Snowflake account. Because of this, a person may conclude that there’s no way to grant privileges on the “special” SNOWFLAKE database.

Granting Privileges on SNOWFLAKE to Human Users

As previously discussed, the only thing special about the SNOWFLAKE database is it’s an inbound shared database. You can grant access to the SNOWFLAKE database the same way you do for any other inbound shared database:

use role ACCOUNTADMIN;
grant imported privileges on database SNOWFLAKE to role SYSADMIN;
use role SYSADMIN; -- Remember to always get out of the ACCOUNTADMIN role when done using it.

This will grant select on all views in the SNOWFLAKE database to the SYSADMIN role. It’s up to your organization whether or not you want to grant this access. Personally, I recommend it. It allows users with SYSADMIN role but not ACCOUNTADMIN role to monitor usage. It also allows users with ACCOUNTADMIN role to use least privilege to access this information without changing roles to ACCOUNTADMIN. Any time a user changes to ACCOUNTADMIN it’s possible to forget to get out of that role. That risks performing other actions such as creating object that generally shouldn’t be done in that role.

Granting Privileges on SNOWFLAKE to Machine Users

If granting access to the SNOWFLAKE database is for a dashboard or machine user, you can do something like this:

-- You must use the ACCOUNTADMIN role to assign the required privileges
use role ACCOUNTADMIN;
 
-- Optionally create a warehouse to monitor Snowflake activity.
-- Only create a warehouse dedicated to account usage if there are several
-- clients (performance monitors, BI packages, etc.) using it.
create or replace warehouse MONITOR_SNOWFLAKE warehouse_size = 'X-Small';
 
-- Create a new role intended to monitor Snowflake usage.
create or replace role MONITOR_SNOWFLAKE;
 
-- Grant privileges on the SNOWFLAKE database to the new role.
grant imported privileges on database SNOWFLAKE to role MONITOR_SNOWFLAKE;
 
-- Create a user.
create or replace user    SNOWFLAKE_MONITOR
    LOGIN_NAME          = SNOWFLAKE_MONITOR
    password            = 'My_Password_123!'
    default_warehouse   = MONITOR_SNOWFLAKE
    default_role        = MONITOR_SNOWFLAKE
    default_namespace   = SNOWFLAKE.ACCOUNT_USAGE
--  rsa_public_key      = 'MIIBIjANBgkqh...' -- Optional, see note.
;
 
-- Note: Snowflake recommends using key authentication for machine users:
-- https://docs.snowflake.com/en/user-guide/snowsql-start.html#using-key-pair-authentication
 
-- Grant usage on the warehouse used to monitor Snowflake.
grant usage on warehouse MONITOR_SNOWFLAKE to role MONITOR_SNOWFLAKE;
 
-- Grant the monitor role to the user.
grant role MONITOR_SNOWFLAKE to user SNOWFLAKE_MONITOR;
 
-- Get out of the ACCOUNTADMIN role when done.
use role SYSADMIN;

Increasing Granularity of the Grants

Let’s discuss more granular access to the views in an imported shared database. Privileges on the imported shared database itself are all or nothing. If you want to control access with more granularity, create a set of “select * from…” views selecting from the SNOWFLAKE database. You can then manage the grants on those views individually.