Row-level access control

OceanBase Database is compatible with Oracle’s label security feature, which enables access control at the row level, ensuring the security of reading and writing data. Security policies for finer-grained access control of row-level data can be built based on label security. After creating a policy, you can apply it to the tables that need protection, and grant labels to users.

Label security is a type of mandatory access control that involves adding a Label column to a table to record the Label value of each row. When accessing the data, the user’s Label is compared to the data’s Label to constrain the subject’s (user’s) access to the object (data in the table).

OceanBase Database provides a built-in security administrator LBACSYS to manage and use the label security feature. The security administrator can customize their own security policies by creating security policies, defining labels in the policies, and setting user labels. A security policy can be applied to multiple tables, and multiple security policies can be applied to one table. When a security policy is applied to a table, a column is automatically added to the table to control the table access.

Limitations

Currently, only the Oracle mode of OceanBase Database supports label security.

Set row-level access control

1. Log on to an Oracle tenant as the sys user and unlock the LBACSYS user.

   OceanBase Database will create an LBACSYS user by default, who is in the locked state. To use the label security feature, you need to unlock this user first.

   obclient> ALTER USER LBACSYS ACCOUNT UNLOCK;
   

2. Change the logon password for the LBACSYS user.

   For security purposes, the password for the LBACSYS user is specified by the sys user. If you need to change this password later, simply specify a new one.

   obclient> ALTER USER LBACSYS IDENTIFIED BY ***R***;
   

3. Grant privileges to the LBACSYS user.

   The LBACSYS user has no privileges by default. To set row-level access control, you must grant the CREATE SESSION privilege to the LBACSYS user using the GRANT statement.

   obclient> GRANT CREATE SESSION TO LBACSYS;
   

4. Log on to the Oracle tenant as the LBACSYS user.

5. Create a security policy.

   A security policy named MY_POLICY has been created in this example.

   For more information about how to create a label security policy, see SA_SYSDBA.CREATE_POLICY.

   Here is an example:

   obclient> CALL SA_SYSDBA.CREATE_POLICY('MY_POLICY','MY_LABEL','');
   

6. Create a level and specify its short name and long name.

   For more information about how to create a level, see SA_COMPONENTS.CREATE_LEVEL.

   Here is an example:

   obclient> CALL SA_COMPONENTS.CREATE_LEVEL('MY_POLICY', 100, 'PUBLIC','Public Level');
   obclient> CALL SA_COMPONENTS.CREATE_LEVEL('MY_POLICY', 200, 'INTERNAL','Internal Level');
   obclient> CALL SA_COMPONENTS.CREATE_LEVEL('MY_POLICY', 300, 'HIGH', 'High Level');
   obclient> SELECT * FROM sys.ALL_VIRTUAL_TENANT_OLS_COMPONENT_REAL_AGENT;
   +-----------+-----------------------+--------------------+-----------+----------+------------+----------------+-------------+------------------------------+------------------------------+
   | TENANT_ID | LABEL_SE_COMPONENT_ID | LABEL_SE_POLICY_ID | COMP_TYPE | COMP_NUM | SHORT_NAME | LONG_NAME      | PARENT_NAME | GMT_CREATE                   | GMT_MODIFIED                 |
   +-----------+-----------------------+--------------------+-----------+----------+------------+----------------+-------------+------------------------------+------------------------------+
   |      1004 |                500019 |             500018 |         0 |      100 | PUBLIC     | PUBLIC LEVEL   | NULL        | 09-DEC-22 02.38.37.835729 PM | 09-DEC-22 02.38.37.835729 PM |
   |      1004 |                500020 |             500018 |         0 |      200 | INTERNAL   | INTERNAL LEVEL | NULL        | 09-DEC-22 02.38.49.336692 PM | 09-DEC-22 02.38.49.336692 PM |
   |      1004 |                500021 |             500018 |         0 |      300 | HIGH       | HIGH LEVEL     | NULL        | 09-DEC-22 02.39.02.742233 PM | 09-DEC-22 02.39.02.742233 PM |
   +-----------+-----------------------+--------------------+-----------+----------+------------+----------------+-------------+------------------------------+------------------------------+
   3 rows in set (0.001 sec)
   
   

7. Create a label.

   For more information about how to create a label, see SA_LABEL_ADMIN.CREATE_LABEL.

   Here is an example:

   obclient> DELIMITER /
   obclient> BEGIN
               SA_LABEL_ADMIN.CREATE_LABEL('MY_POLICY',10000,'PUBLIC',TRUE);
               SA_LABEL_ADMIN.CREATE_LABEL('MY_POLICY',20000,'INTERNAL',TRUE);
               SA_LABEL_ADMIN.CREATE_LABEL('MY_POLICY',30000, 'HIGH',TRUE);
             END;
            /
   Query OK, 0 rows affected
   obclient> DELIMITER;
   obclient> SELECT * FROM sys.ALL_VIRTUAL_TENANT_OLS_LABEL_REAL_AGENT;
   +-----------+-------------------+--------------------+-----------+----------+------+------------------------------+------------------------------+
   | TENANT_ID | LABEL_SE_LABEL_ID | LABEL_SE_POLICY_ID | LABEL_TAG | LABEL    | FLAG | GMT_CREATE                   | GMT_MODIFIED                 |
   +-----------+-------------------+--------------------+-----------+----------+------+------------------------------+------------------------------+
   |      1004 |            500022 |             500018 |     10000 | PUBLIC   |    1 | 09-DEC-22 04.25.59.611117 PM | 09-DEC-22 04.25.59.611117 PM |
   |      1004 |            500023 |             500018 |     20000 | INTERNAL |    1 | 09-DEC-22 04.25.59.636390 PM | 09-DEC-22 04.25.59.636390 PM |
   |      1004 |            500024 |             500018 |     30000 | HIGH     |    1 | 09-DEC-22 04.25.59.651032 PM | 09-DEC-22 04.25.59.651032 PM |
   +-----------+-------------------+--------------------+-----------+----------+------+------------------------------+------------------------------+
   3 rows in set (0.001 sec)
   

