Category: Security

Snowflake in the Carolinas > Security
Least Privilege Access to Monitor Snowflake Usage

Least Privilege Access to Monitor Snowflake Usage

Posted on by Greg Pavlik

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.

Getting a Complete List of User Privileges in Snowflake

Getting a Complete List of User Privileges in Snowflake

Posted on by Greg Pavlik

These queries don’t need much explanation. I’ve had some customers request how to get a complete list of user privileges, often for auditing purposes. The two queries below will show the role hierarchy (which roles have been granted which other roles) and a complete list of effective permissions for each user.

For instance, if someone grants user ‘MARY’ the ‘PLAN_9’ role, and that role has a privilege to select from ‘TABLE_X”, then one row in the result will show that MARY can select from TABLE_X because she’s been granted the PLAN_9 role. All other users in the PLAN_9 role will also show a row with this set of user, role granting the privilege, and then the privilege itself.

Snowflake enforces a best practice for security and governance called RBAC, role based access control. Privileges go to roles, not directly to users. To grant a user a privilege, add the user to a role with the privilege.

-- The data returned by both queries is in the
-- SNOWFLAKE database, which has latency of up
-- to 3 hours to reflect changes

-- Get the effective role hierarchy for each user.
with
   -- CTE gets all the roles each role is granted
   ROLE_MEMBERSHIPS(ROLE_GRANTEE, ROLE_GRANTED_THROUGH_ROLE)
   as
    (
    select   GRANTEE_NAME, "NAME"
    from     SNOWFLAKE.ACCOUNT_USAGE.GRANTS_TO_ROLES
    where    GRANTED_TO = 'ROLE' and
             GRANTED_ON = 'ROLE' and
             DELETED_ON is null
    ),
    -- CTE gets all roles a user is granted
    USER_MEMBERSHIPS(ROLE_GRANTED_TO_USER, USER_GRANTEE, GRANTED_BY)
    as
     (
     select ROLE,
            GRANTEE_NAME,
            GRANTED_BY
     from SNOWFLAKE.ACCOUNT_USAGE.GRANTS_TO_USERS
     where DELETED_ON is null
     )
-- 
select 
        USER_GRANTEE,
        case
            when ROLE_GRANTED_THROUGH_ROLE is null 
                then ROLE_GRANTED_TO_USER 
            else ROLE_GRANTED_THROUGH_ROLE
        end 
        EFFECTIVE_ROLE,
        GRANTED_BY,
        ROLE_GRANTEE,
        ROLE_GRANTED_TO_USER,
        ROLE_GRANTED_THROUGH_ROLE
from    USER_MEMBERSHIPS U
    left join ROLE_MEMBERSHIPS R
        on U.ROLE_GRANTED_TO_USER = R.ROLE_GRANTEE
;

--------------------------------------------------------------------------------------------------

-- This gets all the grants for all of the users:
with 
    ROLE_MEMBERSHIPS
        (
            ROLE_GRANTEE, 
            ROLE_GRANTED_THROUGH_ROLE
        )
    as
    (
        -- This lists all the roles a role is in
        select   GRANTEE_NAME, "NAME"
        from     SNOWFLAKE.ACCOUNT_USAGE.GRANTS_TO_ROLES
        where    GRANTED_TO = 'ROLE' and
                 GRANTED_ON = 'ROLE' and
                 DELETED_ON is null
    ),
    USER_MEMBERSHIPS
        (
            ROLE_GRANTED_TO_USER,
            USER_GRANTEE,
            GRANTED_BY
        )
    as
     (
        select ROLE,GRANTEE_NAME,GRANTED_BY
        from SNOWFLAKE.ACCOUNT_USAGE.GRANTS_TO_USERS
        where DELETED_ON is null
     ),
    EFFECTIVE_ROLES
    (
        USER_GRANTEE,
        EFFECTIVE_ROLE,
        GRANTED_BY,
        ROLE_GRANTEE,
        ROLE_GRANTED_TO_USER,
        ROLE_GRANTED_THROUGH_ROLE
    )
    as
    (
        select 
            USER_GRANTEE,
            case 
                when ROLE_GRANTED_THROUGH_ROLE is null
                    then ROLE_GRANTED_TO_USER
                else ROLE_GRANTED_THROUGH_ROLE
            end
            EFFECTIVE_ROLE,
            GRANTED_BY,
            ROLE_GRANTEE,
            ROLE_GRANTED_TO_USER,
            ROLE_GRANTED_THROUGH_ROLE
        from USER_MEMBERSHIPS U
            left join ROLE_MEMBERSHIPS R
            on U.ROLE_GRANTED_TO_USER = R.ROLE_GRANTEE
    ),
    GRANT_LIST
        (
            CREATED_ON,
            MODIFIED_ON,
            PRIVILEGE,
            GRANTED_ON, 
            "NAME",
            TABLE_CATALOG,
            TABLE_SCHEMA,
            GRANTED_TO,
            GRANTEE_NAME,
            GRANT_OPTION
        )
    as
    (
        -- This shows all the grants (other than to roles)
        select  CREATED_ON,
                MODIFIED_ON,
                PRIVILEGE,
                "NAME",
                TABLE_CATALOG,
                TABLE_SCHEMA,
                GRANTED_TO,
                GRANTEE_NAME,
                GRANT_OPTION,
                GRANTED_ON
        from    SNOWFLAKE.ACCOUNT_USAGE.GRANTS_TO_ROLES
        where   GRANTED_ON <> 'ROLE' and
                PRIVILEGE <> 'USAGE' and 
                DELETED_ON is null
    )
select * from EFFECTIVE_ROLES R
    left join GRANT_LIST G 
        on G.GRANTED_TO = R.EFFECTIVE_ROLE
where G.PRIVILEGE is not null
;

Theme: Overlay by Kaira.