Connections > Amazon Athena connection properties > Prepare for authentication

Prepare for authentication

You can configure permanent IAM credentials, EC2 instance profile, EC2 role to asssume role authentication types to access Amazon Athena.

To use the permanent IAM credentials authentication, create an IAM user, attach the required policies, and generate the access and secret key in the AWS Console. Keep these details handy to use in the connection properties.

To use EC2 instance profile authentication, install the Secure Agent on the EC2 instance and attach the EC2 role to the EC2 instance.

To use EC2 role to assume role authentication, install the Secure Agent on the EC2 instance, attach the EC2 role to the EC2 instance, and enable the EC2 role to assume another IAM role specified by the IAM Role ARN.

Before you configure the connection properties, create the minimal Amazon S3 policy, AWS Glue data catalog policy, and the Amazon Athena policies. Define the required permissions for the IAM user or EC2 role in the policies.

Attach the policies to the IAM user or EC2 role based on the authentication type that you want to configure.

Create an Amazon S3 policy

Create an Amazon S3 policy in the AWS console and define the permissions to store Amazon Athena results on Amazon S3.

Use the following minimum required permissions to store Amazon Athena results on Amazon S3:

•PutObject
•GetObject
•DeleteObject
•ListBucket
•GetBucketLocation
•ListAllMyBuckets
•GetBucketAcl

You can use the following sample Amazon S3 policy:

{
"Version": "2012-10-17",
"Statement": [
{
"Sid": "VisualEditor0",
"Effect": "Allow",
"Action": [
"s3:PutObject",
"s3:GetObject",
"s3:ListBucket",
"s3:DeleteObject"
],
"Resource": "*"
},
{
"Effect": "Allow",
"Action": [
"s3:ListBucket",
"s3:GetBucketLocation",
"s3:ListAllMyBuckets",
"s3:GetBucketAcl"
],
"Resource": [
"*"
]
}
]
}

Create an AWS Glue data catalog policy

You can use AWS IAM to define policies and roles to access resources used by AWS Glue.

Amazon Athena uses the AWS Glue Data Catalog to store and retrieve table metadata for the Amazon S3 data in your AWS account.

You can use the following sample policy for AWS Glue Data Catalog:

{
"Version": "2012-10-17",
"Statement": [
{
"Effect": "Allow",
"Action": [
"glue:*",
],
"Resource": [
"*"
]
}
]
}

Create an Amazon Athena policy

Specify the minimum required permissions for Amazon Athena Connector to read data from views and external tables in the AWS Glue data catalog and to read and query Amazon S3 files.

You can use the following minimum required permissions:

•GetWorkGroup
•GetTableMetadata
•StartQueryExecution
•GetQueryResultsStream
•ListDatabases
•GetQueryExecution
•GetQueryResults
•GetDatabase
•ListTableMetadata
•GetDataCatalog
•CreatePreparedStatement
•DeletePreparedStatement

You can use the following sample policy for Amazon Athena:

{
"Version": "2012-10-17",
"Statement": [
{
"Effect": "Allow",
"Action": [
"athena:GetWorkGroup",
"athena:GetTableMetadata",
"athena:StartQueryExecution",
"athena:GetQueryResultsStream",
"athena:ListDatabases",
"athena:GetQueryExecution",
"athena:GetQueryResults",
"athena:GetDatabase",
"athena:ListTableMetadata",
"athena:GetDataCatalog"
"athena:CreatePreparedStatement",
"athena:DeletePreparedStatement"
],
"Resource": [
"arn:aws:athena:*:*:workgroup/*",
"arn:aws:athena:*:*:datacatalog/*"
]
},
{
"Effect": "Allow",
"Action": [
"athena:ListDataCatalogs",
"athena:ListWorkGroups"
],
"Resource": "*"
}
]
}

Configure EC2 Role to Assume Role

You can configure an EC2 role to assume an IAM role and generate temporary security credentials to connect to Amazon Athena from the same or different AWS accounts.

The EC2 role can assume another IAM role from the same or different AWS account without requiring a permanent access key and secret key.

When you configure EC2 role to assume role, ensure that you have the sts:AssumeRole permission and a trust relationship established within the AWS accounts to use the temporary security credentials. The trust relationship is defined in the trust policy of the IAM role when you create the role. The IAM role adds the EC2 role as a trusted entity allowing the EC2 role to use the temporary security credentials and access the AWS accounts.

When the trusted EC2 role requests for the temporary security credentials, the AWS Security Token Service (AWS STS) dynamically generates the temporary security credentials that are valid for a specified period and provides the credentials to the trusted EC2 role.

Consider the following prerequisites before you use the EC2 role to assume role authentication:

•Install the Secure Agent on the AWS EC2 instance.
•The EC2 role attached to the AWS EC2 instance must not have access to Amazon Athena but must have permissions to assume another IAM role.

The following is a sample trust policy that is attached to the EC2 role that allows the EC2 role to assume another IAM role:

{
"Version": "2012-10-17",
"Statement": [
{
"Sid": "Statement1",
"Effect": "Allow",
"Action": "sts:AssumeRole",
"Resource": "arn:aws:iam::006102214893:role/aws_athena_assumeRole"
}
]
}

•The IAM role that needs to be assumed by the EC2 role must have a permission policy and a trust policy attached to access Amazon Athena.

The trust policy must contain the ARN of the EC2 role.

You can also specify the external ID of your AWS account for a more secure access to Amazon Athena. The external ID must be a string.

The following sample shows the assumed IAM role's trust policy with the ARN of the EC2 role and external ID:

{
"Version": "2012-10-17",
"Statement": [
{
"Effect": "Allow",
"Principal": {
"AWS": "arn:aws:iam::006102214893:root" //anyone in this account 006102214893 can assume this role, this can also be limited to one role.
},
"Action": "sts:AssumeRole",
"Condition": {
"StringEquals": {
"sts:ExternalId": "athena_dev"
}
}
}
]
}