I have been looking for a way of querying Snowflake tags and their belongings like schema, table, view, and column, as well as the masking policies, applied. The easiest way to do it is by querying it from the account_usage schema as below. Only some people have the account_admin role assigned, so we cannot use this option.
select * from snowflake.account_usage.tags
order by tag_name;
select * from snowflake.account_usage.tag_references
order by tag_name;
I have been checking the options and querying them from the information schema, but I have to write input to make it happen like below.
I would use
show tables
And then use the listed tables below, but it does not allow me to write a variable where the "DimCustomer" is.
select *
from table(information_schema.tag_references_all_columns('"DimCustomer"', 'table'))
For the policies;
select *
from table (information_schema.policy_references(
POLICY_NAME => 'CUST_KEY_MASK')
);
Considering that If I had not known dim_customers has a tag and had not known the tag has the CUST_KEY_MASK policy, how would I list all the tagged objects and applied to the masking policies?
As a summary:
1- List all the objects, tables, views
2- List all the objects with their tag applied
3- List all the objects, their tag, and the tag policies applied.
Could you please help me with this?
Many thanks,
Hazal
"The easiest way to do it is by querying it from the account_usage schema as below. Only some people have the account_admin role assigned, so we cannot use this option."
It is possible to grant access to SNOWFLAKE data share and ACCOUNT_USAGE schema to other roles.
Enabling Snowflake Database Usage for Other Roles
By default, the SNOWFLAKE database is available only to the ACCOUNTADMIN role.
To enable other roles to access the database and schemas, and query the views, a user with the ACCOUNTADMIN role must grant the following data sharing privilege to the desired roles: IMPORTED PRIVILEGES
USE ROLE ACCOUNTADMIN; GRANT IMPORTED PRIVILEGES ON DATABASE snowflake TO ROLE SYSADMIN; GRANT IMPORTED PRIVILEGES ON DATABASE snowflake TO ROLE customrole1;
If more granular permissions assignment is required then:
ACCOUNT_USAGE Schema SNOWFLAKE Database Roles
In addition, you can grant finer control to accounts using SNOWFLAKE Database roles. For more information on Database roles see database roles.
ACCOUNT_USAGE schemas have four defined SNOWFLAKE database roles, each granted the SELECT privilege on specific views.
ACCOUNT_USAGE Views by Database Role
The OBJECT_VIEWER, USAGE_VIEWER, GOVERNACE_VIEWER, and SECURITY_VIEWER roles have the SELECT privilege to query Account Usage views in the shared SNOWFLAKE database.
The Tags view is available for OBJECT_VIEWER Role and GOVERNANCE_VIEWER Role
USE SNOWFLAKE;
GRANT DATABASE ROLE GOVERNANCE_VIEWER TO ROLE SYSADMIN;
GRANT DATABASE ROLE GOVERNANCE_VIEWER TO ROLE CUSTOM_ROLE;