Skip to content

AWS Athena Guide

Screenshot

Learn more about the benefits of Satori for Amazon Athena and Schedule a demo

Satori streamlines and simplifies the process of controlling access to data in AWS Athena. Satori reduces the risk of data leakage by misconfiguring users or permissions. When using Satori to deliver access to data in AWS Athena, organizations provision a single set of AWS credentials that are only used by the Satori DAC to access AWS Athena. Satori generates temporary credentials for data consumers instead of their existing AWS credentials. The benefits of this approach are as follows:

  1. There is no need to create an AWS IAM user for each data consumer, which reduces the risk of granting access to AWS resources users do not need.
  2. There is no need to grant users direct access to data in S3, which reduces the risk of data leakage by users downloading data directly from buckets.

Prerequisites

Perform the following steps to grant Satori access to AWS Athena.

Step 1: Create an IAM Policy for Satori

The IAM policy used by the Satori AWS user should be configured with read access to any S3 bucket where data that should be queried via AWS Athena is stored, as well as read and write access to the S3 bucket used for storing query result sets (S3 Output Location). For maximum security the policy should limit access to S3 only via AWS Athena and the Satori DAC IP addresses. For more information read the AWS documentation.

The following example provides an IAM policy you can use as a starting point to create your own IAM policy:

  • Replace <data_bucket1> and <data_bucket2> with the names of the buckets where you store your data you query with Athena
  • Replace <output_bucket> with the name of the bucket where query results should be stored
  • Replace the <1.1.1.1>, <2.2.2.2>, <3.3.3.3> IPs with the IPs of your DAC
{
    "Version": "2012-10-17",
    "Statement": [
        {
            "Sid": "AllowUsersToReadBucketsMetadata",
            "Effect": "Allow",
            "Action": [
                "s3:ListBucket",
                "s3:GetBucketLocation"
            ],
            "Resource": [
                "arn:aws:s3:::<data_bucket1>",
                "arn:aws:s3:::<data_bucket2>",
                "arn:aws:s3:::<output_bucket>"
            ],
            "Condition": {
                "ForAnyValue:IpAddress": {
                    "aws:SourceIp": [
                        "<1.1.1.1>",
                        "<2.2.2.2>",
                        "<3.3.3.3>"
                    ]
                }
            }
        },
        {
            "Sid": "AllowUsersToReadFromDataBuckets",
            "Effect": "Allow",
            "Action": [
                "s3:GetObject"
            ],
            "Resource": [
                "arn:aws:s3:::<data_bucket1>/*",
                "arn:aws:s3:::<data_bucket2>/*"
            ],
            "Condition": {
                "ForAnyValue:IpAddress": {
                    "aws:SourceIp": [
                        "<1.1.1.1>",
                        "<2.2.2.2>",
                        "<3.3.3.3>"
                    ]
                }
            }
        },
        {
            "Sid": "AllowAthenaToWriteToOutputBucket",
            "Effect": "Allow",
            "Action": [
                "s3:PutObject"
            ],
            "Resource": [
                "arn:aws:s3:::<output_bucket>/*"
            ]
        },
        {
            "Sid": "AllowUsersToReadFromOutputBucket",
            "Effect": "Allow",
            "Action": [
                "s3:GetObject"
            ],
            "Resource": [
                "arn:aws:s3:::<output_bucket>/*"
            ],
            "Condition": {
                "ForAnyValue:IpAddress": {
                    "aws:SourceIp": [
                        "<1.1.1.1>",
                        "<2.2.2.2>",
                        "<3.3.3.3>"
                    ]
                }
            }
        },
        {
            "Sid": "AllowUsersToUseAthena",
            "Effect":"Allow",
            "Action":[
                "athena:BatchGetNamedQuery",
                "athena:BatchGetQueryExecution",
                "athena:CreateNamedQuery",
                "athena:DeleteNamedQuery",
                "athena:GetDatabase",
                "athena:GetNamedQuery",
                "athena:GetQueryExecution",
                "athena:GetQueryResults",
                "athena:GetQueryResultsStream",
                "athena:GetTableMetadata",
                "athena:GetWorkGroup",
                "athena:ListDataCatalogs",
                "athena:ListDatabases",
                "athena:ListNamedQueries",
                "athena:ListQueryExecutions",
                "athena:ListTableMetadata",
                "athena:ListWorkGroups",
                "athena:StartQueryExecution",
                "athena:StopQueryExecution",
                "glue:GetDatabase",
                "glue:GetDatabases",
                "glue:GetTable",
                "glue:GetTables",
                "lakeformation:GetDataAccess"
            ],
            "Resource":[
                "*"
            ],
            "Condition": {
                "ForAnyValue:IpAddress": {
                    "aws:SourceIp": [
                        "<1.1.1.1>",
                        "<2.2.2.2>",
                        "<3.3.3.3>"
                    ]
                }
            }
        }
    ]
}

Perform the following steps to create the IAM policy for Satori:

  1. Go to AWS IAM
  2. Click on Policies and select Create Policy
  3. Click on the JSON tab and paste the JSON policy file you prepared.
  4. Complete the policy creation process and name your policy, for example: satori_athena_policy.

Step 2: Create an IAM User for Satori

  1. Go to AWS IAM.
  2. Click on Users and select Add Users.
  3. Enter satori_athena_user in the Username input field and check the Access key - Programmatic access checkbox.
  4. Select Attach Existing Policy and search for the policy created in the previous step.
  5. Now, attach the policy to the user.
  6. In the last step of the process, copy the AWS Access Key ID and Secret Access Key.

Adding an AWS Athena Data Store to Satori

  1. Login to the Satori Management Console.
  2. Go to the Data Stores view and click the Add Data Store button.
  3. Select the AWS Athena option.
  4. Provide an informative name for the data store, for example: Sales Data Warehouse.
  5. Select the AWS region where your data is hosted, for example: us-east-1.
  6. Select the Data Access Controller (DAC) to use for accessing the data store.
  7. Enter the AWS account ID for the Athena data store.
  8. Enter the AWS Access Key ID and Secret Access Key you copied in the previous step.
  9. Click the Create button. Satori will then validate that the credentials and AWS Account ID match
  10. Finally, you will be redirected to the Data Stores list view.

Connect to AWS Athena via Satori

Satori provides two options for obtaining credentials to connect to AWS Athena, for two specifc use-cases:

  1. User-specific Temporary Credentials - Each user can generate personal credentials from the Satori Data Portal. In this option, SATORI_USERNAME and SATORI_PASSWORD shown in the example below refer to the generated username and password.
  2. Service Account Credentials - Use the DAC Access Token credentials located in the management console. Go to the Satori setting and select the User management view, then click the DAC Access Token tab. In this option, the SATORI_USERNAME refers to the real username connected to the BI tool and the SATORI_PASSWORD refers to the Token value of the DAC Access Token. For more information see the Integration with BI Tools section.

Connecting with the JDBC driver

To connect to Athena via Satori using the JDBC driver, override or add the following parameters in the JDBC URL:

  • EndpointOverride=<Satori Hostname>:443
  • UseResultsetStreaming=0
  • User=<SATORI_USERNAME>
  • Password=<SATORI_PASSWORD>

For example:

jdbc:awsathena://AwsRegion=us-east-1;EndpointOverride=abc123.us-east-1.a.p0.satoricyber.net:443;UseResultsetStreaming=0;User=eddievh;Password=Jump1984!;...

Connecting with Looker

To connect to Athena via Satori using Looker and service account credentials update the following parameters:

  • Username: select the Email option.
  • Password: copy the UUID Token value from the DAC Access Token tab in the Satori settings, for example: befdcb5b-e1b3-49e8-84d8-093116c9daf5.
  • Additional Params: add the EndpointOverride and UseResultStreaming parameters, for example: AwsRegion=us-east-1;S3OutputLocation=s3://athena-output;EndPointOverride=athena-prod.us-east-1.a.p0.satoricyber.net;UseResultsetStreaming=0

Connecting with Tableau

Tableau can connect to Athena via Satori using a Tableau custom athena.properties file. Important note: custom *.properties files are not supported by the Tableau Online platform. Thus, in order to connect to Athena from Tableau via Satori, your version of Tableau Server needs to be hosted by your organization, either on-premise or on your own machine instances in your own cloud environment.

To connect to Athena via Satori using Tableau, follow these general high level steps:

  • Connect to Athena using Tableau Desktop and verify the connection and that you are able to retrieve expected results in Tableau. This will require Athena drivers to be installed for Tableau Desktop, as well your AWS access ID and token/secret.
  • Create a Satori Datastore for your Athena connection.
  • On your desktop, you will now need to create an athena.properties file and save it in ~/My Tableau Repository/Datasources/athena.properties
  • in this file you only need two lines/entries:
EndpointOverride=<Satori Hostname>:443
UseResultsetStreaming=0
  • You do not need to store your user ID and secret in this file, as Tableau stores this info at the workbook level.
  • Restart Tableau Desktop, and verify that your Athena data loads via Satori. Any policies or masking you have configured should be working at this point.

Connecting with Python

To connect to Athena via Satori using Python, set the endpoint_url argument to the client function, and the AWS credentials as described above. For example:

import boto3

client = boto3.client(
  service_name='athena',
  region_name='us-east-1',
  endpoint_url='https://abc123.us-east-1.a.p0.satoricyber.net',
  aws_access_key_id='<SATORI_USERNAME>',
  aws_secret_access_key='<SATORI_PASSWORD>'
)

Connecting with the AWS Command Line

To connect to Athena via Satori using the AWS command line interface, first create a profile using your personal credentials. For example:

export AWS_ACCESS_KEY_ID='<SATORI_USERNAME>'
export AWS_SECRET_ACCESS_KEY='<SATORI_PASSWORD>'

Then add the endpoint-url parameter to your aws athena command with the Satori hostname for the data store. For example: aws athena start-query-execution --query-string "select 1" --region us-east-1 --result-configuration '{ "OutputLocation": "s3://athena-output"}' --endpoint-url https://athena-prod.us-east-1.a.p0.satoricyber.net.

Network Policy Settings

Satori provides you with the ability to define your network security policy for your data store.

Screenshot

Simply, specify which IP addresses and subnet masks should be allowed access to the Data Store and which IP addresses and subnet masks should be blocked. Note that by default, Satori allows all IP address ranges if you leave the form empty.

Example 1

To allow all IP addresses - Leave the form empty. Screenshot

Example 2

To block specific IP addresses - Add them to the blocked IP address list. Screenshot

Example 3

To only allow access to specific IP addresses - Add them to the allow list. Note: Access from all other IP addresses ware blocked. Screenshot

Example 4

To allow access from a specific IP address range while blocking parts of the range - Add the IP range to the list of allowed IP addresses and add the IP address that you wish to block to the Block list. Screenshot