Creating Column Access Policies
Creating a column access policy allows different users to run the same query and receive different results. For example, you can create an access policy authorizing access to a column of bank account numbers. You can specify that a user with the role employee cannot access this information. However, you do give access to a user with a manager role.
Conditions specified in the access policy determine whether the user can see data restricted by the policy. This example shows how you can specify that the manager role can view the entire Social Security number while the operator role can only view the last four digits. The first five digits are masked for the operator role (THEN SUBSTR (SSN, 8, 4)). The 8 indicates the operator sees data starting on the eighth character (such as 123-45-6789).
=> CREATE ACCESS POLICY ON customers_table FOR COLUMN SSN CASE WHEN ENABLED_ROLE('manager') THEN SSN WHEN ENABLED_ROLE('operator') THEN SUBSTR(SSN, 8, 4) ELSE NULL END ENABLE;
Access Policy Limitations
When you use column access policies, be aware of thefollowing limitations:
- When using an access policy you cannot use any of the following in an expression:
- Aggregate functions
- Subquery
- Analytics
- UDT
- If the query cannot be folded by the Vertica optimizer, all functions other than
SELECT
are blocked. The following error message appears:
ERROR 0: Unable to INSERT: "Access denied due to active access policy on table <tablename> for column <columnname>
Note: Folding a query refers to the act of replacing deterministic expressions involving only constants, with their computed values.
- You cannot create a column access policy on temporary tables.
- It is recommended to not use a column access policy on a flex table. If you create a column access policy on a flex table, the following appears:
WARNING 0: Column Access Policies on flex tables may not be completely secure
Examples
The following examples show how to create a column access policy for various situations.
Create Access Policy in Public Schema for Column in Customer Table
=> CREATE ACCESS POLICY on public.customer FOR COLUMN cid length('xxxxx') enable;
Use Expression to Further Specify Data Access and Restrictions
In this example, a user with a supervisor role can see data from the deal_size column in the vendor_dimension table. However, a user assigned an employee role cannot.
=> CREATE ACCESS POLICY ON vendor_dimension FOR COLUMN deal_size CASE WHEN ENABLED_ROLE('supervisor') THEN deal_size WHEN ENABLED_ROLE('employee') THEN NULL END ENABLE;
Substitute Specific Data for Actual Data in Column
In this example, the value 1000 appears rather than the actual column data:
=> CREATE ACCESS POLICY on public.customer FOR COLUMN cid 1000 enable;
=> SELECT * FROM customer;
cid | dist_code
------+----
1000 | 2
1000 | 10
(2 rows)