Snowflake: Most or Least Privileged Access

Role-Based Access Controls (RBAC)

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.

Quick Test of AccountAdmin

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?

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.

Quick Test of Public

Results from Query

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.

Assume All of the Roles

ID-10-T Error

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 in Snowflake!

What About DDL?

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.

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

Conclusion

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

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

Originally published at http://bigdatadave.com on April 27, 2022.