运维首页 > 数据运维 > MogDB/openGauss数据库行级安全策略测试

MogDB/openGauss数据库行级安全策略测试

2024年 3月 5日 23.8k 0

原作者:tracy

一、测试环境

数据库版本：MogDB1.1.0/OpenGauss1.1.0
操作系统版本: Centos7.9

二、数据库对象权限

1. MogDB/OpenGauss数据库对象权限包括：

SELECT、INSERT、UPDATE、DELETE、TRUNCATE、REFERENCES、CREATE、CONNECT、EXECUTE、USAGE、ALTER、DROP、COMMENT、INDEX和VACUUM。

这些权限可以通过GRANT/REVOKE 来对用户进行授予或撤销。

2. 给用户赋予表的查询权限：

GRANT { { SELECT | INSERT | UPDATE | DELETE | TRUNCATE | REFERENCES | ALTER | DROP | COMMENT | INDEX | VACUUM } [, …] | ALL [ PRIVILEGES ] }
ON { [ TABLE ] table_name [, …] | ALL TABLES IN SCHEMA schema_name [, …] }
TO { [ GROUP ] role_name | PUBLIC } [, …]
[ WITH GRANT OPTION ];
例：GRANT SELECT ON all_data TO alice, bob, peter;

为了让用户成功的查询不属于它自己的sechma下的表，除了要执行上面语句赋予该用户对表的查询权限以外，还需要赋予该用户使用表所属shema的使用权限：

GRANT { { CREATE | USAGE | ALTER | DROP | COMMENT } [, …] | ALL [ PRIVILEGES ] }
ON SCHEMA schema_name [, …]
TO { [ GROUP ] role_name | PUBLIC } [, …]
[ WITH GRANT OPTION ];
例：GRANT USAGE ON SCHEMA enmo TO alice, bob, peter;

否则，用户在查询表数据时会报错：

enmo=> select * from enmo.all_data;
ERROR: permission denied for schema enmo

三、行级访问控制

1.行级访问控制简介

行级访问控制特性将数据库访问控制精确到数据表行级别，使数据库达到行级访问控制的能力。不同用户执行相同的SQL查询操作，读取到的结果是不同的。
用户可以在数据表创建行访问控制(Row Level Security)策略，该策略是指针对特定数据库用户、特定SQL操作生效的表达式。当数据库用户对数据表访问时，若SQL满足数据表特定的Row Level Security策略，在查询优化阶段将满足条件的表达式，按照属性(PERMISSIVE | RESTRICTIVE)类型，通过 AND或OR方式拼接，应用到执行计划上。
行级访问控制的目的是控制表中行级数据可见性，通过在数据表上预定义Filter，在查询优化阶段将满足条件的表达式应用到执行计划上，影响最终的执行结果。当前受影响的SQL语句包括SELECT，UPDATE，DELETE。

开启表的行级访问策略：
ALTER TABLE [ IF EXISTS ] { table_name [*] | ONLY table_name | ONLY ( table_name )} ENABLE ROW LEVEL SECURITY;
例：ALTER TABLE all_data ENABLE ROW LEVEL SECURITY;

关闭表的行级访问策略：
ALTER TABLE [ IF EXISTS ] { table_name [*] | ONLY table_name | ONLY ( table_name )} DISABLE ROW LEVEL SECURITY;
例：ALTER TABLE all_data DISABLE ROW LEVEL SECURITY;

创建行级访问控制策略：
CREATE [ ROW LEVEL SECURITY ] POLICY policy_name ON table_name
[ AS { PERMISSIVE | RESTRICTIVE } ]
[ FOR { ALL | SELECT | UPDATE | DELETE } ]
[ TO { role_name | PUBLIC } [, …] ]
USING ( using_expression )
例：CREATE ROW LEVEL SECURITY POLICY all_data_rls ON all_data USING(role = CURRENT_USER);

