暂无图片
暂无图片
暂无图片
暂无图片
暂无图片
首页 / MogDB/openGauss数据库行级安全策略测试

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

由迪 2024-03-05
207

原作者: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=>
opengaussmogdb访问控制selectdelete
最后修改时间：2024-03-05 19:02:39
「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」
关注作者
【版权声明】本文为墨天轮用户原创内容，转载时必须标注文章的来源（墨天轮），文章链接，文章作者等基本信息，否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容，欢迎发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。

文章被以下合辑收录

Mogdb 学习记录（共582篇）
Mogdb 学习指南

评论