Review Immuta policy objects created natively in Snowflake

• The SQL code block below can review all policy objects created within the Immuta database. First, identify the Immuta SYSTEM role and grant it to a Snowflake user account to explore Immuta policy objects. By default, the naming convention for the SYSTEM role is <Immuta_db_name>_SYSTEM. Next, navigate to the Immuta database and policy schema in Snowflake, and then run a show row or show masking policies.

use role accountadmin;
grant role <IMMUTA_DB>_SYSTEM to <your user>;
use role <IMMUTA_DB>_SYSTEM;
use database <IMMUTA_DB>;
use schema <IMMUTA_DB>."IMMUTA_POLICIES";

show row access policies;
describe row access policy <IMMUTA_DB>."IMMUTA_POLICIES"."RLS_xx_xxxxxxxxxxxxxx";
-- RLS is row level security; the _xx_ after is the Immuta datasource ID.

show masking policies;
describe masking policy <IMMUTA_DB>."IMMUTA_POLICIES"."xx_CREDIT_CARD_NUMBER_xxxxxxxxxxxxxx";
-- The first two 'xx' is the Immuta datasource ID, then follow the column name, then a hash string.

• Find policy objects have been ADD or SET in tables or views. The code block makes use of the Snowflake policy_references().

use role accountadmin;
use database <data_database>;
use schema <data_database>.<schema name>;
SELECT * FROM TABLE(information_schema.policy_references(ref_entity_name =>'"<data_database>"."<schema name>"."<table name>"', ref_entity_domain => 'TABLE'));

• Find out where the policy objects have been ADD or SET.

use role BC_IMMUTADB_SYSTEM;
use database BC_IMMUTADB;
use schema IMMUTA_POLICIES;
show row access policies;

-- e.g., found this RLS_11_41ef0e2b92c8bcbf on the result set
SELECT * FROM TABLE("BC_IMMUTADB".information_schema.policy_references(policy_name => '"BC_IMMUTADB"."IMMUTA_POLICIES"."RLS_11_b28a03a39a7c0c7f"'));

-- or find out more detail about this row-access policy object
describe row access policy "RLS_11_b28a03a39a7c0c7f";