Configure and manage row level access control - MaxCompute

Row-level access control lets you define per-user or per-role filter rules on a MaxCompute table so that each principal sees only the rows they are authorized to access. Instead of creating separate views or extract, transform, and load (ETL) pipelines per user, you attach policies directly to the table and MaxCompute enforces them at query time.

How it works

When a user queries a table that has row access policies:

MaxCompute checks whether any row access policies are attached to the table.
MaxCompute evaluates the applicable policies for the querying user or role.
For each candidate row, MaxCompute evaluates the filter expressions of all matching policies.
MaxCompute returns only the rows for which the combined policy evaluation is TRUE.

This filtering applies to SQL queries, Tunnel downloads, and reads from external engines such as Spark and Flink.

Engines that do not support MaxCompute row-level access control—such as Hologres—cannot access policy-protected tables. Use views or data copy approaches for those engines.

When to use row-level access control

Approach	Applies to	Best for
Row access policies	Tables	Controlling row visibility for many users or roles without creating extra objects
Views	Views	Sharing pre-filtered data or applying filters that span multiple tables

Row access policies are most effective when:

Multiple data consumers need access to the same table but with different row ranges.
Consumer access ranges overlap.
The number of consumers is large enough to make per-user view maintenance impractical.

Compared to the method of creating shared objects for each user, row-level access control makes the query execution plans based on the original table more complex. However, it eliminates the need to create shared objects individually for each user and avoids redundant storage of shared objects. It is also more suitable for defining rules for large numbers of users. For more details, see Implement row-level access control.

Prerequisites

Before you begin, make sure you have:

The Admin role or table owner permissions in the MaxCompute project.
The row-level access control feature enabled at the session level (see Enable the feature).

Enable the feature

Run the following statement at the session level before executing any CREATE, DROP, DESC, or LIST commands:

SET odps.sql.row.policy.enabled=true;

This parameter will be enabled by default at the session level in a future release.

Syntax

CREATE ROW ACCESS POLICY

Creates or replaces a row access policy on a table.

CREATE [OR REPLACE] ROW ACCESS POLICY [IF NOT EXISTS] <policy_name>
ON <table_name>
TO <authorized_objects>
FILTER USING <filter_expr>
[AS <clause>];

Parameters

Parameter	Description
`policy_name`	A custom name for the policy.
`table_name`	The table to protect.
`authorized_objects`	The principals to grant access to. Valid values: `USER <user_list>` (comma-separated user names), `ROLE <role_list>` (comma-separated role names), or `DEFAULT` (applies when no user or role policy matches).
`filter_expr`	The row filter expression. Must be a scalar BOOLEAN expression. See filter_expr limits.
`clause`	Policy combination attribute: `PERMISSIVE` (default) or `RESTRICTIVE`. See Combine multiple policies.

Supported platforms

DROP ROW ACCESS POLICY

Drops a specific policy from a table:

DROP ROW ACCESS POLICY <policy_name> ON <table_name>;

Drops all policies from a table:

DROP ALL ROW ACCESS POLICY ON <table_name>;

DESC ROW ACCESS POLICY

Returns the details of a specific policy, including its filter expression, normalization, restrictive flag, and recorded flag settings:

DESC ROW ACCESS POLICY <policy_name> ON <table_name>;

LIST ROW ACCESS POLICY

Lists all policies on a table:

LIST ROW ACCESS POLICY ON <table_name>;

Lists policies for a specific user:

LIST ROW ACCESS POLICY ON <table_name> TO USER <user_name>;

Lists policies for a specific role:

LIST ROW ACCESS POLICY ON <table_name> TO ROLE <role_name>;

Grant access to users, roles, and the default principal

Grant access to specific users

Allow named users to see only rows where region = "china":

CREATE ROW ACCESS POLICY policy01
ON table01
TO USER (aliyun$odps_tes***@aliyun.com, aliyun$odps_tes***@aliyun.com)
FILTER USING (region = "china");

Grant access to specific roles

Allow role1 and role2 to see only rows where region = "china":

