PolarDB-X Enterprise Edition: Row-level Access Control

By Xingyi

This article introduces the label-based access control (LBAC) feature in PolarDB-X, which enables fine-grained access control over both rows and columns of data. This feature restricts user access and operations on data in a detailed manner, ensuring secure read and write operations.

1. Background

Database software has evolved over decades to become a critical technology in IT systems. Databases store large amounts of sensitive data, making access control and permission management essential for ensuring data security and privacy. MySQL provides permission management with the GRANT statement, allowing users or user groups to perform specific operations on database objects at various levels. PolarDB-X goes beyond standard permission management by offering enterprise-level features such as transparent data encryption (TDE) to further enhance permission management and data encryption, helping to prevent data leaks, tampering, and damage, and reducing security risks at the database level.

MySQL only provides permission management at the table and column levels, making it impossible to control read and write access for individual rows of data. However, access control over data rows is often needed in real-world applications. PolarDB-X addresses this issue by implementing LBAC, allowing precise control over data access at both the row and column levels, ensuring secure read and write operations. This article covers the design and usage of the LBAC feature of PolarDB-X, illustrated in real-world application scenarios.

2. LBAC Application Scenario

A company stores all employee personal information (name, years of service, performance, salary, and more) in the PolarDB-X database. Some employees have database accounts that allow them to query and update data. To ensure data security, the company has implemented the following access control policies: Employee A can access the personal information of Employee B only if all the following conditions are met:

• The rank of Employee A is higher than that of Employee B.

• Employee A is in the same department or a higher department than Employee B.

• Employee A and Employee B have the same job type.

PolarDB-X uses LBAC to enforce these requirements by assigning a security label to each employee. When Employee 1 queries information about Employee 2, the query is successful only if label1 >= label2. How should security labels be designed to meet these data access control policies?

The security label consists of three components: rank, type, and structure, representing the employee's rank, job type, and department, respectively. Different comparison rules apply to each component:

• Rank: Compared by order. For example, M5 >= M4, M4 >= M3, M3 >= M2.

• Type: Compared by inclusion. For example, (pd, se) >= (pd), (pd) >= (pd).

• Structure: Compared by hierarchy. For example, division >= teamB, teamA >= group1.

• An empty value in any component is considered the smallest value.

To ensure label1 >= label2, each component of label1 must be greater than or equal to the corresponding component of label2.

• label1.rank >= label2.rank

• label1.type >= label2.type

• label1.structure >= label2.structure

As illustrated:

• label1 >= label2, level4Label

• bossLabel >= label1/2/3, level4Label

Security labels can protect not only individual employee records (rows) but also specific aspects of employee data (columns). For example, if only the boss is allowed to view salary information in the employee table, the salary column can be assigned a security label of bossLabel. Users can query this column only if their security label is greater than or equal to bossLabel. The PSL column (PolarSecurityLabelColumn) stores the security label for each row of data.

3. LBAC Basic Concepts

3.1 Security Label

A security label is used to control access to data.

• Entities that can be assigned a security label: rows, columns, users.

• How LBAC works: It compares the security labels of users with those of rows and columns to decide if access is allowed.

• Comparison criteria for security labels: CanRead, CanWrite. These rules can be customized.

3.2 Security Policy

A security policy defines the structure and set of security labels.

• Consistency: All security labels within a security policy follow the same structure.

• Comparability: Only security labels within the same security policy can be compared.

• Applicable entities: tables. This means all rows and columns in a table must use security labels from the same security policy.

3.3 Security Label Component

A security policy uses one or more security label components to define the structure of security labels.

• Types of security label components: Array, Set, Tree.

• Comparison of security labels: This involves comparing their internal components.

• Comparison of components:

• Array: Compared by order.

• Set: Compared by inclusion.

• Tree: Compared by hierarchy

3.4 PolarSecurityLabelColumn (PSL column)

The PolarSecurityLabelColumn is a special column in the PolarDB-X database for storing security labels. This column is named _polar_security_label, and its data type must be varchar.

• The PSL column stores the names of security labels, not their details.

• When a table has a PSL column, its data is protected by LBAC.

• The PSL column functions in a similar way as any other regular column.

4. Using LBAC

This section explains how to implement data access control policies using the LBAC feature in the PolarDB-X database, based on the scenarios described above.

4.1 Creating Security Attributes

