In addition to object-level GRANT, OceanBase AI Database also provides fine-grained access control: it supports granting or restricting permissions on specific columns, as well as row-level security (RLS). This topic describes RLS.
RLS can limit the rows that a regular query can return, and the rows that data modification commands can insert, update, or delete, based on the current user, role, or row data, without changing the business SQL. By default, a table has no RLS policy. As long as a user has SQL object privileges, they can access all rows in the table equally.
Prerequisites and Considerations
The prerequisites are as follows:
- To create, modify, or drop a policy, or to configure the table-level RLS switch, you need the object-level
ALTERprivilege on the target table.
Considerations are as follows:
- Additional restriction: RLS is an additional restriction on top of object privileges. A user must first have the required
SELECT,INSERT,UPDATE, orDELETEprivilege on the target table before RLS is applied. RLS itself does not grant any object privileges. - Independent configuration: Creating a policy (definition) and enabling RLS are two separate operations. Policy configuration does not automatically enable RLS for a table; enabling RLS does not automatically create a policy.
- Configure separately: Configure policies separately for the actual DML operations; do not assume that
FOR SELECTwill automatically protectUPDATEorDELETE. - Role activation: When specifying a role in the
TOclause of a policy, consider the activated roles of the current session, not just the granted roles. For role activation, see Activate a role. After a role authorization changes, a persistent connection requires executing a controlledSET ROLEor reconnecting. SUPERrestriction:SUPERdoes not bypass RLS.- Privilege bypass: RLS supports bypass. If bypass is needed, grant the
BYPASSRLSglobal privilege. For more information, see Permission classification and GRANT.
This section describes only general considerations for the feature. For syntax details, see the SQL syntax documentation. You need to understand both.
Configuration model
To fully configure RLS, you need to configure both the table-level switch and the policy.
Table-level switch
The table-level switch is configured using ALTER TABLE to determine whether RLS is enabled for the table, and whether the table owner is also restricted:
SQL clause |
Note |
|---|---|
ENABLE ROW LEVEL SECURITY |
When RLS is enabled, the policy takes effect for non-exempt users. |
DISABLE ROW LEVEL SECURITY |
RLS is disabled. The policy definition is retained but does not take effect. |
FORCE ROW LEVEL SECURITY |
Table owners are also subject to RLS restrictions. |
NO FORCE ROW LEVEL SECURITY |
Default value. The table owner is not restricted: when ENABLE is set, the table owner still bypasses RLS for the table. You can also execute FORCE to enforce RLS, and then use this clause to restore the default exemption. |
For a newly created table or a table for which this is not explicitly set, it is equivalent to NO FORCE ROW LEVEL SECURITY. Setting FORCE or NO FORCE alone when RLS is not enabled has no practical effect. For an SQL example, see Examples.
Policy configuration
Policies are created by using CREATE POLICY and modified or dropped by using ALTER POLICY / DROP POLICY. For more information about the syntax, restrictions, and examples, see the SQL syntax documentation. For a complete example, see Examples.
Bypass row-level permissions
RLS supports bypass under different circumstances, as follows:
Identity |
Behavior |
|---|---|
Table owner (ENABLE + NO FORCE) |
Bypass the RLS for this table. |
Table owner (FORCE set) |
The table owner is subject to the same policy restrictions as regular users. |
Users with the global BYPASSRLS privilege |
Bypass RLS in all states (including FORCE), including read filtering and write validation. |
Users with the SUPER privilege (such as the root user) |
RLS is not bypassed. |
View row-level permission configuration
View |
Note |
|---|---|
| information_schema.TABLES | View the table-level switch by using the ROW_SECURITY and ROW_SECURITY_FORCED fields. |
| oceanbase.DBA_RLS_POLICIES | View the policy definition. |
Examples
The following example restricts regular users to operating only on rows where owner_account equals the current logged-in account. In this example, owner_account stores the full account returned by CURRENT_USER(), for example, alice@%.
Create a business table.
CREATE TABLE orders ( order_id BIGINT PRIMARY KEY, owner_account VARCHAR(128) NOT NULL, amount DECIMAL(18, 2), order_status VARCHAR(32) );Create a policy.
CREATE POLICY orders_owner_policy ON orders AS PERMISSIVE FOR ALL TO PUBLIC USING (owner_account = CURRENT_USER()) WITH CHECK (owner_account = CURRENT_USER());View the policy configuration.
SELECT TABLE_SCHEMA, TABLE_NAME, ROW_SECURITY, ROW_SECURITY_FORCED FROM information_schema.TABLES WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'orders'; SELECT POLICY_NAME, POLICY_TYPE, COMMAND_TYPE, ROLES, USING_EXPRESSION, WITH_CHECK_EXPRESSION FROM oceanbase.DBA_RLS_POLICIES WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'orders';Note
We recommend that you create and verify the policy configuration before enabling RLS. Before enabling it, test the configuration using the table owner, a regular user, a role user, and the
BYPASSRLSadministrative account.Enable RLS for the table.
ALTER TABLE orders ENABLE ROW LEVEL SECURITY;At this point:
SELECT,UPDATE, andDELETEoperations can only affect rows owned by the current account.INSERToperations can only write to rows whereowner_account = CURRENT_USER().- New rows generated by an
UPDATEoperation must still satisfyowner_account = CURRENT_USER().
Restrict the table owner (optional).
By default, the table owner bypasses RLS. If the table owner also needs to be restricted, execute the following statement:
ALTER TABLE orders FORCE ROW LEVEL SECURITY;