CREATE ROW ACCESS POLICY policy02
ON table01
TO ROLE (role1, role2)
FILTER USING (region = "china");

Set a default policy

When any user or role policy is configured on a table, MaxCompute blocks all principals that have no matching policy. Use a DEFAULT policy to control what unlisted users see.

Block all unlisted users:

CREATE ROW ACCESS POLICY policy03
ON table01
TO DEFAULT
FILTER USING (false);

Allow unlisted users to see rows where region = "other":

CREATE ROW ACCESS POLICY policy04
ON table01
TO DEFAULT
FILTER USING (region = "other");

Important

When adding a row access policy for a specific user, also configure a DEFAULT policy to avoid unintentionally blocking other users who already have table access.

Combine multiple policies

When a user matches multiple policies, MaxCompute combines them using the following logic:

PERMISSIVE (default): policies combine with OR. The user can access a row if any one PERMISSIVE policy evaluates to TRUE.
RESTRICTIVE: policies combine with AND. The user can access a row only when all RESTRICTIVE policies evaluate to TRUE.

When both types exist simultaneously, the user can access a row only if:

At least one PERMISSIVE policy evaluates to TRUE.
All RESTRICTIVE policies evaluate to TRUE.

Specify the attribute with the AS clause:

CREATE ROW ACCESS POLICY policy_restrictive
ON table01
TO DEFAULT
FILTER USING (region = "china")
AS RESTRICTIVE;

Each time you add a policy to a table, evaluate the combined effect of all existing policies for all principals.

Authentication flow when a user accesses a policy-protected table:

Examples

The following examples use a shared test table. Set it up before running any example.

-- Create a table.
CREATE TABLE policy_test(a bigint, b string);

-- Insert data into the table.
INSERT OVERWRITE TABLE policy_test VALUES(1L, "1"), (2L, "2"), (3L, "3"), (4L, "4");

-- Verify the data.
SELECT * FROM policy_test;
-- Expected output:
+------------+---+
| a          | b |
+------------+---+
| 1          | 1 |
| 2          | 2 |
| 3          | 3 |
| 4          | 4 |
+------------+---+

Example 1: Single PERMISSIVE policy

Allow the default user to see only the row where a = 2.

Create the policy:

CREATE ROW ACCESS POLICY policy01 ON policy_test TO DEFAULT FILTER USING (a = 2L);

Inspect the policy:

DESC ROW ACCESS POLICY policy01 ON policy_test;

Expected output:

Authorization Type: Row Access Policy
Name: policy01
Objects: acs:odps:*:projects/clone_table_2/tables/policy_test
FilterExpr: (a = 2L)
NormalizedFilterExpr: (policy_test.a = 2L)
Restrictive: false
Settings:

OK

Query the table to confirm the policy is in effect:

SELECT * FROM policy_test;

Expected output:

+------------+---+
| a          | b |
+------------+---+
| 2          | 2 |
+------------+---+

Example 2: Two PERMISSIVE policies (OR logic)

Add a second PERMISSIVE policy so the default user sees rows where a = 2 or a = 3.

Create the second policy:

CREATE ROW ACCESS POLICY policy02 ON policy_test TO DEFAULT FILTER USING (a = 3L);

List all policies on the table:

LIST ROW ACCESS POLICY ON policy_test;

Expected output:

Authorization Type: Row Access Policy
Name: policy01
Objects: acs:odps:*:projects/clone_table_2/tables/policy_test
FilterExpr: (a = 2L)
NormalizedFilterExpr: (policy_test.a = 2L)
Restrictive: false
Settings:
Name: policy02
Objects: acs:odps:*:projects/clone_table_2/tables/policy_test
FilterExpr: (a = 3L)
NormalizedFilterExpr: (policy_test.a = 3L)
Restrictive: false
Settings:

OK

Query the table:

SELECT * FROM policy_test;

Both PERMISSIVE policies combine with OR, so both matching rows are returned:

+------------+---+
| a          | b |
+------------+---+
| 2          | 2 |
| 3          | 3 |
+------------+---+

