Snowflake: Most or Least Privileged Access

Posted by

I was attending a conference session last week, led by the great Kent Graziano, and a question came from the audience that I had never heard before. “If I assume secondary roles, does snowflake grant most or least privileged access?”. To my surprise, I was called upon by the host to answer this question from the audience. I was able to fumble my way through an assumed answer, but I had to know for sure.

Role-Based Access Controls (RBAC)

In Snowflake users cannot own or operate on any object. The user’s session must assume a role through the USE command as only roles can operate on and own objects in Snowflake. This is just the right way to database and points back to the founders’ Oracle roots. Knowing this, if a user’s session has assumed more than one role with the use secondary roles feature, what privileges are evaluated with DML and DDL?

Quick Test of AccountAdmin

I wanted to verify my statements to this audience as a known Snowflake expert. I did some quick SQL in my account to test this.

--Least or Most Privileges of Current Used Roles
select current_role();
select current_secondary_roles();

use role accountadmin;
use secondary roles none;
select * from snowflake.account_usage.query_history limit 10;

Sure enough with this SQL, I am able to query the account usage schema.

Results from the Query

Quick Test of Public

I wanted to make sure I was just using the primary role and completely turned off secondary roles.

--Least or Most Privileges of Current Used Roles
use role public;
use secondary roles none;
select * from snowflake.account_usage.query_history limit 10;

Working as desired with the “SQL compilation error: Database ‘SNOWFLAKE’ does not exist or not authorized.” error. Notice the UI also has far fewer tables as well further indicating the lesser privileges for this assumed role of Public.

ID-10-T Error

Assume All of the Roles

Now, let’s assume all of the roles for my user. We can do that with the “use secondary roles all” command.

{"roles":
    "ACCOUNTADMIN,
    SYSADMIN,
    JUNIOR_DBA,
    RL_EMPLOYEE,
    RL_MANAGER,
    RL_HR_REP,
    RL_FINANCE,
    RL_PRIVACY_ADMIN,
    ENG_MANAGER_ROLE_PII,
    ENGINEER_ROLE_NONPII,
    MARKETING_MANAGER_ROLE_PII,
    MARKETING_ROLE_NONPII,
    SUPPORT_MANAGER_ROLE_PII,
    SUPPORT_ROLE_NONPII",
"value":"ALL"}

I have many secondary roles assumed in my demo account. We are primary on Public which has demonstrated it cannot query the access history schema. Secondarily, we have AccountAdmin applied, what happens?

--Least or Most Privileges of Current Used Roles
use role public;
use secondary roles all;
select current_secondary_roles();
select * from snowflake.account_usage.query_history limit 10;
Bingo! Most Privileged Snowflake

Just like that we can see for DML the most privileged access is honored when running the query, and the UI updated to more tables again.

What About DDL?

Great question, users cannot own objects, roles must own objects. The primary role used will be evaluated for object manipulation, deletion, and creation.

--Nuance of Create <Object> for Ownership
use role public;
use secondary roles all;
create or replace table demo.public.foo as (select * from dual);
Cannot Create Table

The table cannot be created because the primary assumed role of Public does not have privileges to operate on this schema. Even though the secondary role of AccountAdmin does. This is by design when performing DDL.

--Nuance of Create <Object> for Ownership
use role accountadmin;
use secondary roles none;
create or replace table demo.public.foo as (select * from dual);
Table Created

After assuming the primary role of AccountAdmin we are able to create the table and the owning role is AccountAdmin.

Conclusion

It truly is most privileged to be able to query Snowflake. Now you know, happy most privileged querying!

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s