Set row-level access control

OceanBase Database is compatible with the label security feature of Oracle databases. You can configure row-level access control to ensure the security of data reads and writes.

Label security is a forcible access control strategy. It records the label value of each row by adding a label column to the table, and compares the user label and data label to constrain the access.

OceanBase Database provides a built-in security administrator LBACSYS to manage and use label security. The security administrator can create customized security strategies by defining labels in the strategies and setting user labels. A security strategy can be applied to multiple tables, and multiple security strategies can be applied to one table. When a security strategy is applied to a table, a column is automatically added to the table to control the table access.

Limitations

The label security feature is supported only in Oracle mode.

Examples

1. Log on to the Oracle tenant with the sys account.

2. Unlock the LBACSYS user.

   The LBACSYS user is created by default and the password is LBACSYS. By default, this user is locked. You must unlock this user before you use the label security feature.

   obclient> ALTER USER LBACSYS ACCOUNT UNLOCK;
   

3. Log on to the Oracle tenant as the security administrator LBACSYS.

4. Create a strategy MY_POLICY. For more information about how to create a new label security strategy, see SA_SYSDBA.CREATE_POLICY.

   For example:

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

5. 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.

   For 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_AGENT;
   +-----------+-----------------------+------------------------------+------------------------------+--------------------+-----------+----------+------------+----------------+-------------+
   | TENANT_ID | LABEL_SE_COMPONENT_ID | GMT_CREATE | GMT_MODIFIED | LABEL_SE_POLICY_ID | COMP_TYPE | COMP_NUM | SHORT_NAME | LONG_NAME | PARENT_NAME |
   +-----------+-----------------------+------------------------------+------------------------------+--------------------+-----------+----------+------------+----------------+-------------+
   | 1002 | 1101710651032553 | 30-DEC-21 02.35.19.069966 PM | 30-DEC-21 02.35.19.069966 PM | 1101710651032553 | 0 | 100 | PUBLIC | PUBLIC LEVEL | NULL |
   | 1002 | 1101710651032554 | 30-DEC-21 02.35.25.787711 PM | 30-DEC-21 02.35.25.787711 PM | 1101710651032553 | 0 | 200 | INTERNAL | INTERNAL LEVEL | NULL |
   | 1002 | 1101710651032555 | 30-DEC-21 02.35.31.772544 PM | 30-DEC-21 02.35.31.772544 PM | 1101710651032553 | 0 | 300 | HIGH | HIGH LEVEL | NULL |
   +-----------+-----------------------+------------------------------+------------------------------+--------------------+-----------+----------+------------+----------------+-------------+
   3 rows in set
   

6. Create a data label and view the result. For more information about how to create a data label, see SA_LABEL_ADMIN.CREATE_LABEL.

   For 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_AGENT;
   +-----------+-------------------+------------------------------+------------------------------+--------------------+-----------+----------+------+
   | TENANT_ID | LABEL_SE_LABEL_ID | GMT_CREATE | GMT_MODIFIED | LABEL_SE_POLICY_ID | LABEL_TAG | LABEL | FLAG |
   +-----------+-------------------+------------------------------+------------------------------+--------------------+-----------+----------+------+
   | 1002 | 1101710651032553 | 30-DEC-21 02.41.06.668980 PM | 30-DEC-21 02.41.06.668980 PM | 1101710651032553 | 10000 | PUBLIC | 1 |
   |      1002 |  1101710651032554 | 30-DEC-21 02.42.09.740930 PM | 30-DEC-21 02.42.09.740930 PM |   1101710651032553 |     20000 | INTERNAL |    1 |
   |      1002 |  1101710651032555 | 30-DEC-21 02.42.09.750423 PM | 30-DEC-21 02.42.09.750423 PM |   1101710651032553 |     30000 | HIGH     |    1 |
   +-----------+-------------------+------------------------------+------------------------------+--------------------+-----------+----------+------+
   3 rows in set
   

7. 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 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 to the database 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> SELECT * FROM sys.ALL_VIRTUAL_TENANT_OLS_USER_LEVEL_AGENT;
      +-----------+------------------------+------------------------------+------------------------------+------------------+--------------------+---------------+---------------+---------------+-----------+
      | TENANT_ID | LABEL_SE_USER_LEVEL_ID | GMT_CREATE                   | GMT_MODIFIED                 | USER_ID          | LABEL_SE_POLICY_ID | MAXIMUM_LEVEL | MINIMUM_LEVEL | DEFAULT_LEVEL | ROW_LEVEL |
      +-----------+------------------------+------------------------------+------------------------------+------------------+--------------------+---------------+---------------+---------------+-----------+
      |      1002 |       1101710651032553 | 30-DEC-21 04.04.06.712035 PM | 30-DEC-21 04.04.06.712035 PM | 1101710651032553 |   1101710651032553 |           300 |           100 |           100 |       100 |
      +-----------+------------------------+------------------------------+------------------------------+------------------+--------------------+---------------+---------------+---------------+-----------+
      1 row in set
      

8. Apply the strategy 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 to the database as the security administrator LBACSYS and apply the strategy named MY_POLICY to tbl1. For more information about how to apply a specified strategy 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
      

9. Insert data and verify the result.

   1. Log on to the database 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
      

   2. Log on to the database as user1 and set the session label and row label. 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
      

   3. Query data from tbl1. You can see that user1 has access to data at the INTERNAL level and the level lower than it.

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

   4. Modify the label and query the table again. You can see that user1 can access only 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