Example 3: Two PERMISSIVE policies + one RESTRICTIVE policy (AND logic)

Add a RESTRICTIVE policy a < 3. The default user can now see only rows that satisfy (a = 2 OR a = 3) AND a < 3, which is a = 2.

Create the RESTRICTIVE policy:

CREATE ROW ACCESS POLICY policy03 ON policy_test TO DEFAULT FILTER USING (a < 3L) AS RESTRICTIVE;

Inspect the policy:

DESC ROW ACCESS POLICY policy03 ON policy_test;

Expected output:

Authorization Type: Row Access Policy
Name: policy03
Objects: acs:odps:*:projects/clone_table_2/tables/policy_test
FilterExpr: (a < 3L)
NormalizedFilterExpr: (policy_test.a < 3L)
Restrictive: true
Settings:

OK

Query the table:

SELECT * FROM policy_test;

Expected output — only a = 2 satisfies all three policies:

+------------+---+
| a          | b |
+------------+---+
| 2          | 2 |
+------------+---+

Example 4: Conflicting PERMISSIVE and RESTRICTIVE policies (empty result)

Drop policy01, leaving policy02 (PERMISSIVE, a = 3) and policy03 (RESTRICTIVE, a < 3). The only row that satisfies both is a = 3 AND a < 3, which is impossible—so no rows are returned.

Drop policy01:

SET odps.sql.row.policy.enabled=true;
DROP ROW ACCESS POLICY policy01 ON policy_test;

List remaining policies:

SET odps.sql.row.policy.enabled=true;
LIST ROW ACCESS POLICY ON policy_test;

Expected output:

Authorization Type: Row Access Policy
Name: policy02
Objects: acs:odps:*:projects/clone_table_2/tables/policy_test
FilterExpr: (a = 3L)
NormalizedFilterExpr: (policy_test.a = 3L)
Restrictive: false
Settings:
Name: policy03
Objects: acs:odps:*:projects/clone_table_2/tables/policy_test
FilterExpr: (a < 3L)
NormalizedFilterExpr: (policy_test.a < 3L)
Restrictive: true
Settings:

OK

Query the table:

SELECT * FROM policy_test;

Expected output — no rows satisfy both policies simultaneously:

+------------+------------+
| a          | b          |
+------------+------------+
+------------+------------+

Limitations

General limitations

Only the Admin role and table owner can create or modify row access policies.
Row access policies cannot be configured on transactional tables, views, or materialized views.
Materialized views cannot be created on tables that have row access policies.
Row access policies cannot be added to base tables of materialized views.
Views can be created on tables that have row access policies. Query results from those views reflect the row access policies on the base tables combined with the view owner's view rules.
Schema evolution is not supported on tables that have row access policies.
A table with row access policies cannot be added as a user-defined function (UDF) resource, and row access policies cannot be added to tables that are UDF resources. No immediate error is raised, but the related UDF fails at execution time.
Masking rules cannot be added to fields in a table that has row access policies.
Row-level access control does not support partition pruning. Even when a query includes a partition filter such as ds='20220101', a full table scan may be triggered.

Package-based cross-project access

Row access policies can be attached to users outside the current project, as long as they are Alibaba Cloud accounts or RAM users of the current tenant.
When a policy-protected table is accessed from another project via a package, only user policies or default policies apply.
Row access policies cannot be added to a table that already has row access policies in a package.

Usage notes

Flag parameter consistency

The execution of some operators and functions in filter_expr depends on flag parameters (for example, odps.sql.hive.compatible). MaxCompute records the flag values in effect when a policy is created (visible in DESC output under Settings). At query time, MaxCompute checks whether the current flag values match those recorded in the policy. A mismatch causes the query to fail:

FAILED: ODPS-0130071:[0,0] Semantic analysis exception - physical plan generation failed:
java.lang.IllegalArgumentException: Row access policy flag mismatch for: xxx

To avoid this error, keep the session flag settings consistent with those in effect when the policy was created, or contact your project administrator.

Billing and scan volume

