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:

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)

See Also