Help Center > > Developer Guide> Database Security Management> Managing Users and Their Permissions> Data Redaction

Data Redaction

Updated at: Jul 14, 2021 GMT+08:00

Database permission control in GaussDB(DWS) can be implemented at the level of column and row. In the current database permission system, if sensitive information needs to be hidden for a user, only the permission on the table or column can be revoked. For unauthorized query, an error is reported.

GaussDB(DWS) supports data masking to protect sensitive information with higher availability. You can create a data redaction policy for a table object. A data redaction policy is a combination of specific database users, data objects, SQL operations, and redaction modes. The data redaction feature can be used to hide sensitive information of some users, ensuring sensitive information security.

Data redaction aims to prevent privacy leakage of some data columns in a table. After a column object is bound to a redaction policy, the redaction policy that meets the conditions is applied to the execution plan in the query rewriting phase, affecting the final execution result of the SELECT statement.

To ensure data security, an error message is displayed when the INSERT INTO SELECT, UPDATE FROM, or MERGE INTO statement contains sensitive information.

GaussDB(DWS) provides function interfaces for creating, modifying, and deleting redaction policies. For details, see the data redaction function.

Scenario example: A table summarizes personal information. Many attributes in the personal information are sensitive data. A redaction policy is created for sensitive columns.

Create users alice, bob, and peter.

CREATE ROLE alice PASSWORD 'Gauss@123';
CREATE ROLE bob PASSWORD 'Gauss@123';
CREATE ROLE peter PASSWORD 'Gauss@123';

User alice creates the customer table in public mode to record user information (name and salary).

CREATE TABLE public.customer(id int, name varchar(100), salary int);

Insert three users to the data table.

INSERT INTO customer VALUES(1, 'Michael', 10000);
INSERT INTO customer VALUES(2, 'bryant', 20000);
INSERT INTO customer VALUES(3, 'harden', 15000);

User alice grants the read permission of the customer table to bob and peter.

GRANT SELECT ON customer TO bob, peter;

alice does not want bob and peter to view salary data, and adds a redaction policy to the customer table.

SELECT DBMS_REDACT.add_policy(object_schema=>'public',object_name=>'customer',policy_name=>'pol_1',column_name=>'salary',expression=>'1=1');

After the user is switched to bob or peter, the salary data in the customer table cannot be viewed.

set role bob password 'Gauss@123';
SET
select * from customer;
id |  name   | salary
----+---------+--------
1 | Michael |      
2 | bryant  |      
3 | harden  |      
(3 rows)

set role peter password 'Gauss@123';
SET
select * from customer;
id |  name   | salary
----+---------+--------
1 | Michael |      
2 | bryant  |      
3 | harden  |      
(3 rows)

alice changes her mind and does not want peter to see salary data, and modifies the policy.

SELECT DBMS_REDACT.alter_policy(object_schema=>'public',object_name=>'customer',policy_name=>'pol_1',action=>'3',expression=>'SYS_CONTEXT(''USERENV'', ''CURRENT_USER'') = ''peter''');

Switch to user bob and view the salary data in the customer table.

set role bob password 'Gauss@123';
SET
SELECT * FROM customer;
id |  name   | salary
----+---------+--------
1 | Michael |  10000
2 | bryant  |  20000
3 | harden  |  15000
(3 rows)

View the existing redaction policy and redacted column information.

select * from redaction_policies;
 object_schema | object_owner | object_name | policy_name |                              expression                              | enable | policy_description
---------------+--------------+-------------+-------------+----------------------------------------------------------------------+--------+--------------------
 public        | yxw          | customer    | pol_1       | (sys_context('USERENV'::text, 'CURRENT_USER'::text) = 'peter'::text) | t      |
(1 row)

select * from redaction_columns;
 object_schema | object_owner | object_name | column_name | function_type | function_parameters | regexp_pattern | regexp_replace_string | regexp_position | regexp_occurrence | regexp_match_parameter | colum
n_description
---------------+--------------+-------------+-------------+---------------+---------------------+----------------+-----------------------+-----------------+-------------------+------------------------+------
--------------
 public        | yxw          | customer    | salary      |             1 |                     |                |                       |                 |                   |                        |
(1 row)

Did you find this page helpful?

Submit successfully!

Thank you for your feedback. Your feedback helps make our documentation better.

Failed to submit the feedback. Please try again later.

Which of the following issues have you encountered?







Please complete at least one feedback item.

Content most length 200 character

Content is empty.

OK Cancel