Reputation: 4860
What are the out-of-the-box grants for AccountAdmin and SysAdmin in Snowflake?
If I had a brand new Snowflake account and ran the following:
show grants to role sysadmin;
show grants to role accountadmin;
What grants would each have?
Upvotes: 1
Views: 396
Answers (3)
Reputation: 241
The ACCOUNTADMIN role has the following grants per se; on these various ref article pages, you can see what the role allows the user to do (and how to grant other roles the ability as well):
- View account level Credit and Storage usage
- View account usage views
- Configure virtual warehouse resource monitors
- Configure account parameters
- Configure network policies
- Manage Provider Data Shares
- Manage Reader accounts
- Manage Consumer Data Shares
Here's a great reference page w/ all the privileges. To recap what Seeling mentioned, SECURITYADMIN has implicit grants for User and Role management, and SYSADMIN has grants for Warehouse and Database object management.
Upvotes: 1
Reputation: 4860
I'm looking for someone to officially refute what I have below. Until that happens, I'm going to guess that any grant on ACCOUNTADMIN or SYSADMIN that doesn't have a "granted_by" field was set up or controlled by Snowflake itself.
show grants to role accountadmin;
select * from table(result_scan(last_query_id())) t
where "granted_by" = '';
I think this makes logical sense, and it's also consistent w/ how SECURITYADMIN is set up. I know I haven't changed any grants on SecurityAdmin, and the three grants that it currently has are the default ones and have blanks for 'granted_by'
Here is the output:
created_on privilege granted_on name grant_option
2019-12-17 18:20:34.000 -0800 CREATE ACCOUNT ACCOUNT YOUR_ACCOUNT_NAME true
2019-12-17 18:20:34.000 -0800 CREATE SHARE ACCOUNT YOUR_ACCOUNT_NAME true
2019-12-17 18:20:34.000 -0800 EXECUTE MANAGED TASK ACCOUNT YOUR_ACCOUNT_NAME true
2019-12-17 18:20:34.000 -0800 EXECUTE TASK ACCOUNT YOUR_ACCOUNT_NAME true
2019-12-17 18:20:34.000 -0800 IMPORT SHARE ACCOUNT YOUR_ACCOUNT_NAME true
2019-12-17 18:20:34.000 -0800 MONITOR EXECUTION ACCOUNT YOUR_ACCOUNT_NAME true
2019-12-17 18:20:34.000 -0800 MONITOR SECURITY ACCOUNT YOUR_ACCOUNT_NAME true
2019-12-17 18:20:34.000 -0800 MONITOR USAGE ACCOUNT YOUR_ACCOUNT_NAME true
2019-03-15 09:27:10.000 -0700 REFERENCE_USAGE DATABASE ORGANIZATION_USAGE false
2019-03-15 09:27:08.000 -0700 USAGE ROLE SECURITYADMIN true
2019-03-15 09:27:08.000 -0700 USAGE ROLE SYSADMIN true
2019-03-15 09:27:10.000 -0700 USAGE SCHEMA SNOWFLAKE.ACCOUNT_USAGE false
2019-03-15 09:27:10.000 -0700 USAGE SCHEMA SNOWFLAKE.ORGANIZATION_USAGE false
2019-03-15 09:27:10.000 -0700 USAGE SCHEMA SNOWFLAKE.READER_ACCOUNT_USAGE false
2019-03-15 09:27:10.000 -0700 SELECT VIEW SNOWFLAKE.ACCOUNT_USAGE.AUTOMATIC_CLUSTERING_HISTORY false
2019-03-15 09:27:10.000 -0700 SELECT VIEW SNOWFLAKE.ACCOUNT_USAGE.COLUMNS false
2019-03-15 09:27:10.000 -0700 SELECT VIEW SNOWFLAKE.ACCOUNT_USAGE.COPY_HISTORY false
2019-03-15 09:27:10.000 -0700 SELECT VIEW SNOWFLAKE.ACCOUNT_USAGE.DATABASES false
2019-03-15 09:27:10.000 -0700 SELECT VIEW SNOWFLAKE.ACCOUNT_USAGE.DATABASE_STORAGE_USAGE_HISTORY false
2019-03-15 09:27:10.000 -0700 SELECT VIEW SNOWFLAKE.ACCOUNT_USAGE.DATA_TRANSFER_HISTORY false
2019-03-15 09:27:10.000 -0700 SELECT VIEW SNOWFLAKE.ACCOUNT_USAGE.FILE_FORMATS false
2019-03-15 09:27:10.000 -0700 SELECT VIEW SNOWFLAKE.ACCOUNT_USAGE.FUNCTIONS false
2019-03-15 09:27:10.000 -0700 SELECT VIEW SNOWFLAKE.ACCOUNT_USAGE.GRANTS_TO_ROLES false
2019-03-15 09:27:10.000 -0700 SELECT VIEW SNOWFLAKE.ACCOUNT_USAGE.GRANTS_TO_USERS false
2019-03-15 09:27:10.000 -0700 SELECT VIEW SNOWFLAKE.ACCOUNT_USAGE.LOAD_HISTORY false
2019-03-15 09:27:10.000 -0700 SELECT VIEW SNOWFLAKE.ACCOUNT_USAGE.LOGIN_HISTORY false
2019-03-15 09:27:10.000 -0700 SELECT VIEW SNOWFLAKE.ACCOUNT_USAGE.MATERIALIZED_VIEW_REFRESH_HISTORY false
2019-03-15 09:27:10.000 -0700 SELECT VIEW SNOWFLAKE.ACCOUNT_USAGE.METERING_DAILY_HISTORY false
2019-03-15 09:27:10.000 -0700 SELECT VIEW SNOWFLAKE.ACCOUNT_USAGE.METERING_HISTORY false
2019-03-15 09:27:10.000 -0700 SELECT VIEW SNOWFLAKE.ACCOUNT_USAGE.PIPES false
2019-03-15 09:27:10.000 -0700 SELECT VIEW SNOWFLAKE.ACCOUNT_USAGE.PIPE_USAGE_HISTORY false
2019-03-15 09:27:10.000 -0700 SELECT VIEW SNOWFLAKE.ACCOUNT_USAGE.QUERY_HISTORY false
2019-03-15 09:27:10.000 -0700 SELECT VIEW SNOWFLAKE.ACCOUNT_USAGE.REFERENTIAL_CONSTRAINTS false
2019-03-15 09:27:10.000 -0700 SELECT VIEW SNOWFLAKE.ACCOUNT_USAGE.REPLICATION_USAGE_HISTORY false
2019-03-15 09:27:10.000 -0700 SELECT VIEW SNOWFLAKE.ACCOUNT_USAGE.ROLES false
2019-03-15 09:27:10.000 -0700 SELECT VIEW SNOWFLAKE.ACCOUNT_USAGE.SCHEMATA false
2019-03-15 09:27:10.000 -0700 SELECT VIEW SNOWFLAKE.ACCOUNT_USAGE.SEQUENCES false
2019-03-15 09:27:10.000 -0700 SELECT VIEW SNOWFLAKE.ACCOUNT_USAGE.STAGES false
2019-03-15 09:27:10.000 -0700 SELECT VIEW SNOWFLAKE.ACCOUNT_USAGE.STAGE_STORAGE_USAGE_HISTORY false
2019-03-15 09:27:10.000 -0700 SELECT VIEW SNOWFLAKE.ACCOUNT_USAGE.STORAGE_USAGE false
2019-03-15 09:27:10.000 -0700 SELECT VIEW SNOWFLAKE.ACCOUNT_USAGE.TABLES false
2019-03-15 09:27:10.000 -0700 SELECT VIEW SNOWFLAKE.ACCOUNT_USAGE.TABLE_CONSTRAINTS false
2019-03-15 09:27:10.000 -0700 SELECT VIEW SNOWFLAKE.ACCOUNT_USAGE.TABLE_STORAGE_METRICS false
2019-03-15 09:27:10.000 -0700 SELECT VIEW SNOWFLAKE.ACCOUNT_USAGE.USERS false
2019-03-15 09:27:10.000 -0700 SELECT VIEW SNOWFLAKE.ACCOUNT_USAGE.VIEWS false
2019-03-15 09:27:10.000 -0700 SELECT VIEW SNOWFLAKE.ACCOUNT_USAGE.WAREHOUSE_LOAD_HISTORY false
2019-03-15 09:27:10.000 -0700 SELECT VIEW SNOWFLAKE.ACCOUNT_USAGE.WAREHOUSE_METERING_HISTORY false
2019-03-15 09:27:10.000 -0700 SELECT VIEW SNOWFLAKE.ORGANIZATION_USAGE.PREVIEW_DATA_TRANSFER_DAILY_HISTORY false
2019-03-15 09:27:10.000 -0700 SELECT VIEW SNOWFLAKE.ORGANIZATION_USAGE.PREVIEW_METERING_DAILY_HISTORY false
2019-03-15 09:27:10.000 -0700 SELECT VIEW SNOWFLAKE.ORGANIZATION_USAGE.PREVIEW_STORAGE_DAILY_HISTORY false
2019-03-15 09:27:10.000 -0700 SELECT VIEW SNOWFLAKE.READER_ACCOUNT_USAGE.LOGIN_HISTORY false
2019-03-15 09:27:10.000 -0700 SELECT VIEW SNOWFLAKE.READER_ACCOUNT_USAGE.QUERY_HISTORY false
2019-03-15 09:27:10.000 -0700 SELECT VIEW SNOWFLAKE.READER_ACCOUNT_USAGE.RESOURCE_MONITORS false
2019-03-15 09:27:10.000 -0700 SELECT VIEW SNOWFLAKE.READER_ACCOUNT_USAGE.STORAGE_USAGE false
2019-03-15 09:27:10.000 -0700 SELECT VIEW SNOWFLAKE.READER_ACCOUNT_USAGE.WAREHOUSE_METERING_HISTORY false
For SYSADMIN, I'll do the same, and assume the same:
show grants to role sysadmin;
select * from table(result_scan(last_query_id())) t
where "granted_by" = '';
2019-03-15 09:27:08.000 -0700 CREATE DATABASE ACCOUNT YOUR_ACCOUNT_NAME ROLE SYSADMIN true
2019-03-15 09:27:08.000 -0700 CREATE WAREHOUSE ACCOUNT YOUR_ACCOUNT_NAME ROLE SYSADMIN true
Upvotes: 1
Reputation: 157
The Snowflake documentation on system roles provides some high level recommendation on privileges recommended for the system built-in roles: https://docs.snowflake.net/manuals/user-guide/security-access-control-overview.html#system-defined-roles
Some details for AccountAdmin: https://docs.snowflake.net/manuals/user-guide/security-access-control-considerations.html#using-the-accountadmin-role
which implies that for SECURITYADMIN role:
The security administrator (SECURITYADMIN) role includes the privileges to create and manage users and roles
and for SYSADMIN role:
The system administrator (SYSADMIN) role includes the privileges to create warehouses, databases, and all database objects (schemas, tables, etc.).
Upvotes: 0
Related Questions
- Grant create user permission Snowflake
- Need information on "Service Accounts in Snowflake"
- Looking for full documentation of Snowflake privileges
- Snowflake: what is the APPADMIN role?
- Snowflake privileges
- How to grant roles usage on security integrations Snowflake
- Snowflake System Roles - USERADMIN vs SECURITYADMIN
- What does the GRANT MONITOR ON SCHEMA allow in Snowflake?
- Why does a FUTURE grant in Snowflake require AccountAdmin by default?
- Giving MANAGE GRANTS to a Role