如何为 Amazon S3 中的 AWS KMS 加密数据启用跨账户 Amazon Redshift COPY 和 Redshift Spectrum 查询（四）

   drop schema if exists spectrumxacct;   create external schema spectrumxacct   from data catalog    database 'spectrumdb_account_b'   iam_role '<IAM role ARN of redshift_role_account_b,IAM role ARN of xacct_kms_role_account_b>'   create external database if not exists;

**注意：**替换账户 B 中的 IAM 角色 ARN，用逗号隔开，周围不加任何空格。

   select * from spectrumxacct.customer limit 10;

{    "Version": "2012-10-17",    "Statement": [        {            "Sid": "AllowS3",            "Effect": "Allow",            "Action": [                "s3:ListBucket",                "s3:GetObject"            ],            "Resource": [                "arn:aws:s3:::rs-xacct-kms-bucket/*",                "arn:aws:s3:::rs-xacct-kms-bucket"            ]        }    ]}

{    "Version": "2012-10-17",    "Statement": [        {            "Sid": "AllowUseOfTheKey",            "Effect": "Allow",            "Action": [                "kms:Encrypt",                "kms:Decrypt",                "kms:ReEncrypt*",                "kms:GenerateDataKey*",                "kms:DescribeKey"            ],            "Resource": [                "<ARN of kms_key_account_a from Account A>"            ]        },        {            "Sid": "AllowAttachmentOfPersistentResources",            "Effect": "Allow",            "Action": [                "kms:CreateGrant",                "kms:ListGrants",                "kms:RevokeGrant"            ],            "Resource": [                "<ARN of kms_key_account_a from Account A>"            ],            "Condition": {                "Bool": {                    "kms:GrantIsForAWSResource": true                }            }        }    ]}

{  "Version": "2012-10-17",  "Statement": [    {      "Effect": "Allow",      "Principal": {        "Service": "redshift.amazonaws.com"      },      "Action": "sts:AssumeRole"    },    {      "Effect": "Allow",      "Principal": {        "AWS": "arn:aws:iam::<Account B>:root"      },      "Action": "sts:AssumeRole"    }  ]}

{    "Version": "2012-10-17",    "Statement": [        {            "Sid": "Stmt1487639602000",            "Effect": "Allow",            "Action": [                "sts:AssumeRole"            ],            "Resource": ["<ARN for IAM role xacct_kms_role_account_a from Account A>"            ]        }    ]}

CREATE TABLE customer (  c_custkey      INTEGER NOT NULL,  c_name         VARCHAR(25) NOT NULL,  c_address      VARCHAR(25) NOT NULL,  c_city         VARCHAR(10) NOT NULL,  c_nation       VARCHAR(15) NOT NULL,  c_region       VARCHAR(12) NOT NULL,  c_phone        VARCHAR(15) NOT NULL,  c_mktsegment   VARCHAR(10) NOT NULL);

copy customer from 's3://rs-xacct-kms-bucket/customer/' iam_role '<ARN for IAM role redshift_role_account_b from Account B,<ARN for IAM role xacct_kms_role_account_a from Account A>'gzipregion 'us-west-2';

select * from customer limit 10;

drop schema if exists spectrumxacct2;create external schema spectrumxacct2from data catalog database 'spectrumdb_account_a' iam_role '<ARN for IAM role redshift_role_account_b from Account B,<ARN for IAM role xacct_kms_role_account_a from Account A>'create external database if not exists;

select * from spectrumxacct2.customer limit 10;