为每个用户组合多个策略
Amazon Redshift 中的 RLS 支持为每个用户和对象附加多个策略。当为一个用户定义了多个策略时,Amazon Redshift 会根据表的 RLS CONJUNCTION TYPE 设置,使用 AND 或 OR 语法应用所有策略。有关联接类型的更多信息,请参阅ALTER TABLE。
一个表上的多个策略可以与您关联。将多个策略直接附加到您,或是您属于多个角色,并且这些角色附加了不同策略。
当多个策略应限制给定关系中的行访问权限时,可以将该关系的 RLS CONJUNCTION TYPE 设置为 AND。考虑以下示例。Alice 只能看到策略指定的“catname”为 NBA 的体育赛事。
-- Create an analyst role and grant it to a user named Alice. CREATE ROLE analyst; CREATE USER alice WITH PASSWORD 'Name_is_alice_1'; GRANT ROLE analyst TO alice; -- Create an RLS policy that only lets the user see sports. CREATE RLS POLICY policy_sports WITH (catgroup VARCHAR(10)) USING (catgroup = 'Sports'); -- Create an RLS policy that only lets the user see NBA. CREATE RLS POLICY policy_nba WITH (catname VARCHAR(10)) USING (catname = 'NBA'); -- Attach both to the analyst role. ATTACH RLS POLICY policy_sports ON category TO ROLE analyst; ATTACH RLS POLICY policy_nba ON category TO ROLE analyst; -- Activate RLS on the category table with AND CONJUNCTION TYPE. ALTER TABLE category ROW LEVEL SECURITY ON CONJUNCTION TYPE AND; -- Change session to Alice. SET SESSION AUTHORIZATION alice; -- Select all from the category table. SELECT catgroup, catname FROM category; catgroup | catname ---------+--------- Sports | NBA (1 row)
当多个策略应允许用户查看给定关系中的更多行时,用户可以将该关系的 RLS CONJUNCTION TYPE 设置为 OR。考虑以下示例。Alice 只能看到策略指定的“Concerts”和“Sports”。
-- Create an analyst role and grant it to a user named Alice. CREATE ROLE analyst; CREATE USER alice WITH PASSWORD 'Name_is_alice_1'; GRANT ROLE analyst TO alice; -- Create an RLS policy that only lets the user see concerts. CREATE RLS POLICY policy_concerts WITH (catgroup VARCHAR(10)) USING (catgroup = 'Concerts'); -- Create an RLS policy that only lets the user see sports. CREATE RLS POLICY policy_sports WITH (catgroup VARCHAR(10)) USING (catgroup = 'Sports'); -- Attach both to the analyst role. ATTACH RLS POLICY policy_concerts ON category TO ROLE analyst; ATTACH RLS POLICY policy_sports ON category TO ROLE analyst; -- Activate RLS on the category table with OR CONJUNCTION TYPE. ALTER TABLE category ROW LEVEL SECURITY ON CONJUNCTION TYPE OR; -- Change session to Alice. SET SESSION AUTHORIZATION alice; -- Select all from the category table. SELECT catgroup, count(*) FROM category GROUP BY catgroup ORDER BY catgroup; catgroup | count ---------+------- Concerts | 3 Sports | 5 (2 rows)