ALTER ACCESS POLICY

Performs one of the following actions on existing access policies:

  • Modify an access policy by changing its expression, and by enabling/disabling the policy.
  • Copy an access policy from one table to another.

Syntax

ALTER ACCESS POLICY ON [[database.]schema.]table 
   { FOR COLUMN column [ expression ] | FOR ROWS [ WHERE expression ] } { GRANT TRUSTED } { ENABLE | DISABLE }
ALTER ACCESS POLICY ON [[database.]schema.]table 
   { FOR COLUMN column | FOR ROWS } COPY TO TABLE table;

Parameters

[database.]schema

Database and schema. The default schema is public. If you specify a database, it must be the current database.
table

The name of the table that contains the access policy you want to enable, disable, or copy.
FOR COLUMN column [expression]

Replaces the access policy expression that was previously set for this column. Omit expression from the FOR COLUMN clause in order to enable or disable this policy only, or copy it to another table.
FOR ROWS [WHERE expression]

Replaces the row access policy expression that was previously set for this table. Omit WHERE expression from the FOR ROWS clause in order to enable or disable this policy only, or copy it to another table.
GRANT TRUSTED

Specifies that GRANT statements take precedence over the access policy in determining whether users can perform DML operations on the target table. If omitted, users can only modify table data if the access policy allows them to see the stored data in its original, unaltered state. For more information, see Access Policies and DML Operations.

GRANT TRUSTED only affects DML operations and does not enable users to see data that the access policy would otherwise mask. Specifying this option may allow users with certain grants to update data that they cannot see.
ENABLE | DISABLE Indicates whether to enable or disable the access policy at the table level.
COPY TO TABLE tablename

Copies the existing access policy to the specified table. The copied access policy includes its enabled/disabled and GRANT TRUSTED statuses.

The following requirements apply:

  • Copying a column access policy:
    • The target table must have a column of the same name and compatible data type.
    • The target colum must not have an access policy.
  • Copying a row access policy: The target table must not have an access policy.

Privileges

Modify Access Policy

Non-superuser: Ownership of the table

Copy Access Policy

Non-superuser: Ownership of the source and destination tables

Examples

See Managing Access Policies

See Also

CREATE ACCESS POLICY