Row Access Policy
Use the CREATE ACCESS POLICY statement to create a row access policy for a specific row in a table. You must use a WHERE clause to set the access policy's condition.
Example
Suppose you have a customers table but want to limit users with the broker role to being able to view only customers to which they are assigned. This example shows how to create a row access policy that allows managers to view everything and brokers to view only their customers.
Run the following SQL statement:
=> SELECT * FROM customers_table;
The customers_table appears as follows:
custID | password | SSN --------+------------+-------------- 1 | secret | 123456789 2 | secret | 123456780 3 | secret | 123456781 (3 rows)
Run the following SQL statement:
=> SELECT * FROM broker_info;
The broker_info table shows that each customer has an assigned broker:
broker | custID --------+--------- user1 | 1 user2 | 2 user3 | 3 (3 rows)
Create the following access policy that only allows brokers to see customers to which they are associated:
=> CREATE ACCESS POLICY on customers_table for rows WHERE ENABLED_ROLE('manager') or (ENABLED_ROLE('broker') AND customers_table.custID in (SELECT broker_info.custID FROM broker_info WHERE broker = CURRENT_USER())) ENABLE;
As user1, run the following SQL command:
user1=> SELECT * FROM customers_table;
The following is returned because user1 is associated with custID 1:
custID | password | SSN --------+------------+-------------- 1 | secret | 123456789 (1 rows)