通过视图管控数据权限

场景

dbadmin用户连接集群后，创建示例表customer：

1

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

向示例表customer插入测试数据：

1
2

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

查询示例表customer：

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)

实现方式

通过创建视图实现上述场景中的需求，具体操作步骤如下：

1. dbadmin用户连接集群后，在dbadmin模式下为省份1和省份2分别创建视图v1和视图v2。
   使用CREATE VIEW语句创建查询省份1数据的视图v1：

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

   使用CREATE VIEW语句创建查询省份2数据的视图v2：

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

2. 创建用户u1和u2。

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

3. 使用GRANT语句将对应的数据查询权限授予目标用户。

   授予u1和u2对应视图schema的权限。

   1	GRANT USAGE ON schema dbadmin TO u1,u2;

   授予u1通过v1视图查询省份1数据的权限：

   1	GRANT SELECT ON v1 TO u1;

   授予u2通过v2视图查询省份2数据的权限：

   1	GRANT SELECT ON v2 TO u2;

查询结果验证

• 切换到u1账号连接集群。

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

  查询v1视图，u1仅能查询到视图v1数据。

  1 2 3 4 5 6

  SELECT * FROM dbadmin.v1; id | province_id | user_info ----+-------------+----------- 1 | 1 | Alice 2 | 1 | Jack (2 rows)

  若u1试图查询视图v2中的数据，则会返回如下报错：

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

  结果显示用户u1仅能查看省份1（即province_id=1）的数据。

• 使用u2账号连接集群。

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

  查询v2视图，u2仅能查询到视图v2数据。

  1 2 3 4 5

  SELECT * FROM dbadmin.v2; id | province_id | user_info ----+-------------+----------- 3 | 2 | Jack (1 row)

  若u2试图查询视图v1中的数据，则会返回如下报错：

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

  结果显示用户u2仅能查看省份2（即province_id=2）的数据。