Column Privileges¶

When you grant the EXECUTE privilege to a user/role over a view, this user or the users with this role can:

• Execute a SELECT statement to query this view

• If the user/role also has the INSERT/UPDATE/DELETE privileges over a view, they can execute INSERT/UPDATE/DELETE statements over this view.

In some scenarios, you may want to limit the access to certain columns of a view to users. The columns of a view that cannot be queried or modified through INSERT/UPDATE/DELETE statements are called protected columns.

For example, let us say you have a view “employee”. You may want to forbid users with the role “developer” to see the column “salary”. That is, you want “salary” to be a protected column for the users of the role “developer”. To do this, follow these steps:

1. Click the menu Administration > Role Management. In the tables with roles, select the role “developer”.

2. Click Assign privileges and on the row of the database of the view “employee”, click edit.

3. Select, at least, the privilege “Execute” over this view.

4. Select the row of the view “employee” and click Assign column privileges.

5. In this dialog, clear the check box next to the field “salary”.

With this change, the user will be able to run this query:

SELECT ename
FROM employee

However, this one will fail:

SELECT ename, salary
FROM employee

If instead of making the query fail, you want to mask the values of a column, you can specify a row restriction with column masking (see section Row Restrictions below).

When assigning column privileges, consider this:

• If you assign column privileges to a user over a view, the statement executed by this user will fail if the query references a protected column of this view in any of the clauses of the query (SELECT, WHERE, GROUP BY, etc.). This is to ensure that users cannot guess the value of protected columns by for example, using them in the WHERE clause. Find an example of this in the next section.

• Column privileges do not affect global administrators or administrators of the database to which the view belongs. They can reference protected columns in their statements.

• The Execution Engine takes into account the column privileges, row restrictions and custom policies granted over a view to a user/role, regardless of if the view with the column privilege is directly referenced in the query or not.

The following table lists the behavior of column privileges for each DML statement:

Row Restrictions¶

When a user/role has the EXECUTE/UPDATE/DELETE privilege over a view, you can define a restriction over that view to restrict the rows returned to that user/role or affected by the UPDATE/DELETE statements. That way, when that user executes a DML operation over the view, they will only obtain/update/delete the rows that match a certain condition and/or some of the fields of some rows will be masked.

For example, let us say you want to restrict the access to the view “employee” to users with role “developer”. To assign a row restriction to a user/role: edit that user/role, select the view you want to restrict (“employee” in our example), click Assign restrictions and then click New restriction.

In this dialog, you first need to specify a condition. The user will be able to see the rows that do meet the condition you enter (e.g. position <> 'manager'). For the rows that do not meet the condition, you have to select one of the following actions:

1. Reject row. In our example, the user will only see/update/delete the employees who are not managers.

2. Reject row if any or all sensitive fields are used (you can select which ones). For example, you could allow the user to see all the employees’ data if the statement does not reference use the column “salary”. If the select/update/delete operation references the column “salary” then the statement will only affect the employees that are not managers.

3. Mask sensitive fields if any or all of them are used (you can select which ones). For example, you could allow the user to see all the employees’ data, but for the ones who are managers, the values of the field salary will be replaced by a masking expression.

The following table shows the masking expressions to replace sensitive values.

The following table shows the REDACT default expressions per type.

You can develop what is called a custom policy that takes into account any information you want to decide if the result of the query is filtered or not. The section Custom Policies of the Developer Guide explains what they are and how to develop them.

When assigning row restrictions, consider this:

• The row restrictions do not affect global administrators or administrators of the database to which the view belongs. I.e. no row will be rejected or masked regardless for them.

• The Execution Engine takes into account the column privileges, row restrictions and custom policies granted over a view to a user/role, regardless of if the view with the column privilege is directly referenced in the query or not.

• The option Reject row if any/all sensitive fields are used and the masking are applied when the query references a sensitive field of this view from any of the clauses of the query (SELECT, WHERE, GROUP BY, etc.). This is to avoid users guessing the value of a sensitive field by using it from the WHERE clause. For example, if the sensitive field salary was only masked on the SELECT clause, the user could try to guess the values using queries like:

SELECT ename FROM employee WHERE salary > 50000 and salary < 100000;

or

UPDATE employee SET ename = ename || '_100000' WHERE salary > 100000

The following table shows the behavior of “Reject Row” restrictions, for each type of DML statement:

For example, let us say you assign a row restriction over the view “employee” to the role “sales_manager”. This row restriction rejects rows that do not verify the condition department = 'sales'.

If a user with role “sales_manager” executes:

SELECT * FROM employee

SELECT * FROM employee WHERE department = 'sales'

If the user has the UPDATE privilege over employee and executes:

UPDATE employee SET manager_id = 1 WHERE manager_id = 2

UPDATE employee SET manager_id = 1 WHERE manager_id = 2 AND department = 'sales'

The following table shows the behavior of restrictions “Reject Row if sensitive fields are used”, for each type of DML statement:

For example, let us say you assign a row restriction over the view “employee” to the role “developer”. This row restriction rejects rows that do not verify the condition position <> 'manager', but only if the statement uses the sensitive column salary.

If a user with role “developer” executes:

SELECT ename FROM employee

SELECT ename FROM employee WHERE salary > 50000

SELECT ename FROM employee WHERE salary > 50000 and position <> 'manager'

The same way, if the same user executes:

CREATE MATERIALIZED TABLE employee_salary AS SELECT ename, salary FROM employee

SELECT ename, salary FROM employee WHERE position <> 'manager'

The following table show the behavior of restrictions “Mask sensitive fields if ANY/ALL of them are used”, for each type of statement:

Using the previous example, if a user with role “developer” executes:

SELECT ename, salary FROM employee

It will return all rows, but for those rows corresponding to managers, the values of salary will be masked.

The same way, if the same user executes the query:

SELECT ename FROM employee WHERE salary > 50000

The user will see the result of the following query:

SELECT ename FROM employee WHERE CASE WHEN (position <> 'manager') THEN salary ELSE null END > 50000

This is, the names for the employees whose position is not manager.

If the same user has the privilege DELETE over employee and executes:

DELETE FROM employee

It will delete all employees. However, if the user executes:

DELETE FROM employee WHERE salary > 50000

It will only delete the rows WHERE salary > 50000 and position is not ‘manager’