本文為英文版的機器翻譯版本,如內容有任何歧義或不一致之處,概以英文版為準。
每個使用者結合多個政策
RLS 在 Amazon Redshift 中,支援為每個使用者和物件連接多個政策。當為使用者定義多個政策時,Amazon Redshift 會根據資料表RLSCONJUNCTIONTYPE的設定,使用 AND或 OR 語法套用所有政策。如需結合類型的更多相關資訊,請參閱 ALTER TABLE。
資料表上的多個政策都可以與您建立關聯。您可以直接附加多個政策,或者您屬於多個角色,而這些角色附加了不同的政策。
當多個政策應該限制指定關係中的資料列存取時,您可以將RLSCONJUNCTIONTYPE關係設定為 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)
當多個政策應允許使用者在指定關係中看到更多資料列時,使用者可以設定與 OR RLS CONJUNCTION TYPE 的關係。請考量下列範例。Alice 只能看到「音樂會」和「體育」做為指定政策。
-- 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)