云数据库 GaussDB-CREATE ROW LEVEL SECURITY POLICY:示例

时间：2023-11-01 16:22:10

云数据库 GaussDB

示例

--创建用户aliceopenGauss=# CREATE USER alice PASSWORD 'xxxxxxxxx';--创建用户bobopenGauss=# CREATE USER bob PASSWORD 'xxxxxxxxx';--创建数据表all_dataopenGauss=# CREATE TABLE public.all_data(id int, role varchar(100), data varchar(100));--向数据表插入数据openGauss=# INSERT INTO all_data VALUES(1, 'alice', 'alice data');openGauss=# INSERT INTO all_data VALUES(2, 'bob', 'bob data');openGauss=# INSERT INTO all_data VALUES(3, 'peter', 'peter data');--将表all_data的读取权限赋予alice和bob用户openGauss=# GRANT SELECT ON all_data TO alice, bob;--打开行访问控制策略开关openGauss=# ALTER TABLE all_data ENABLE ROW LEVEL SECURITY;--创建行访问控制策略，当前用户只能查看用户自身的数据openGauss=# CREATE ROW LEVEL SECURITY POLICY all_data_rls ON all_data USING(role = CURRENT_USER);--查看表all_data相关信息openGauss=# \d+ all_data                               Table "public.all_data" Column |          Type          | Modifiers | Storage  | Stats target | Description--------+------------------------+-----------+----------+--------------+------------- id     | integer                |           | plain    |              | role   | character varying(100) |           | extended |              | data   | character varying(100) |           | extended |              |Row Level Security Policies:    POLICY "all_data_rls"      USING (((role)::name = "current_user"()))Has OIDs: noOptions: orientation=row, compression=no, enable_rowsecurity=true--当前用户执行SELECT操作openGauss=# SELECT * FROM all_data; id | role  |    data----+-------+------------  1 | alice | alice data  2 | bob   | bob data  3 | peter | peter data(3 rows)openGauss=# EXPLAIN(COSTS OFF) SELECT * FROM all_data;      QUERY PLAN---------------------- Seq Scan on all_data(1 row)--切换至alice用户执行SELECT操作openGauss=# SELECT * FROM all_data; id | role  |    data----+-------+------------  1 | alice | alice data(1 row)openGauss=# EXPLAIN(COSTS OFF) SELECT * FROM all_data; QUERY PLAN---------------------------------------------------------------- Seq Scan on all_data   Filter: ((role)::name = 'alice'::name) Notice: This query is influenced by row level security feature(3 rows)