First, design the security label model based on the application scenario.

1. Create security label components

create security label component
'rank' array 'm5,m4,m3,m2';
create security label component
'type' set 'se,op,pd';
create security label component
'structure' tree '(division,teama);(division,teamb);(teama,group1);(teama,group2);(teamb,group3);(teamb,group4)';

Security label components are the building blocks of security labels. Here: rank represents employee levels. type represents job types. structure represents departments. Different components have different rules for comparison.

2. Create a security policy

create security policy
'staff_data_access' components 'rank,type,structure';

A security policy defines the structure for security labels. Security labels under this policy must follow the defined structure. The staff_data_access policy here requires labels to include rank, type, and structure components.

3. Create security labels

create security label
staff_data_access.label1 '(m4):(pd):(teamb)';
create security label
staff_data_access.label2 '(m3):(pd):(group4)';
create security label
staff_data_access.label3 '(m2):(se):(group3)';
create security label
staff_data_access.bossLabel'(m4):(pd,se,op):(division)';
create security label
staff_data_access.level4Label '(m4):():()';
create security label
staff_data_access.level2Label '(m2):():()';

Here, six security labels are created, all under the staff_data_access policy, sharing the same structure.

• label1, label2, and label3 represent regular employees with different levels, job types, and departments.

• bossLabel represents a high-privilege security label with the highest level, all job types, and the top-level department.

• level2Label and level4Label only specify the level component, indicating that only employees of the specified level or higher can access data, without restricting job types and departments.

4.2 Creating Users

create user tmb_pd_ld identified by 'password';
grant all privileges on *.* to tmb_pd_ld;
grant security label
staff_data_access.label1 to user 'tmb_pd_ld'@'%' for read access;
grant security label
staff_data_access.label2 to user 'tmb_pd_ld'@'%' for write access;

In PolarDB-X, each user can have two types of security labels for each security policy: read_label and write_label. These labels control what data a user can read and write. The read_label must always be greater than or equal to the write_label, ensuring that the data a user can write is a subset of what they can read. Here, a database account tmb_pd_ld@'%' is created for the product leader of group4 in the teamB department:

• read_label is label1, allowing the user to read information about employees at level m4 and below, who work in the product job type, in teamB and its subordinate departments.

• write_label is label2, allowing the user to write information about employees at level m3 and below, who work in the product job type, in group4 and its subordinate departments.

4.3 Data Access Control

UDF test

PolarDB-X provides functions for LBAC:

• lbac_user_write_label(security_policy): Returns the write_label of the current user for the specified security policy.

• lbac_user_read_label(security_policy): Returns the read_label of the current user for the specified security policy.

• lbac_check(security_label1, security_label2): Returns whether security_label1 is greater than or equal to security_label2.

//1. Check if label1 is greater than or equal to other labels
select lbac_check('label1','label1'); //Returns 1
select lbac_check('label1','label2'); //Returns 1
select lbac_check('label1','label3'); //Returns 0
select lbac_check('label1','bossLabel'); //Returns 0
select lbac_check('label1','level4Label'); //Returns 1
select lbac_check('label1','level2Label'); //Returns 1
//2. Check if label2 is greater than or equal to other labels
select lbac_check('label2','label1'); //Returns 0
select lbac_check('label2','label2'); //Returns 1
select lbac_check('label2','label3'); //Returns 0
select lbac_check('label2','bossLabel'); //Returns 0
select lbac_check('label2','level4Label'); //Returns 0
select lbac_check('label2','level2Label'); //Returns 1

Create a data table

// Log in as the root user
create table staff_info(
  username varchar(255),
  seniority int column secured with level2Label,
  performance varchar(50) column secured with level4Label,
  salary int column secured with bossLabel,
  _polar_security_label varchar(255)
)security policy=staff_data_access;
insert into staff_info values("name1",10,"A",20000,"bossLabel");
insert into staff_info values("name2",6,"B",15000,"label1");
insert into staff_info values("name3",3,"C",10000,"label2");
insert into staff_info values("name4",2,"B",8000,"label3");
insert into staff_info values("name5",5,"C",12000,"label1");
insert into staff_info values("name6",4,"B",11000,"label2");
insert into staff_info values("name7",1,"D",5000,"label3");

Based on the LBAC function test results, the user tmb_pd_ld has the following permissions:

• Readable columns: username, seniority, performance, _polar_security_label

• Writable columns: username, seniority, _polar_security_label

• Readable rows: name2, name3, name5, name6

• Writable rows: name3, name6

To verify the LBAC access control capability of PolarDB-X, log in as the tmb_pd_ld user and test the execution of Select, Update, Insert, and Delete SQL statements.

Select

Scenario 1: Query all employee information

According to the results:

• If the query involves columns that the current user cannot read, the query will fail.

• Rows that the current user cannot read will be invisible to them.

Scenario 2: Query employees with salaries above RMB 10,000

According to the results, if the WHERE clause contains columns that the user cannot read, the query will fail.

Scenario 3: Query the difference between the salary of each employee and the average salary

According to the results, if subqueries contain columns that the user cannot read, the query will fail. In addition, if the ORDER BY, GROUP BY, or WINDOW functions contain columns that the user cannot read, the query will fail.

Update

Scenario 1: Increase years of service by 1

According to the results:

• The current user cannot read the data row for employee name1, so 0 rows were updated, and the update failed.

• The current user can read the data row for employee name2 but cannot write updates, so 0 rows were updated, and the update failed.

• The current user can read and write the data row for employee name3, so 1 row was updated, and the update succeeded.

Scenario 2: Increase employee salary by RMB 1,000

According to the results, if the user tries to update a data row that includes columns they do not have write access to, the SQL execution will fail.

Scenario 3: Update the PSL column for employees

In PolarDB-X, to update the PSL column in a table, the write_label of the current user must be greater than or equal to the new label value. According to the results, updating the PSL column for employee name3 to label1 failed, and updating the PSL column for employee name3 to label2 succeeded.

Insert

Scenario 1: Insert an employee record

According to the results, if the user tries to insert a record into a column that they cannot write to, the SQL execution will fail.

In PolarDB-X, to insert a record containing a PSL column, the write_label of the current user must be greater than or equal to the label value being inserted. According to the results, when the current user tried to insert label1, the SQL execution failed.

In PolarDB-X, when the user inserts a record that does not contain a PSL column, the PSL column value defaults to the write_label of the current user. According to the results, the PSL column value for employee name11 is label2.

Delete

Scenario 1: Deleting all employee records

According to the results, to delete data, the user must have write access to all columns in the table. If any column cannot be written to, the SQL execution will fail. To better demonstrate the delete operation, let's create a new table where all columns can be written to.

// Log in as the root user
create table staff_info_new(
  username varchar(255),
  seniority int column secured with level2Label,
  performance varchar(50) column secured with level2Label,
  salary int column secured with level2Label,
  _polar_security_label varchar(255)
)security policy=staff_data_access;
insert into staff_info_new values("name1",10,"A",20000,"bossLabel");
insert into staff_info_new values("name2",6,"B",15000,"label1");
insert into staff_info_new values("name3",3,"C",10000,"label2");
insert into staff_info_new values("name4",2,"B",8000,"label3");
insert into staff_info_new values("name5",5,"C",12000,"label1");
insert into staff_info_new values("name6",4,"B",11000,"label2");
insert into staff_info_new values("name7",1,"D",5000,"label3");

According to the results, only rows which can be written to can be deleted. Rows that cannot be written to cannot be deleted.

5. Summary

This article provides a detailed introduction to the design and implementation of the LBAC feature in PolarDB-X, with real-world application scenarios as a basis. LBAC serves to enhance data access control by assigning security labels to users, rows, and columns, and then restricting access based on the comparison of user labels with those of the data rows and columns. This ensures fine-grained control over data access.

Try out database products for free:

Community

PolarDB-X Enterprise Edition: Row-level Access Control

1. Background

2. LBAC Application Scenario

3. LBAC Basic Concepts

3.1 Security Label

3.2 Security Policy

3.3 Security Label Component

3.4 PolarSecurityLabelColumn (PSL column)

4. Using LBAC

4.1 Creating Security Attributes

4.2 Creating Users

4.3 Data Access Control

UDF test

Create a data table

Select

Update

Insert

Delete

5. Summary

Read previous post:

Read next post:

ApsaraDB

You may also like

Comments

ApsaraDB

Related Products

PolarDB for Xscale

PolarDB for MySQL

PolarDB for PostgreSQL

AnalyticDB for MySQL