# Use Athena to query data registered with AWS Lake Formation
[AWS Lake Formation](https://docs.aws.amazon.com/lake-formation/latest/dg/what-is-lake-formation.html) allows you to define and enforce database, table, and column-level access policies when using Athena queries to read data stored in Amazon S3 or accessed through federated data sources. Lake Formation provides an authorization and governance layer on data stored in Amazon S3 or federated data catalogs. You can use a hierarchy of permissions in Lake Formation to grant or revoke permissions to read data catalog objects such as databases, tables, and columns. Lake Formation simplifies the management of permissions and allows you to implement fine-grained access control (FGAC) for your data.
You can use Athena to query both data that is registered with Lake Formation and data that is not registered with Lake Formation.
Lake Formation permissions apply when using Athena to query source data from Amazon S3 locations or data catalogs that are registered with Lake Formation. Lake Formation permissions also apply when you create databases and tables that point to registered Amazon S3 data locations or data catalogs.
Lake Formation permissions do not apply when writing objects, nor do they apply when querying data or metadata that are not registered with Lake Formation. For source data and metadata that are not registered with Lake Formation, access is determined by IAM permissions policies and AWS Glue actions. Athena query results locations in Amazon S3 cannot be registered with Lake Formation, and IAM permissions policies for Amazon S3 control access. In addition, Lake Formation permissions do not apply to Athena query history. You can use Athena workgroups to control access to query history.
For more information about Lake Formation, see [Lake Formation FAQs](https://aws.amazon.com/lake-formation/faqs/) and the [AWS Lake Formation Developer Guide](https://docs.aws.amazon.com/lake-formation/latest/dg/what-is-lake-formation.html).
## Apply Lake Formation permissions to existing databases and tables
If you are new to Athena and you use Lake Formation to configure access to query data, you do not need to configure IAM policies so that users can read data and create metadata. You can use Lake Formation to administer permissions.
Registering data with Lake Formation and updating IAM permissions policies is not a requirement. If data is not registered with Lake Formation, Athena users who have appropriate permissions can continue to query data not registered with Lake Formation.
If you have existing Athena users who query Amazon S3 data not registered with Lake Formation, you can update IAM permissions for Amazon S3—and the AWS Glue Data Catalog, if applicable—so that you can use Lake Formation permissions to manage user access centrally. For permission to read Amazon S3 data locations, you can update resource-based and identity-based policies to modify Amazon S3 permissions. For access to metadata, if you configured resource-level policies for fine-grained access control with AWS Glue, you can use Lake Formation permissions to manage access instead.
For more information, see [Configure access to databases and tables in the AWS Glue Data Catalog](fine-grained-access-to-glue-resources.md) and [Upgrading AWS Glue data permissions to the AWS Lake Formation model](https://docs.aws.amazon.com/lake-formation/latest/dg/upgrade-glue-lake-formation.html) in the *AWS Lake Formation Developer Guide*.
**Topics**
+ [Apply Lake Formation permissions to existing databases and tables](#lf-athena-apply-lf-permissions-to-existing-databases-and-tables)
+ [How data access works](lf-athena-access.md)
+ [Considerations and limitations](lf-athena-limitations.md)
+ [Cross-account access](lf-athena-limitations-cross-account.md)
+ [Manage user permissions](lf-athena-user-permissions.md)
+ [Use Lake Formation and JDBC or ODBC for federated access](security-athena-lake-formation-jdbc.md)
# How Athena accesses data registered with Lake Formation
The access workflow described in this section applies when you run Athena queries on Amazon S3 locations, data catalogs, or metadata objects that are registered with Lake Formation. For more information, see [Registering a data lake](https://docs.aws.amazon.com/lake-formation/latest/dg/register-data-lake.html) in the *AWS Lake Formation Developer Guide*. In addition to registering data, the Lake Formation administrator applies Lake Formation permissions that grant or revoke access to metadata in the data catalog, AWS Glue Data Catalog, or the data location in Amazon S3. For more information, see [Security and access control to metadata and data](https://docs.aws.amazon.com/lake-formation/latest/dg/security-data-access.html#security-data-access-permissions) in the *AWS Lake Formation Developer Guide*.
Each time an Athena principal (user, group, or role) runs a query on data registered using Lake Formation, Lake Formation verifies that the principal has the appropriate Lake Formation permissions to the database, table, and data source location as appropriate for the query. If the principal has access, Lake Formation *vends* temporary credentials to Athena, and the query runs.
The following diagram shows how credential vending works in Athena on a query-by-query basis for a hypothetical `SELECT` query on a table with an Amazon S3 location or data catalog registered in Lake Formation:
![\[Credential vending workflow for a query on an Athena table.\]](http://docs.aws.amazon.com/athena/latest/ug/images/lake-formation-athena-security.png)
1. A principal runs a `SELECT` query in Athena.
1. Athena analyzes the query and checks Lake Formation permissions to see if the principal has been granted access to the table and table columns.
1. If the principal has access, Athena requests credentials from Lake Formation. If the principal *does not* have access, Athena issues an access denied error.
1. Lake Formation issues credentials to Athena to use when reading data from Amazon S3 or catalog, along with the list of allowed columns.
1. Athena uses the Lake Formation temporary credentials to query the data from Amazon S3 or catalog. After the query completes, Athena discards the credentials.
# Considerations and limitations for querying data registered with Lake Formation
Consider the following when using Athena to query data registered in Lake Formation. For additional information, see [Known issues for AWS Lake Formation](https://docs.aws.amazon.com/lake-formation/latest/dg/limitations.html) in the *AWS Lake Formation Developer Guide*.
**Topics**
+ [Column metadata visible to users without data permissions to column in some circumstances](#lf-athena-limitations-column-metadata)
+ [Working with Lake Formation permissions to views](#lf-athena-limitations-permissions-to-views)
+ [Iceberg DDL support](#lf-athena-limitations-iceberg-ddl-operations)
+ [Lake Formation fine-grained access control and Athena workgroups](#lf-athena-limitations-fine-grained-access-control)
+ [Athena query results location in Amazon S3 not registered with Lake Formation](#lf-athena-limitations-query-results-location)
+ [Use Athena workgroups to limit access to query history](#lf-athena-limitations-use-workgroups-to-limit-access-to-query-history)
+ [CSE-KMS Amazon S3 registered with Lake Formation cannot be queried in Athena](#lf-athena-limitations-cse-kms)
+ [Partitioned data locations registered with Lake Formation must be in table subdirectories](#lf-athena-limitations-partioned-data-locations)
+ [Create table as select (CTAS) queries require Amazon S3 write permissions](#lf-athena-limitations-ctas-queries)
+ [The DESCRIBE permission is required on the default database](#lf-athena-limitations-describe-default)
## Column metadata visible to unauthorized users in some circumstances with Avro and custom SerDe
Lake Formation column-level authorization prevents users from accessing data in columns for which the user does not have Lake Formation permissions. However, in certain situations, users are able to access metadata describing all columns in the table, including the columns for which they do not have permissions to the data.
This occurs when column metadata is stored in table properties for tables using either the Apache Avro storage format or using a custom Serializer/Deserializer (SerDe) in which table schema is defined in table properties along with the SerDe definition. When using Athena with Lake Formation, we recommend that you review the contents of table properties that you register with Lake Formation and, where possible, limit the information stored in table properties to prevent any sensitive metadata from being visible to users.
## Understand Lake Formation and views
For data registered with Lake Formation, an Athena user can create a `VIEW` only if they have Lake Formation permissions to the tables, columns, and source Amazon S3 data locations on which the `VIEW` is based. After a `VIEW` is created in Athena, Lake Formation permissions can be applied to the `VIEW`. Column-level permissions are not available for a `VIEW`. Users who have Lake Formation permissions to a `VIEW` but do not have permissions to the table and columns on which the view was based are not able to use the `VIEW` to query data. However, users with this mix of permissions are able to use statements like `DESCRIBE VIEW`, `SHOW CREATE VIEW`, and `SHOW COLUMNS` to see `VIEW` metadata. For this reason, be sure to align Lake Formation permissions for each `VIEW` with underlying table permissions. Cell filters defined on a table do not apply to a `VIEW` for that table. Resource link names must have the same name as the resource in the originating account. There are additional limitations when working with views in a cross-account setup. For more information about setting up permissions for shared views across accounts, see [Configure cross-account Data Catalog access](lf-athena-limitations-cross-account.md).
## Iceberg DDL support
Athena does not currently support DDL operations on Iceberg tables whose location is registered with Lake Formation. Attempting to run a DDL query on one of these Iceberg tables can return an Amazon S3 access denied error or fail with a query timeout. DDL operations on Iceberg tables require the user to have direct Amazon S3 access to the Iceberg table location.
## Lake Formation fine-grained access control and Athena workgroups
Users in the same Athena workgroup can see the data that Lake Formation fine-grained access control has configured to be accessible to the workgroup. For more information about using fine-grained access control in Lake Formation, see [Manage fine-grained access control using AWS Lake Formation](https://aws.amazon.com/blogs/big-data/manage-fine-grained-access-control-using-aws-lake-formation/) in the *AWS Big Data Blog*.
## Athena query results location in Amazon S3 not registered with Lake Formation
The query results locations in Amazon S3 for Athena cannot be registered with Lake Formation. Lake Formation permissions don't limit access to these locations. Unless you limit access, Athena users can access query result files and metadata when they don't have Lake Formation permissions for the data. To avoid this, we recommend that you use workgroups to specify the location for query results and align workgroup membership with Lake Formation permissions. You can then use IAM permissions policies to limit access to query results locations. For more information about query results, see [Work with query results and recent queries](querying.md).
## Use Athena workgroups to limit access to query history
Athena query history exposes a list of saved queries and complete query strings. Unless you use workgroups to separate access to query histories, Athena users who are not authorized to query data in Lake Formation are able to view query strings run on that data, including column names, selection criteria, and so on. We recommend that you use workgroups to separate query histories, and align Athena workgroup membership with Lake Formation permissions to limit access. For more information, see [Use workgroups to control query access and costs](workgroups-manage-queries-control-costs.md).
## Query CSE\$1KMS encrypted tables registered with Lake Formation
Open Table Format (OTF) tables such as Apache Iceberg that have the following characteristics cannot be queried with Athena:
+ The tables are based on Amazon S3 data locations that are registered with Lake Formation.
+ The objects in Amazon S3 are encrypted using client-side encryption (CSE).
+ The encryption uses AWS KMS customer-managed keys (`CSE_KMS`).
To query non-OTF tables that are encrypted with a `CSE_KMS` key), add the following block to the policy of the AWS KMS key that you use for CSE encryption. ** is the ARN of the AWS KMS key that encrypts the data. ** is the ARN of the IAM role that registers the Amazon S3 location in Lake Formation.
```
{
"Sid": "Allow use of the key",
"Effect": "Allow",
"Principal": {
"AWS": "*"
},
"Action": "kms:Decrypt",
"Resource": "",
"Condition": {
"ArnLike": {
"aws:PrincipalArn": ""
}
}
}
```
## Partitioned data locations registered with Lake Formation must be in table subdirectories
Partitioned tables registered with Lake Formation must have partitioned data in directories that are subdirectories of the table in Amazon S3. For example, a table with the location `s3://amzn-s3-demo-bucket/mytable` and partitions `s3://amzn-s3-demo-bucket/mytable/dt=2019-07-11`, `s3://amzn-s3-demo-bucket/mytable/dt=2019-07-12`, and so on can be registered with Lake Formation and queried using Athena. On the other hand, a table with the location `s3://amzn-s3-demo-bucket/mytable` and partitions located in `s3://amzn-s3-demo-bucket/dt=2019-07-11`, `s3://amzn-s3-demo-bucket/dt=2019-07-12`, and so on, cannot be registered with Lake Formation. Because such partitions are not subdirectories of `s3://amzn-s3-demo-bucket/mytable`, they also cannot be read from Athena.
## Create table as select (CTAS) queries require Amazon S3 write permissions
Create Table As Statements (CTAS) require write access to the Amazon S3 location of tables. To run CTAS queries on data registered with Lake Formation, Athena users must have IAM permissions to write to the table Amazon S3 locations in addition to the appropriate Lake Formation permissions to read the data locations. For more information, see [Create a table from query results (CTAS)](ctas.md).
## The DESCRIBE permission is required on the default database
The Lake Formation `DESCRIBE` permission is required on the `default` database so that Lake Formation can view it. The following example AWS CLI command grants the `DESCRIBE` permission on the `default` database to the user `datalake_user1` in AWS account `111122223333`.
```
aws lakeformation grant-permissions --principal DataLakePrincipalIdentifier=arn:aws:iam::111122223333:user/datalake_user1 --permissions "DESCRIBE" --resource '{ "Database": {"Name":"default"}}
```
For more information, see [DESCRIBE](https://docs.aws.amazon.com/lake-formation/latest/dg/lf-permissions-reference.html#perm-describe) in the *AWS Lake Formation Developer Guide*.
# Configure cross-account Data Catalog access
To access a data catalog in another account, you can use Athena's cross-account AWS Glue feature or set up cross-account access in Lake Formation.
## Option A: Configure cross-account Data Catalog access in Athena
You can use Athena's cross-account AWS Glue catalog feature to register the catalog in your account. This capability is available only in Athena engine version 2 and later versions and is limited to same-Region use between accounts. For more information, see [Register a Data Catalog from another account](data-sources-glue-cross-account.md).
If the Data Catalog to be shared has a resource policy configured in AWS Glue, it must be updated to allow access to the AWS Resource Access Manager and grant permissions to Account B to use Account A's Data Catalog.
For more information, see [Configure cross-account access to AWS Glue data catalogs](security-iam-cross-account-glue-catalog-access.md).
## Option B: Configure cross-account access in Lake Formation
AWS Lake Formation lets you use a single account to manage a central Data Catalog. You can use this feature to implement [cross-account access](https://docs.aws.amazon.com/lake-formation/latest/dg/access-control-cross-account.html) to Data Catalog metadata and underlying data. For example, an owner account can grant another (recipient) account `SELECT` permission on a table.
For a shared database or table to appear in the Athena Query Editor, you [create a resource link](https://docs.aws.amazon.com/lake-formation/latest/dg/resource-links-about.html) in Lake Formation to the shared database or table. When the recipient account in Lake Formation queries the owner's table, [CloudTrail](https://docs.aws.amazon.com/lake-formation/latest/dg/cross-account-logging.html) adds the data access event to the logs for both the recipient account and the owner account.
For shared views, keep in mind the following points:
+ Queries are run on target resource links, not on the source table or view, and then the output is shared to the target account.
+ It is not sufficient to share only the view. All the tables that are involved in creating the view must be part of the cross-account share.
+ The name of the resource link created on the shared resources must match the name of the resource in the owner account. If the name does not match, an error message like Failed analyzing stored view 'awsdatacatalog.*my-lf-resource-link*.*my-lf-view*': line 3:3: Schema *schema\$1name* does not exist occurs.
For more information about cross-account access in Lake Formation, see the following resources in the *AWS Lake Formation Developer Guide*:
[Cross-account access](https://docs.aws.amazon.com/lake-formation/latest/dg/access-control-cross-account.html)
[How resource links work in Lake Formation](https://docs.aws.amazon.com/lake-formation/latest/dg/resource-links-about.html)
[Cross-account CloudTrail logging](https://docs.aws.amazon.com/lake-formation/latest/dg/cross-account-logging.html)
# Manage Lake Formation and Athena user permissions
Lake Formation vends credentials to query Amazon S3 data stores or federated catalogs that are registered with Lake Formation. If you previously used IAM policies to allow or deny permissions to read catalogs or data locations in Amazon S3, you can use Lake Formation permissions instead. However, other IAM permissions are still required.
Whenever you use IAM policies, make sure that you follow IAM best practices. For more information, see [Security best practices in IAM](https://docs.aws.amazon.com/IAM/latest/UserGuide/best-practices.html) in the *IAM User Guide*.
The following sections summarize the permissions required to use Athena to query data registered in Lake Formation. For more information, see [Security in AWS Lake Formation](https://docs.aws.amazon.com/lake-formation/latest/dg/security.html) in the *AWS Lake Formation Developer Guide*.
**Topics**
+ [Identity-based permissions for Lake Formation and Athena](#lf-athena-user-permissions-identity-based)
+ [Amazon S3 permissions for Athena query results locations](#lf-athena-user-permissions-query-results-locations)
+ [Athena workgroup memberships to query history](#lf-athena-user-permissions-workgroup-memberships-query-history)
+ [Lake Formation permissions to data](#lf-athena-user-permissions-data)
+ [IAM permissions to write to Amazon S3 locations](#lf-athena-user-permissions-s3-write)
+ [Permissions to encrypted data, metadata, and Athena query results](#lf-athena-user-permissions-encrypted)
+ [Resource-based permissions for Amazon S3 buckets in external accounts (optional)](#lf-athena-user-permissions-s3-cross-account)
## Identity-based permissions for Lake Formation and Athena
Anyone using Athena to query data registered with Lake Formation must have an IAM permissions policy that allows the `lakeformation:GetDataAccess` action. The [AWS managed policy: AmazonAthenaFullAccess](security-iam-awsmanpol.md#amazonathenafullaccess-managed-policy) allows this action. If you use inline policies, be sure to update permissions policies to allow this action.
In Lake Formation, a *data lake administrator* has permissions to create metadata objects such as databases and tables, grant Lake Formation permissions to other users, and register new Amazon S3 locations or data catalogs. To register new locations, permissions to the service-linked role for Lake Formation are required. For more information, see [Create a data lake administrator](https://docs.aws.amazon.com/lake-formation/latest/dg/getting-started-setup.html#create-data-lake-admin) and [Service-linked role permissions for Lake Formation](https://docs.aws.amazon.com/lake-formation/latest/dg/service-linked-roles.html#service-linked-role-permissions) in the *AWS Lake Formation Developer Guide*.
A Lake Formation user can use Athena to query databases, tables, table columns, and underlying Amazon S3 data stores or catalogs based on Lake Formation permissions granted to them by data lake administrators. Users cannot create databases or tables, or register new Amazon S3 locations with Lake Formation. For more information, see [Create a data lake user](https://docs.aws.amazon.com/lake-formation/latest/dg/cloudtrail-tut-create-lf-user.html) in the *AWS Lake Formation Developer Guide*.
In Athena, identity-based permissions policies, including those for Athena workgroups, still control access to Athena actions for Amazon Web Services account users. In addition, federated access might be provided through the SAML-based authentication available with Athena drivers. For more information, see [Use workgroups to control query access and costs](workgroups-manage-queries-control-costs.md), [Use IAM policies to control workgroup access](workgroups-iam-policy.md), and [Enable federated access to the Athena API](access-federation-saml.md).
For more information, see [Granting Lake Formation permissions](https://docs.aws.amazon.com/lake-formation/latest/dg/lake-formation-permissions.html) in the *AWS Lake Formation Developer Guide*.
## Amazon S3 permissions for Athena query results locations
The query results locations in Amazon S3 for Athena cannot be registered with Lake Formation. Lake Formation permissions don't limit access to these locations. Unless you limit access, Athena users can access query result files and metadata when they don't have Lake Formation permissions for the data. To avoid this, we recommend that you use workgroups to specify the location for query results and align workgroup membership with Lake Formation permissions. You can then use IAM permissions policies to limit access to query results locations. For more information about query results, see [Work with query results and recent queries](querying.md).
## Athena workgroup memberships to query history
Athena query history exposes a list of saved queries and complete query strings. Unless you use workgroups to separate access to query histories, Athena users who are not authorized to query data in Lake Formation are able to view query strings run on that data, including column names, selection criteria, and so on. We recommend that you use workgroups to separate query histories, and align Athena workgroup membership with Lake Formation permissions to limit access. For more information, see [Use workgroups to control query access and costs](workgroups-manage-queries-control-costs.md).
## Lake Formation permissions to data
In addition to the baseline permission to use Lake Formation, Athena users must have Lake Formation permissions to access resources that they query. These permissions are granted and managed by a Lake Formation administrator. For more information, see [Security and access control to metadata and data](https://docs.aws.amazon.com/lake-formation/latest/dg/security-data-access.html#security-data-access-permissions) in the *AWS Lake Formation Developer Guide*.
## IAM permissions to write to Amazon S3 locations
Lake Formation permissions to Amazon S3 do not include the ability to write to Amazon S3. Create Table As Statements (CTAS) require write access to the Amazon S3 location of tables. To run CTAS queries on data registered with Lake Formation, Athena users must have IAM permissions to write to the table Amazon S3 locations in addition to the appropriate Lake Formation permissions to read the data locations. For more information, see [Create a table from query results (CTAS)](ctas.md).
## Permissions to encrypted data, metadata, and Athena query results
Underlying source data in Amazon S3 and metadata in the catalog that is registered with Lake Formation can be encrypted. There is no change to the way that Athena handles encryption of query results when using Athena to query data registered with Lake Formation. For more information, see [Encrypt Athena query results stored in Amazon S3](encrypting-query-results-stored-in-s3.md).
+ **Encrypting source data** – Encryption of Amazon S3 data locations source data is supported. Athena users who query encrypted Amazon S3 locations that are registered with Lake Formation need permissions to encrypt and decrypt data. For more information about requirements, see [Supported Amazon S3 encryption options](encryption.md#encryption-options-S3-and-Athena) and [Permissions to encrypted data in Amazon S3](encryption.md#permissions-for-encrypting-and-decrypting-data).
+ **Encrypting metadata** – Encrypting metadata in the AWS Glue Data Catalog is supported. For principals using Athena, identity-based policies must allow the `"kms:GenerateDataKey"`, `"kms:Decrypt"`, and `"kms:Encrypt"` actions for the key used to encrypt metadata. For more information, see [Encrypting your Data Catalog](https://docs.aws.amazon.com/glue/latest/dg/encrypt-glue-data-catalog.html) in the *AWS Glue Developer Guide* and [Configure access from Athena to encrypted metadata in the AWS Glue Data Catalog](access-encrypted-data-glue-data-catalog.md).
## Resource-based permissions for Amazon S3 buckets in external accounts (optional)
To query an Amazon S3 data location in a different account, a resource-based IAM policy (bucket policy) must allow access to the location. For more information, see [Configure cross-account access in Athena to Amazon S3 buckets](cross-account-permissions.md).
For information about accessing catalogs in another account, see [Option A: Configure cross-account Data Catalog access in Athena](lf-athena-limitations-cross-account.md#lf-athena-limitations-cross-account-glue).
# Use Lake Formation and JDBC or ODBC drivers for federated access to Athena
The Athena JDBC and ODBC drivers support SAML 2.0-based federation with Athena using Okta and Microsoft Active Directory Federation Services (AD FS) identity providers. By integrating Amazon Athena with AWS Lake Formation, you enable SAML-based authentication to Athena with corporate credentials. With Lake Formation and AWS Identity and Access Management (IAM), you can maintain fine-grained, column-level access control over the data available to the SAML user. With the Athena JDBC and ODBC drivers, federated access is available for tool or programmatic access.
To use Athena to access a data source controlled by Lake Formation, you need to enable SAML 2.0-based federation by configuring your identity provider (IdP) and AWS Identity and Access Management (IAM) roles. For detailed steps, see [Tutorial: Configure federated access for Okta users to Athena using Lake Formation and JDBC](security-athena-lake-formation-jdbc-okta-tutorial.md).
## Prerequisites
To use Amazon Athena and Lake Formation for federated access, you must meet the following requirements:
+ You manage your corporate identities using an existing SAML-based identity provider, such as Okta or Microsoft Active Directory Federation Services (AD FS).
+ You use the AWS Glue Data Catalog as a metadata store.
+ You define and manage permissions in Lake Formation to access databases, tables, and columns in AWS Glue Data Catalog. For more information, see the [AWS Lake Formation Developer Guide](https://docs.aws.amazon.com/lake-formation/latest/dg/).
+ You use version 2.0.14 or later of the [Athena JDBC driver](https://docs.aws.amazon.com/athena/latest/ug/connect-with-jdbc.html) or version 1.1.3 or later of the [Athena ODBC driver](connect-with-odbc.md).
## Considerations and limitations
When using the Athena JDBC or ODBC driver and Lake Formation to configure federated access to Athena, keep in mind the following points:
+ Currently, the Athena JDBC driver and ODBC drivers support the Okta, Microsoft Active Directory Federation Services (AD FS), and Azure AD identity providers. Although the Athena JDBC driver has a generic SAML class that can be extended to use other identity providers, support for custom extensions that enable other identity providers (IdPs) for use with Athena may be limited.
+ Federated access using the JDBC and ODBC drivers is not compatible with the IAM Identity Center trusted identity propagation feature.
+ Currently, you cannot use the Athena console to configure support for IdP and SAML use with Athena. To configure this support, you use the third-party identity provider, the Lake Formation and IAM management consoles, and the JDBC or ODBC driver client.
+ You should understand the [SAML 2.0 specification](https://www.oasis-open.org/standards#samlv2.0) and how it works with your identity provider before you configure your identity provider and SAML for use with Lake Formation and Athena.
+ SAML providers and the Athena JDBC and ODBC drivers are provided by third parties, so support through AWS for issues related to their use may be limited.
**Topics**
+ [Prerequisites](#security-athena-lake-formation-jdbc-prerequisites)
+ [Considerations and limitations](#security-athena-lake-formation-jdbc-considerations-and-limitations)
+ [Tutorial: Configure federated access for Okta users to Athena using Lake Formation and JDBC](security-athena-lake-formation-jdbc-okta-tutorial.md)
# Tutorial: Configure federated access for Okta users to Athena using Lake Formation and JDBC
This tutorial shows you how to configure Okta, AWS Lake Formation, AWS Identity and Access Management permissions, and the Athena JDBC driver to enable SAML-based federated use of Athena. Lake Formation provides fine-grained access control over the data that is available in Athena to the SAML-based user. To set up this configuration, the tutorial uses the Okta developer console, the AWS IAM and Lake Formation consoles, and the SQL Workbench/J tool.
**Prerequisites**
This tutorial assumes that you have done the following:
+ Created an Amazon Web Services account. To create an account, visit the [Amazon Web Services home page](https://aws.amazon.com/).
+ [Set up a query results location](query-results-specify-location.md) for Athena in Amazon S3.
+ [Registered an Amazon S3 data bucket location](https://docs.aws.amazon.com/lake-formation/latest/dg/register-data-lake.html) with Lake Formation.
+ Defined a [database](https://docs.aws.amazon.com/glue/latest/dg/define-database.html) and [tables](https://docs.aws.amazon.com/glue/latest/dg/tables-described.html) on the [AWS Glue Data Catalog](https://docs.aws.amazon.com/glue/latest/dg/what-is-glue.html) that point to your data in Amazon S3.
+ If you have not yet defined a table, either [run a AWS Glue crawler](https://docs.aws.amazon.com/glue/latest/dg/add-crawler.html) or [use Athena to define a database and one or more tables](work-with-data.md) for the data that you want to access.
+ This tutorial uses a table based on the [NYC taxi trips dataset](https://registry.opendata.aws/nyc-tlc-trip-records-pds/) available in the [Registry of open data on AWS](https://registry.opendata.aws/). The tutorial uses the database name `tripdb` and the table name `nyctaxi`.
**Topics**
+ [Step 1: Create an Okta account](#security-athena-lake-formation-jdbc-okta-tutorial-step-1-create-an-okta-account)
+ [Step 2: Add users and groups to Okta](#security-athena-lake-formation-jdbc-okta-tutorial-step-2-set-up-an-okta-application-for-saml-authentication)
+ [Step 3: Set up an Okta application for SAML authentication](#security-athena-lake-formation-jdbc-okta-tutorial-step-3-set-up-an-okta-application-for-saml-authentication)
+ [Step 4: Create an AWS SAML Identity Provider and Lake Formation access IAM role](#security-athena-lake-formation-jdbc-okta-tutorial-step-4-create-an-aws-saml-identity-provider-and-lake-formation-access-IAM-role)
+ [Step 5: Add the IAM role and SAML Identity Provider to the Okta application](#security-athena-lake-formation-jdbc-okta-tutorial-step-5-update-the-okta-application-with-the-aws-role-and-saml-identity-provider)
+ [Step 6: Grant user and group permissions through AWS Lake Formation](#security-athena-lake-formation-jdbc-okta-tutorial-step-6-grant-permissions-through-aws-lake-formation)
+ [Step 7: Verify access through the Athena JDBC client](#security-athena-lake-formation-jdbc-okta-tutorial-step-7-verify-access-through-athena-jdbc-client)
+ [Conclusion](#security-athena-lake-formation-jdbc-okta-tutorial-conclusion)
+ [Related resources](#security-athena-lake-formation-jdbc-okta-tutorial-related-resources)
## Step 1: Create an Okta account
This tutorial uses Okta as a SAML-based identity provider. If you do not already have an Okta account, you can create a free one. An Okta account is required so that you can create an Okta application for SAML authentication.
**To create an Okta account**
1. To use Okta, navigate to the [Okta developer sign up page](https://developer.okta.com/signup/) and create a free Okta trial account. The Developer Edition Service is free of charge up to the limits specified by Okta at [developer.okta.com/pricing](https://developer.okta.com/pricing).
1. When you receive the activation email, activate your account.
An Okta domain name will be assigned to you. Save the domain name for reference. Later, you use the domain name (**) in the JDBC string that connects to Athena.
## Step 2: Add users and groups to Okta
In this step, you use the Okta console to perform the following tasks:
+ Create two Okta users.
+ Create two Okta groups.
+ Add one Okta user to each Okta group.
**To add users to Okta**
1. After you activate your Okta account, log in as administrative user to the assigned Okta domain.
1. In the left navigation pane, choose **Directory**, and then choose **People**.
1. Choose **Add Person** to add a new user who will access Athena through the JDBC driver.
![\[Choose Add Person.\]](http://docs.aws.amazon.com/athena/latest/ug/images/security-athena-lake-formation-jdbc-okta-tutorial-3.png)
1. In the **Add Person** dialog box, enter the required information.
+ Enter values for **First name** and **Last name**. This tutorial uses *athena-okta-user*.
+ Enter a **Username** and **Primary email**. This tutorial uses *athena-okta-user@anycompany.com*.
+ For **Password**, choose **Set by admin**, and then provide a password. This tutorial clears the option for **User must change password on first login**; your security requirements may vary.
![\[Adding a user to the Okta application.\]](http://docs.aws.amazon.com/athena/latest/ug/images/security-athena-lake-formation-jdbc-okta-tutorial-4.png)
1. Choose **Save and Add Another**.
1. Enter the information for another user. This example adds the business analyst user *athena-ba-user@anycompany.com*.
![\[Adding a user to the Okta application.\]](http://docs.aws.amazon.com/athena/latest/ug/images/security-athena-lake-formation-jdbc-okta-tutorial-4a.png)
1. Choose **Save**.
In the following procedure, you provide access for two Okta groups through the Athena JDBC driver by adding a "Business Analysts" group and a "Developer" group.
**To add Okta groups**
1. In the Okta navigation pane, choose **Directory**, and then choose **Groups**.
1. On the **Groups** page, choose **Add Group**.
![\[Choose Add Group.\]](http://docs.aws.amazon.com/athena/latest/ug/images/security-athena-lake-formation-jdbc-okta-tutorial-4c.png)
1. In the **Add Group** dialog box, enter the required information.
+ For **Name**, enter *lf-business-analyst*.
+ For **Group Description**, enter *Business Analysts*.
![\[Adding an Okta group.\]](http://docs.aws.amazon.com/athena/latest/ug/images/security-athena-lake-formation-jdbc-okta-tutorial-4d.png)
1. Choose **Add Group**.
1. On the **Groups** page, choose **Add Group** again. This time you will enter information for the Developer group.
1. Enter the required information.
+ For **Name**, enter *lf-developer*.
+ For **Group Description**, enter *Developers*.
1. Choose **Add Group**.
Now that you have two users and two groups, you are ready to add a user to each group.
**To add users to groups**
1. On the **Groups** page, choose the **lf-developer** group that you just created. You will add one of the Okta users that you created as a developer to this group.
![\[Choose lf-developer.\]](http://docs.aws.amazon.com/athena/latest/ug/images/security-athena-lake-formation-jdbc-okta-tutorial-4f.png)
1. Choose **Manage People**.
![\[Choose Manage People.\]](http://docs.aws.amazon.com/athena/latest/ug/images/security-athena-lake-formation-jdbc-okta-tutorial-4g.png)
1. From the **Not Members** list, choose **athena-okta-user**.
![\[Choose a user to add to the members list.\]](http://docs.aws.amazon.com/athena/latest/ug/images/security-athena-lake-formation-jdbc-okta-tutorial-4h.png)
The entry for the user moves from the **Not Members **list on the left to the **Members **list on the right.
![\[Okta user added to an Okta group.\]](http://docs.aws.amazon.com/athena/latest/ug/images/security-athena-lake-formation-jdbc-okta-tutorial-4i.png)
1. Choose **Save**.
1. Choose **Back to Group**, or choose **Directory**, and then choose **Groups**.
1. Choose the **lf-business-analyst** group.
1. Choose **Manage People**.
1. Add the **athena-ba-user** to the **Members** list of the **lf-business-analyst** group, and then choose **Save**.
1. Choose **Back to Group**, or choose **Directory**, **Groups**.
The **Groups** page now shows that each group has one Okta user.
![\[One user has been added to each Okta group in the Okta console.\]](http://docs.aws.amazon.com/athena/latest/ug/images/security-athena-lake-formation-jdbc-okta-tutorial-4j.png)
## Step 3: Set up an Okta application for SAML authentication
In this step, you use the Okta developer console to perform the following tasks:
+ Add a SAML application for use with AWS.
+ Assign the application to the Okta user.
+ Assign the application to an Okta group.
+ Download the resulting identity provider metadata for later use with AWS.
**To add an application for SAML authentication**
1. In the Okta navigation pane, choose **Applications**, **Applications** so that you can configure an Okta application for SAML authentication to Athena.
1. Click **Browse App Catalog**.
1. In the search box, enter **Redshift**.
1. Choose **Amazon Web Services Redshift**. The Okta application in this tutorial uses the existing SAML integration for Amazon Redshift.
![\[Choose Amazon Web Services Redshift.\]](http://docs.aws.amazon.com/athena/latest/ug/images/security-athena-lake-formation-jdbc-okta-tutorial-7.png)
1. On the **Amazon Web Services Redshift** page, choose **Add** to create a SAML-based application for Amazon Redshift.
![\[Choose Add to create a SAML-based application.\]](http://docs.aws.amazon.com/athena/latest/ug/images/security-athena-lake-formation-jdbc-okta-tutorial-8.png)
1. For **Application label**, enter `Athena-LakeFormation-Okta`, and then choose **Done**.
![\[Enter a name for the Okta application.\]](http://docs.aws.amazon.com/athena/latest/ug/images/security-athena-lake-formation-jdbc-okta-tutorial-9.png)
Now that you have created an Okta application, you can assign it to the users and groups that you created.
**To assign the application to users and groups**
1. On the **Applications** page, choose the **Athena-LakeFormation-Okta** application.
1. On the **Assignments** tab, choose **Assign**, **Assign to People**.
![\[Choose Assign, Assign to People.\]](http://docs.aws.amazon.com/athena/latest/ug/images/security-athena-lake-formation-jdbc-okta-tutorial-10.png)
1. In the **Assign Athena-LakeFormation-Okta to People** dialog box, find the **athena-okta-user** user that you created previously.
1. Choose **Assign** to assign the user to the application.
![\[Choose Assign.\]](http://docs.aws.amazon.com/athena/latest/ug/images/security-athena-lake-formation-jdbc-okta-tutorial-11.png)
1. Choose **Save and Go Back**.
1. Choose **Done**.
1. On the **Assignments** tab for the **Athena-LakeFormation-Okta** application, choose **Assign**, **Assign to Groups**.
1. For **lf-business-analyst**, choose **Assign** to assign the **Athena-LakeFormation-Okta** application to the **lf-business-analyst** group, and then choose **Done**.
![\[Assigning an Okta application to an Okta user group.\]](http://docs.aws.amazon.com/athena/latest/ug/images/security-athena-lake-formation-jdbc-okta-tutorial-12b.png)
The group appears in the list of groups for the application.
![\[The Okta application is assigned to the Okta group.\]](http://docs.aws.amazon.com/athena/latest/ug/images/security-athena-lake-formation-jdbc-okta-tutorial-12c.png)
Now you are ready to download the identity provider application metadata for use with AWS.
**To download the application metadata**
1. Choose the Okta application **Sign On** tab, and then right-click **Identity Provider metadata**.
![\[Right-click Identity Provider metadata.\]](http://docs.aws.amazon.com/athena/latest/ug/images/security-athena-lake-formation-jdbc-okta-tutorial-13.png)
1. Choose **Save Link As** to save the identity provider metadata, which is in XML format, to a file. Give it a name that you recognize (for example, `Athena-LakeFormation-idp-metadata.xml`).
![\[Saving the identity provider metadata.\]](http://docs.aws.amazon.com/athena/latest/ug/images/security-athena-lake-formation-jdbc-okta-tutorial-14.png)
## Step 4: Create an AWS SAML Identity Provider and Lake Formation access IAM role
In this step, you use the AWS Identity and Access Management (IAM) console to perform the following tasks:
+ Create an identity provider for AWS.
+ Create an IAM role for Lake Formation access.
+ Add the AmazonAthenaFullAccess managed policy to the role.
+ Add a policy for Lake Formation and AWS Glue to the role.
+ Add a policy for Athena query results to the role.
**To create an AWS SAML identity provider**
1. Sign in to the **Amazon Web Services account** **console** as **Amazon Web Services account administrator** and navigate to the **IAM** console ([https://console.aws.amazon.com/iam/](https://console.aws.amazon.com/iam/)).
1. In the navigation pane, choose **Identity providers**, and then click **Add provider**.
1. On the **Configure provider** screen, enter the following information:
+ For **Provider type**, choose **SAML**.
+ For **Provider name**, enter `AthenaLakeFormationOkta`.
+ For **Metadata document**, use the **Choose file** option to upload the identity provider (IdP) metadata XML file that you downloaded.
1. Choose **Add provider**.
Next, you create an IAM role for AWS Lake Formation access. You add two inline policies to the role. One policy provides permissions to access Lake Formation and the AWS Glue APIs. The other policy provides access to Athena and the Athena query results location in Amazon S3.
**To create an IAM role for AWS Lake Formation access**
1. In the IAM console navigation pane, choose **Roles**, and then choose **Create role**.
1. On the **Create role** page, perform the following steps:
![\[Configuring an IAM role to use SAML 2.0.\]](http://docs.aws.amazon.com/athena/latest/ug/images/security-athena-lake-formation-jdbc-okta-tutorial-20.png)
1. For **Select type of trusted entity**, choose **SAML 2.0 Federation.**
1. For **SAML provider**, select **AthenaLakeFormationOkta**.
1. For **SAML provider**, select the option **Allow programmatic and AWS Management Console access**.
1. Choose **Next: Permissions**.
1. On the **Attach Permissions policies** page, for **Filter policies**, enter **Athena**.
1. Select the **AmazonAthenaFullAccess** managed policy, and then choose **Next: Tags**.
![\[Attaching the AmazonAthenaFullAccess managed policy to the IAM role.\]](http://docs.aws.amazon.com/athena/latest/ug/images/security-athena-lake-formation-jdbc-okta-tutorial-21.png)
1. On the **Add tags** page, choose **Next: Review**.
1. On the **Review** page, for **Role name**, enter a name for the role (for example, *Athena-LakeFormation-OktaRole*), and then choose **Create role**.
![\[Enter a name for the IAM role.\]](http://docs.aws.amazon.com/athena/latest/ug/images/security-athena-lake-formation-jdbc-okta-tutorial-22.png)
Next, you add inline policies that allow access to Lake Formation, AWS Glue APIs, and Athena query results in Amazon S3.
Whenever you use IAM policies, make sure that you follow IAM best practices. For more information, see [Security best practices in IAM](https://docs.aws.amazon.com/IAM/latest/UserGuide/best-practices.html) in the *IAM User Guide*.
**To add an inline policy to the role for Lake Formation and AWS Glue**
1. From the list of roles in the IAM console, choose the newly created `Athena-LakeFormation-OktaRole`.
1. On the **Summary** page for the role, on the **Permissions** tab, choose **Add inline policy**.
1. On the **Create policy** page, choose **JSON**.
1. Add an inline policy like the following that provides access to Lake Formation and the AWS Glue APIs.
------
#### [ JSON ]
****
```
{
"Version":"2012-10-17",
"Statement": {
"Effect": "Allow",
"Action": [
"lakeformation:GetDataAccess",
"glue:GetTable",
"glue:GetTables",
"glue:GetDatabase",
"glue:GetDatabases",
"glue:CreateDatabase",
"glue:GetUserDefinedFunction",
"glue:GetUserDefinedFunctions"
],
"Resource": "*"
}
}
```
------
1. Choose **Review policy**.
1. For **Name**, enter a name for the policy (for example, **LakeFormationGlueInlinePolicy**).
1. Choose **Create policy**.
**To add an inline policy to the role for the Athena query results location**
1. On the **Summary** page for the `Athena-LakeFormation-OktaRole` role, on the **Permissions** tab, choose **Add inline policy**.
1. On the **Create policy** page, choose **JSON**.
1. Add an inline policy like the following that allows the role access to the Athena query results location. Replace the ** placeholders in the example with the name of your Amazon S3 bucket.
------
#### [ JSON ]
****
```
{
"Version":"2012-10-17",
"Statement": [
{
"Sid": "AthenaQueryResultsPermissionsForS3",
"Effect": "Allow",
"Action": [
"s3:ListBucket",
"s3:PutObject",
"s3:GetObject"
],
"Resource": [
"arn:aws:s3:::",
"arn:aws:s3:::/*"
]
}
]
}
```
------
1. Choose **Review policy**.
1. For **Name**, enter a name for the policy (for example, **AthenaQueryResultsInlinePolicy**).
1. Choose **Create policy**.
Next, you copy the ARN of the Lake Formation access role and the ARN of the SAML provider that you created. These are required when you configure the Okta SAML application in the next section of the tutorial.
**To copy the role ARN and SAML identity provider ARN**
1. In the IAM console, on the **Summary** page for the `Athena-LakeFormation-OktaRole` role, choose the **Copy to clipboard** icon next to **Role ARN**. The ARN has the following format:
```
arn:aws:iam:::role/Athena-LakeFormation-OktaRole
```
1. Save the full ARN securely for later reference.
1. In the IAM console navigation pane, choose **Identity providers**.
1. Choose the **AthenaLakeFormationOkta** provider.
1. On the **Summary** page, choose the **Copy to clipboard** icon next to **Provider ARN**. The ARN should look like the following:
```
arn:aws:iam:::saml-provider/AthenaLakeFormationOkta
```
1. Save the full ARN securely for later reference.
## Step 5: Add the IAM role and SAML Identity Provider to the Okta application
In this step, you return to the Okta developer console and perform the following tasks:
+ Add user and group Lake Formation URL attributes to the Okta application.
+ Add the ARN for the identity provider and the ARN for the IAM role to the Okta application.
+ Copy the Okta application ID. The Okta application ID is required in the JDBC profile that connects to Athena.
**To add user and group Lake Formation URL attributes to the Okta application**
1. Sign into the Okta developer console.
1. Choose the **Applications** tab, and then choose the `Athena-LakeFormation-Okta` application.
1. Choose on the **Sign On** tab for the application, and then choose **Edit**.
![\[Edit the Okta application.\]](http://docs.aws.amazon.com/athena/latest/ug/images/security-athena-lake-formation-jdbc-okta-tutorial-24.png)
1. Choose **Attributes (optional)** to expand it.
![\[Adding a user Lake Formation URL attribute to the Okta application.\]](http://docs.aws.amazon.com/athena/latest/ug/images/security-athena-lake-formation-jdbc-okta-tutorial-25.png)
1. For **Attribute Statements (optional)**, add the following attribute:
+ For **Name**, enter **https://lakeformation.amazon.com/SAML/Attributes/Username**.
+ For **Value**, enter **user.login**
1. Under **Group Attribute Statements (optional)**, add the following attribute:
+ For **Name**, enter **https://lakeformation.amazon.com/SAML/Attributes/Groups**.
+ For **Name format**, enter **Basic**
+ For **Filter**, choose **Matches regex**, and then enter **.\$1** in the filter box.
![\[Adding a group Lake Formation URL attribute to the Okta application.\]](http://docs.aws.amazon.com/athena/latest/ug/images/security-athena-lake-formation-jdbc-okta-tutorial-25a.png)
1. Scroll down to the **Advanced Sign-On Settings** section, where you will add the identity provider and IAM Role ARNs to the Okta application.
**To add the ARNs for the identity provider and IAM role to the Okta application**
1. For **Idp ARN and Role ARN**, enter the AWS identity provider ARN and role ARN as comma separated values in the format **,**. The combined string should look like the following:
```
arn:aws:iam:::saml-provider/AthenaLakeFormationOkta,arn:aws:iam:::role/Athena-LakeFormation-OktaRole
```
![\[Entering the identity provider ARN and IAM role ARN in the Okta application.\]](http://docs.aws.amazon.com/athena/latest/ug/images/security-athena-lake-formation-jdbc-okta-tutorial-26.png)
1. Choose **Save**.
Next, you copy the Okta application ID. You will require this later for the JDBC string that connects to Athena.
**To find and copy the Okta application ID**
1. Choose the **General** tab of the Okta application.
![\[Choose the General tab of the Okta application.\]](http://docs.aws.amazon.com/athena/latest/ug/images/security-athena-lake-formation-jdbc-okta-tutorial-27.png)
1. Scroll down to the **App Embed Link** section.
1. From **Embed Link**, copy and securely save the Okta application ID portion of the URL. The Okta application ID is the part of the URL after `amazon_aws_redshift/` but before the next forward slash. For example, if the URL contains `amazon_aws_redshift/aaa/bbb`, the application ID is `aaa`.
![\[Copy the ID of the Okta application.\]](http://docs.aws.amazon.com/athena/latest/ug/images/security-athena-lake-formation-jdbc-okta-tutorial-28.png)
**Note**
The embed link cannot be used to log directly into the Athena console to view databases. The Lake Formation permissions for SAML users and groups are recognized only when you use the JDBC or ODBC driver to submit queries to Athena. To view the databases, you can use the SQL Workbench/J tool, which uses the JDBC driver to connect to Athena. The SQL Workbench/J tool is covered in [Step 7: Verify access through the Athena JDBC client](#security-athena-lake-formation-jdbc-okta-tutorial-step-7-verify-access-through-athena-jdbc-client).
## Step 6: Grant user and group permissions through AWS Lake Formation
In this step, you use the Lake Formation console to grant permissions on a table to the SAML user and group. You perform the following tasks:
+ Specify the ARN of the Okta SAML user and associated user permissions on the table.
+ Specify the ARN of the Okta SAML group and associated group permissions on the table.
+ Verify the permissions that you granted.
**To grant permissions in Lake Formation for the Okta user**
1. Sign in as data lake administrator to the AWS Management Console.
1. Open the Lake Formation console at [https://console.aws.amazon.com/lakeformation/](https://console.aws.amazon.com/lakeformation/).
1. From the navigation pane, choose **Tables**, and then select the table that you want to grant permissions for. This tutorial uses the `nyctaxi` table from the `tripdb` database.
![\[Choose the table that you want to grant permissions for.\]](http://docs.aws.amazon.com/athena/latest/ug/images/security-athena-lake-formation-jdbc-okta-tutorial-29.png)
1. From **Actions**, choose **Grant**.
![\[Choose Grant.\]](http://docs.aws.amazon.com/athena/latest/ug/images/security-athena-lake-formation-jdbc-okta-tutorial-30.png)
1. In the **Grant permissions** dialog, enter the following information:
1. Under **SAML and Amazon Quick users and groups**, enter the Okta SAML user ARN in the following format:
```
arn:aws:iam:::saml-provider/AthenaLakeFormationOkta:user/@
```
1. For **Columns**, for **Choose filter type**, and optionally choose **Include columns** or **Exclude columns**.
1. Use the **Choose one or more columns** dropdown under the filter to specify the columns that you want to include or exclude for or from the user.
1. For **Table permissions**, choose **Select**. This tutorial grants only the `SELECT` permission; your requirements may vary.
![\[Granting table and column-level permissions to an Okta user.\]](http://docs.aws.amazon.com/athena/latest/ug/images/security-athena-lake-formation-jdbc-okta-tutorial-31.png)
1. Choose **Grant**.
Now you perform similar steps for the Okta group.
**To grant permissions in Lake Formation for the Okta group**
1. On the **Tables** page of the Lake Formation console, make sure that the **nyctaxi** table is still selected.
1. From **Actions**, choose **Grant**.
1. In the **Grant permissions** dialog, enter the following information:
1. Under **SAML and Amazon Quick users and groups**, enter the Okta SAML group ARN in the following format:
```
arn:aws:iam:::saml-provider/AthenaLakeFormationOkta:group/lf-business-analyst
```
1. For **Columns**, **Choose filter type**, choose **Include columns**.
1. For **Choose one or more columns**, choose the first three columns of the table.
1. For **Table permissions**, choose the specific access permissions to grant. This tutorial grants only the `SELECT` permission; your requirements may vary.
![\[Granting table permissions to an Okta group.\]](http://docs.aws.amazon.com/athena/latest/ug/images/security-athena-lake-formation-jdbc-okta-tutorial-31b.png)
1. Choose **Grant**.
1. To verify the permissions that you granted, choose **Actions**, **View permissions**.
![\[Choose View permissions to verify the permissions that were granted.\]](http://docs.aws.amazon.com/athena/latest/ug/images/security-athena-lake-formation-jdbc-okta-tutorial-32.png)
The **Data permissions** page for the `nyctaxi` table shows the permissions for **athena-okta-user** and the **lf-business-analyst** group.
![\[Viewing the permissions that were granted to the Okta user and group.\]](http://docs.aws.amazon.com/athena/latest/ug/images/security-athena-lake-formation-jdbc-okta-tutorial-33.png)
## Step 7: Verify access through the Athena JDBC client
Now you are ready to use a JDBC client to perform a test connection to Athena as the Okta SAML user.
In this section, you perform the following tasks:
+ Prepare the test client – Download the Athena JDBC driver, install SQL Workbench, and add the driver to Workbench. This tutorial uses SQL Workbench to access Athena through Okta authentication and to verify Lake Formation permissions.
+ In SQL Workbench:
+ Create a connection for the Athena Okta user.
+ Run test queries as the Athena Okta user.
+ Create and test a connection for the business analyst user.
+ In the Okta console, add the business analyst user to the developer group.
+ In the Lake Formation console, configure table permissions for the developer group.
+ In SQL Workbench, run test queries as the business analyst user and verify how the change in permissions affects the results.
**To prepare the test client**
1. Download and extract the Lake Formation compatible Athena JDBC driver (2.0.14 or later version) from [Connect to Amazon Athena with JDBC](connect-with-jdbc.md).
1. Download and install the free [SQL Workbench/J](https://www.sql-workbench.eu/index.html) SQL query tool, available under a modified Apache 2.0 license.
1. In SQL Workbench, choose **File**, and then choose **Manage Drivers**.
![\[Choose Manage Drivers.\]](http://docs.aws.amazon.com/athena/latest/ug/images/security-athena-lake-formation-jdbc-okta-tutorial-verify-access-1.png)
1. In the **Manage Drivers** dialog box, perform the following steps:
1. Choose the new driver icon.
1. For **Name**, enter **Athena**.
1. For **Library**, browse to and choose the Simba Athena JDBC `.jar` file that you just downloaded.
1. Choose **OK**.
![\[Adding the Athena JDBC driver to SQL Workbench.\]](http://docs.aws.amazon.com/athena/latest/ug/images/security-athena-lake-formation-jdbc-okta-tutorial-verify-access-2.png)
You are now ready to create and test a connection for the Athena Okta user.
**To create a connection for the Okta user**
1. Choose **File**, **Connect window**.
![\[Choose Connect window.\]](http://docs.aws.amazon.com/athena/latest/ug/images/security-athena-lake-formation-jdbc-okta-tutorial-verify-access-3.png)
1. In the **Connection profile** dialog box, create a connection by entering the following information:
+ In the name box, enter **Athena\$1Okta\$1User\$1Connection**.
+ For **Driver**, choose the Simba Athena JDBC Driver.
+ For **URL**, do one of the following:
+ To use a connection URL, enter a single-line connection string. The following example adds line breaks for readability.
```
jdbc:awsathena://AwsRegion=region-id;
S3OutputLocation=s3://amzn-s3-demo-bucket/athena_results;
AwsCredentialsProviderClass=com.simba.athena.iamsupport.plugin.OktaCredentialsProvider;
user=athena-okta-user@anycompany.com;
password=password;
idp_host=okta-idp-domain;
App_ID=okta-app-id;
SSL_Insecure=true;
LakeFormationEnabled=true;
```
+ To use an AWS profile-based URL, perform the following steps:
1. Configure an [AWS profile](https://docs.aws.amazon.com/cli/latest/userguide/cli-configure-profiles.html) that has an AWS credentials file like the following example.
```
[athena_lf_dev]
plugin_name=com.simba.athena.iamsupport.plugin.OktaCredentialsProvider
idp_host=okta-idp-domain
app_id=okta-app-id
uid=athena-okta-user@anycompany.com
pwd=password
```
1. For **URL**, enter a single-line connection string like the following example. The example adds line breaks for readability.
```
jdbc:awsathena://AwsRegion=region-id;
S3OutputLocation=s3://amzn-s3-demo-bucket/athena_results;
profile=athena_lf_dev;
SSL_Insecure=true;
LakeFormationEnabled=true;
```
Note that these examples are basic representations of the URL needed to connect to Athena. For the full list of parameters supported in the URL, refer to the [JDBC documentation](connect-with-jdbc.md).
The following image shows a SQL Workbench connection profile that uses a connection URL.
![\[A connection profile in SQL Workbench.\]](http://docs.aws.amazon.com/athena/latest/ug/images/security-athena-lake-formation-jdbc-okta-tutorial-verify-access-4.png)
Now that you have established a connection for the Okta user, you can test it by retrieving some data.
**To test the connection for the Okta user**
1. Choose **Test**, and then verify that the connection succeeds.
1. From the SQL Workbench **Statement** window, run the following SQL `DESCRIBE` command. Verify that all columns are displayed.
```
DESCRIBE "tripdb"."nyctaxi"
```
![\[All columns displayed.\]](http://docs.aws.amazon.com/athena/latest/ug/images/security-athena-lake-formation-jdbc-okta-tutorial-verify-access-5.png)
1. From the SQL Workbench **Statement** window, run the following SQL `SELECT` command. Verify that all columns are displayed.
```
SELECT * FROM tripdb.nyctaxi LIMIT 5
```
![\[Verify that all columns are displayed.\]](http://docs.aws.amazon.com/athena/latest/ug/images/security-athena-lake-formation-jdbc-okta-tutorial-verify-access-6.png)
Next, you verify that the **athena-ba-user**, as a member of the **lf-business-analyst** group, has access to only the first three columns of the table that you specified earlier in Lake Formation.
**To verify access for the **athena-ba-user****
1. In SQL Workbench, in the **Connection profile** dialog box, create another connection profile.
+ For the connection profile name, enter ** Athena\$1Okta\$1Group\$1Connection**.
+ For **Driver**, choose the Simba Athena JDBC driver.
+ For **URL**, do one of the following:
+ To use a connection URL, enter a single-line connection string. The following example adds line breaks for readability.
```
jdbc:awsathena://AwsRegion=region-id;
S3OutputLocation=s3://amzn-s3-demo-bucket/athena_results;
AwsCredentialsProviderClass=com.simba.athena.iamsupport.plugin.OktaCredentialsProvider;
user=athena-ba-user@anycompany.com;
password=password;
idp_host=okta-idp-domain;
App_ID=okta-application-id;
SSL_Insecure=true;
LakeFormationEnabled=true;
```
+ To use an AWS profile-based URL, perform the following steps:
1. Configure an AWS profile that has a credentials file like the following example.
```
[athena_lf_ba]
plugin_name=com.simba.athena.iamsupport.plugin.OktaCredentialsProvider
idp_host=okta-idp-domain
app_id=okta-application-id
uid=athena-ba-user@anycompany.com
pwd=password
```
1. For **URL**, enter a single-line connection string like the following. The example adds line breaks for readability.
```
jdbc:awsathena://AwsRegion=region-id;
S3OutputLocation=s3://amzn-s3-demo-bucket/athena_results;
profile=athena_lf_ba;
SSL_Insecure=true;
LakeFormationEnabled=true;
```
1. Choose **Test** to confirm that the connection is successful.
1. From the **SQL Statement** window, run the same `DESCRIBE` and `SELECT` SQL commands that you did before and examine the results.
Because **athena-ba-user** is a member of the **lf-business-analyst** group, only the first three columns that you specified in the Lake Formation console are returned.
![\[Only the first three columns are returned.\]](http://docs.aws.amazon.com/athena/latest/ug/images/security-athena-lake-formation-jdbc-okta-tutorial-verify-access-7.png)
![\[Data from the first three columns.\]](http://docs.aws.amazon.com/athena/latest/ug/images/security-athena-lake-formation-jdbc-okta-tutorial-verify-access-8.png)
Next, you return to the Okta console to add the `athena-ba-user` to the `lf-developer` Okta group.
**To add the athena-ba-user to the lf-developer group**
1. Sign in to the Okta console as an administrative user of the assigned Okta domain.
1. Choose **Directory**, and then choose **Groups**.
1. On the Groups page, choose the **lf-developer** group.
![\[Choose the lf-developer group.\]](http://docs.aws.amazon.com/athena/latest/ug/images/security-athena-lake-formation-jdbc-okta-tutorial-verify-access-9.png)
1. Choose **Manage People**.
1. From the **Not Members** list, choose the **athena-ba-user** to add it to the **lf-developer group**.
1. Choose **Save**.
Now you return to the Lake Formation console to configure table permissions for the **lf-developer** group.
**To configure table permissions for the lf-developer-group**
1. Log into the Lake Formation console as Data Lake administrator.
1. In the navigation pane, choose **Tables**.
1. Select the **nyctaxi** table.
1. Choose **Actions**, **Grant**.
1. In the **Grant Permissions** dialog, enter the following information:
+ For **SAML and Amazon Quick users and groups**, enter the Okta SAML lf-developer group ARN in the following format:
+ For **Columns**, **Choose filter type**, choose **Include columns**.
+ Choose the **trip\$1type** column.
+ For **Table permissions**, choose **SELECT**.
1. Choose **Grant**.
Now you can use SQL Workbench to verify the change in permissions for the **lf-developer** group. The change should be reflected in the data available to **athena-ba-user**, who is now a member of the **lf-developer** group.
**To verify the change in permissions for athena-ba-user**
1. Close the SQL Workbench program, and then re-open it.
1. Connect to the profile for **athena-ba-user**.
1. From the **Statement** window, issue the same SQL statements that you ran previously:
This time, the **trip\$1type** column is displayed.
![\[The fourth column is available for query.\]](http://docs.aws.amazon.com/athena/latest/ug/images/security-athena-lake-formation-jdbc-okta-tutorial-verify-access-10.png)
Because **athena-ba-user** is now a member of both the **lf-developer** and **lf-business-analyst** groups, the combination of Lake Formation permissions for those groups determines the columns that are returned.
![\[The fourth column in the data results.\]](http://docs.aws.amazon.com/athena/latest/ug/images/security-athena-lake-formation-jdbc-okta-tutorial-verify-access-11.png)
## Conclusion
In this tutorial you configured Athena integration with AWS Lake Formation using Okta as the SAML provider. You used Lake Formation and IAM to control the resources that are available to the SAML user in your data lake AWS Glue Data Catalog.
## Related resources
For related information, see the following resources.
+ [Connect to Amazon Athena with JDBC](connect-with-jdbc.md)
+ [Enable federated access to the Athena API](access-federation-saml.md)
+ [AWS Lake Formation Developer Guide](https://docs.aws.amazon.com/lake-formation/latest/dg/)
+ [Granting and revoking Data Catalog permissions](https://docs.aws.amazon.com/lake-formation/latest/dg/granting-catalog-permissions.html) in the *AWS Lake Formation Developer Guide*.
+ [Identity providers and federation](https://docs.aws.amazon.com/IAM/latest/UserGuide/id_roles_providers.html) in the *IAM User Guide*.
+ [Creating IAM SAML identity providers](https://docs.aws.amazon.com/IAM/latest/UserGuide/id_roles_providers_create_saml.html) in the *IAM User Guide*.
+ [Enabling federation to AWS using Windows Active Directory, ADFS, and SAML 2.0](https://aws.amazon.com/blogs/security/enabling-federation-to-aws-using-windows-active-directory-adfs-and-saml-2-0/) on the *AWS Security Blog*.