I can use something like
select * from table(information_schema.policy_references(ref_entity_name => '<table_name>', ref_entity_domain => 'table'));
But this only works if you have accountadmin role. Is there any other way to get to know if a table has row access policy applied or not ?
Note that you don't need to be accountadmin, but the role needs to have any of the following privileges:
If the role has the global APPLY MASKING POLICY privilege, Snowflake returns all masking policy associations in the query result.
If the role has the global APPLY ROW ACCESS POLICY privilege, Snowflake returns all row access policy associations in the query result.
If the role has the APPLY privilege on a given policy (e.g. APPLY on MASKING POLICY), Snowflake returns associations of that policy only for objects that are owned by the role executing the query.
If the role has either of the global APPLY privileges or the OWNERSHIP privilege on the policy, but not OWNERSHIP on the table or view (e.g. SELECT on the table), Snowflake does not show policy associations in the query result.
If the role does not have any policy permissions but has the OWNERSHIP privilege on the table, Snowflake returns an error message and does not show policy associations.
https://docs.snowflake.com/en/sql-reference/functions/policy_references.html
I have tried below workaround, it makes non accountadmin user to be able to check if a table/view has any row access policy applied:
use role accountadmin;
grant imported privileges on database snowflake to role <the non accountadmin role you want to use>;
use role <the non accountadmin role you want to use>;
SELECT *
FROM SNOWFLAKE.ACCOUNT_USAGE.POLICY_REFERENCES
WHERE POLICY_KIND = 'ROW_ACCESS_POLICY'
AND REF_ENTITY_DOMAIN = '<TABLE/VIEW>'
AND REF_DATABASE_NAME = '<your db name>'
AND REF_SCHEMA_NAME = '<your schema name>'
AND REF_ENTITY_NAME = '<your table/view name>';