How do I resolve "Access Denied" permission errors when I run a query in Amazon Athena?

7 minute read
0

When I run an Amazon Athena query, I get an "Access Denied" error.

Short description

Access Denied query errors are usually related to permission issues with other AWS services or AWS accounts that Athena interacts with. Examples of services that Athena commonly interacts with include AWS Identity and Access Management (IAM), Amazon Simple Storage Service (Amazon S3), and AWS Key Management Service (AWS KMS).

The following reasons can cause an Access Denied error:

  • The AWS Glue Data Catalog policy doesn't allow access to the IAM entity (user or role).
  • The IAM entity doesn't have permissions to read the S3 source data bucket or write the results to the query result bucket.
  • The S3 bucket policies don't allow the required permissions to the IAM entity when the Athena table and buckets are in different accounts.
  • The object owner is different from the Amazon S3 bucket owner.
  • You don't have access to the AWS KMS key that's used to read or write the encrypted data.

Resolution

To troubleshoot the Access Denied error, confirm the following permissions for your use case. For more information, see Troubleshooting in Athena.

Note: If you receive errors when you run AWS Command Line Interface (AWS CLI) commands, then see Troubleshoot AWS CLI errors. Also, make sure that you're using the most recent AWS CLI version.

Check that the Glue Data Catalog policy allows access to the IAM entity

If you use an Glue Data Catalog policy, then be sure that the policy allows access to the IAM entity. For example, if you have the following policy in your Data Catalog, then the athena_user IAM entity is denied access to the Data Catalog:

{  "Version": "2012-10-17",
  "Statement": [
    {
      "Principal": {
        "AWS": [
          "arn:aws:iam::1111222233334444:user/athena_user"
        ]
      },
      "Effect": "Deny",
      "Action": [
        "glue:*"
      ],
      "Resource": [
        "arn:aws:glue:us-east-1:1111222233334444:*"
      ]
    }
  ]
}

For example policies, see Resource-based policy examples for AWS Glue.

When the preceding policy is in the Data Catalog, the Athena queries that the user runs might fail with the following error response:

"Insufficient permissions to execute the query. User: arn:aws:iam:: 1111222233334444:user/athena_user is not authorized to perform: glue:GetTable on resource: arn:aws:glue:us-east-1:1111222233334444:database/doc_example_database with an explicit deny Query Id: example_query_ID"

Be sure that the IAM entity that runs the queries has the required permissions to access the AWS Glue resources. For a complete list of required AWS Glue permissions, see AWS managed policy: AmazonAthenaFullAccess.

Be sure that the Data Catalog resource policy doesn't deny the required AWS Glue actions. For more information, see Fine-grained access to databases and tables in the AWS Glue Data Catalog.

Check that the IAM entity has the required permissions to access the source data bucket and query result bucket

When you don't have access to the source data bucket, you get an error response similar to the following one:

"Your query has the following errors:com.amazonaws.services.s3.model.AmazonS3Exception: Access Denied (Service: Amazon S3; Status Code: 403; Error Code: AccessDenied; Request ID: 72VA5FB0ASWCQFPD; S3 Extended Request ID: cykX1CZ/KfxpL/h8/DOQoCBJ88qFGYqt6J52Jqh87qBfyN8c2P2azRiYjjJE1HL7i0Mg9xxxxxx=; Proxy: null), S3 Extended Request ID: cykX1CZ/KfxpL/h8/DOQoCBJ88qFGYqt6J52Jqh87qBfyN8c2P2azRiYjjJE1HL7i0Mg9xxxxxx= (Path: s3://my-athena-source-bucket/athena_data.csv)"

When you don't have access to the query result bucket, you get an error response similar to the following one:

"Your query has the following errors:Access denied when writing output to url: s3://my-athena-result-bucket/Unsaved/2021/05/07/example_query_ID.csv . Please ensure you are allowed to access the S3 bucket. If you are encrypting query results with KMS key, please ensure you are allowed to access your KMS key"

The IAM entity that runs the queries must have access to the source data bucket and the query result bucket. To grant the required permissions for the IAM entity, attach an IAM policy to the IAM entity.

Example IAM policy:

{  "Version": "2012-10-17",
  "Statement": [
    {
      "Effect": "Allow",
      "Action": [
        "s3:ListBucket"
      ],
      "Resource": [
        "arn:aws:s3:::my-athena-source-bucket"
      ]
    },
    {
      "Effect": "Allow",
      "Action": [
        "s3:GetObject"
      ],
      "Resource": [
        "arn:aws:s3:::my-athena-source-bucket/data/*"
      ]
    },
    {
      "Effect": "Allow",
      "Action": [
        "s3:GetBucketLocation",
        "s3:GetObject",
        "s3:ListBucket",
        "s3:ListBucketMultipartUploads",
        "s3:AbortMultipartUpload",
        "s3:PutObject",
        "s3:ListMultipartUploadParts"
      ],
      "Resource": [
        "arn:aws:s3:::my-athena-result-bucket",
        "arn:aws:s3:::my-athena-result-bucket/*"
      ]
    }
  ]
}

In the preceding policy, replace the following variables:

  • my-athena-source-bucket with the name of your source data bucket
  • my-athena-source-bucket/data/ with the source data location
  • my-athena-result-bucket with the name of your query result bucket

For the complete list of policies, see AWS managed policies for Amazon Athena.

Attach the Amazon S3 bucket policy with required permissions for cross-account queries

If your Athena table and S3 bucket are in the same account, then you don't need to attach an S3 bucket policy. However, if you already attached a bucket policy, then check that it grants S3 permissions to the IAM entity that's querying the data.

If your Athena table and S3 buckets are in different accounts, then attach the S3 bucket policies that grant access to the IAM entity.

In the account that has the S3 bucket, attach an S3 bucket policy that grants access to the athena_user in the other account. The following example policy explicitly allows an IAM user from account A to access the my-athena-source-bucket S3 bucket in account B:

{  "Version": "2012-10-17",
  "Id": "Policy1620692934647",
  "Statement": [
    {
      "Sid": "Stmt1620692932186",
      "Effect": "Allow",
      "Principal": {
        "AWS": "arn:aws:iam::1111222233334444:user/athena_user"
      },
      "Action": [
        "s3:*"
      ],
      "Resource": [
        "arn:aws:s3:::my-athena-source-bucket",
        "arn:aws:s3:::my-athena-source-bucket/data/*"
      ]
    }
  ]
}

In the preceding example policy, replace the following variables:

  • my-athena-source-bucket with the name of your source data bucket
  • my-athena-source-bucket/data/ with the source data location
  • 1111222233334444 with the ID for the IAM user account (account A)
  • athena_user with the name of the IAM user (account A)

To grant access to the bucket to all users in the other account, replace the Principal key with a key that specifies root ("arn:aws:iam::1111222233334444:root").

Note: The preceding policy allows all S3 actions to my-athena-source-bucket. Update the S3 actions based on whether the S3 bucket is the source bucket or the query result bucket. For more information, Cross-account access to bucket objects.

Confirm that the S3 bucket policy doesn't include statements that explicitly deny access to account A or its IAM users. Also, be sure that your policy doesn't include conditions that might deny the requests. For more information, see How do I troubleshoot 403 Access Denied errors from Amazon S3?

Update your AWS KMS key policy

If your source data is encrypted, or your Athena query uses an AWS KMS key to write encrypted results, then confirm the following permissions:

  • The IAM user's policy allows the necessary AWS KMS actions.
  • The AWS KMS keys policy allows access to the user.

The following example AWS KMS key policy allows all AWS KMS actions to all users in account 1111222233334444:

{  "Sid": "Enable IAM policies",
  "Effect": "Allow",
  "Principal": {
    "AWS": "arn:aws:iam::1111222233334444:root"
   },
  "Action": "kms:*",
  "Resource": "*"
}

Note: In the preceding policy, replace the example variables with your variables.

For more information, see Key policies in AWS KMS and Permissions to encrypted data in Amazon S3.

Be sure that the S3 bucket owner has access to objects

By default, the account that uploads an S3 object to a bucket owns the object. This is true even when another account owns the bucket. If users in other accounts can upload objects to your bucket, then check the account that owns the objects that your users can't access. Run the GetObjectAcl command to check the object owner.

If the S3 bucket owner and object owner are different, then the object owner can grant you full control of the object. To do this, the object owner runs the PutObjectAcl command with the bucket-owner-full-control parameter.

To change the ownership of the object to the account that owns the S3 bucket, run the AWS CLI cp command. Make sure to run the cp command from the bucket's account to copy the object over itself.

For more information, see Why can't I access an object that was uploaded to my Amazon S3 bucket by another AWS account?

Related information

Identity and access management in Athena

AWS OFFICIAL
AWS OFFICIALUpdated a month ago