Access Policies
You can create the following access policy types to restrict access to sensitive information to only those users authorized to view it:
Important: If you have a table with both a row level access policy and a column level access policy, Vertica filters the row level access policy first. Then Vertica uses the column level access policy to filter the columns.
Use Cases
Column Access Policy Use Case
Base a column access policy on a user's role and the privileges granted to that role.
For example, in a healthcare organization, customer support representatives and account managers have access to the same customer table. The table contains the column SSN
for storing customer Social Security numbers. to which customer support representatives have only partial access,to view the last four digits. The account manager, however, must be able to view entire Social Security numbers. Therefore, the manager role has privileges to view all nine digits of the social security numbers.
When creating a column access policy, use expressions to specify exactly what different users or roles can access within the column.
In this case, a manager can access the entire SSN
column, while customer support representatives can only access the last four digits:
=> CREATE ACCESS POLICY ON schema.customers_table
FOR COLUMN SSN
CASE
WHEN ENABLED_ROLE('manager') THEN SSN
else substr(SSN, 8, 4)
END
ENABLE;
Row Access Policy Use Case
You can also create a row access policy on the same table. For example, you can modify access to a customer table so a manager can view data in all rows. However, a broker can see a row only if the customer is associated with that broker:
=> select * from customers_table; custID | password | ssn -------+----------+--------- 1 | secret | 12345678901 2 | secret | 12345678902 3 | secret | 12345678903 (3 rows)
Each customer in the customers_table has an assigned broker:
=> select * from broker_info; broker | custID --------+--------- u1 | 1 u2 | 2 u3 | 3
Create the access policy to allow a manager to see all data in all rows. Limit a broker's view to only those customers to which the broker is assigned:
=> CREATE ACCESS POLICY ON schema.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;
Access Policy Creation Workflow
You can create access policies for any table type, columnar, external, or flex. You can also create access policies on any column type, including joins.
If no users or roles are already created, you must create them before creating an access policy:
- The user enables the role with the SET ROLE statement (unless the administration user assigned a default role to the user)
- Create the access policy with the CREATE ACCESS POLICY statement.