ALTER ACCESS POLICY

The ALTER ACCESS POLICY statement:

Important:  When you copy or rename a table, access policies associated with the original table are not included in the copied table. You must use ALTER ACCESS POLICY to copy the access policies to the new table.

Syntax

ALTER ACCESS POLICY ON tablename
.. .FOR COLUMN columnname
... expression
... [ENABLE | DISABLE];
... | FOR COLUMN columname
...   COPY TO TABLE tablename;

 

ALTER ACCESS POLICY ON tablename
...  FOR ROWS
....expression
... [ENABLE | DISABLE];
...FOR ROWS
...expression
... COPY TO TABLE tablename;

Parameters

tablename

The name of the table that contains the access policy you want to enable, disable, or copy.

columnname

Either of the following values:

  • The name of the column on which to enable or disable the access policy
  • The name of the column you want to copy
expression

An expression that provides further information to limit column or row access.

  • In a column access policy the expression is the transformation of the column.
  • In a row access policy the expressions is the content of the WHERE clause.

For example, the expression:

=> ALTER ACCESS POLICY on customer_dimension for column customer_key  length('xxxxx') enable;

limits access to strings in the customer_key column to a specific length.

ENABLE/DISABLE
Indicates whether to enable or disable the access policy at the table level.
COPY TO TABLE
Copies the existing access policy to the specified table. Do not use an expression when performing a COPY TO function.

Privileges

You must be a dbadmin user to alter an access policy.

Examples

Copy Access Policy from Table to Table

=> ALTER ACCESS POLICY on customer FOR COLUMN customer_number COPY TO TABLE customer_old;

Enable Access Policy

=> ALTER ACCESS POLICY on customer for rows where cid1>1 enable;