Working With Access Policies
This section describes areas that may affect how you use access policies.
Performing Operations
Having row and column access policies enabled on a table may affect the behavior when you attempt to perform the following DML operations:
- Insert
- Update
- Delete
- Merge
- Copy
- Select
Row Level Access Behavior
On tables where a row access policy is enabled, you can only perform DML operations when the condition in the Row access policy evaluates to TRUE. For example:
Table1 appears as follows:
A | B ---+---- 1 | 1 2 | 2 3 | 3
Create the following row access policy on Table1:
=> CREATE ACCESS POLICY on table1 for ROWS WHERE enabled_role('manager') OR A<2 ENABLE;
With this policy enabled, the following behavior exists for users who want to perform DML operations:
- A user with the manager role can perform DML on all rows in the table, because the WHERE clause in the policy evaluates to TRUE.
- Users with non-manager roles can only perform a SELECT to return data in column A that has a value of less than two. If the access policy has to read the data in the table to confirm a condition, it does not allow DML operations.
Column Level Access Behavior
On tables where a column access policy is enabled, you can perform DML operations if you can view the entire column. For example:
Table1 appears as follows:
A | B ---+---- 1 | 1 2 | 2 3 | 3
Create the following column access policy on Table1:
=> CREATE ACCESS POLICY on Table1 FOR column A NULL::int enable;
In this case users cannot perform DML operations on column A.
Important: Users who can access all the rows and columns in a table with an access policy enabled can perform DML operations. Therefore, when you create an access policy, make sure you construct it in a manner that all row and column data is accessible by at least one user. This allows at least one user to perform any DML that may be required. Otherwise, you can temporarily disable the access policy to perform DML.
Schema Table and Privileges
Only dbadmin users can create access policies. If you want a user to be able to use access policies, you must first assign that user the appropriate privileges.
- Grant schema or table privileges to a table non-owner to allow that user to use the access policy.
- Revoke schema or table privileges to prohibit the user from using the access policy.
This example shows how you can create an access policy without the user being granted privilege for the public schema:
=> CREATE ACCESS POLICY ON public.customers_table FOR COLUMN SSN WHEN ENABLED_ROLE('operator') THEN SUBSTR(SSN, 8, 4)
Enable and Disable Access Policy Creation
Access policies are enabled by default for all tables in the database. To disable and enable the creation of new access policies at the database level, use the ALTER DATABASE statement.
Disable Creation of New Access Policies
=> ALTER DATABASE dbname SET EnableAccessPolicy=0;
Enable Creation of New Access Policies
=> ALTER DATABASE dbname SET EnableAccessPolicy=1;
Limitations on Creating Access Policies with Projections
You can create access policies on columns in tables that are part of a projection. However, you cannot create an access policy on an input table for the following projections:
- Top-K projections
- Aggregate projections
- Projections with expressions
Sometimes, a table already has an access policy and is part of a projection. In such cases, if the Vertica optimizer cannot fold (or compress) the query, the access query is blocked.
Query Optimization Considerations
When using access policies be aware of the following potential behaviors, and design tables optimally.
Design Tables That All Authorized Users Can Access
When Database Designer creates projections for a given table, it takes into account the access policies that apply to the current user. The set of projections that Database Designer produces for the table are optimized for that user's access privileges, and other users with similar access privileges. However, these projections might be less than optimal for users with different access privileges. These differences might have some effect on how efficiently Vertica processes queries from those users. Therefore, when you evaluate projection designs for that table using Database Designer, design a given table so that all authorized users have optimal access.
Avoid Performance Issues Caused by Dynamic Rewrite
To enforce row -level access policies, the system dynamically rewrites user queries. Therefore, query performance may be affected by how row-level access policies are written.
For example, referring to preceding Access Policy Use Cases , run the following query. Enable both the row and column access policies on the customers_table:
=> SELECT * from customers_table;
Vertica rewrites this query plan to:
=> SELECT * from (select custID, password, CASE WHEN enabled_role('manager') THEN SSN else substr(SSN, 8, 4) end AS SSN FROM customers_table
WHERE
enabled_role('broker')
AND
customers_table.custID IN (SELECT broker_info.custID FROM broker_info WHERE broker = current_user())
) customers_table;