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里面的非常不错的安全控制功能。在某些特定场景下非常有用。当前未来也由可能出现列级访问控制,对数据库进行更好的管理。