Help Center/ GaussDB(DWS)/ Standard Data Warehouse (8.1.3.x)/ Database Security Management/ Sensitive Data Management/ Row-Level Access Control
Updated on 2023-10-31 GMT+08:00
View PDF

Row-Level Access Control

The row-level access control feature restricts users to access only specific data rows in the data table, ensuring data read and write security.

Configuring Row-Level Access Control

Row-level access control is used to control the visibility of row-level data in tables. By predefining filters for data tables, the expressions that meet the specified condition can be applied to execution plans in the query optimization phase, which will affect the final execution result. Currently, the SQL statements that can be affected include SELECT, UPDATE, and DELETE.

  • You can use the CREATE ROW LEVEL SECURITY POLICY statement to create a row-level security policy on a table.

    This policy works only for expressions that take effect for specific database users and SQL operations. When a database user accesses the data table, if a SQL statement meets the specified row-level access control policies of the data table, the expressions that meet the specified condition will be combined by using AND or OR based on the attribute type (PERMISSIVE | RESTRICTIVE) and applied to the execution plan in the query optimization phase.

  • After a row-level access control policy is created for a table, it takes effect only when the row-level access control switch (ALTER TABLE...ENABLE ROW LEVEL SECURITY) of the table is turned on.

Example of Row-Level Access Control

The data of all users is aggregated in table all_data. Implement row-level access control on this table so that different users can view only their own data.

  1. Create users alice, bob, and peter.

    Create table all_data and insert data of different users into it.

  2. Grant the read permission on table all_data to users alice, bob, and peter.

  3. Run the ALTER TABLE tablename ENABLE ROW LEVEL SECURITY statement to enable the row-level access control.

  4. Run the CREATE ROW LEVEL SECURITY POLICY statement to create a row-level access control policy so that the current user can view only its own data.

  5. View information about the all_data table.

  6. Switch to user alice and query the data in table all_data. The query result shows that the row-level access control policy takes effect. User alice can only view its own data.

    The execution plan of the query is displayed, indicating that access to table all_data is under the row-level access control.

  7. Switch to user peter and query the data in table all_data. The query result shows that the row-level access control policy takes effect. User peter can only view its own data.

    The execution plan of the table query is displayed, indicating that the query of table all_data is under the row-level access control.