snowflake-cloud-data-platform

Snowflake roles and accountadmin privileges on a DB

-- current user (accountadmin): rsahu 
use role useradmin;
create role r1;

use role useradmin;
create role r2;

use role useradmin;
grant role r1 to user rsahu;
grant role r2 to user rsahu;

use role sysadmin;
grant create database on account to role r1;

use role r1;
create database db1;
show grants on database db1; -- ownership by r1

use role r2;
drop database db1; -- success, how?

rsahu has different roles granted to it: accountadmin, useradmin, sysadmin, securityadmin, r1 and r2.

Using role r2, how did the last statement succeeded even though r2 does not have any privilege on db1?

TIA!

Solution

  • Secondary roles may be used. Before you switch to role r2, use the command below and try the drop.

    use secondary roles none;

    https://docs.snowflake.com/en/sql-reference/sql/use-secondary-roles