Help Center/ GaussDB(DWS)/ Best Practices/ Security Management/ Managing and Controlling Data Permissions Through Views
Updated on 2024-03-13 GMT+08:00
View PDF

Managing and Controlling Data Permissions Through Views

Use views to grant different users the permission to query different data in the same table, providing data permission management and security.

Scenario

After connecting to the cluster as user dbadmin, create a sample table customer.

1
 
CREATE TABLE customer (id bigserial NOT NULL, province_id bigint NOT NULL, user_info varchar, primary key (id)) DISTRIBUTE BY HASH(id);

Insert test data into the sample table customer.

1
2
 
INSERT INTO customer(province_id,user_info) VALUES (1,'Alice'),(1,'Jack'),(2,'Jack'),(3,'Matu');
INSERT 0 4

Query the customer table.

1
2
3
4
5
6
7
8
 
SELECT * FROM customer;
 id | province_id | user_info
----+-------------+-----------
  3 |           2 | Jack
  1 |           1 | Alice
  2 |           1 | Jack
  4 |           3 | Matu
(4 rows)

Requirement: User u1 can view only the data of province 1 (province_id=1), and user u2 can view only the data of province 2 (province_id=2).

Implementation

You can create a view to meet the requirements in the preceding scenario. The procedure is as follows:

  1. After connecting to the cluster as user dbadmin, create views v1 and v2 for provinces 1 and 2 in dbadmin mode.

    Run the CREATE VIEW statement to create view v1 for querying the data of province 1. 
    1
2
    		 
    CREATE VIEW v1 AS
    SELECT * FROM customer WHERE province_id=1;

    Run the CREATE VIEW statement to create view v2 for querying the data of province 2.

    1
2
    		 
    CREATE VIEW v2 AS
    SELECT * FROM customer WHERE province_id=2;

  2. Create users u1 and u2. 

    1
2
    		 
    CREATE USER u1 PASSWORD '*********';
CREATE USER u2 PASSWORD '*********';

  3. Run the GRANT statement to grant the data query permission to the target user.

    Grant the permission on the schema view corresponding to u1 and u2.

    1
    		 
    GRANT USAGE ON schema dbadmin TO u1,u2;

    Grant u1 the permission to query data of province 1 in the v1 view.

    1
    		 
    GRANT SELECT ON v1 TO u1;

    Grant u2 the permission to query data of province 2 in the V2 view.

    1
    		 
    GRANT SELECT ON v2 TO u2;

Verifying the Query Result

  • Switch to the u1 account to connect to the cluster. 
    1
    		 
    SET ROLE u1 PASSWORD '*********';
    This interface is used to query the v1 view. u1 can query only the v1 view data. 
    1
2
3
4
5
6
    		 
    SELECT * FROM dbadmin.v1;
 id | province_id | user_info
----+-------------+-----------
  1 |           1 | Alice
  2 |           1 | Jack
(2 rows)
    If u1 attempts to query data in view v2, the following error information is displayed: 
    1
2
    		 
    SELECT * FROM dbadmin.v2;
ERROR:  SELECT permission denied to user "u1" for relation "dbadmin.v2"

    The result shows that user u1 can view only the data of province 1 (province_id=1).

  • Use the u2 account to connect to the cluster. 
    1
    		 
    SET ROLE u2 PASSWORD '*********';
    This interface is used to query the v2 view. u2 can query only the v2 view data. 
    1
2
3
4
5
    		 
    SELECT * FROM dbadmin.v2;
 id | province_id | user_info
----+-------------+-----------
  3 |           2 | Jack
(1 row)

    If u2 attempts to query data in view v1, the following error information is displayed:

    1
2
    		 
    SELECT * FROM dbadmin.v1;
ERROR:  SELECT permission denied to user "u2" for relation "dbadmin.v1"

    The result shows that user u2 can view only the data of province 2 (province_id=2).

Parent topic: Security Management