How to get all the masking policies created in a particular account in snowflake? Is there any view to see it?
show masking policies only retrieves data related to the policies and not where it is applied?
How can I get all policies and in which columns in which tables it is applied?
You can query the information schema table-function POLICY_REFERENCES, see here: https://docs.snowflake.com/en/sql-reference/functions/policy_references.html
Here is also an example from the docs:
use database my_db;
use schema information_schema;
select *
from table(information_schema.policy_references(policy_name => 'ssn_mask'));
Important: You have to execute the USE DATABASE ... and USE SCHEMA INFORMATION_SCHEMA commands or use a full qualified identifier.
If this is not enough, you can query all policies with all tables and all columns when you combine the query above with your SHOW MASKING POLICIES; and the RESULT_SCAN()-function.
RESULT_SCAN() allows you to query the results of SHOW MASKING POLICIES; (https://docs.snowflake.com/en/sql-reference/functions/result_scan.html)
Consequence: You get all names of policies and for each of them you can call POLICY_REFERENCES().
