Purpose
This statement is used to create a Row-Level Security (RLS) policy on a table. The policy defines the applicable SQL commands, roles, and the USING / WITH CHECK conditions.
Privilege requirements
To execute the CREATE POLICY statement, the current user must have the object-level ALTER privilege on the target table. If the table already has database-level or global ALTER privileges, this requirement is also met. For more information, see Permission classification.
Limitations and considerations
- Internal system tables, external catalog tables, and objects in the recycle bin do not support creating RLS policies.
- Enabling row-level security for a table is not automatic after creating a policy. You must also execute the
ALTER TABLE ... ENABLE ROW LEVEL SECURITYstatement.
Syntax
CREATE POLICY [IF NOT EXISTS] policy_name ON [schema_name.]table_name
[AS {PERMISSIVE | RESTRICTIVE}]
[FOR {ALL | SELECT | INSERT | UPDATE | DELETE}]
[TO role_name [, role_name ...]]
[USING (using_expression)]
[WITH CHECK (check_expression)]
Parameters
Parameters |
Default Value |
Note |
|---|---|---|
| IF NOT EXISTS | If a policy with the same name already exists, no error is reported; instead, it is silently skipped. | |
| policy_name | Policy name. | |
| table_name | The name of the target object. You can use database_name.table_name Specify the database explicitly. |
|
| AS | PERMISSIVE | Policy type: PERMISSIVE(Loose) or RESTRICTIVE(Strict). For the major compaction rules when multiple policies coexist for the same table, see the description below. |
| FOR | ALL | SQL command types supported by the policy: ALL(Also applies to the following four types of commands), SELECT, INSERT, UPDATE or DELETE. |
| TO | PUBLIC | Applicable roles. Omitted or PUBLIC Represents all users. |
| USING | Row visibility filtering expressions (for reading, updating old rows, and deleting). | |
| WITH CHECK | If omitted and already present USING, then it equals USING |
Write operation new row validation expression (for newly inserted or updated rows). |
The expressions allowed for each FOR type are as follows:
FOR type |
USING |
WITH CHECK |
Purpose |
|---|---|---|---|
| SELECT | Required | Not allowed | Filter rows to read |
| INSERT | Not allowed | Required | Verify new row |
| UPDATE | Optional | Optional | USING Filter out old rows, WITH CHECK Verify new row |
| DELETE | Required | Not allowed | Filter rows to be deleted |
| ALL | Optional | Optional | Also used for four types of commands |
The description and limitations are as follows:
- If a policy with the same name already exists,
IF NOT EXISTSwill only silently skip it and will not replace or merge the original policy. - The policy name must be unique within the same table; the same policy name can be used in different tables.
- Multiple policies on the same table are merged by type: Loose policies (
PERMISSIVE) of the same type are merged usingOR, and strict policies (RESTRICTIVE) of the same type are merged usingAND. When both types exist, all loose policies are merged first, then each is combined with each strict policy usingAND. This results in a format like(PERM1 OR PERM2 OR ...) AND REST1 AND REST2 AND .... If only loose policies are present, any one of them meeting the condition is sufficient; if only strict policies are present, all must meet the condition. - A policy must specify at least one
USINGorWITH CHECKexpression. - For
UPDATEorALL, if onlyUSINGis specified, the write validation will by default reuse theUSINGclause. If onlyWITH CHECKis specified, and there are no availableUSINGconditions for reading, updating old rows, or deleting, the corresponding command will reject all rows. - The result of
USING/WITH CHECKmust be of the Boolean type. Column references, subqueries, and built-in functions are supported; aggregate functions and window functions are prohibited. - If the new row does not satisfy the
WITH CHECKcondition, the write operation returns the errorOB_ERR_POLICY_WITH_CHECK_OPTION_VIOLATION.
Examples
The following examples are for reference only. Adjust them according to your actual situation when using them:
-- Create a Loose Policy for All Users
CREATE POLICY emp_dept_policy ON hr.employees
FOR SELECT
-- Only rows from your own department are visible in SELECT queries
USING (dept_id = CURRENT_USER_DEPT());
CREATE POLICY mgr_write_policy ON hr.employees
-- Create a restrictive policy for the manager role
AS RESTRICTIVE
FOR INSERT, UPDATE
TO manager
USING (dept_id = GET_MY_DEPT())
-- dept_id of the new row during INSERT/UPDATE
WITH CHECK (dept_id = GET_MY_DEPT());
-- No error is reported if an object with the same name already exists.
CREATE POLICY IF NOT EXISTS emp_dept_policy ON hr.employees
USING (1 = 1);