从补丁 198 开始，Amazon Redshift 将不再支持创建新的 Python UDF。现有的 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 联合身份验证权限：端到端示例
以下端到端示例说明您如何使用 Amazon Redshift 联合身份验证权限来创建和管理全面的数据治理策略。这些策略包括行级别安全性（RLS）、动态数据掩蔽（DDM）和列级别权限（CLP），它们协同工作，根据用户角色和条件控制数据访问权限。
您必须是 `superuser` 或具有 [sys:secadmin](https://docs.aws.amazon.com/redshift/latest/dg/r_roles-default.html) 角色才能运行此示例。
## 先决条件
这些示例假设账户中已存在以下“IAMR:role\$1name”角色，如果没有，请创建。此外，Redshift 数据仓库注册到名为“catalog\$1name”的 AWS Glue Data Catalog，并具有数据库“db\$1name”。
## 在目录实例上，将 sys:secadmin 角色授予对应的 IAM 角色
```
-- Grant sys:secadmin role to relevant user (must be run on Redshift catalog instance)
GRANT ROLE sys:secadmin TO "IAMR:AccountSecurityAdminrole_name";
```
### 剩余部分将在 Redshift 计算仓库上运行
如果您在使用 IAM 或 IdC 用户，则可以跳过以下两个步骤来创建本地仓库用户和全局身份映射。
#### 步骤 1：创建本地仓库所需的用户用于测试治理策略
```
-- 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 角色映射
```
-- 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;
```
## 设置环境
首先，创建表并在其中填充示例客户数据，以及创建用于治理策略的查找表。
```
-- 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）
为不同用户配置列级别访问权限，用于控制用户可以访问哪些列。
```
-- 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）策略
创建 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）策略
创建掩蔽策略来根据用户角色和条件对敏感数据进行模糊处理。
```
-- 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;
```
## 审计可用/已应用的策略
使用 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;
```
## 测试访问模式
测试根据用户的角色和应用的策略，不同用户会如何查看数据。
```
-- 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;
```
## 修改策略
修改现有策略以更改其行为，而无需重新创建策略。
```
-- 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
));
```
## 分离和删除策略
删除不再需要的策略。
```
-- 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 并删除策略
```
-- 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;
```
## 重置全局身份
```
-- TO rest the global identity.
ALTER USER alice RESET GLOBAL IDENTITY;
ALTER USER oscar RESET GLOBAL IDENTITY;
ALTER USER sierra RESET GLOBAL IDENTITY;
```