MaxCompute: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-level access policy to grant permissions to the specified object (user or role).

• Parameters

  Parameter	Description
  policy_name	The name of the row-level access policy. You can specify a custom name.
  table_name	The name of the table to be accessed.
  authorized_objects	The object to which permissions are granted. Valid values: USER <user_list>: the names of the users to which permissions are granted. Separate multiple user names with commas (,). ROLE <role_list>: the names of the roles to which permissions are granted. Separate multiple role names with commas (,). DEFAULT: the default policy when a user policy or role policy is not met.
  filter_expr	The filter expression. For more information, see Limits on filter_expr.
  clause	The attributes of the row-level access policy. Valid values: PERMISSIVE and RESTRICTIVE. For more information, see Specify the PERMISSIVE or RESTRICTIVE attribute.

  • Limits on filter_expr

    filter_expr has the following limits in this version:

    • The value of filter_expr must be a scalar expression and of the BOOLEAN data type.

    • filter_expr cannot contain subqueries or statements such as SELECT, CREATE, and UPDATE.

    • filter_expr can reference only constants or fields in the authorized table. filter_expr cannot reference fields in other tables.

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

    • filter_expr can contain only specific built-in scalar functions. This parameter cannot call UDFs, aggregate functions, or window functions. filter_expr supports the following functions:

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

      • Mathematical functions: ABS and ROUND.

      • Time functions: DATEADD, TO_DATE, and TO_CHAR.

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

  • Description of the PERMISSIVE and RESTRICTIVE attributes

    Multiple policies may take effect on a specific user. The system combines multiple policies to determine whether the user can access a row of data. When you create a row-level access policy, you can use AS {PERMISSIVE | RESTRICTIVE} to specify the PERMISSIVE or RESTRICTIVE attribute for the policy. If you do not specify the attribute, the default value is PERMISSIVE. Example:

    Multiple policies take effect on a user.

    • If the attributes of the policies are PERMISSIVE, the relationship between the policies is OR. In this case, the user can access a row of data if the value of filter_expr in one of the policies is true.

    • If the attributes of the policies are RESTRICTIVE, the relationship between the policies is AND. In this case, the user can access a row of data only if all policies are met.

    • If the attributes of specific policies are PERMISSIVE and the attributes of other policies are RESTRICTIVE, the user can access a row of data only if the following two conditions are met:

      • At least one of the policies whose attributes are PERMISSIVE is met.

      • All policies whose attributes are RESTRICTIVE are met.

    Note

    Each time you add a row-level access policy to a table, you must evaluate the overall effect of the combination of all row-level access policies on the table. For example, if a RESTRICTIVE policy and a PERMISSIVE policy are configured for a user at the same time, the PERMISSIVE policy must also be met.

• Scenarios

  • Grant permissions to the specified user.

    In this example, a table named table01 contains a field whose data type is STRING and whose column name is region. Configure a row-level access policy for specific users to allow the users to access only the records in which the value in the region field is china. Sample statement:

    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 the specified role.

    In this example, two roles named role1 and role2 exist in the system. Grant permissions to the two roles so that they can access only records whose value in the region field is china. Sample statement:

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

  • Grant permissions to the default user.

    For example, approximately 100 potential users may access a specific table. The administrator separately grants permissions to one of the users by using the authorization command. Access control is also required for other users. If you grant permissions to a specific user or role on the table, row-level access control is enabled for the table. Other users cannot access data in the table because they do not have access permissions on the table. In this case, the administrator can execute the following statement to modify the access permissions of the default user.

    By default, other users are not allowed to access the table. This is the same as the effect of specifying that the default user does not have access permissions.

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

    If you want to restrict the default user to access only the records in which the value of the region field is other, execute the following statement:

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

    Important

    When you add a row-level access policy to a table for a specific user, you must consider the access behavior of other users. If the table has been accessed by other users, you must also configure a policy to allow access from the users to avoid unexpected access prohibition errors.

• Authentication logic

  The following figure shows the authentication process when a user accesses a table for which row-level access policies are configured.

  

DROP

• 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

Views the permission details of a policy on a table.

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

LIST

• Lists all policies in a table.

  LIST ROW ACCESS POLICY ON <table_name>;

• Views the policies that are configured for a user in a table.

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

• Views the policies that are configured for a role in a table.

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

Check of compatible behaviors

When MaxCompute performs calculations based on a filter expression, the result may be affected by flag parameters. If users define a row-level access policy under a compatible behavior and define other row-level access policies under another compatible behavior, data leaks may occur when the result does not meet expectations. Therefore, when MaxCompute performs row-level access control, it checks the compatible behavior that is performed when the row-level access policy is defined. If the compatible behaviors are inconsistent, an error is reported and the access is prohibited.

Example: This example shows the application of a policy under different compatible behaviors based on sample data.

1. The behavior when the second parameter of the SUBSTR function is set to 0 is affected in the Hive-compatible data type edition. For more information, see SUBSTR.

   • If the Hive-compatible data type edition is enabled, the result obtained when the start position of the SUBSTR function is 0 is the same as the result obtained when the start position of the SUBSTR function is 1.

     SET odps.sql.hive.compatible=true;
     SELECT substr('abc', 0);
     -- If the Hive-compatible data type edition is enabled, the result obtained when the start position of the SUBSTR function is 0 is the same as the result obtained when the start position of the SUBSTR function is 1. 
     +-----+
     | _c0 |
     +-----+
     | abc |
     +-----+

   • If the Hive-compatible data type edition is disabled, the return value is an empty string when the start position of the SUBSTR function is 0.

     SET odps.sql.hive.compatible=false;
     SELECT substr('abc', 0);
     -- If the Hive-compatible data type edition is disabled, the return value is an empty string when the start position of the SUBSTR function is 0.
     +-----+
     | _c0 |
     +-----+
     |     |
     +-----+

2. When you create a row-level access policy, the system checks the operators and functions used in filter_expr. If the behaviors of the operators and functions depend on specific flag parameters, the system records these flag parameters in Settings. You can execute the DESC statement to view the flag parameters.

   -- Drop all policies that are configured for a table.
   DROP ALL row access policy ON policy_test;
   
   -- Configure a policy in the Hive-compatible data type edition. The SUBSTR function is used 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 policy details in the policy_test table.
   DESC row access policy policy04 on policy_test;

   The following result is returned. The value of the odps.sql.hive.compatible parameter is recorded in Settings.

   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 applied, the system determines whether Settings of the current environment are consistent with Settings configured when the policy is created. If they are inconsistent, an error is reported.

   • In the Hive-compatible data type edition, the policy is applied.

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

     Returned result:

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

   • In an edition other than the Hive-compatible data type edition, the query fails because the value of the odps.sql.hive.compatible parameter is different from the value that is specified when the policy is created.

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

     Returned result:

     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.

Tunnel download behavior

For tables for which row-level access policies are configured, you must comply with the row-level access policies when you use Tunnel commands to download table data. However, MaxCompute Tunnel does not have the computing capabilities to execute the row-level access control filtering logic. The system starts an SQL task to filter data based on the rules and then downloads the computing results of the task.

Therefore, when you use Tunnel commands or Tunnel SDK to download data from a MaxCompute table for which row-level access policies are configured, a period of time is required to wait for the running of the SQL task.

Prohibition of the creation of row-level access policies

If a project administrator wants to prohibit the creation of new row-level access policies in a project, the project administrator can run the following command to modify the project properties:

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

Valid values:

• false: You are allowed to create row-level access policies. This is the default value.

• true: You are not allowed to create row-level access policies. You are allowed to modify or drop existing policies.