Description
This statement is used to modify existing Row-Level Security (RLS) policies, including role lists, USING / WITH CHECK expressions, and policy renaming.
Privilege requirements
To execute the ALTER POLICY statement, the current user must have the object-level ALTER privilege on the target table. If the table has already been covered by a database-level or global ALTER privilege, that also meets the requirement. For more information, see Permission classification.
Limitations and considerations
- You cannot use the
ALTER POLICYstatement to modify theAS PERMISSIVE/RESTRICTIVEorFORclause. To change these attributes, you must drop and recreate the policy. - A single
ALTER POLICYstatement can modify the role list,USING, andWITH CHECKclauses simultaneously. Unspecified attributes remain unchanged. - After a policy is modified, existing business sessions do not need to reconnect; subsequent SQL statements will use the new policy.
Syntax
-- Modify Policy Attributes
ALTER POLICY policy_name ON [schema_name.]table_name
[TO role_name [, role_name ...]]
[USING (using_expression)]
[WITH CHECK (check_expression)];
-- Rename Policy
ALTER POLICY policy_name ON [schema_name.]table_name
RENAME TO new_policy_name;
Parameters
Parameters |
Note |
|---|---|
| policy_name | The name of the policy to be modified. |
| table_name | The name of the object to which the policy belongs. You can use database_name.table_name Specify the database explicitly. |
| TO | Modify the applicable role list. Omitted or PUBLIC Represents all users. |
| USING | Row visibility filtering expressions (for reading, updating old rows, and deleting). |
| WITH CHECK | Write operation new row validation expression (for newly inserted or updated rows). |
| RENAME TO | Rename the policy to new_policy_name. |
The description and limitations are as follows:
- When modifying attributes, all three clauses—
TO,USING, andWITH CHECK—are optional, but at least one of them must be specified. - The role list specified by
TOreplaces the existing binding (overwriting it rather than appending). UsingTO PUBLICchanges the role policy to apply to all users. - When using
TOto specify a role list, the current session must be activated with any role in the list; only existing role names can be used. - When modifying
USINGorWITH CHECK, follow the same clause validity rules as CREATE POLICY, for example, theWITH CHECKclause is not allowed in aFOR SELECTpolicy. - The result of
USINGorWITH CHECKmust be a boolean type. Column references, functions, and expressions are subject to the same restrictions as those in CREATE POLICY. - If
WITH CHECKis omitted andUSINGis present, the write check equalsUSING. - If the new row does not satisfy the
WITH CHECKcondition, the write operation returns the errorOB_ERR_POLICY_WITH_CHECK_OPTION_VIOLATION. - The renamed policy name must be unique within the same table; the same policy name can be used in different tables.
Examples
The following examples are for reference only. Adjust them according to your actual situation:
-- Change the policy to apply to all users
ALTER POLICY emp_dept_policy ON hr.employees TO PUBLIC;
-- Modify Role and Expression Simultaneously
ALTER POLICY emp_dept_policy ON hr.employees
TO manager
USING (dept_id = GET_MY_DEPT())
WITH CHECK (dept_id = GET_MY_DEPT());
-- Rename Policy
ALTER POLICY emp_dept_policy ON hr.employees RENAME TO emp_dept_rls;