Classified: FAQs on Access Policies

Posted May 2, 2016 by Sarah Lemaire, Manager, Vertica Documentation

 classified

In Vertica 7.2.2 we’ve added more security features, including a row-level access policy option. Combined with our previously existing column access policy, Vertica verifies that your data is more secure than ever.

When can I use this feature?

Let’s say you maintain a database of employees, including their id, name, and address. It’s the end of the year and you want to make sure all your employee’s addresses are up to date for tax purposes.

Here’s a portion of your table:
=> SELECT * FROM employeeTax;
employee_id | name | street | state | zipcode | salary
-------------+---------+--------------------+-------+---------+-------
101 | Sue | 7 State Street | MA | 02140 | 100000
356 | William | 37A Park Circle | CA | 90210 | 99000
103 | Steven | 12478 Main Street | CA | 92130 | 55000
107 | Debby | 2 Ash Street | MA | 02138 | 77500
165 | Mark | 15 Center Street | MA | 02142 | 75000
245 | Nancy | 57763 Maple Drive | MA | 02133 | 120000 …

To achieve your goal, you could have users view the table and check if the address on file is correct. However, you don’t want to let everyone to see everyone else’s information, especially their id number and salary. So what if you block the most sensitive data?

With column-level access, you could allow all users to access only the name and address fields. While this method successfully hides each employee’s id and salary, it also allows any employee to see any other employee’s address.

How can you make sure a user sees only their own information?

The answer involves the Vertica row-level access policy feature. With row-level access policies, you can restrict a user’s access to only the rows you want them to see. In this example, you can make sure that each employee only sees the row associated with themselves. Or, you can allow a user to see their own row along with the rows associated with any of their direct trainees.

How do I use this feature?

To verify that employee only sees the appropriate information, use the CREATE ACCESS POLICY statement:
CREATE ACCESS POLICY ON [schema][tablename]
... | FOR COLUMN column...
| FOR ROWS where
[expression] [ENABLE | DISABLE];

For this  example, you’ll use the FOR ROWS option with an expression that limits the viewable table to specific rows, depending on the user. In a row access policy, the expression is the content of the WHERE clause.

 

classsified2

 

As the DBADMIN user, you can still see the whole table:
=> SELECT * FROM employeeTax;
employee_id | name | street | state | zipcode | salary
-------------+---------+--------------------+-------+---------+-------
101 | Sue | 7 State Street | MA | 02140 | 100000
356 | William | 37A Park Circle | CA | 90210 | 99000
103 | Steven | 12478 Main Street | CA | 92130 | 55000
107 | Debby | 2 Ash Street | MA | 02138 | 77500
165 | Mark | 15 Center Street | MA | 02142 | 75000
245 | Nancy | 57763 Maple Drive | MA | 02133 | 120000
(6 rows)

Now, create user Mark and grant him privileges on the table:

=> CREATE USER Mark IDENTIFIED BY ‘Markpassword’; => GRANT ALL ON employeeTax TO Mark;

When Mark queries the table, he’ll only see his information:
=> SELECT * FROM employeeTax;
employee_id | name | street | state | zipcode | salary
-------------+------+------------------+-------+---------+--------
165 | Mark | 15 Center Street | MA | 2142 | 75000
(1 row)

Mark sees this output because Vertica goes through the rows in the table and enables Mark access only to rows that satisfy the WHERE clause. In other words, Vertica allows Mark to view rows where the name column matches his name.

What else can I do?

Combined, you can use row-level and column-level access policies to control exactly what information each user has access to.

For example, maybe you want to allow managers to be able to look up employees and their corresponding id numbers. But you don’t want them to have access to an employee’s street address. You can create a row- level access policy and a column-level access policy on the employeeTax table to achieve this.

First, create a manager role and give it to Nancy and Sue:

=> CREATE ROLE manager;
=> GRANT manager to Sue;
=> GRANT manager to Nancy;

Next, create a row-level access policy so that only managers (or the employee themselves) can view a specific employee’s row:

  1. Drop the previous access policy:=> DROP ACCESS POLICY ON employeeTax FOR ROWS;
  2. Create a new policy on the rows:

     classified3

    Now, normal users can view only their own row and anyone with the manager role can see all rows.

  3. Sue sees only her row:

    As user Sue:

    => SELECT * FROM employeeTax;
    employee_id | name | street | state | zipcode | salary
    ------------+------+----------------+-------+---------+--------
    101 | Sue | 7 State Street | MA | 2140 | 100000
    (1 row)

    Once Sue sets the manager role:

    => SET ROLE manager;

    She can see all the rows:

    As user Sue:

    => SELECT * FROM employeeTax;
    employee_id | name | street | state | zipcode | salary
    ------------+---------+--------------------+-------+---------+--------
    101 | Sue | 7 State Street | MA | 2140 | 100000
    103 | Steven | 12478 Main Street | CA | 92130 | 55000
    107 | Debby | 2 Ash Street | MA | 2138 | 77500
    165 | Mark | 15 Center Street | MA | 2142 | 75000
    245 | Nancy | 57763 Maple Drive | MA | 2133 | 120000
    356 | William | 37A Park Circle | CA | 90210 | 99000
    (6 rows)

  4. Next, create a column-level policy so the managers can’t view the street column:

     classified4

As you can see, after creating and enabling these policies, user Mark can still see his row:

As user Mark:
=> SELECT * FROM employeeTax;
employee_id | name | street | state | zipcode | salary
-------------+------+------------------+-------+---------+--------
165 | Mark | 15 Center Street | MA | 2142 | 75000
(1 row)

And the managers can see all rows but nothing in the street column:

As user with manager role:
=> SELECT * FROM employeeTax;
employee_id | name | street | state | zipcode | salary
-------------+---------+--------+-------+---------+--------
101 | Sue | | MA | 2140 | 100000
103 | Steven | | CA | 92130 | 55000
107 | Debby | | MA | 2138 | 77500
165 | Mark | | MA | 2142 | 75000
245 | Nancy | | MA | 2133 | 120000
356 | William | | CA | 90210 | 99000
(6 rows)

From the basic examples described above, you can see how these security policies can provide huge benefits to companies with high volumes of very sensitive data. Health care companies, banks, universities, you name it, they need security. And Vertica is here to help.

Read more

Check out our other blog on access policies: For Your Eyes Only: Using Vertica Access Policies and stay tuned for a new video about access policies!