Categories:

Table, View, & Sequence DDL

ALTER ROW ACCESS POLICY

Modifies the properties for an existing row access policy, including renaming the policy or replacing the policy rules.

Any changes made to the policy rules go into effect when the next SQL query that uses the row access policy runs.

See also:

Table, View, & Sequence DDL

In this Topic:

Syntax

ALTER ROW ACCESS POLICY [ IF EXISTS ] <name> SET BODY -> <expression_ON_VAL>

ALTER ROW ACCESS POLICY [ IF EXISTS ] <name> RENAME TO <new_name>

ALTER ROW ACCESS POLICY [ IF EXISTS ] <NAME> SET COMMENT = '<string_literal>'

Parameters

name

Identifier for the row access policy; must be unique in the parent schema of the policy.

The identifier value must start with an alphabetic character and cannot contain spaces or special characters unless the entire identifier string is enclosed in double quotes (e.g. "My object"). Identifiers enclosed in double quotes are also case-sensitive.

For more details, see Identifier Requirements.

expression_ON_VAL

SQL expression that filters the data.

The expression can include Conditional Expression Functions to represent conditional logic, built-in functions, or UDFs to transform the data.

If a UDF or external function is used inside the row access policy body, the policy owner must have OWNERSHIP on the UDF or external function. Users querying a database object that has a row access policy applied to it do not need to have USAGE on the UDF or external function.

COMMENT = 'string_literal'

Adds a comment or overwrites the existing comment for the masking policy.

Default: No value

RENAME TO new_name

Specifies the new identifier for the row access policy; must be unique for your schema. The new identifier cannot be used if the identifier is already in place for a different policy (e.g. masking policy).

For more details, see Identifier Requirements.

Usage Notes

  • If you want to update an existing row access policy and need to see the current definition of the policy, call the GET_DDL function or run the DESCRIBE ROW ACCESS POLICY command.

  • You cannot change the policy signature (i.e. argument name or input/output data type). Similarly, using CREATE OR REPLACE ROW ACCESS POLICY is not supported if the policy is attached to a table or view. If you need to change the signature, execute a DROP ROW ACCESS POLICY statement on the policy and create a new row access policy.

  • Before executing an ALTER statement, you can execute a DESCRIBE ROW ACCESS POLICY statement to determine the argument name to use for updating the policy.

  • Including one or more subqueries in the policy body may cause errors. When possible, limit the number of subqueries, limit the number of JOIN operations, and simplify WHERE clause conditions.

  • Moving a row access policy to a managed access schema (using the ALTER ROW ACCESS POLICY … RENAME TO syntax) is prohibited unless the row access policy owner (i.e. the role that has the OWNERSHIP privilege on the row access policy) also owns the target schema.

Examples

The following example updates the row access policy.

-- determine current definition

desc row access policy rap_table_employee_info;

+-------------------------+-------------+-------------+------+
| name                    | signature   | return_type | body |
+-------------------------+-------------+-------------+------+
| rap_table_employee_info | (V VARCHAR) | BOOLEAN     | true |
+-------------------------+-------------+-------------+------+

alter row access policy rap_table_employee_info set body -> false;