8.GaussDB行级访问控制策略的使用

1.什么是行级访问控制

就是让某些用户对表的访问，只能访问行级控制策略满足条件的行。不满足条件的行，即使执行SELECT * FROM TAB1；也不会显示。

2.行 级访问控制使用

(1)查看原表数据

--给user1用户授予查看：sjzt.test2表权限。
sjzt=> grant select on sjzt.test2 to user1;
GRANT

--起初user1可以看到全部的数据。
sjzt=>  select * from sjzt.test2;
 id | name | age 
----+------+-----
  1 | xsq  |  15
  2 | xsq2 |  16
  3 | xsq3 |  17
(3 rows)
sjzt=> \conninfo
You are connected to database "sjzt" as user "user1" via socket in "/data/cluster/temp" at port "8000".

（2）对表sjzt.test2创建行级访问策略,并只对USER2做限制

--让user1用户只能访问,sjzt.test2表里面id>=3的数据。
sjzt=> CREATE ROW LEVEL SECURITY POLICY RL_SELECT ON SJZT.TEST2 FOR SELECT TO USER1 USING(ID>=3);
CREATE ROW LEVEL SECURITY POLICY
sjzt=> ALTER TABLE SJZT.TEST2 ENABLE ROW LEVEL SECURITY ;
ALTER TABLE

（3）查看表上的行级访问控制策略

--检查表的行级策略情况。
sjzt=> \dt+ SJZT.TEST2
                                                List of relations
 Schema | Name  | Type  | Owner | Size  |                         Storage                          | Description 
--------+-------+-------+-------+-------+----------------------------------------------------------+-------------
 sjzt   | test2 | table | sjzt  | 16 kB | {orientation=row,compression=no,enable_rowsecurity=true} | 
(1 row)

sjzt=> \d+ SJZT.TEST2
                                 Table "sjzt.test2"
 Column |         Type          | Modifiers | Storage  | Stats target | Description 
--------+-----------------------+-----------+----------+--------------+-------------
 id     | integer               |           | plain    |              | 
 name   | character varying(20) |           | extended |              | 
 age    | integer               |           | plain    |              | 
Row Level Security Policies:
    POLICY "rl_select" FOR SELECT
      TO user1
      USING ((id >= 3))
Has OIDs: no
Distribute By: HASH(id)
Location Nodes: ALL DATANODES
Options: orientation=row, compression=no, enable_rowsecurity=true

可以看到只对user1用户有控制。且访问策略激活。

（4）查看使用访问控制后的效果

--授予USER1对SJZT.TEST2表的查询权限。
sjzt=> grant select on sjzt.test2 to user1;
GRANT

--使用USER1进行查询。
sjzt=> select * from sjzt.test2;
 id | name | age 
----+------+-----
  3 | xsq3 |  17
(1 row)
sjzt=> \conninfo
You are connected to database "sjzt" as user "user1" via socket in "/data/cluster/temp" at port "8000".

果然USER1用户只能看到满足策略的数据。

(5)对多个用户使用访问控制策略

--让多个用户都使用访问控制策略。
ALTER POLICY RL_SELECT ON sjzt.test2 to user2,user1 USING(ID>=3);
ALTER ROW LEVEL SECURITY  POLICY RL_SELECT ON sjzt.test2 to user2,user1 USING(ID>=3);

--让用户能够登陆sjzt schema,同时授予能够访问sjzt.test2表。
grant usage on schema sjzt to user2; 
grant select on sjzt.test2 to user2; 
grant usage on schema sjzt to user1; 
grant select on sjzt.test2 to user1; 

sjzt=> \c - user1
Password for user user1: 
Non-SSL connection (SSL connection is recommended when requiring high-security)
You are now connected to database "sjzt" as user "user1".
sjzt=> select * from sjzt.test2;
 id | name | age 
----+------+-----
  3 | xsq3 |  17
(1 row)

sjzt=> \c - user2
Password for user user2: 
Non-SSL connection (SSL connection is recommended when requiring high-security)
You are now connected to database "sjzt" as user "user2".
sjzt=> select * from sjzt.test2;
 id | name | age 
----+------+-----
  3 | xsq3 |  17
(1 row)

sjzt=> \c - sjzt
Password for user sjzt: 
Non-SSL connection (SSL connection is recommended when requiring high-security)
You are now connected to database "sjzt" as user "sjzt".
sjzt=> select * from sjzt.test2;
 id | name | age 
----+------+-----
  3 | xsq3 |  17
  1 | xsq  |  15
  2 | xsq2 |  16
(3 rows)

可以发现，sjzt用户是表的属主，可以看到所有数据，但是user1,user2使用了访问控制策略，只能访问到部分数。

3.总结

行级访问控制，主要是针对行数据进行限制。没有对列进行限制。这个功能类似于:

创建视图：create view v1 as select * from sjzt.test2 where id>=3; 

行级访问控制是GaussDB里面的非常不错的安全控制功能。在某些特定场景下非常有用。当前未来也由可能出现列级访问控制，对数据库进行更好的管理。