How to find tables using row access policies in Snowflake

Question

I have created few tables in a schema and a row access policy with "create or replace row access policy <policy_name>.." command. Then I have added the row access policy to the tables with "alter table <table_name> add row access policy <policy_name>..."

It is working as expected. My question is, In future, If I want to get a list of tables in a schema that are having the row access policy, How can I get that?

"show tables .. " command doesn't show that table has a row access policy.

Answer 1

I just had the same issue and I think I figured it out.

Check out the docs here - https://docs.snowflake.com/en/sql-reference/functions/policy_references.html.

You can search for row access policies based on the policy name or the object name that you want to check.

In your example, let's say your table is called my_db.my_schema.my_table. To view row access policies on the table, just run:

use database my_db;
use schema information_schema;
select *
  from table(information_schema.policy_references(ref_entity_name => 'my_db.my_schema.my_table', ref_entity_domain => 'table'));