1 背景
行級訪問控制特性將數據庫的訪問控制精確到數據表行級別?，只允許用戶查看?、更新或刪除特定的行數據。
2 實例場景
實例以醫生只能看到治療的病人，不能看其它醫生的病人為例：
1)醫院病人的信息表pat_info：
csdn=> set search_path=csdn;
SET
csdn=>
csdn=> create table pat_info(patient varchar2(64),doctor varchar2(64),age int);
CREATE TABLE
csdn=> insert into pat_info(patient,doctor,age)values('peter','mary',25),('bob','mary',56),('julie','tom',38);
INSERT 0 3
csdn=>
csdn=> select * from pat_info;patient | doctor | age
---------+--------+-----peter | mary | 25bob | mary | 56julie | tom | 38
(3 rows)csdn=>
2) 創建行級訪問控制策略?，使得醫生只能查看屬于自己的病?人信息?，并打開表上的行級訪問控制開關：
csdn=> CREATE ROW LEVEL SECURITY POLICY rl_select ON
csdn-> pat_info FOR select USING(doctor=current_user);
CREATE ROW LEVEL SECURITY POLICY
csdn=> alter table pat_info enable row level security;
ALTER TABLE
csdn=>
csdn=> \d+ pat_info;Table "csdn.pat_info"Column | Type | Modifiers | Storage | Stats target | Description
---------+-----------------------+-----------+----------+--------------+-------------patient | character varying(64) | | extended | |doctor | character varying(64) | | extended | |age | integer | | plain | |
Row Level Security Policies:POLICY "rl_select" FOR SELECTTO publicUSING (((doctor)::name = "current_user"()))
Has OIDs: no
Options: orientation=row, compression=no, storage_type=USTORE, segment=off, enable_rowsecurity=truecsdn=>
3) 創建兩個用戶tom,mary并授予usage與select權限;
[Ruby@dtest1 ~]$ gsql -h 192.168.0.71 -dcsdn -p8000 -U root -W '*******' -ar
gsql ((GaussDB Kernel 505.2.1 build 159cea95) compiled at 2024-12-27 09:22:44 commit 10161 last mr 21504 release)
SSL connection (cipher: ECDHE-RSA-AES128-GCM-SHA256, bits: 128)
Type "help" for help.csdn=>
csdn=>csdn=> create user mary password '*******';
CREATE ROLE
csdn=> create user tom password '*******';
CREATE ROLE
csdn=> grant usage on schema csdn to mary;
GRANT
csdn=> grant usage on schema csdn to tom;
GRANT
csdn=> grant select on csdn.pat_info to tom;
GRANT
csdn=> grant select on csdn.pat_info to mary;
GRANT
csdn=> \q
4）驗證一下tom、mary用戶查詢結果
mary只能看到peter、bob兩個病人。
[Ruby@dtest1 ~]$ gsql -h 192.168.0.71 -dcsdn -p8000 -U mary -W '******' -ar
gsql ((GaussDB Kernel 505.2.1 build 159cea95) compiled at 2024-12-27 09:22:44 commit 10161 last mr 21504 release)
SSL connection (cipher: ECDHE-RSA-AES128-GCM-SHA256, bits: 128)
Type "help" for help.csdn=> set search_path=csdn;
SET
csdn=> select * from pat_info;patient | doctor | age
---------+--------+-----peter | mary | 25bob | mary | 56
(2 rows)csdn=> \q
tom只能看到julie
[Ruby@dtest1 ~]$ gsql -h 192.168.0.71 -dcsdn -p8000 -U tom -W '*****' -ar
gsql ((GaussDB Kernel 505.2.1 build 159cea95) compiled at 2024-12-27 09:22:44 commit 10161 last mr 21504 release)
SSL connection (cipher: ECDHE-RSA-AES128-GCM-SHA256, bits: 128)
Type "help" for help.csdn=> set search_path=csdn;
SET
csdn=> select * from pat_info;patient | doctor | age
---------+--------+-----julie | tom | 38
(1 row)csdn=>
3 行級別安全實現原理
1）行級訪問控制的目的是控制表中行級數據可見性，通過在數據表上預定義Filter，在查詢優化階段將滿足條件 的表達式應用到執行計劃上，影響最終的執行結果。
2）當前受影響的SQL語句包括SELECT，?UPDATE，?DELETE?。
3）用戶可以在數據表創建行訪問控制（ Row ?Level?Security）策略，當數據庫用戶對數據表訪問時，若SQL滿足?數據表特定的RLS策略，在查詢優化階段將滿足條件的表達式，按照屬性（ PERMISSIVE ?| ?RESTRICTIVE）類???型，通過OR或AND方式拼接，應用到執行計劃上。
4 實現操作步驟
步驟1：打開行訪問控制策略開關：
ALTER TABLE tablename ENABLE ROW LEVEL SECURITY;步驟2：創建行訪問控制策略，當前用戶只能查看用戶自身的數據：
CREATE?ROW?LEVEL?SECURITY?POLICY??tablename_rls?ON??tablename?USING(role?= ?CURRENT_USER);注意：tablename為所創建的表名，?tablename_rls為創建的行級訪問控制策略名。
與ResizeObserver())
