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: