更新时间:2024-02-07 GMT+08:00
分享

行级访问控制

行级访问控制特性可以将数据库访问控制精确到数据表行级别,控制用户只能访问数据表的特定数据行,保证读写数据的安全。

行级访问控制设置

行级访问控制的目的是控制表中行级数据可见性,通过在数据表上预定义Filter,在查询优化阶段将满足条件的表达式应用到执行计划上,影响最终的执行结果。当前受影响的SQL语句包括SELECT,UPDATE,DELETE。

  • 用户可以使用CREATE ROW LEVEL SECURITY POLICY语句在表上创建行访问控制策略(Row Level Security)。

    该策略针对特定数据库用户、特定SQL操作生效的表达式。当数据库用户对数据表访问时,若SQL满足数据表特定的Row Level Security策略,在查询优化阶段将满足条件的表达式,按照属性(PERMISSIVE | RESTRICTIVE)类型,通过AND或OR方式拼接,应用到执行计划上。

  • 对表创建了行级访问控制策略后,只有打开该表的行访问控制开关(ALTER TABLE ... ENABLE ROW LEVEL SECURITY),策略才能生效。

行级访问控制示例

某业务场景中,表all_data中汇总了不同用户的数据,使用行级访问控制功能实现:不同用户只能查看自身相关的数据信息,不能查看其他用户的数据信息。

  1. 创建用户alice,bob和peter。

    1
    2
    3
    CREATE ROLE alice PASSWORD '********';
    CREATE ROLE bob PASSWORD '********';
    CREATE ROLE peter PASSWORD '********';
    

    创建表all_data并插入数据,表all_data包含不同用户的数据信息。

    1
    2
    3
    4
    5
    CREATE TABLE public.all_data(id int, role varchar(100), data varchar(100));
    
    INSERT INTO all_data VALUES(1, 'alice', 'alice data');
    INSERT INTO all_data VALUES(2, 'bob', 'bob data');
    INSERT INTO all_data VALUES(3, 'peter', 'peter data');
    

  2. 将表all_data的读取权限赋予用户alice,bob和peter。

    1
    GRANT SELECT ON all_data TO alice, bob, peter;
    

  3. 使用ALTER TABLE tablename ENABLE ROW LEVEL SECURITY语句打开行访问控制策略开关。

    1
    ALTER TABLE all_data ENABLE ROW LEVEL SECURITY;
    

  4. 使用CREATE ROW LEVEL SECURITY POLICY语句创建行访问控制策略,使当前用户只能查看用户自己的数据。

    1
    CREATE ROW LEVEL SECURITY POLICY all_data_rls ON all_data USING(role = CURRENT_USER);
    

  5. 查看表all_data信息。

     1
     2
     3
     4
     5
     6
     7
     8
     9
    10
    11
    12
    13
    14
     \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: no
    Distribute By: ROUND ROBIN
    Location Nodes: ALL DATANODES
    Options: orientation=row, compression=no, enable_rowsecurity=true
    

  6. 切换至用户alice,查询表all_data。查询结果显示行级访问控制策略生效:用户alice只能查看自己的数据。

    1
    2
    3
    4
    5
    6
    SET ROLE alice PASSWORD '********';
    
    SELECT * FROM all_data;
     id | role  |    data
    ----+-------+------------
      1 | alice | alice data
    

    显示表查询的执行计划,提示访问all_data表时受行级访问控制特性影响。

     1
     2
     3
     4
     5
     6
     7
     8
     9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    EXPLAIN(COSTS OFF) SELECT * FROM all_data;
                               QUERY PLAN
    ----------------------------------------------------------------
      id |          operation
     ----+------------------------------
       1 | ->  Streaming (type: GATHER)
       2 |    ->  Seq Scan on all_data
    
             Predicate Information (identified by plan id)
     --------------------------------------------------------------
       2 --Seq Scan on all_data
             Filter: ((role)::name = 'alice'::name)
     Notice: This query is influenced by row level security feature
    
       ====== Query Summary =====
     -------------------------------
     System available mem: 4833280KB
     Query Max mem: 4833280KB
     Query estimated mem: 1024KB
    (16 rows)
    

  7. 切换至用户peter,查询表all_data。查询结果显示行级访问控制策略生效:用户peter只能查看自己的数据。

    1
    2
    3
    4
    5
    6
    7
    SET ROLE peter PASSWORD '********';
    
    SELECT * FROM all_data;
     id | role  |    data
    ----+-------+------------
      3 | peter | peter data
    (1 row)
    

    显示表查询的执行计划,提示查询all_data表受行级访问控制特性影响。

     1
     2
     3
     4
     5
     6
     7
     8
     9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    EXPLAIN(COSTS OFF) SELECT * FROM all_data;
                               QUERY PLAN
    ----------------------------------------------------------------
      id |          operation
     ----+------------------------------
       1 | ->  Streaming (type: GATHER)
       2 |    ->  Seq Scan on all_data
    
             Predicate Information (identified by plan id)
     --------------------------------------------------------------
       2 --Seq Scan on all_data
             Filter: ((role)::name = 'peter'::name)
     Notice: This query is influenced by row level security feature
    
       ====== Query Summary =====
     -------------------------------
     System available mem: 4833280KB
     Query Max mem: 4833280KB
     Query estimated mem: 1024KB
    (16 rows)
    

分享:

    相关文档

    相关产品