Sign In to the Console
Amazon Athena
User Guide

AWS Documentation » Amazon Athena » User Guide » Security » Access Control Policies » Fine-Grained Access to Databases and Tables in the AWS Glue Data Catalog

Fine-Grained Access to Databases and Tables in the AWS Glue Data Catalog

After you upgrade to the AWS Glue Data Catalog, you can define resource-level policies for the following Data Catalog objects that are used in Athena:

  • Databases

  • Tables

These policies enable you to define fine-grained access to databases and tables. You define resource-level permissions in identity-based (IAM) policies in the IAM Console.

Important

This section discusses identity-based (IAM) policies that allow you to define fine-grained access to specific resources. These are not the same as resource-based policies. For more information about the differences, see Identity-Based Policies and Resource-Based Policies in the AWS Identity and Access Management User Guide.

See the following topics for these tasks:

To perform this task See the following topic
Create an IAM policy that defines fine-grained access to resources Creating IAM Policies in the AWS Identity and Access Management User Guide.
Learn about identity-based (IAM) policies used in AWS Glue Identity-Based Policies (IAM Policies) in the AWS Glue Developer Guide.

In this section

Limitations

Consider the following limitations when using fine-grained access control with the AWS Glue Data Catalog and Athena:

  • You must upgrade from a data catalog managed in Athena to the AWS Glue Data Catalog.

  • You can limit access only to databases and tables. Fine-grained access controls apply at the table level and you cannot limit access to individual partitions within a table. For more information, see Table Partitions and Versions in AWS Glue.

  • Athena does not support cross-account access to the AWS Glue Data Catalog.

  • The AWS Glue Data Catalog contains the following resources: CATALOG, DATABASE, TABLE, and FUNCTION.

    Note

    From this list, resources that are common between Athena and the AWS Glue Data Catalog are TABLE, DATABASE, and CATALOG, for each account. Functions are specific to AWS Glue, however, for delete actions in Athena, you must include permissions to them, if you delete a database. See Fine-Grained Policy Examples.

    The hierarchy is as follows: CATALOG is an ancestor of all DATABASES in each account, and each DATABASE is an ancestor for all of its TABLES and FUNCTIONS. For example, for a table named table_test that belongs to a database db in the catalog in your account, its ancestors are db and the catalog in your account. For the db database, its ancestor is the catalog in your account, and its descendants are tables and functions. For more information about the hierarchical structure of resources, see List of ARNs in Data Catalog in the AWS Glue Developer Guide.

  • For any non-delete Athena action on a resource, such as CREATE DATABASE, CREATE TABLE, SHOW DATABASE, SHOW TABLE, or ALTER TABLE, you need permissions to call this action on this resource (table or database) and all ancestors of this resource in the Data Catalog. For example, for a table, its ancestors are database and catalog for the account. For a database, its ancestor is the catalog for this account. See Fine-Grained Policy Examples.

  • For a delete action in Athena, such as DROP DATABASE or DROP TABLE, you additionally need permissions to call this delete action on all descendants of this resource in the Data Catalog. For example, to delete a database you need permissions on the database, on its ancestor, which is the catalog, and on all descendants, which are the tables and the user defined functions. A table does not have descendants, and therefore, to run DROP TABLE, you need permissions to this action on the table and its ancestors. See Fine-Grained Policy Examples.

  • When limiting access to a specific database in the Data Catalog, you must also specify the access policy to the default database and catalog for each AWS Region for GetDatabase and CreateDatabase actions. If you use Athena in more than one Region, add a separate line to the policy for the resource ARN for each default database and catalog in each Region.

    For example, to allow GetDatabase access to example_db in the us-east-1 (N.Virginia) Region, also include the default database and catalog in the policy for that Region for two actions: GetDatabase and CreateDatabase: 

    {
   "Effect": "Allow",
   "Action": [
      "glue:GetDatabase", 
      "glue:CreateDatabase"
   ],
   "Resource": [
     "arn:aws:glue:us-east-1:123456789012:catalog",
     "arn:aws:glue:us-east-1:123456789012:database/default",
     "arn:aws:glue:us-east-1:123456789012:database/example_db"
   ]
}

Mandatory: Access Policy to the Default Database and Catalog per AWS Region

The following access policy to the default database and to the AWS Glue Data Catalog per AWS Region for GetDatabase and CreateDatabase must be present for Athena to work with the AWS Glue Data Catalog: 

{
   "Effect": "Allow",
   "Action": [
      "glue:GetDatabase", 
      "glue:CreateDatabase"
   ],
   "Resource": [
     "arn:aws:glue:us-east-1:123456789012:catalog",
     "arn:aws:glue:us-east-1:123456789012:database/default"
   ]
}

Table Partitions and Versions in AWS Glue

In AWS Glue, tables can have partitions and versions. Table versions and partitions are not considered to be independent resources in AWS Glue. Access to table versions and partitions is given by granting access on the table and ancestor resources for the table.

For the purposes of fine-grained access control, this means that:

  • Fine-grained access controls apply at the table level. You can limit access only to databases and tables. For example, if you allow access to a partitioned table, this access applies to all partitions in the table. You cannot limit access to individual partitions within a table.

    Important

    Having access to all partitions within a table is not sufficient if you need to run actions in AWS Glue on partitions. To run actions on partitions, you need permissions for those actions. For example, to run GetPartitions on table myTable in the database myDB, you need permissions for the action glue:GetPartitions in the Data Catalog, the myDB database, and myTable.

  • Fine-grained access controls do not apply to table versions. As with partitions, access to previous versions of a table is granted through access to the table version APIs in AWS Glue on the table, and to the table ancestors.

For information about permissions on AWS Glue actions, see AWS Glue API Permissions: Actions and Resources Reference in the AWS Glue Developer Guide.

Examples of Fine-Grained Permissions to Tables and Databases

The following table lists examples of identity-based (IAM) policies that allow fine-grained access to databases and tables in Athena.

As with any IAM policy, you define these policies in the IAM Console. We recommend that you start with these examples and, depending on your needs, adjust them to allow or deny specific actions to particular databases and tables.

These examples include the access policy to the default database and catalog, for GetDatabase and CreateDatabase actions. This policy is required for Athena and the AWS Glue Data Catalog to work together. For multiple AWS Regions, include this policy for each of the default databases and their catalogs, one line for each Region.

In addition, replace the example_db database and test table names with the names for your databases and tables.

DDL Statement Example of an IAM access policy granting access to the resource
CREATE DATABASE Allows you to create the database named example_db.
{
   "Effect": "Allow",
   "Action": [
      "glue:GetDatabase", 
      "glue:CreateDatabase"
   ],
   "Resource": [
     "arn:aws:glue:us-east-1:123456789012:catalog",
     "arn:aws:glue:us-east-1:123456789012:database/default",
     "arn:aws:glue:us-east-1:123456789012:database/example_db"
   ]
 }
ALTER DATABASE Allows you to modify the properties for the example_db database.
{
   "Effect": "Allow",
   "Action": [
      "glue:GetDatabase", 
      "glue:CreateDatabase"
   ],
   "Resource": [
     "arn:aws:glue:us-east-1:123456789012:catalog",
     "arn:aws:glue:us-east-1:123456789012:database/default"
   ]
},
{
   "Effect": "Allow",
   "Action": [
      "glue:GetDatabase", 
      "glue:UpdateDatabase"
   ],
   "Resource": [
     "arn:aws:glue:us-east-1:123456789012:catalog",
     "arn:aws:glue:us-east-1:123456789012:database/example_db"
   ]
 }