删除行级访问控制策略：
DROP [ ROW LEVEL SECURITY ] POLICY [ IF EXISTS ] policy_name ON table_name [ CASCADE | RESTRICT ]
例：DROP ROW LEVEL SECURITY POLICY all_data_rls ON all_data;

修改行级访问控制策略名称：
ALTER [ ROW LEVEL SECURITY ] POLICY [ IF EXISTS ] policy_name ON table_name RENAME TO new_policy_name;
例:ALTER ROW LEVEL SECURITY POLICY all_data_rls ON all_data RENAME TO all_data_new_rls;

修改行级访问控制策略影响用户：
ALTER [ ROW LEVEL SECURITY ] POLICY policy_name ON table_name
[ TO { role_name | PUBLIC } [, …] ]
[ USING ( using_expression ) ];
例：ALTER ROW LEVEL SECURITY POLICY all_data_new_rls ON all_data TO alice, bob;

查询数据库中表的行级访问策略：
select *from PG_RLSPOLICIES;

2.测试：

a.创建行级访问控制策略，并验证策略生效

enmo=> select * from enmo.all_data;
id | role | data
----+-------+------------
1 | alice | alice data
2 | bob | bob data
3 | peter | peter data
(3 rows)

​ enmo=> \d+ all_data
​ Table "enmo.all_data"
​ Column | Type | Modifiers | Storage | Stats target | Description
​ --------+------------------------+-----------+----------+--------------+-------------
​ id | integer | | plain | |
​ role | character varying(100) | | extended | |
​ data | character varying(100) | | extended | |
​ Has OIDs: no
​ Options: orientation=row, compression=no

​ enmo=> --打开行访问控制策略开关
​ enmo=> ALTER TABLE all_data ENABLE ROW LEVEL SECURITY;
​ ALTER TABLE
​ enmo=> --创建行访问控制策略，当前用户只能查看用户自身的数据
​ enmo=> CREATE ROW LEVEL SECURITY POLICY all_data_rls ON all_data USING(role = CURRENT_USER);
​ CREATE ROW LEVEL SECURITY POLICY
enmo=> \d+ all_data

​ Table "enmo.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" FOR ALL
​ TO public
​ USING (((role)::name = "current_user"()))
​ Has OIDs: no
​ Options: orientation=row, compression=no, enable_rowsecurity=true

​ enmo=> select *from PG_RLSPOLICIES;
​ -[ RECORD 1 ]----+----------------------------------
​ schemaname | enmo
​ tablename | all_data
​ policyname | all_data_rls
​ policypermissive | PERMISSIVE
​ policyroles | {public}
​ policycmd | ALL
​ policyqual | ((role)::name = "current_user"())

​ enmo=>--行级访问策略对表的所有者无效
​ enmo=> select * from enmo.all_data;
​ id | role | data
​ ----+-------+------------
​ 1 | alice | alice data
​ 2 | bob | bob data
​ 3 | peter | peter data
​ (3 rows)
​ enmo=> \c - alice
​ Password for user alice:
​ Non-SSL connection (SSL connection is recommended when requiring high-security)
​ You are now connected to database "enmo" as user "alice".
​ enmo=>--用户alice只能访问到role='alice'的数据行
​ enmo=> select *from enmo.all_data;
​ id | role | data
​ ----+-------+------------
​ 1 | alice | alice data
​ (1 row)
​ enmo=>--用户alice对表enmo.all_data的insert操作未受行级访问控制影响
​ enmo=> INSERT INTO enmo.all_data VALUES(4, 'ogdb', 'ogdb data');
​ INSERT 0 1
​ enmo=> SELECT * FROM enmo.all_data;
​ id | role | data
​ ----+-------+------------
​ 1 | alice | alice data
​ (1 row)

​ enmo=> \conninfo
​ You are connected to database "enmo" as user "enmo" via socket in "/opt/OpenGauass/tmp" at port "15400".
​ enmo=> SELECT * FROM enmo.all_data;
​ id | role | data
​ ----+-------+------------
​ 1 | alice | alice data
​ 2 | bob | bob data
​ 3 | peter | peter data
​ 4 | ogdb | ogdb data
​ (4 rows)

​ enmo=>--用户alice对表enmo.all_data的update操作只能修改role='alice'的数据行
​ enmo=> update enmo.all_data set id=id+10;
​ UPDATE 1
​ enmo=> EXPLAIN(COSTS OFF) update enmo.all_data set id=id+10;
​ QUERY PLAN
​ \----------------------------------------------------------------
​ Update on all_data
​ -> Seq Scan on all_data
​ Filter: ((role)::name = 'alice'::name)
​ Notice: This query is influenced by row level security feature
​ (4 rows)

​ enmo=> SELECT * FROM enmo.all_data;
​ id | role | data
​ ----+-------+------------
​ 11 | alice | alice data
​ (1 row)

​ enmo=> \conninfo
​ You are connected to database "enmo" as user "enmo" via socket in "/opt/OpenGauss/tmp" at port "15400".
​ enmo=> SELECT * FROM enmo.all_data;
​ id | role | data
​ ----+-------+------------
​ 2 | bob | bob data
​ 3 | peter | peter data
​ 4 | ogdb | ogdb data
​ 11 | alice | alice data
​ (4 rows)

​ enmo=>--用户alice对表enmo.all_data的delete操作只能删除role='alice'的数据行
​ enmo=> delete from enmo.all_data;
​ DELETE 1
​ enmo=> EXPLAIN(COSTS OFF) delete from enmo.all_data;
​ QUERY PLAN
​ \----------------------------------------------------------------
​ Delete on all_data
​ -> Seq Scan on all_data
​ Filter: ((role)::name = 'alice'::name)
​ Notice: This query is influenced by row level security feature
​ (4 rows)

​ enmo=> SELECT * FROM enmo.all_data;
​ id | role | data
​ ----+------+------
​ (0 rows)
​ enmo=> \conninfo
​ You are connected to database "enmo" as user "enmo" via socket in "/opt/OpenGauss/tmp" at port "15400".
​ enmo=> SELECT * FROM enmo.all_data;
​ id | role | data
​ ----+-------+------------
​ 2 | bob | bob data
​ 3 | peter | peter data
​ 4 | ogdb | ogdb data
​ (3 rows)
​ enmo=> --用户bob只能访问到role='bob'的数据行
​ enmo=> \c - bob
​ Password for user bob:
​ Non-SSL connection (SSL connection is recommended when requiring high-security)
​ You are now connected to database "enmo" as user "bob".
​ enmo=> SELECT * FROM enmo.all_data;
​ id | role | data
​ ----+------+----------
​ 2 | bob | bob data
​ (1 row)

​ enmo=> EXPLAIN(COSTS OFF) SELECT * FROM enmo.all_data;
​ QUERY PLAN
​ \----------------------------------------------------------------
​ Seq Scan on all_data
​ Filter: ((role)::name = 'bob'::name)
​ Notice: This query is influenced by row level security feature
​ (3 rows)

​ enmo=>--行级访问策略对数据库初始化用户无效
​ enmo=# \c - ogdb
​ Non-SSL connection (SSL connection is recommended when requiring high-security)
​ You are now connected to database "enmo" as user "ogdb".
​ enmo=# select *from enmo.all_data;
​ id | role | data
​ ----+-------+------------
​ 1 | alice | alice data
​ 2 | bob | bob data
​ 3 | peter | peter data
​ (3 rows)

​ enmo=#