Row-level access control does not support partition pruning, so the amount of data scanned is not reduced by policy filtering. In pay-as-you-go billing, charges are based on the full scan volume, not on the filtered result size. To control costs, reduce the amount of data to be scanned.

Tunnel download behavior

MaxCompute Tunnel does not have computing capabilities to evaluate row access policy filter logic inline. When you download data from a policy-protected table using Tunnel commands or the Tunnel SDK, MaxCompute starts an SQL task to filter the data first and then downloads the result. Expect additional latency for the SQL task to complete before the download begins.

filter_expr limits

filter_expr must satisfy the following constraints:

Must be a scalar expression of BOOLEAN type.
Cannot contain subqueries.
Cannot contain SELECT, CREATE, or UPDATE statements.
Can only reference constants or fields in the authorized table. References to fields in other tables are not allowed.
Can contain built-in MaxCompute operators: relational, arithmetic, bitwise, and logical operators. See Operators.
Can call only specific built-in scalar functions. UDFs, aggregate functions, and window functions are not supported.

Supported built-in functions:

Category	Functions
String	CONCAT, CONCAT_WS, GET_JSON_OBJECT, INSTR, LENGTH, LENGTHB, REGEXP_EXTRACT, REGEXP_REPLACE, REVERSE, SUBSTR, TOLOWER, TOUPPER, TRIM, LTRIM, RTRIM, REPLACE
Mathematical	ABS, ROUND
Date and time	DATEADD, TO_DATE, TO_CHAR
Other	SIZE, FIELD, COALESCE, IF, SPLIT

Compatible behavior check

When filter_expr uses functions whose behavior depends on flag parameters, MaxCompute records those parameters in Settings at policy creation time.

Example: The SUBSTR function behaves differently depending on odps.sql.hive.compatible:

When odps.sql.hive.compatible=true, SUBSTR('abc', 0) returns abc (same as position 1).
When odps.sql.hive.compatible=false, SUBSTR('abc', 0) returns an empty string.

The following sequence demonstrates flag recording and consistency checking:

-- Drop all existing policies.
DROP ALL ROW ACCESS POLICY ON policy_test;

-- Create a policy with Hive-compatible mode enabled.
SET odps.sql.hive.compatible=true;
CREATE ROW ACCESS POLICY policy04 ON policy_test TO DEFAULT FILTER USING (substr(b, 0) = '1');

-- Inspect the policy — the flag is recorded in Settings.
DESC ROW ACCESS POLICY policy04 ON policy_test;

Expected output:

Authorization Type: Row Access Policy
Name: policy04
Objects: acs:odps:*:projects/sql_optimizer/tables/policy_test
FilterExpr: substr(b, 0) = '1'
NormalizedFilterExpr: ::substr(policy_test.b, 0) = '1'
Restrictive: false
Settings: odps.sql.hive.compatible=true

Querying with the same flag setting succeeds:

SET odps.sql.hive.compatible=true;
SELECT * FROM policy_test;

Expected output:

+------------+---+
| a          | b |
+------------+---+
| 1          | 1 |
+------------+---+

Querying with a different flag setting fails:

SET odps.sql.hive.compatible=false;
SELECT * FROM policy_test;

Expected output:

FAILED: ODPS-0130071:[0,0] Semantic analysis exception - physical plan generation failed: java.lang.IllegalArgumentException: Row access policy flag mismatch for: odps.sql.hive.compatible, flag value when grant this policy is true, while at runtime is false. please set odps.sql.hive.compatible = true or contact your project manager.

Disable policy creation at the project level

To prevent new row access policies from being created in a project, run the following command as the project administrator:

setproject odps.sql.create.row.policy.disable=true;

Important

Only the project administrator can set odps.sql.create.row.policy.disable at the project level using setproject. Users cannot change this parameter at the session level.

Valid values:

false (default): Creating row access policies is allowed.
true: Creating new row access policies is blocked. Existing policies can still be modified or dropped.

What's next

Implement row-level access control — an end-to-end guide using views and shared objects as an alternative approach.
Operators — reference for operators supported in filter_expr.