Managing Data Permissions Through Views

Scenario

After connecting to a cluster as user dbadmin, create an example 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 example 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)

Implementation

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

1. After connecting to a 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 target view schema 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 u1 to connect to the cluster.

  1	SET ROLE u1 PASSWORD '*********';

  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 u2 to connect to the cluster.

  1	SET ROLE u2 PASSWORD '*********';

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