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.
Syntax
CREATE ACCESS POLICY ON [schema.]table‑name ... { FOR COLUMN column‑name | FOR ROWS WHERE } expression ENABLE
Parameters
schema |
Specifies a schema. If multiple schemas are defined in the database, include the schema name. For example: myschema.thisDbObject |
table‑name |
The table that contains the target column. |
column‑name |
The column on which to apply an access policy. |
expression |
An SQL expression that specifies conditions for column or row access:
The expression in the example below shows the first 5 characters of the key in the => 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: