Settings for querying tables of other accounts with Athena

aws

Redshift Serverless and other serverless aggregation services, run query with Glue Data Catalog - sambaiz-net

First, make it possible to access resources and Glue Data Catalog of the Owner Account from the Borrower Account which executes queries. There are some ways to access resources of other accounts, such as Owner Account’s user credentials or AssumeRole, but in this case, the role used to execute queries also needs the Athena permission of the Borrower Account, so it should be allowed to access resources of both accounts.

AWSのAssumeRole - sambaiz-net

Cross-account access to Glue Data Catalog

Allow the Borrower Account to grant permissions from the Data Catalog Settings of the Owner Account.

{
  "Version" : "2012-10-17",
  "Statement" : [ 
    {
        "Effect" : "Allow",
        "Principal" : {
          "AWS" : "<Borrower Account ID>"
        },
        "Action" : "glue:*",
        "Resource" : [
            "arn:aws:glue:ap-northeast-1:<Owner Account ID>:catalog",
            "arn:aws:glue:ap-northeast-1:<Owner Account ID>:database/*",
            "arn:aws:glue:ap-northeast-1:<Owner Account ID>:table/*"
        ]
    } 
  ]
}

Cross-account access to S3

Similarly, grant the S3 permission referred by the Data Catalog. This can be set from the Bucket Policy.

{
   "Version": "2012-10-17",
   "Statement": [
      {
          "Effect": "Allow",
          "Principal": {
             "AWS": "<Borrower Account ID>"
          },
          "Action": [
             "s3:GetObject",
             "s3:ListBucket"
          ],
          "Resource": [
             "arn:aws:s3:::<Owner Bucket>/*",
             "arn:aws:s3:::<Owner Bucket>"
          ]
       }
    ]
}

Careate an Athena data source

Create an Athena data source for Owner Account’s Glue Data Catalog in Borrower Account.

Attach policies

Attach policies for resources of both accounts required to execute the query to the role and use it in the client.

{
    "Effect": "Allow",
    "Action": "athena:*",
    "Resource": "arn:aws:athena:ap-northeast-1:<Borrower Account ID>:workgroup/<workgroup_name>"
},
{
    "Effect": "Allow",
    "Action": "athena:GetDataCatalog",
    "Resource": "arn:aws:athena:ap-northeast-1:<Borrower Account ID>:datacatalog/<data_source_name>"
},
{
    "Effect": "Allow",
    "Action": [
        "s3:GetBucketLocation",
        "s3:GetObject",
        "s3:ListBucket",
        "s3:ListBucketMultipartUploads",
        "s3:ListMultipartUploadParts",
        "s3:AbortMultipartUpload",
        "s3:PutObject"
    ],
    "Resource": [
        "arn:aws:s3:::<athena_query_results_bucket>",
        "arn:aws:s3:::<athena_query_results_bucket>/*"
    ]
},
{
    "Effect": "Allow",
    "Action": "glue:*",
    "Resource": [
        "arn:aws:glue:ap-northeast-1:<Owner Account ID>:catalog",
        "arn:aws:glue:ap-northeast-1:<Owner Account ID>:database/*",
        "arn:aws:glue:ap-northeast-1:<Owner Account ID>:table/*"
    ]
},
{
    "Effect": "Allow",
    "Action": [
        "s3:GetObject",
        "s3:ListBucket",
    ],
    "Resource": [
        "arn:aws:s3:::<Owner Bucket>",
        "arn:aws:s3:::<Owner Bucket>/*"
    ]
},
{
    "Effect": "Allow",
    "Action": [
        "s3:GetBucketLocation",
        "s3:GetObject",
        "s3:ListBucket",
        "s3:ListBucketMultipartUploads",
        "s3:ListMultipartUploadParts",
        "s3:AbortMultipartUpload",
        "s3:PutObject"
    ],
    "Resource": [
        "arn:aws:s3:::<athena_query_results_bucket>",
        "arn:aws:s3:::<athena_query_results_bucket>/*"
    ]
}

Now you can query tables in the Glue Data Catalog of other accounts.

SELECT * FROM <data_source_name>.<database>.<table>