Creating Row Access Policies
Creating a row access policy determines what rows a user can access during a query. Row access policies include a WHERE clause that prompts the query to return only those rows where the condition is true. For example, a user with a BROKER role should only be able to access customer information for which the user is a broker. You can write a predicate for this situation as follows:
WHERE ENABLED_ROLE('broker') AND customers_table.custID in (SELECT broker_info.custID FROM broker_info WHERE broker = CURRENT_USER())
You can use a row access policy to enforce this restriction. The following example shows how you can create a row access policy. This policy limits a user with a broker role to access information for customers whose custID in the customers_table matches the custID in the broker_info table.
=> CREATE ACCESS POLICY on customers_table
for rows
WHERE ENABLED_ROLE('broker')
AND
customers_table.custID in (SELECT broker_info.custID FROM broker_info WHERE broker = CURRENT_USER())
enable;
Row Access Policy Limitations
Be aware of the following limitations when using row access policies:
- You can only have one row access policy per table. If you need to add more later, place the policies in a single WHERE predicate and use ALTER ACCESS POLICY to enable the new condition.
- You cannot use row access policies on:
- Tables with aggregate projections
- Temporary tables
- System tables. If you try to create a row access policy on a system table the following message appears:
=> ROLLBACK 0: Access policy cannot be created on system table <system table name>
- Views
- When a row access policy exists on a table, you cannot create directed queries on that table.
Examples
The following examples show you can create a row access policy:
Create Access Policy in for specific row in Customer Table
=> CREATE ACCESS POLICY on customer FOR ROWS where cust_id > 3 enable;
