CREATE ACCESS POLICY

Creates an access policy that filters access to table data to users and roles. You can create access policies for table rows and columns. Vertica applies the access policy filters with each query and returns only the data that is permissible for the current user or role.

Syntax

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

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 with the target column or rows.

FOR COLUMN column

The column on which to apply this access policy. Must not be a column with an external data type (see Complex Types).

FOR ROWS WHERE The rows on which to apply this access policy.
expression

An SQL expression that specifies conditions for accessing row or column data:

  • Row access policies limits access to specific rows in a table, as specified by the policy's WHERE expression. Only rows that satisfy this expression are fetched from the table. For details and sample usage, see Creating Row Access Policies.
  • Column access policies limit access to specific table columns. The access policy expression can also specify how to render column data to specific users and roles. For details and sample usage, see Creating Column Access Policies.
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 Specifies whether to enable the access policy. You can enable and disable existing access policies with ALTER ACCESS POLICY.

Privileges

Superuser