Row-level access control

CREATE/REPLACE

• Syntax

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

• Description

  Creates or modifies a Row Access Policy to grant permissions to a specified user or role.

• Parameters

  Parameter	Description
  policy_name	The name of the Row Access Policy. You can customize the name.
  table_name	The name of the table to be accessed.
  authorized_objects	The object to be authorized. Valid values: USER <user_list>: A list of usernames to be authorized, separated by commas. ROLE <role_list>: The list of role names to be granted. Multiple role names are separated by commas. DEFAULT: The default rule that applies when no user rule or role rule is matched.
  filter_expr	The filter expression. For more information, see filter_expr description.
  clause	The attribute of the Row Access Policy. The value can be PERMISSIVE or RESTRICTIVE. For more information, see Specifying the PERMISSIVE or RESTRICTIVE Attribute.

  • filter_expr description

    The current version imposes strict constraints on filter_expr:

    • filter_expr must be a scalar expression that evaluates to a BOOLEAN type.

    • The expression cannot contain subqueries or statements such as SELECT, CREATE, or UPDATE.

    • filter_expr can only reference constants or columns from the authorized table. It cannot reference columns from other tables.

    • You can use built-in MaxCompute operators, including relational, arithmetic, bitwise, and logical operators. For more information, see Operators.

    • Only a subset of built-in scalar functions is allowed. User-defined functions (UDFs), aggregate functions, and window functions are not supported. The supported functions are:

      • String functions: CONCAT, CONCAT_WS, GET_JSON_OBJECT, INSTR, LENGTH, LENGTHB, REGEXP_EXTRACT, REGEXP_REPLACE, REVERSE, SUBSTR, TOLOWER, TOUPPER, TRIM, LTRIM, RTRIM, REPLACE.

      • Mathematical functions: ABS, ROUND.

      • Date and time functions: DATEADD, TO_DATE, TO_CHAR.

      • Other functions: SIZE, FIELD, COALESCE, IF, SPLIT.

  • Specify the PERMISSIVE or RESTRICTIVE attribute

    For a specific user, multiple Policies may apply. These policies are then combined to determine whether the user can ultimately access a specific row of data. When you create a Row Access Policy, you can use AS {PERMISSIVE | RESTRICTIVE} to specify the policy attribute as PERMISSIVE or RESTRICTIVE. If you do not specify an attribute, the policy is PERMISSIVE by default. The following is an example:

    If multiple policies apply to a user:

    • If all policies are PERMISSIVE, they combine with an OR relationship. A user can access a row if any policy’s filter_expr evaluates to true.

    • If all policies are RESTRICTIVE, they combine with an AND relationship. A user can access a row only if all policies are satisfied.

    • If some policies are PERMISSIVE and others are RESTRICTIVE, a user can access a row only if both of the following conditions are met for that row:

      • At least one of the PERMISSIVE policies is satisfied.

      • All of the RESTRICTIVE policies are satisfied.

    Note

    Each time you add a new Row Access Policy to a table, you must evaluate the combined effect of all policies on the table. For example, if a user is subject to both RESTRICTIVE and PERMISSIVE policies, they must satisfy the conditions of all RESTRICTIVE policies and at least one PERMISSIVE policy.

• Use cases

  • Grant permissions to a specific user

    Assume that a table is named table01 and contains a STRING column named region. A Row Access Policy is set for some users to restrict their access to only the records where the value in the region column is china. The command example is as follows:

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

  • Grant permissions to a specific role

    Suppose that the system has two roles, role1 and role2. You can authorize these two roles to access only the records where the region field is china. The command is as follows:

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

  • Grant permissions to the default user

    When you add the first Row Access Policy to a table, access becomes restricted. Users who do not match any policy lose access to the data because they are not on an allowlist. To control access for the remaining users, you can configure a default policy. At this point, the administrator can use the following commands to modify the access permissions for the default user.

    Denying access to all other users by default is equivalent to specifying that the default user has no access permissions.

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

    If you want to restrict the Default user to accessing only records where the region field is other, the command is as follows:

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

    Important

    When adding a Row Access Policy to a table, consider the access behavior of users other than those being controlled. If other users previously accessed the table, you must set explicit rules for them to avoid unexpected access denial errors.

• Authorization logic

  The following flowchart shows the authorization process when a user accesses a table with a Row Access Policy.

DROP

• Deletes a specific policy from a table.

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

• Deletes all policies from a table.

  DROP ALL ROW ACCESS POLICY ON <table_name>;

DESC

Views the details of a specific policy on a table.

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

LIST

• Lists all policies on a table.

  LIST ROW ACCESS POLICY ON <table_name>;

• Views the policies configured for a specific user on a table.

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

• Views the policies configured for a specific role on a table.

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

Compatibility behavior check

When MaxCompute evaluates a filter expression, its behavior may be affected by flag parameters. If a user defines a Row Access Policy under one compatibility behavior and then defines other policies under a different behavior, data leakage can occur due to unexpected results. Therefore, when row-level access control is enforced, the system checks the compatibility behavior at the time of definition. If the settings are inconsistent, an error is reported, and access is denied.

Example: This example uses the sample data to show how policies are applied under different compatibility behaviors.

1. The behavior of the SUBSTR function when its second parameter is 0 is affected by Hive compatibility mode. For more information, see SUBSTR.

   • In Hive compatibility mode, a start position of 0 for the SUBSTR function produces the same result as a start position of 1.

     SET odps.sql.hive.compatible=true;
     SELECT substr('abc', 0);
     -- In Hive compatibility mode, a start position of 0 is treated the same as a start position of 1.
     +-----+
     | _c0 |
     +-----+
     | abc |
     +-----+

   • Outside of Hive compatibility mode, a start position of 0 returns an empty string.

     SET odps.sql.hive.compatible=false;
     SELECT substr('abc', 0);
     -- Outside of Hive compatibility mode, a start position of 0 returns an empty string.
     +-----+
     | _c0 |
     +-----+
     |     |
     +-----+

2. When you create a Row Access Policy, the system checks the operators and functions used in filter_expr. If their behavior depends on certain Flag parameters, these parameters are recorded in Settings. You can use the DESC command to view the relevant Flag parameters.

   -- Delete all policies on the table.
   DROP ALL row access policy ON policy_test;
   
   -- Configure a policy in Hive compatibility mode, using the SUBSTR function in filter_expr.
   SET odps.sql.hive.compatible=true;
   CREATE row access policy policy04 ON policy_test TO default filter using(substr(b, 0)='1');
   
   -- View the details of the policy configured on the policy_test table.
   DESC row access policy policy04 on policy_test;

   The following result is returned: The Settings field shows the value of the odps.sql.hive.compatible parameter.

   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

3. When the policy is subsequently applied, the system checks if the current environment's Settings are consistent with the Settings at the time of policy creation. If they are inconsistent, an error is reported.

   • Apply the policy in Hive compatibility mode.

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

     The following result is returned:

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

   • In non-Hive compatible mode, the query fails to execute because the value of the odps.sql.hive.compatible parameter is inconsistent with the value specified when the policy was created.

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

     The following result is returned:

     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.

MaxCompute Tunnel download behavior

When you download data from a table with a Row Access Policy by using MaxCompute Tunnel, the rules of row-level access control must still be followed. However, Tunnel cannot execute the filtering logic itself. Instead, it starts an SQL task to filter the data and then downloads the result.

Therefore, when you use Tunnel commands or the Tunnel SDK to download data from a MaxCompute table with a Row Access Policy, there is a waiting period while the SQL task is executed.

Disabling the Creation of Row Access Policies

To prevent the creation of new Row Access Policies in a project, a project administrator can run the following command to modify the project properties:

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

Valid values are:

• false (default): Allows new Row Access Policies to be created.

• true: Prohibits new Row Access Policies from being created, but allows existing policies to be modified and deleted.