CREATE ACCESS POLICY

Creates a secure access policy to prevent unauthorized users from accessing potentially sensitive information. You can create access policies for table rows and columns. Access policies are a technique of on-the-fly query modification in which the query excludes rows or modifies data from a column in the results returned to the user running the query. Access policies allow different users to run the same query and receive different results.

Column access policies limit access to specific column in a table. Creating a column access policy depends on the expressions specified when creating the policy. The expression is substituted for the column's actual value in any data fetched from the table.

Row access policies limits access to a specific row in a table. You must use a WHERE clause to set the access policy's condition. Only rows that satisfy the WHERE clause are fetched from the table.

For information on how implementing access policies affects how you manage data, see Working With Access Policies in the Administrator's Guide.

Syntax

CREATE ACCESS POLICY ON [[database.]schema.]table... { FOR COLUMN column | FOR ROWS WHERE } expression ENABLE

Parameters

[database.]schema

Specifies a schema, by default public. If schema is any schema other than public, you must supply the schema name. For example:

myschema.thisDbObject

If you specify a database, it must be the current database.
table

The table that contains the target column.
column

The column on which to apply an access policy.
expression

An SQL expression that specifies conditions for column or row access:

  • In a column access policy, the expression defines what value is returned when this column is fetched. The expression can contain conditions such as a role to determine what value to return. The expression might return null if no data is returned for this column.

  • In a row access policy, only rows for which the expression is true are returned.

The expression in the example below shows the first 5 characters of the key in the customer_key column:

=> CREATE ACCESS POLICY ON customer_dimension 
    FOR COLUMN customer_key substr(customer_key, 1, 5) ENABLE;
ENABLE Enables the access policy. Always add this to end of the statement when creating an access policy.

Privileges

One of the following:

Examples

Create access policy per role on column

Add an expression to the policy to specify the access each role receives. A manager can access the complete customer number, while an operator can only see a portion of the number:

=> CREATE ACCESS POLICY on customer FOR column customer_number
CASE
WHEN enabled_role ('manager') then customer_number
WHEN enabled_role ('operator') then substr (customer_number, 8,2)
ELSE NULL
END
ENABLE;

Create access policy for rows 

=> CREATE ACCESS POLICY ON customer FOR ROWS WHERE cid1>1 ENABLE;

See Also

In the Administrator's Guide: