I am trying to share the data of SNOWFLAKE.ACCOUNT_USAGE view with roles that are not ACCOUNTADMIN. I do not want to grant them ACCOUNTADMIN privileges.
So, as per the Snowflake documentation, I tried exploring database roles. As per the Snowflake documentation https://docs.snowflake.com/en/sql-reference/snowflake-db-roles “ACCOUNT_USAGE schemas have four defined SNOWFLAKE database roles, each granted the SELECT privilege on specific views.”
I am not sure, what this means. Is it trying to say, that the roles exist?
Or, is it trying to say, that I need to create database roles exactly like the one said in the document, grant select privilege on the view and then it will work? (This kind of sounds weird).
As you can understand, I am a little confused, and I need some guidance from anybody who has done this. Please let me know if you need further information in this context.
Thanks.
These are existing roles, and these different database roles control access to different schema objects in the SNOWFLAKE database.
The below link has complete information on what views are available through each SNOWFLAKE database role.
https://docs.snowflake.com/en/sql-reference/snowflake-db-roles#account-usage-views-by-database-role
You can use the below command to grant a database role
GRANT DATABASE ROLE <name> TO ROLE <parent_role_name>
https://docs.snowflake.com/en/sql-reference/sql/grant-database-role
e.g., if you grant OBJECT_VIEWER database role to a custom role, then the role will have SELECT access to all the views listed under “OBJECT_VIEWER Role” in the link shared below
GRANT DATABASE ROLE OBJECT_VIEWER TO ROLE <the custom role>
https://docs.snowflake.com/en/sql-reference/snowflake-db-roles#account-usage-views-by-database-role
By default, the SNOWFLAKE database is visible to all users; however, access to schemas in this database can be granted by a user with the ACCOUNTADMIN role using either of the following approaches:
Grant IMPORTED PRIVILEGES on the SNOWFLAKE database.
Grant a SNOWFLAKE database role to an account role.
https://docs.snowflake.com/en/sql-reference/account-usage#enabling-other-roles-to-use-schemas-in-the-snowflake-database
The OBJECT_VIEWER, USAGE_VIEWER, GOVERNANCE_VIEWER, and SECURITY_VIEWER roles have the SELECT privilege to query Account Usage views in the shared SNOWFLAKE database. These Database roles will already exist on the account.
If you Grant IMPORTED PRIVILEGES on the SNOWFLAKE database, then all the views of the Snowflake database will be granted to a custom role.
If you grant a database role, then all specified views will be granted based on the database role.
https://docs.snowflake.com/en/sql-reference/account-usage#account-usage-views-by-database-role
To grant Imported privileges on snowflake database, you could execute below
USE ROLE ACCOUNTADMIN;
GRANT IMPORTED PRIVILEGES ON DATABASE SNOWFLAKE TO ROLE SYSADMIN;
GRANT IMPORTED PRIVILEGES ON DATABASE SNOWFLAKE TO ROLE customrole1;
To grant a database role on Snowflake database, you could execute below
USE ROLE ACCOUNTADMIN;
GRANT DATABASE ROLE SNOWFLAKE. OBJECT_VIEWER TO ROLE customrole1;