# CREATE ROW LEVEL SECURITY POLICY #### 功能描述 对表创建行级访问控制策略。 行级访问控制策略（ROW LEVEL SECURITY POLICY）表示将数据库访问控制权限精确到数据表行级别，使数据库达到行级访问控制的能力。不同用户执行相同的SQL查询操作，系统将自动根据创建的控制策略返回不同的读取结果。 对表创建行级访问控制策略时，需**打开该表的行级访问控制开关**（ALTER TABLE ... ENABLE ROW LEVEL SECURITY \| ALTER FOREIGN TABLE ... ENABLE ROW LEVEL SECURITY）策略才能生效，否则不生效。 #### 策略应用 - 行级访问控制策略名称是针对表的，同一个数据表上不能有同名的行级访问控制策略；对不同的数据表，可以有同名的行级访问控制策略。 - 行级访问控制策略可以应用到指定的用户（角色），也可应用到全部用户（PUBLIC）；定义行级访问控制策略时，若未指定受影响的用户，默认为PUBLIC。 - 行级访问控制策略可以应用到指定的操作（SELECT、UPDATE、DELETE、ALL），ALL表示会影响SELECT、UPDATE、DELETE三种操作；定义行级访问控制策略时，若未指定受影响的相关操作，默认为ALL。 - 当前行级访问控制会影响数据表的读取操作（SELECT、UPDATE、DELETE），暂不影响数据表的写入操作（INSERT、MERGE INTO）。表所有者或系统管理员可以在USING子句中创建表达式，并在客户端执行数据表读取操作时，数据库后台在查询重写阶段会将满足条件的表达式拼接并应用到执行计划中。针对数据表的每一条元组，当USING表达式返回TRUE时，元组对当前用户可见，当USING表达式返回FALSE或NULL时，元组对当前用户不可见。 #### 注意事项 - 支持对行存表、行存分区表、列存表、列存分区表、复制表、unlogged表、hash表、非EXTERNAL SCHEMA的外表定义行级访问控制策略。 - 同一张表上可以创建多个行级访问控制策略，一张表最多创建100个行级访问控制策略。 - 具有管理员权限的用户、初始运维用户(Ruby)、表的owner和表的owner角色组成员不受行级访问控制影响，可以查看表的全量数据。 - 通过SQL语句、视图、函数、存储过程查询包含行级访问控制策略的表，都会受影响。 - 不支持HDFS表、EXTERNAL SCHEMA的外表、临时表定义行级访问控制策略。 - 不支持对视图定义行级访问控制策略。 - 不支持对行级访问控制策略依赖的列进行类型修改。例如，不支持如下修改： ``` ALTER TABLE public.all_data ALTER COLUMN role TYPE text; ``` #### 语法格式 ``` 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 ) ``` #### 参数说明 表1CREATE ROW LEVEL SECURITY POLICY参数说明 | 参数 | 描述 | 取值范围 | |:---|:---|:---| | policy_name | 要创建的行级访问控制策略名称。 | 同一个数据表上不能有同名的行级访问控制策略。 | | table_name | 要应用行级访问控制策略的表名。 | 有效的表名称。 | | PERMISSIVE | 指定行级访问控制策略的类型为宽容性策略。 对于一个给定的查询，将使用"OR"操作符将所有的宽容性策略组合。行级访问控制策略的类型默认为宽容性策略。 | - | | RESTRICTIVE | 指定行级访问控制策略的类型为限制性策略。 对于一个给定的查询，将使用"AND"操作符将所有的限制性策略组合。 至少需要一条宽容性策略允许对记录的访问。如果只有限制性策略存在，则不能访问任何记录。当宽容性和限制性策略共存时，只有当记录能通过至少一条宽容性策略以及所有的限制性策略时，该记录才能访问。 | - | | command | 当前行级访问控制影响的SQL操作，可指定操作包括：ALL、SELECT、UPDATE、DELETE。 未指定时，ALL为默认值，涵盖SELECT、UPDATE、DELETE操作。 | - 当command为SELECT时，SELECT类操作受行级访问控制的影响，只能查看到满足条件（using_expression返回值为TRUE）的元组数据，受影响的操作包括SELECT，UPDATE ... RETURNING，DELETE ... RETURNING。 - 当command为UPDATE时，UPDATE类操作受行级访问控制的影响，只能更新满足条件（using_expression返回值为TRUE）的元组数据，受影响的操作包括UPDATE，UPDATE ... RETURNING，SELECT ... FOR UPDATE/SHARE。 - 当command为DELETE时，DELETE类操作受行级访问控制的影响，只能删除满足条件（using_expression返回值为TRUE）的元组数据，受影响的操作包括DELETE，DELETE ... RETURNING。 行级访问控制策略与适配的SQL语法关系参见[表2]。 | | role_name | 行级访问控制影响的数据库用户。系统管理员不受行级访问控制特性影响。 未指定时，PUBLIC为默认值，PUBLIC表示影响所有数据库用户，可以指定多个受影响的数据库用户。 | 有效的角色名称。 | | using_expression | 行级访问控制的表达式（返回boolean值）。 | 条件表达式中不能包含AGG函数和窗口（WINDOW）函数。在查询重写阶段，如果数据表的行级访问控制开关打开，满足条件的表达式会添加到计划树中。针对数据表的每条元组，会进行表达式计算，只有表达式返回值为TRUE时，行数据对用户才可见（SELECT、UPDATE、DELETE）；当表达式返回FALSE时，该元组对当前用户不可见，用户无法通过SELECT语句查看此元组，无法通过UPDATE语句更新此元组，无法通过DELETE语句删除此元组。 | 表2ROW LEVEL SECURITY策略与适配SQL语法关系 | **Command** | **SELECT/ALL policy** | **UPDATE/ALL policy** | **DELETE/ALL policy** | |:---|:---|:---|:---| | SELECT | Existing row | No | No | | SELECT FOR UPDATE/SHARE | Existing row | Existing row | No | | UPDATE | No | Existing row | No | | UPDATE RETURNING | Existing row | Existing row | No | | DELETE | No | No | Existing row | | DELETE RETURNING | Existing row | No | Existing row | #### 示例1：创建行级访问控制策略，当前用户只能查看用户自身的数据 1. 创建用户alice和bob。 ``` DROP ROLE IF EXISTS alice; DROP ROLE IF EXISTS bob; CREATE ROLE alice PASSWORD '{password}'; CREATE ROLE bob PASSWORD '{password}'; ``` 2. 创建数据表public.all_data。 ``` DROP TABLE IF EXISTS public.all_data; CREATE TABLE public.all_data(id int, role varchar(100), data varchar(100)); ``` 3. 向数据表插入数据。 ``` 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'); ``` 4. 将表all_data的读取权限赋予alice和bob用户。 ``` GRANT SELECT ON all_data TO alice, bob; ``` 5. 打开行级访问控制策略开关。 ``` ALTER TABLE all_data ENABLE ROW LEVEL SECURITY; ``` 6. 创建行级访问控制策略，当前用户只能查看用户自身的数据。 ``` CREATE ROW LEVEL SECURITY POLICY all_data_rls ON all_data USING(role = CURRENT_USER); ``` 7. 查看表all_data相关信息。 ``` SELECT * FROM PG_GET_TABLEDEF('all_data'); ``` ![](https://support.huaweicloud.com/sqlreference-911-dws/figure/zh-cn_image_0000002630854674.png "点击放大") 8. 当前用户执行SELECT操作。 ``` SELECT * FROM all_data; ``` ![](https://support.huaweicloud.com/sqlreference-911-dws/figure/zh-cn_image_0000002573896965.png "点击放大") ``` EXPLAIN(COSTS OFF) SELECT * FROM all_data; ``` ![](https://support.huaweicloud.com/sqlreference-911-dws/figure/zh-cn_image_0000002543257050.png "点击放大") 9. 切换至alice用户。 ``` SET role alice password '{password}'; ``` 10. 执行SELECT操作。 ``` SELECT * FROM all_data; ``` ![](https://support.huaweicloud.com/sqlreference-911-dws/figure/zh-cn_image_0000002543416708.png "点击放大") ``` EXPLAIN(COSTS OFF) SELECT * FROM all_data; ``` ![](https://support.huaweicloud.com/sqlreference-911-dws/figure/zh-cn_image_0000002574016945.png "点击放大") #### 示例2：通过行级控制实现分区权限管理 1. 创建用户alice。 ``` SET role dbadmin password '{password}'; CREATE ROLE alice PASSWORD '{password1}'; ``` 2. 创建范围分区表web_returns_p1，并插入数据。 ``` DROP TABLE IF EXISTS web_returns_p1; CREATE TABLE web_returns_p1 ( wr_returned_date_sk integer, wr_returned_time_sk integer, wr_item_sk integer NOT NULL, wr_refunded_customer_sk integer ) WITH (orientation = column) DISTRIBUTE BY HASH (wr_item_sk) PARTITION BY RANGE(wr_returned_date_sk) ( PARTITION p2016 START(800) END(830) EVERY(1) ); INSERT INTO web_returns_p1 values (801,17,11,102); INSERT INTO web_returns_p1 values (802,18,12,103); ``` 3. 将表web_returns_p1的读取权限赋予alice用户。 ``` GRANT SELECT ON web_returns_p1 TO alice; ``` 4. 打开行级访问控制策略开关。 ``` ALTER TABLE web_returns_p1 ENABLE ROW LEVEL SECURITY; ``` 5. 创建行级访问控制策略web_returns_rsl。其中wr_returned_date_sk为web_returns_p1分区表的分区名，801为分区值。 ``` CREATE ROW LEVEL SECURITY POLICY web_returns_rsl ON web_returns_p1 USING('wr_returned_date_sk' = '801'); ``` 6. 将行级访问控制策略web_returns_rsl的赋予用户alice。 ``` ALTER ROW LEVEL SECURITY POLICY web_returns_rsl ON web_returns_p1 TO alice; ``` 7. 切换至alice用户。 ``` SET ROLE alice password '{password1}'; ``` 8. 查询表web_returns_p1。 ``` SELECT * FROM web_returns_p1; ``` #### 相关链接 [ALTER ROW LEVEL SECURITY POLICY](https://support.huaweicloud.com/sqlreference-911-dws/dws_06_0135.html)，[DROP ROW LEVEL SECURITY POLICY](https://support.huaweicloud.com/sqlreference-911-dws/dws_06_0200.html)