選取您的 Cookie 偏好設定

我們使用提供自身網站和服務所需的基本 Cookie 和類似工具。我們使用效能 Cookie 收集匿名統計資料,以便了解客戶如何使用我們的網站並進行改進。基本 Cookie 無法停用,但可以按一下「自訂」或「拒絕」以拒絕效能 Cookie。

如果您同意,AWS 與經核准的第三方也會使用 Cookie 提供實用的網站功能、記住您的偏好設定,並顯示相關內容,包括相關廣告。若要接受或拒絕所有非必要 Cookie,請按一下「接受」或「拒絕」。若要進行更詳細的選擇,請按一下「自訂」。

資料列層級安全性end-to-end範例

焦點模式
資料列層級安全性end-to-end範例 - Amazon Redshift

本文為英文版的機器翻譯版本,如內容有任何歧義或不一致之處,概以英文版為準。

本文為英文版的機器翻譯版本,如內容有任何歧義或不一致之處,概以英文版為準。

以下是說明超級使用者如何建立某些使用者和角色的端對端範例。然後,具有 secadmin 角色的使用者會建立、附加、分離和捨棄 RLS 政策。此範例會使用票卷範例資料庫。如需詳細資訊,請參閱《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;
隱私權網站條款Cookie 偏好設定
© 2025, Amazon Web Services, Inc.或其附屬公司。保留所有權利。