DROP DATABASE Allows you to drop the example_db database, including all tables in it.
{
   "Effect": "Allow",
   "Action": [
      "glue:GetDatabase", 
      "glue:CreateDatabase"
   ],
   "Resource": [
    "arn:aws:glue:us-east-1:123456789012:catalog",
     "arn:aws:glue:us-east-1:123456789012:database/default"
   ]
},
{
   "Effect": "Allow",
   "Action": [
      "glue:GetDatabase",
      "glue:DeleteDatabase",
      "glue:GetTables", 
      "glue:GetTable", 
      "glue:DeleteTable" 
   ],
   "Resource": [
    "arn:aws:glue:us-east-1:123456789012:catalog",
     "arn:aws:glue:us-east-1:123456789012:database/example_db", 
     "arn:aws:glue:us-east-1:123456789012:table/example_db/*", 
     "arn:aws:glue:us-east-1:123456789012:userDefinedFunction/example_db/*"
   ]
 }
SHOW DATABASES Allows you to list all databases in the AWS Glue Data Catalog.
{
   "Effect": "Allow",
   "Action": [
      "glue:GetDatabase", 
       "glue:CreateDatabase"

   ],
   "Resource": [
     "arn:aws:glue:us-east-1:123456789012:catalog",
     "arn:aws:glue:us-east-1:123456789012:database/default"
   ]
}, 
{
   "Effect": "Allow",
   "Action": [
      "glue:GetDatabases"
   ],
   "Resource": [
     "arn:aws:glue:us-east-1:123456789012:catalog",
     "arn:aws:glue:us-east-1:123456789012:database/*"
   ]
 }
CREATE TABLE Allows you to create a table named test in the example_db database.
{
   "Effect": "Allow",
   "Action": [
      "glue:GetDatabase", 
      "glue:CreateDatabase"
   ],
   "Resource": [
     "arn:aws:glue:us-east-1:123456789012:catalog",
     "arn:aws:glue:us-east-1:123456789012:database/default"
   ]
},
 {
   "Effect": "Allow",
   "Action": [
      "glue:GetDatabase",
      "glue:GetTable",
      "glue:CreateTable"     
   ],
   "Resource": [
     "arn:aws:glue:us-east-1:123456789012:catalog",
     "arn:aws:glue:us-east-1:123456789012:database/example_db",
     "arn:aws:glue:us-east-1:123456789012:table/example_db/test"
   ]
 }
SHOW TABLES Allows you to list all tables in the example_db database.
{
   "Effect": "Allow",
   "Action": [
      "glue:GetDatabase", 
      "glue:CreateDatabase"
   ],
   "Resource": [
     "arn:aws:glue:us-east-1:123456789012:catalog",
     "arn:aws:glue:us-east-1:123456789012:database/default"
   ]
},
 {
   "Effect": "Allow",
   "Action": [
      "glue:GetDatabase",
      "glue:GetTables"    
   ],
   "Resource": [
     "arn:aws:glue:us-east-1:123456789012:catalog",
     "arn:aws:glue:us-east-1:123456789012:database/example_db",  
     "arn:aws:glue:us-east-1:123456789012:table/example_db/*"
   ]
 }
DROP TABLE Allows you to drop a partitioned table named test in the example_db database. If your table does not have partitions, do not include partition actions.
{
   "Effect": "Allow",
   "Action": [
      "glue:GetDatabase", 
      "glue:CreateDatabase"
   ],
   "Resource": [
     "arn:aws:glue:us-east-1:123456789012:catalog",
     "arn:aws:glue:us-east-1:123456789012:database/default"
   ]
},
 {
   "Effect": "Allow",
   "Action": [
      "glue:GetDatabase",
      "glue:GetTable",
      "glue:DeleteTable", 
      "glue:GetPartitions",
      "glue:GetPartition",
      "glue:DeletePartition" 
   ],
   "Resource": [
     "arn:aws:glue:us-east-1:123456789012:catalog",
     "arn:aws:glue:us-east-1:123456789012:database/example_db",
     "arn:aws:glue:us-east-1:123456789012:table/example_db/test"
   ]
 }