Amazon Redshift 將不再支援從修補程式 198 開始建立新的 Python UDFs。現有 Python UDF 將繼續正常運作至 2026 年 6 月 30 日。如需詳細資訊，請參閱[部落格文章](https://aws.amazon.com/blogs/big-data/amazon-redshift-python-user-defined-functions-will-reach-end-of-support-after-june-30-2026/)。

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

# Amazon Redshift 聯合許可 - end-to-end範例
<a name="federated-permissions-end-examples"></a>

以下是end-to-end範例，示範如何使用 Amazon Redshift 聯合許可建立和管理完整的資料控管政策。這些政策包括資料列層級安全性 (RLS)、動態資料遮罩 (DDM) 和資料欄層級許可 (CLP)，這些政策可共同根據使用者角色和條件控制資料存取。

您必須是 `superuser`或具有 [sys：secadmin](https://docs.aws.amazon.com/redshift/latest/dg/r_roles-default.html) 角色，才能執行此範例。

## 先決條件
<a name="federated-permissions-end-examples-prereqs"></a>

這些範例假設下列「IAMR：role\$1name」角色已存在於帳戶中，否則請建立這些角色。此外，Redshift 資料倉儲使用 AWS Glue Data Catalog 名為 "catalog\$1name" 的 註冊，並具有 "db\$1name" 資料庫。

## 在目錄執行個體上，將 sys：secadmin 角色授予對應的 IAM 角色
<a name="federated-permissions-end-examples-catalog-grant"></a>

```
-- Grant sys:secadmin role to relevant user (must be run on Redshift catalog instance)   
        GRANT ROLE sys:secadmin TO "IAMR:AccountSecurityAdminrole_name";
```

### 靜態將在 Redshift 運算倉儲上執行
<a name="federated-permissions-end-examples-compute"></a>

如果您使用的是 IAM 或 IdC 使用者，您可以略過兩個步驟來建立本機倉儲使用者和全域身分映射。

#### 步驟 1：建立本機倉儲的必要使用者，以測試控管政策
<a name="federated-permissions-end-examples-users"></a>

```
-- Create test users.  
CREATE USER alice WITH PASSWORD 'Alice_pass_1';  
CREATE USER oscar WITH PASSWORD 'Oscar_pass_1';  
CREATE USER sierra WITH PASSWORD 'Sierra_pass_1';
```

#### 步驟 2：設定全域身分 IAM 角色映射
<a name="federated-permissions-end-examples-mapping"></a>

```
-- Map local users to IAM roles (executed by superuser).  
        
-- Make user sierra a sys:secadmin by setting the global identity  
-- to `IAMR:AccountSecurityAdmin`.  
-- This role has been granted secadmin privilege on Redshift catalog instance.  

ALTER USER sierra SET GLOBAL IDENTITY  
IAM_ROLE 'arn:aws:iam::123456789012:role/AccountSecurityAdmin';  

ALTER USER alice SET GLOBAL IDENTITY  
IAM_ROLE 'arn:aws:iam::123456789012:role/Analyst';  

ALTER USER oscar SET GLOBAL IDENTITY  
IAM_ROLE 'arn:aws:iam::123456789012:role/Operator';  

-- Verify global identity settings.  
SET SESSION AUTHORIZATION sierra;  
SHOW GLOBAL IDENTITY;  

SET SESSION AUTHORIZATION alice;  
SHOW GLOBAL IDENTITY;  

SET SESSION AUTHORIZATION oscar;  
SHOW GLOBAL IDENTITY;  

-- Reset to default session.  
RESET SESSION AUTHORIZATION;
```

## 設定環境
<a name="federated-permissions-end-examples-environment"></a>

首先，建立資料表，並填入範例客戶資料和查閱控管政策的資料表。

```
-- Create the main customer table.  
CREATE TABLE db_name@catalog_name.public.customers (  
    id INTEGER,  
    name VARCHAR(50),  
    email VARCHAR(100),  
    region VARCHAR(20),  
    revenue DECIMAL(10,2)  
);  

-- Populate with sample customer data.  
INSERT INTO db_name@catalog_name.public.customers VALUES  
(1, 'John Smith', 'john@email.com', 'US', 1000.00),  
(2, 'Jane Doe', 'jane@email.com', 'EU', 500.00),  
(3, 'Mike Johnson', 'mike@email.com', 'US', 2000.00);  

-- Grant basic table access.  
GRANT ALL ON db_name@catalog_name.public.customers TO PUBLIC;  

-- Create lookup table for region-based policies.  
CREATE TABLE db_name@catalog_name.public.lookup_regions (  
    region_code VARCHAR(20),  
    allowed BOOLEAN  
);  

INSERT INTO db_name@catalog_name.public.lookup_regions VALUES   
('US', TRUE),   
('EU', FALSE),   
('APAC', TRUE);  

GRANT ALL ON db_name@catalog_name.public.lookup_regions TO PUBLIC;  

-- Create lookup table for revenue-based masking.  
CREATE TABLE db_name@catalog_name.public.lookup_revenue_tiers (base_revenue INTEGER);  

INSERT INTO db_name@catalog_name.public.lookup_revenue_tiers VALUES (1000), (2000);  
GRANT ALL ON db_name@catalog_name.public.lookup_revenue_tiers TO PUBLIC;
```

## 設定資料欄層級許可 (CLP)
<a name="federated-permissions-end-examples-clp"></a>

設定不同使用者的資料欄層級存取，以控制使用者可以存取的資料欄。

```
-- Grant specific column access to Analyst i.e. `alice`.  
GRANT SELECT (id, region) ON db_name@catalog_name.public.customers  
    TO "IAMR:Analyst";  

-- Grant different column access to Operator i.e. `oscar`.  
GRANT SELECT (id, name, revenue) ON db_name@catalog_name.public.customers  
    TO "IAMR:operator";
```

## 建立資料列層級安全性 (RLS) 政策
<a name="federated-permissions-end-examples-rls"></a>

建立 RLS 政策，根據使用者授予和資料條件來控制使用者可以看到哪些資料列。

```
-- Switch to admin user to create policies.  
SET SESSION AUTHORIZATION sierra;  

-- Create simple RLS policy: Analysts see only US customers.  
CREATE RLS POLICY db_name@catalog_name.us_only   
WITH (region VARCHAR(20))   
USING (region = 'US');  

-- Attach the policy to the Analyst i.e. `alice`.  
ATTACH RLS POLICY db_name@catalog_name.us_only   
ON db_name@catalog_name.public.customers   
TO "IAMR:Analyst";  

-- Enable row level security on the table.  
ALTER TABLE db_name@catalog_name.public.customers ROW LEVEL SECURITY ON;  

-- Create advanced RLS policy using lookup table.  
CREATE RLS POLICY db_name@catalog_name.region_lookup_policy  
WITH (region VARCHAR(20)) AS r  
USING (r.region IN (  
    SELECT region_code   
    FROM public.lookup_regions   
    WHERE allowed = TRUE  
));  

-- Attach the lookup-based policy to Operator i.e. `oscar`.  
ATTACH RLS POLICY db_name@catalog_name.region_lookup_policy   
ON db_name@catalog_name.public.customers   
TO "IAMR:Operator";
```

## 建立動態資料遮罩 (DDM) 政策
<a name="federated-permissions-end-examples-ddm"></a>

建立遮罩政策，根據使用者角色和條件混淆敏感資料。

```
-- Create masking policy for PII data (names and emails).  
CREATE MASKING POLICY db_name@catalog_name.mask_pii   
WITH (DATA VARCHAR(100))  
USING (SHA2(DATA + 'secret', 256)::TEXT);  

-- Attach masking to name and email columns for all users.  
ATTACH MASKING POLICY db_name@catalog_name.mask_pii   
ON db_name@catalog_name.public.customers (name)   
TO PUBLIC;  

ATTACH MASKING POLICY db_name@catalog_name.mask_pii   
ON db_name@catalog_name.public.customers (email)   
TO PUBLIC;  

-- Create conditional masking policy for revenue data.  
CREATE MASKING POLICY db_name@catalog_name.conditional_mask  
WITH (revenue DECIMAL(10,2))  
USING (CASE WHEN revenue IN (SELECT base_revenue  
                                    FROM public.lookup_revenue_tiers)  
            THEN revenue ELSE 0.00 END);  

-- Attach conditional masking to Analyst i.e. `alice` with priority.  
ATTACH MASKING POLICY db_name@catalog_name.conditional_mask   
ON db_name@catalog_name.public.customers (revenue)   
TO "IAMR:Analyst"   
PRIORITY 20;
```

## 稽核可用/套用的政策
<a name="federated-permissions-end-examples-audit"></a>

使用 SHOW 命令來確認您的控管政策已正確設定。

```
-- Show all RLS policies in the database.  
SHOW RLS POLICIES FROM DATABASE db_name@catalog_name LIMIT 10;  

-- Show RLS policies for specific users and tables.  
SHOW RLS POLICIES ON db_name@catalog_name.public.customers FOR "IAMR:Analyst" LIMIT 10;  
SHOW RLS POLICIES ON db_name@catalog_name.public.customers FOR "IAMR:Operator" LIMIT 10;  
SHOW RLS POLICIES ON db_name@catalog_name.public.customers FOR PUBLIC LIMIT 10;  

-- Show all masking policies in the database.  
SHOW MASKING POLICIES FROM DATABASE db_name@catalog_name LIMIT 10;  

-- Show Masking policies for specific users and tables.  
SHOW MASKING POLICIES ON db_name@catalog_name.public.customers FOR "IAMR:Analyst" LIMIT 10;  
SHOW MASKING POLICIES ON db_name@catalog_name.public.customers FOR "IAMR:Operator" LIMIT 10;  
SHOW MASKING POLICIES ON db_name@catalog_name.public.customers FOR PUBLIC LIMIT 10;
```

## 測試存取模式
<a name="federated-permissions-end-examples-testing"></a>

測試不同的使用者如何根據其角色和套用的政策來查看資料。

```
-- Test as analyst: Only US customers, only id/region columns,  
-- conditional revenue masking.  
SET SESSION AUTHORIZATION alice;  
SELECT id, region FROM db_name@catalog_name.public.customers ORDER BY id;  
SELECT id, region, revenue FROM db_name@catalog_name.public.customers ORDER BY id;  

-- Test as operator: Allowed regions only, masked names, specific columns.  
SET SESSION AUTHORIZATION oscar;  
SELECT id, name, revenue FROM db_name@catalog_name.public.customers ORDER BY id;  
SELECT id, name, region FROM db_name@catalog_name.public.customers ORDER BY id;  

-- Test as admin: Full access to all data.  
SET SESSION AUTHORIZATION sierra;  
SELECT * FROM db_name@catalog_name.public.customers ORDER BY id;
```

## 變更政策
<a name="federated-permissions-end-examples-alter"></a>

修改現有政策以變更其行為，而無需重新建立它們。

```
-- Switch back to admin user.  
SET SESSION AUTHORIZATION sierra;  

-- Alter the PII masking policy to use simple string replacement.  
ALTER MASKING POLICY db_name@catalog_name.mask_pii  
USING ('***MASKED***'::TEXT);  

-- Alter the conditional masking policy to use different threshold.  
ALTER MASKING POLICY db_name@catalog_name.conditional_mask  
USING (CASE WHEN revenue >= 500.00 THEN revenue ELSE -1.00 END);  

-- Alter the RLS policy to show only disallowed regions.  
ALTER RLS POLICY db_name@catalog_name.region_lookup_policy  
USING (r.region IN (  
    SELECT region_code   
    FROM db_name@catalog_name.public.lookup_regions   
    WHERE allowed = FALSE  
));
```

## 分離和捨棄政策
<a name="federated-permissions-end-examples-detach"></a>

不再需要政策時將其移除。

```
-- Detach RLS policies from users.  
DETACH RLS POLICY db_name@catalog_name.us_only   
ON db_name@catalog_name.public.customers   
FROM "IAMR:Analyst";  

DETACH RLS POLICY db_name@catalog_name.region_lookup_policy   
ON db_name@catalog_name.public.customers   
FROM "IAMR:Operator";  

-- Detach masking policies.  
DETACH MASKING POLICY db_name@catalog_name.mask_pii   
ON db_name@catalog_name.public.customers (name)   
FROM PUBLIC;  

DETACH MASKING POLICY db_name@catalog_name.mask_pii   
ON db_name@catalog_name.public.customers (email)   
FROM PUBLIC;  

DETACH MASKING POLICY db_name@catalog_name.conditional_mask   
ON db_name@catalog_name.public.customers (revenue)   
FROM "IAMR:Analyst";
```

## 清除：關閉 RLS 和捨棄政策
<a name="federated-permissions-end-examples-cleanup"></a>

```
-- Turn off Row-level security.  
ALTER TABLE db_name@catalog_name.public.customers ROW LEVEL SECURITY OFF;  

-- Drop policies.  
DROP RLS POLICY db_name@catalog_name.us_only CASCADE;  
DROP RLS POLICY db_name@catalog_name.region_lookup_policy CASCADE;  
DROP MASKING POLICY db_name@catalog_name.mask_pii CASCADE;  
DROP MASKING POLICY db_name@catalog_name.conditional_mask CASCADE;  

-- Drop tables.  
DROP TABLE db_name@catalog_name.public.customers;  
DROP TABLE db_name@catalog_name.public.lookup_regions;  
DROP TABLE db_name@catalog_name.public.lookup_revenue_tiers;
```

## 重設全域身分
<a name="federated-permissions-end-examples-global"></a>

```
-- TO rest the global identity.  
ALTER USER alice RESET GLOBAL IDENTITY;  
ALTER USER oscar RESET GLOBAL IDENTITY;  
ALTER USER sierra RESET GLOBAL IDENTITY;
```