8. Specify a user label.

   1. Log on to the Oracle tenant as the SYS user, create a user named user1, and then grant all privileges of the SYS user, except for the GRANT OPTION privilege, to user1.

      obclient> CREATE USER user1 IDENTIFIED BY *******;
      Query OK, 0 rows affected
      
      obclient> GRANT ALL PRIVILEGES TO user1;
      Query OK, 0 rows affected
      

   2. Log on as the security administrator LBACSYS and specify a label for user1.

      For more information about how to specify a label for a user, see SA_USER_ADMIN.SET_LEVELS.

      obclient> CALL SA_USER_ADMIN.SET_LEVELS('MY_POLICY', 'user1', 'HIGH','PUBLIC', 'PUBLIC', 'PUBLIC');
      Query OK, 0 rows affected
      
      obclient [LBACSYS]> SELECT * FROM sys.ALL_VIRTUAL_TENANT_OLS_USER_LEVEL_REAL_AGENT;
      +-----------+------------------------+---------+--------------------+---------------+---------------+---------------+-----------+------------------------------+------------------------------+
      | TENANT_ID | LABEL_SE_USER_LEVEL_ID | USER_ID | LABEL_SE_POLICY_ID | MAXIMUM_LEVEL | MINIMUM_LEVEL | DEFAULT_LEVEL | ROW_LEVEL | GMT_CREATE                   | GMT_MODIFIED                 |
      +-----------+------------------------+---------+--------------------+---------------+---------------+---------------+-----------+------------------------------+------------------------------+
      |      1004 |                 500027 |  500025 |             500018 |           300 |           100 |           100 |       100 | 09-DEC-22 04.31.23.965076 PM | 09-DEC-22 04.31.23.965076 PM |
      +-----------+------------------------+---------+--------------------+---------------+---------------+---------------+-----------+------------------------------+------------------------------+
      1 row in set (0.002 sec)
      

9. Apply the security policy to a table.

   1. Log on to the Oracle tenant as user1 and create a table named tbl1.

      obclient> CREATE TABLE tbl1(col1 INT,col2 INT);
      Query OK, 0 rows affected
      

   2. Log on as the security administrator LBACSYS and apply the security policy named MY_POLICY to tbl1.

      For more information about how to apply a specified policy to a table, see SA_POLICY_ADMIN.APPLY_TABLE_POLICY.

      obclient> CALL SA_POLICY_ADMIN.APPLY_TABLE_POLICY('MY_POLICY','user1', 'tbl1', '', '', '');
      Query OK, 0 rows affected
      

10. Insert data and verify the result.

11. Log on as user1. Then, insert the col1 and MY_LABEL columns into tbl1.

    obclient> INSERT INTO tbl1 VALUES(1,1,10000);
    Query OK, 1 row affected
    
    obclient> INSERT INTO tbl1(MY_LABEL) VALUES(20000);
    Query OK, 1 row affected
    
    obclient> INSERT INTO tbl1(col1) VALUES (2);
    Query OK, 1 row affected
    
    obclient> COMMIT;
    Query OK, 0 rows affected
    

12. Set SESSION LABEL and ROW LABEL under user1.

    For more information about how to set a label for the current database session, see SA_SESSION.SET_LABEL.

    For more information about how to set the default row label for the current database session, see SA_SESSION.SET_ROW_LABEL.

    obclient> CALL SA_SESSION.SET_LABEL('MY_POLICY', 'INTERNAL');
    Query OK, 0 rows affected
    
    obclient> CALL SA_SESSION.SET_ROW_LABEL('MY_POLICY', 'INTERNAL');
    Query OK, 0 rows affected
    
    obclient> SELECT SA_SESSION.LABEL('MY_POLICY') FROM DUAL;
    +-------------------------------+
    | SA_SESSION.LABEL('MY_POLICY') |
    +-------------------------------+
    | INTERNAL                      |
    +-------------------------------+
    1 row in set
    
    obclient> SELECT SA_SESSION.ROW_LABEL('MY_POLICY') FROM DUAL;
    +-----------------------------------+
    | SA_SESSION.ROW_LABEL('MY_POLICY') |
    +-----------------------------------+
    | INTERNAL                          |
    +-----------------------------------+
    1 row in set
    

13. Query data from tbl1. You can see that user1 can access data at the INTERNAL level and lower.

    obclient> SELECT * FROM tbl1;
    +------+------+----------+
    | COL1 | COL2 | MY_LABEL |
    +------+------+----------+
    |    1 |    1 |    10000 |
    | NULL | NULL |    20000 |
    |    2 | NULL |    10000 |
    +------+------+----------+
    3 rows in set
    

14. Modify the label and query the table again. You can see that user1 can only access data at the PUBLIC level.

    obclient> CALL SA_SESSION.SET_LABEL('MY_POLICY', 'PUBLIC');
    Query OK, 0 rows affected
    
    obclient> CALL SA_SESSION.SET_ROW_LABEL('MY_POLICY', 'PUBLIC');
    Query OK, 0 rows affected
    
    obclient> SELECT * FROM tbl1;
    +------+------+----------+
    | COL1 | COL2 | MY_LABEL |
    +------+------+----------+
    |    1 |    1 |    10000 |
    |    2 | NULL |    10000 |
    +------+------+----------+
    2 rows in set