2

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.

jsrathnayake
  • 139
  • 10
  • 1
    Found it. Following query can be used . select * from table( information_schema.policy_references( policy_name=>'' ) ); – jsrathnayake Feb 01 '22 at 18:03

1 Answers1

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'));
Jeremy Holtzman
  • 26
  • 1
  • It's not the answer though; that response shows the row access policies on a table, whereas your question is what are the tables that a row access policy is applied to. Your comment above with select * from table( information_schema.policy_references( policy_name=>'' ) ); gives the answer. – DavidMWilliams Jul 06 '22 at 00:37