创建、附加、分离和删除 RLS 策略 - Amazon Redshift

创建、附加、分离和删除 RLS 策略

您可以执行以下操作:

  • 要创建 RLS 策略,请使用 CREATE RLS POLICY 命令。

  • 要将表上的 RLS 策略附加到一个或多个用户或角色,请使用 ATTACH RLS POLICY 命令。

  • 要将表上的行级别安全性策略与一个或多个用户或角色分离,请使用 DETACH RLS POLICY 命令。

  • 要删除所有数据库中所有表的 RLS 策略,请使用 DROP RLS POLICY 命令。

下面是一个端到端的示例,用于说明超级用户如何创建某些用户和角色。然后,具有 secadmin 角色的用户将创建、附加、分离和删除 RLS 策略。此示例使用 tickit 示例数据库。有关更多信息,请参阅《Amazon Redshift 入门指南》中的将数据从 Amazon S3 加载到 Amazon Redshift

-- Create users and roles referenced in the policy statements. CREATE ROLE analyst; CREATE ROLE consumer; CREATE ROLE dbadmin; CREATE ROLE auditor; CREATE USER bob WITH PASSWORD 'Name_is_bob_1'; CREATE USER alice WITH PASSWORD 'Name_is_alice_1'; CREATE USER joe WITH PASSWORD 'Name_is_joe_1'; CREATE USER molly WITH PASSWORD 'Name_is_molly_1'; CREATE USER bruce WITH PASSWORD 'Name_is_bruce_1'; GRANT ROLE sys:secadmin TO bob; GRANT ROLE analyst TO alice; GRANT ROLE consumer TO joe; GRANT ROLE dbadmin TO molly; GRANT ROLE auditor TO bruce; GRANT ALL ON TABLE tickit_category_redshift TO PUBLIC; GRANT ALL ON TABLE tickit_sales_redshift TO PUBLIC; GRANT ALL ON TABLE tickit_event_redshift TO PUBLIC; -- Create table and schema referenced in the policy statements. CREATE SCHEMA target_schema; GRANT ALL ON SCHEMA target_schema TO PUBLIC; CREATE TABLE target_schema.target_event_table (LIKE tickit_event_redshift); GRANT ALL ON TABLE target_schema.target_event_table TO PUBLIC; -- Change session to analyst alice. SET SESSION AUTHORIZATION alice; -- Check the tuples visible to analyst alice. -- Should contain all 3 categories. SELECT catgroup, count(*) FROM tickit_category_redshift GROUP BY catgroup ORDER BY catgroup; -- Change session to security administrator bob. SET SESSION AUTHORIZATION bob; CREATE RLS POLICY policy_concerts WITH (catgroup VARCHAR(10)) USING (catgroup = 'Concerts'); SELECT poldb, polname, polalias, polatts, polqual, polenabled, polmodifiedby FROM svv_rls_policy WHERE poldb = CURRENT_DATABASE(); ATTACH RLS POLICY policy_concerts ON tickit_category_redshift TO ROLE analyst, ROLE dbadmin; ALTER TABLE tickit_category_redshift ROW LEVEL SECURITY ON; SELECT * FROM svv_rls_attached_policy; -- Change session to analyst alice. SET SESSION AUTHORIZATION alice; -- Check that tuples with only `Concert` category will be visible to analyst alice. SELECT catgroup, count(*) FROM tickit_category_redshift GROUP BY catgroup ORDER BY catgroup; -- Change session to consumer joe. SET SESSION AUTHORIZATION joe; -- Although the policy is attached to a different role, no tuples will be -- visible to consumer joe because the default deny all policy is applied. SELECT catgroup, count(*) FROM tickit_category_redshift GROUP BY catgroup ORDER BY catgroup; -- Change session to dbadmin molly. SET SESSION AUTHORIZATION molly; -- Check that tuples with only `Concert` category will be visible to dbadmin molly. SELECT catgroup, count(*) FROM tickit_category_redshift GROUP BY catgroup ORDER BY catgroup; -- Check that EXPLAIN output contains RLS SecureScan to prevent disclosure of -- sensitive information such as RLS filters. EXPLAIN SELECT catgroup, count(*) FROM tickit_category_redshift GROUP BY catgroup ORDER BY catgroup; -- Change session to security administrator bob. SET SESSION AUTHORIZATION bob; -- Grant IGNORE RLS permission so that RLS policies do not get applicable to role dbadmin. GRANT IGNORE RLS TO ROLE dbadmin; -- Grant EXPLAIN RLS permission so that anyone in role auditor can view complete EXPLAIN output. GRANT EXPLAIN RLS TO ROLE auditor; -- Change session to dbadmin molly. SET SESSION AUTHORIZATION molly; -- Check that all tuples are visible to dbadmin molly because `IGNORE RLS` is granted to role dbadmin. SELECT catgroup, count(*) FROM tickit_category_redshift GROUP BY catgroup ORDER BY catgroup; -- Change session to auditor bruce. SET SESSION AUTHORIZATION bruce; -- Check explain plan is visible to auditor bruce because `EXPLAIN RLS` is granted to role auditor. EXPLAIN SELECT catgroup, count(*) FROM tickit_category_redshift GROUP BY catgroup ORDER BY catgroup; -- Change session to security administrator bob. SET SESSION AUTHORIZATION bob; DETACH RLS POLICY policy_concerts ON tickit_category_redshift FROM ROLE analyst, ROLE dbadmin; -- Change session to analyst alice. SET SESSION AUTHORIZATION alice; -- Check that no tuples are visible to analyst alice. -- Although the policy is detached, no tuples will be visible to analyst alice -- because of default deny all policy is applied if the table has RLS on. SELECT catgroup, count(*) FROM tickit_category_redshift GROUP BY catgroup ORDER BY catgroup; -- Change session to security administrator bob. SET SESSION AUTHORIZATION bob; CREATE RLS POLICY policy_events WITH (eventid INTEGER) AS ev USING ( ev.eventid IN (SELECT eventid FROM tickit_sales_redshift WHERE qtysold <3) ); ATTACH RLS POLICY policy_events ON tickit_event_redshift TO ROLE analyst; ATTACH RLS POLICY policy_events ON target_schema.target_event_table TO ROLE consumer; RESET SESSION AUTHORIZATION; -- Can not cannot alter type of dependent column. ALTER TABLE target_schema.target_event_table ALTER COLUMN eventid TYPE float; ALTER TABLE tickit_event_redshift ALTER COLUMN eventid TYPE float; ALTER TABLE tickit_sales_redshift ALTER COLUMN eventid TYPE float; ALTER TABLE tickit_sales_redshift ALTER COLUMN qtysold TYPE float; -- Can not cannot rename dependent column. ALTER TABLE target_schema.target_event_table RENAME COLUMN eventid TO renamed_eventid; ALTER TABLE tickit_event_redshift RENAME COLUMN eventid TO renamed_eventid; ALTER TABLE tickit_sales_redshift RENAME COLUMN eventid TO renamed_eventid; ALTER TABLE tickit_sales_redshift RENAME COLUMN qtysold TO renamed_qtysold; -- Can not drop dependent column. ALTER TABLE target_schema.target_event_table DROP COLUMN eventid CASCADE; ALTER TABLE tickit_event_redshift DROP COLUMN eventid CASCADE; ALTER TABLE tickit_sales_redshift DROP COLUMN eventid CASCADE; ALTER TABLE tickit_sales_redshift DROP COLUMN qtysold CASCADE; -- Can not drop lookup table. DROP TABLE tickit_sales_redshift CASCADE; -- Change session to security administrator bob. SET SESSION AUTHORIZATION bob; DROP RLS POLICY policy_concerts; DROP RLS POLICY IF EXISTS policy_events; ALTER TABLE tickit_category_redshift ROW LEVEL SECURITY OFF; RESET SESSION AUTHORIZATION; -- Drop users and roles. DROP USER bob; DROP USER alice; DROP USER joe; DROP USER molly; DROP USER bruce; DROP ROLE analyst; DROP ROLE consumer; DROP ROLE auditor FORCE; DROP ROLE dbadmin FORCE;