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

7 minute read

I want to run an Amazon Athena query, but I get an "Access Denied" error.

Short description

"Access Denied" query errors are usually related to permissions 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 user or IAM 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 S3 bucket owner.
You don't have access to the AWS KMS key that's used to read or write the encrypted data.

To troubleshoot the "Access Denied" error, confirm that you set the correct permissions for your use case. For more information about permissions-related errors, see Permissions.

Resolution

Note: If you receive errors when you run AWS Command Line Interface (AWS CLI) commands, then see Troubleshooting errors for the AWS CLI. 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

You receive the following error message:

"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".

The preceding error occurs when your Data Catalog policy denies access to the Data Catalog. The following example policy includes an explicit Deny that denies athena_user access to the AWS Glue 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:*"  
      ]  
    }  
  ]  
}

To resolve this issue, change the explicit Deny to Allow. To view example policies that include an explicit Allow, see Examples of database and table-level permissions. To change your AWS Glue Data Catalog policy, see Data Catalog settings.

For a complete list of required AWS Glue permissions, see AWS managed policy: AmazonAthenaFullAccess.

Check that the IAM entity has the required permissions to access the buckets

If you receive the following error message, then 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:###; S3 Extended Request ID:###=; Proxy: null), S3 Extended Request ID:###= (Path: s3://my-athena-source-bucket/athena_data.csv)".

If you receive the following error message, then 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 entity that runs the queries must have access to both buckets. To grant the required permissions, attach the following IAM policy to the IAM entity:

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

Note: Replace my-athena-source-bucket with the name of your source data bucket and my-athena-source-bucket/data/ with the source data location. Replace 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.

Add an 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 add a resource-based policy to the S3 bucket. However, if you already added 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 bucket are in different accounts, then add a resource-based policy to the S3 bucket that grants access to the IAM entity.

In the following example, the Athena user is in Account A and the S3 bucket is in Account B.

The following example resource-based policy explicitly allows athena_user in 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/*"  
      ]  
    }  
  ]  
}

Note: Replace my-athena-source-bucket with the name of your source data bucket and my-athena-source-bucket/data/ with the source data location. Replace 1111222233334444 with the ID of the IAM user account in Account A and athena_user with the name of the IAM user in account A.

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

Note: The preceding S3 bucket policy allows all S3 actions to my-athena-source-bucket. Update the S3 actions based on whether the S3 bucket is the source data bucket or the query result bucket. For more information, Configure 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 you encrypted your source data or your Athena query uses an AWS KMS key to write encrypted results, then take the following actions:

Confirm that the IAM user's policy allows the necessary AWS KMS actions.
Confirm that the AWS KMS keys policy allows access to the user.

The following example AWS KMS key policy gives permission to account 1111222233334444 to use IAM policies to allow all actions on the KMS key:

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

Note: In the preceding policy, replace 1111222233334444 with your account ID.

For more information, see Key policies in AWS KMS and Allows access to the AWS account and enabled IAM policies.

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. The object owner must run 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 cp AWS CLI 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?