b.修改行级访问控制策略名称，并设置仅对alice用户生效
enmo=> ALTER ROW LEVEL SECURITY POLICY all_data_rls ON all_data RENAME TO all_data_new_rls;
ALTER ROW LEVEL SECURITY POLICY
enmo=> ALTER ROW LEVEL SECURITY POLICY all_data_new_rls ON all_data TO alice;
ALTER ROW LEVEL SECURITY POLICY
enmo=> select *from PG_RLSPOLICIES;
-[ RECORD 1 ]----+----------------------------------
schemaname | enmo
tablename | all_data
policyname | all_data_new_rls
policypermissive | PERMISSIVE
policyroles | {alice}
policycmd | ALL
policyqual | ((role)::name = "current_user"())

​ enmo=> \c - alice
​ Password for user alice:
​ Non-SSL connection (SSL connection is recommended when requiring high-security)
​ You are now connected to database "enmo" as user "alice".
​ enmo=>--用户alice只能访问到role='alice'的数据行
​ enmo=> SELECT * FROM enmo.all_data;
​ id | role | data
​ ----+-------+------------
​ 1 | alice | alice data
​ (1 row)

​ enmo=>
​ enmo=> \c - bob
​ Password for user bob:
​ Non-SSL connection (SSL connection is recommended when requiring high-security)
​ You are now connected to database "enmo" as user "bob".
​ enmo=> --行级访问策略对用户bob无效
​ enmo=> SELECT * FROM enmo.all_data;
​ id | role | data
​ ----+-------+------------
​ 1 | alice | alice data
​ 2 | bob | bob data
​ 3 | peter | peter data
​ (3 rows)

​ enmo=>

c.对表禁用行级访问控制后，行级访问控制策略失效
enmo=> \conninfo
You are connected to database "enmo" as user "enmo" via socket in "/opt/OpenGauss/tmp" at port "15400".
enmo=> ALTER TABLE all_data DISABLE ROW LEVEL SECURITY;
ALTER TABLE
enmo=> \x
Expanded display is on.
enmo=> select *from PG_RLSPOLICIES;
-[ RECORD 1 ]----+----------------------------------
schemaname | enmo
tablename | all_data
policyname | all_data_new_rls
policypermissive | PERMISSIVE
policyroles | {alice}
policycmd | ALL
policyqual | ((role)::name = "current_user"())

​ enmo=> \dt+ all_data
​ List of relations
​ -[ RECORD 1 ]---------------------------------
​ Schema | enmo
​ Name | all_data
​ Type | table
​ Owner | enmo
​ Size | 8192 bytes
​ Storage | {orientation=row,compression=no}
​ Description |

​ enmo=> SELECT * FROM enmo.all_data;
​ id | role | data
​ ----+-------+------------
​ 2 | bob | bob data
​ 3 | peter | peter data
​ 4 | ogdb | ogdb data
​ 1 | alice | alice data
​ (4 rows)

​ enmo=> \c - alice
​ Password for user alice:
​ Non-SSL connection (SSL connection is recommended when requiring high-security)
​ You are now connected to database "enmo" as user "alice".
​ enmo=>--行级访问控制策略未对alice用户生效
​ enmo=> SELECT * FROM enmo.all_data;
​ id | role | data
​ ----+-------+------------
​ 2 | bob | bob data
​ 3 | peter | peter data
​ 4 | ogdb | ogdb data
​ 1 | alice | alice data
​ (4 rows)

​ enmo=> EXPLAIN(COSTS OFF) SELECT * FROM enmo.all_data;
​ QUERY PLAN
​ \----------------------
​ Seq Scan on all_data
​ (1 row)

​ enmo=>

的头像 醒在深海的猫
数据运维
0 0

相关文章

pt-kill工具的使用
pt-ioprofile工具包的使用
数据库管理-第216期 Oracle的高可用-01（20240703）
DBMS_REPAIR EXAMPLE SCRIPT WITH PARTITION
数据库事务的四大特性： ACID 
使用BBED修复损坏的SYSTEM文件头

发布评论

醒在深海的猫的头像

醒在深海的猫

这个人很懒，什么都没有留下～

文章7929 评论0