Why do I get the "Access Denied" error when I run a query in Amazon Athena?

Short description

You can get an "Access Denied" error because of the following reasons:

• The AWS Identity and Access Management (IAM) user doesn't have one or more of the following permissions:

• Read the source data bucket.
• Write the results to the query result bucket.

• The Amazon Simple Storage Service (Amazon S3) bucket policies don't allow the required permissions to the IAM user.
• The object owner is different from the Amazon S3 bucket owner.
• You don't have access to the AWS Key Management Service (AWS KMS) key that's used to read or write the encrypted data.
• The AWS Glue Data Catalog policy doesn't allow access to the IAM user.

Resolution

To troubleshoot the "Access Denied" error, confirm the following. For more information, see Troubleshooting in Athena.

Be sure that the IAM user has the required permissions to access the source data bucket and query result bucket

You get an error similar to the following if you don't have access to the source data bucket:

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)

You get an error similar to the following if you don't have access to the query result bucket:

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 user that runs the queries needs access to the source data bucket and the query result bucket. You can grant the required permission to the IAM user by attaching an IAM policy to the IAM user/role. The following IAM policy allows minimum permissions to the source data bucket and query result bucket:

{
  "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/*"
      ]
    }
  ]
}

Be sure to replace the following in this example policy:

• 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

You don't need to attach S3 bucket policies if your Athena table and S3 buckets are in the same account. However, if you do have S3 bucket policies, then be sure that they grant the required S3 actions to the IAM user/role. If your Athena table and S3 buckets are in different accounts, then you must attach the S3 bucket policies that grant the required access to the IAM user.

You can attach an S3 bucket policy similar to the following in account B to grant access to the IAM user athena_user in account A. This example policy explicitly allows the IAM user to access the S3 bucket my-athena-source-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/*"
      ]
    }
  ]
}

Be sure to replace the following in this example policy:

• 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 account ID for account A
• athena_user with the name of the IAM user in account A

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

Note: This policy allows all S3 actions to my-athena-source-bucket. You can update the S3 actions based on whether the S3 bucket is the source bucket or the query result bucket. For more information, see the Bucket policy or IAM user policies section in Cross-account access in Athena to Amazon S3 Buckets.

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? To define least-privileged permissions for the S3 bucket, see How to define least-privileged permissions for actions called by AWS services.

Update your AWS KMS key policy

If your source data is encrypted, or your Athena query writes encrypted results using an AWS KMS key, then be sure of the following:

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

This is an example of an AWS KMS key policy that allows all AWS KMS actions to all users in account ID 1111222233334444:

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

Be sure to update this policy based on your use case. 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, an S3 object is owned by the AWS account that uploaded it. This is true even when the bucket is owned by another account. If other accounts can upload objects to your bucket, then check the account that owns the objects that your users can't access. You can check the object owner by running the GetObjectAcl command.

If the S3 bucket owner and object owner are different, then the object owner can grant you full control of the object. The object owner can do this by running 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 Command Line Interface (AWS CLI) command cp from the bucket's account to copy the object over itself.

Note: If you receive errors when running AWS CLI commands, make sure that you’re using the most recent version of the AWS CLI.

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

Verify that the AWS Glue Data Catalog policy allows access to the IAM user/role

If you have an existing AWS Glue Data Catalog policy, then be sure that the policy allows access to the IAM user/role. For example, if you have the following policy in your Data Catalog, then the IAM user athena_user 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 AWS Glue resource-based access control policy examples.

With this policy in the Data Catalog, the Athena queries run by the user might fail with the following error:

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

Also, be sure that the IAM user/role that runs the queries has the required permissions to access the AWS Glue resources. For a complete list of required AWS Glue permissions, see AmazonAthenaFullAccess managed policy. Be sure that the required AWS Glue actions aren't denied by the Data Catalog resource policy. For more information, see Fine-grained access to databases and tables in the AWS Glue Data Catalog.

Related information

Identity and access management in Athena