Post Syndicated from Jan Michael Go Tan original https://aws.amazon.com/blogs/big-data/build-a-data-sharing-workflow-with-aws-lake-formation-for-your-data-mesh/

A key benefit of a data mesh architecture is allowing different lines of business (LOBs) and organizational units to operate independently and offer their data as a product. This model not only allows organizations to scale, but also gives the end-to-end ownership of maintaining the product to data producers that are the domain experts of the data. This ownership entails maintaining the data pipelines, debugging ETL scripts, fixing data quality issues, and keeping the catalog entries up to date as the dataset evolves over time.

On the consumer side, teams can search the central catalog for relevant data products and request access. Access to the data is done via the data sharing feature in AWS Lake Formation. As the amount of data products grow and potentially more sensitive information is stored in an organization’s data lake, it’s important that the process and mechanism to request and grant access to specific data products are done in a scalable and secure manner.

This post describes how to build a workflow engine that automates the data sharing process while including a separate approval mechanism for data products that are tagged as sensitive (for example, containing PII data). Both the workflow and approval mechanism are customizable and should be adapted to adhere to your company’s internal processes. In addition, we include an optional workflow UI to demonstrate how to integrate with the workflow engine. The UI is just one example of how the interaction works. In a typical large enterprise, you can also use ticketing systems to automatically trigger both the workflow and the approval process.

Solution overview

A typical data mesh architecture for analytics in AWS contains one central account that collates all the different data products from multiple producer accounts. Consumers can search the available data products in a single location. Sharing data products to consumers doesn’t actually make a separate copy, but instead just creates a pointer to the catalog item. This means any updates that producers make to their products are automatically reflected in the central account as well as in all the consumer accounts.

Building on top of this foundation, the solution contains several components, as depicted in the following diagram:

The central account includes the following components:

• AWS Glue – Used for Data Catalog purposes.
• AWS Lake Formation – Used to secure access to the data as well as provide the data sharing capabilities that enable the data mesh architecture.
• AWS Step Functions – The actual workflow is defined as a state machine. You can customize this to adhere to your organization’s approval requirements.
• AWS Amplify – The workflow UI uses the Amplify framework to secure access. It also uses Amplify to host the React-based application. On the backend, the Amplify framework creates two Amazon Cognito components to support the security requirements:
  • User pools – Provide a user directory functionality.
  • Identity pools – Provide federated sign-in capabilities using Amazon Cognito user pools as the location of the user details. The identity pools vend temporary credentials so the workflow UI can access AWS Glue and Step Functions APIs.
• AWS Lambda – Contains the application logic orchestrated by the Step Functions state machine. It also provides the necessary application logic when a producer approves or denies a request for access.
• Amazon API Gateway – Provides the API for producers to accept and deny requests.

The producer account contains the following components:

• Amazon Simple Notification Service (Amazon SNS) – Producers maintain their list of approvers in an SNS topic. The workflow engine publishes a message in the respective data owner’s SNS topic when an approval is required.
• AWS Identity and Access Management (IAM) – The ProducerWorkflowRole role is assumed by the workflow engine if it needs to send an approval request to the producer’s SNS topic.

The consumer account contains the following components:

• AWS Glue – Used for Data Catalog purposes.
• AWS Lake Formation – After the data has been made available, consumers can grant access to its own users via Lake Formation.
• AWS Resource Access Manager (AWS RAM) – If the grantee account is in the same organization as the grantor account, the shared resource is available immediately to the grantee. If the grantee account is not in the same organization, AWS RAM sends an invitation to the grantee account to accept or reject the resource grant. For more details about Lake Formation cross-account access, see Cross-Account Access: How It Works.

The solution is split into multiple steps:

1. Deploy the central account backend, including the workflow engine and its associated components.
2. Deploy the backend for the producer accounts. You can repeat this step multiple times depending on the number of producer accounts that you’re onboarding into the workflow engine.
3. Deploy the optional workflow UI in the central account to interact with the central account backend.

Workflow overview

The following diagram illustrates the workflow. In this particular example, the state machine checks if the table or database (depending on what is being shared) has the pii_flag parameter and if it’s set to TRUE. If both conditions are valid, it sends an approval request to the producer’s SNS topic. Otherwise, it automatically shares the product to the requesting consumer.

This workflow is the core of the solution, and can be customized to fit your organization’s approval process. In addition, you can add custom parameters to databases, tables, or even columns to attach extra metadata to support the workflow logic.

Prerequisites

The following are the deployment requirements:

• A data mesh set up
• NodeJS v14.x
• Yarn v1.22.10+
• The AWS Cloud Development Kit (AWS CDK) CLI v1.119.0+
• The Amplify CLI v5.3.0+ (for the workflow UI)
• AWS profiles for each of the accounts (central and producers) that you’re deploying in

You can clone the workflow UI and AWS CDK scripts from the GitHub repository.

Deploy the central account backend

To deploy the backend for the central account, go to the root of the project after cloning the GitHub repository and enter the following code:

yarn deploy-central --profile <PROFILE_OF_CENTRAL_ACCOUNT>

This deploys the following:

• IAM roles used by the Lambda functions and Step Functions state machine
• Lambda functions
• The Step Functions state machine (the workflow itself)
• An API Gateway

When the deployment is complete, it generates a JSON file in the src/cfn-output.json location. This file is used by the UI deployment script to generate a scoped-down IAM policy and workflow UI application to locate the state machine that was created by the AWS CDK script.

The actual AWS CDK scripts for the central account deployment are in infra/central/. This also includes the Lambda functions (in the infra/central/functions/ folder) that are used by both the state machine and the API Gateway.

Lake Formation permissions

The following table contains the minimum required permissions that the central account data lake administrator needs to grant to the respective IAM roles for the backend to have access to the AWS Glue Data Catalog.

Workflow catalog parameters

The workflow uses the following catalog parameters to provide its functionality.

You can use UpdateDatabase and UpdateTable to add parameters to database and column-level granularity, respectively. Alternatively, you can use the CLI for AWS Glue to add the relevant parameters.

Use the AWS CLI to run the following command to check the current parameters in your database:

aws glue get-database --name <DATABASE_NAME> --profile <PROFILE_OF_CENTRAL_ACCOUNT>

You get the following response:

{
  "Database": {
    "Name": "<DATABASE_NAME>",
    "CreateTime": "<CREATION_TIME>",
    "CreateTableDefaultPermissions": [],
    "CatalogId": "<CATALOG_ID>"
  }
}

To update the database with the parameters indicated in the preceding table, we first create the input JSON file, which contains the parameters that we want to update the database with. For example, see the following code:

{
  "Name": "<DATABASE_NAME>",
  "Parameters": {
    "data_owner": "<AWS_ACCOUNT_ID_OF_OWNER>",
    "data_owner_name": "<AWS_ACCOUNT_NAME_OF_OWNER>",
    "pii_flag": "true"
  }
}

Run the following command to update the Data Catalog:

aws glue update-database --name <DATABASE_NAME> --database-input file://<FILE_NAME>.json --profile <PROFILE_OF_CENTRAL_ACCOUNT>

Deploy the producer account backend

To deploy the backend for your producer accounts, go to the root of the project and run the following command:

yarn deploy-producer --profile <PROFILE_OF_PRODUCER_ACCOUNT> --parameters centralMeshAccountId=<central_account_account_id>

This deploys the following:

• An SNS topic where approval requests get published.
• The ProducerWorkflowRole IAM role with a trust relationship to the central account. This role allows Amazon SNS publish to the previously created SNS topic.

You can run this deployment script multiple times, each time pointing to a different producer account that you want to participate in the workflow.

To receive notification emails, subscribe your email in the SNS topic that the deployment script created. For example, our topic is called DataLakeSharingApproval. To get the full ARN, you can either go to the Amazon Simple Notification Service console or run the following command to list all the topics and get the ARN for DataLakeSharingApproval:

aws sns list-topics --profile <PROFILE_OF_PRODUCER_ACCOUNT>

After you have the ARN, you can subscribe your email by running the following command:

aws sns subscribe --topic-arn <TOPIC_ARN> --protocol email --notification-endpoint <EMAIL_ADDRESS> --profile <PROFILE_OF_PRODUCER_ACCOUNT>

You then receive a confirmation email via the email address that you subscribed. Choose Confirm subscription to receive notifications from this SNS topic.

Deploy the workflow UI

The workflow UI is designed to be deployed in the central account where the central data catalog is located.

To start the deployment, enter the following command:

yarn deploy-ui

This deploys the following:

• Amazon Cognito user pool and identity pool
• React-based application to interact with the catalog and request data access

The deployment command prompts you for the following information:

• Project information – Use the default values.
• AWS authentication – Use your profile for the central account. Amplify uses this profile to deploy the backend resources.

UI authentication – Use the default configuration and your username. Choose No, I am done when asked to configure advanced settings.

• UI hosting – Use hosting with the Amplify console and choose manual deployment.

The script gives a summary of what is deployed. Entering Y triggers the resources to be deployed in the backend. The prompt looks similar to the following screenshot:

When the deployment is complete, the remaining prompt is for the initial user information such as user name and email. A temporary password is automatically generated and sent to the email provided. The user is required to change the password after the first login.

The deployment script grants IAM permissions to the user via an inline policy attached to the Amazon Cognito authenticated IAM role:

{
   "Version":"2012-10-17",
   "Statement":[
      {
         "Effect":"Allow",
         "Action":[
            "glue:GetDatabase",
            "glue:GetTables",
            "glue:GetDatabases",
            "glue:GetTable"
         ],
         "Resource":"*"
      },
      {
         "Effect":"Allow",
         "Action":[
            "states:ListExecutions",
            "states:StartExecution"
         ],
         "Resource":[
"arn:aws:states:<REGION>:<AWS_ACCOUNT_ID>:stateMachine:<STATE_MACHINE_NAME>"
]
      },
      {
         "Effect":"Allow",
         "Action":[
             "states:DescribeExecution"
         ],
         "Resource":[
"arn:aws:states:<REGION>:<AWS_ACCOUNT_ID>:execution:<STATE_MACHINE_NAME>:*"
]
      }


   ]
}

The last remaining step is to grant Lake Formation permissions (DESCRIBE for both databases and tables) to the authenticated IAM role associated with the Amazon Cognito identity pool. You can find the IAM role by running the following command:

cat amplify/team-provider-info.json

The IAM role name is in the AuthRoleName property under the awscloudformation key. After you grant the required permissions, you can use the URL provided in your browser to open the workflow UI.

Your temporary password is emailed to you so you can complete the initial login, after which you’re asked to change your password.

The first page after logging in is the list of databases that consumers can access.

Choose Request Access to see the database details and the list of tables.

Choose Request Per Table Access and see more details at the table level.

Going back in the previous page, we request database-level access by entering the consumer account ID that receives the share request.

Because this database has been tagged with a pii_flag, the workflow needs to send an approval request to the product owner. To receive this approval request email, the product owner’s email needs to be subscribed to the DataLakeSharingApproval SNS topic in the product account. The details should look similar to the following screenshot:

The email looks similar to the following screenshot:

The product owner chooses the Approve link to trigger the Step Functions state machine to continue running and share the catalog item to the consumer account.

For this example, the consumer account is not part of an organization, so the admin of the consumer account has to go to AWS RAM and accept the invitation.

After the resource share is accepted, the shared database appears in the consumer account’s catalog.

Clean up

If you no longer need to use this solution, use the provided cleanup scripts to remove the deployed resources.

Producer account

To remove the deployed resources in producer accounts, run the following command for each producer account that you deployed in:

yarn clean-producer --profile <PROFILE_OF_PRODUCER_ACCOUNT>

Central account

Run the following command to remove the workflow backend in the central account:

yarn clean-central --profile <PROFILE_OF_CENTRAL_ACCOUNT>

Workflow UI

The cleanup script for the workflow UI relies on an Amplify CLI command to initiate the teardown of the deployed resources. Additionally, you can use a custom script to remove the inline policy in the authenticated IAM role used by Amazon Cognito so that Amplify can fully clean up all the deployed resources. Run the following command to trigger the cleanup:

yarn clean-ui

This command doesn’t require the profile parameter because it uses the existing Amplify configuration to infer where the resources are deployed and which profile was used.

Conclusion

This post demonstrated how to build a workflow engine to automate an organization’s approval process to gain access to data products with varying degrees of sensitivity. Using a workflow engine enables data sharing in a self-service manner while codifying your organization’s internal processes to be able to safely scale as more data products and teams get onboarded.

The provided workflow UI demonstrated one possible integration scenario. Other possible integration scenarios include integration with your organization’s ticketing system to trigger the workflow as well as receive and respond to approval requests, or integration with business chat applications to further shorten the approval cycle.

Lastly, a high degree of customization is possible with the demonstrated approach. Organizations have complete control over the workflow, how data product sensitivity levels are defined, what gets auto-approved and what needs further approvals, the hierarchy of approvals (such as a single approver or multiple approvers), and how the approvals get delivered and acted upon. You can take advantage of this flexibility to automate your company’s processes to help them safely accelerate towards being a data-driven organization.

About the Author

Jan Michael Go Tan is a Principal Solutions Architect for Amazon Web Services. He helps customers design scalable and innovative solutions with the AWS Cloud.

Post Syndicated from Nivas Shankar original https://aws.amazon.com/blogs/big-data/easily-manage-your-data-lake-at-scale-using-tag-based-access-control-in-aws-lake-formation/

Thousands of customers are building petabyte-scale data lakes on AWS. Many of these customers use AWS Lake Formation to easily build and share their data lakes across the organization. As the number of tables and users increase, data stewards and administrators are looking for ways to manage permissions on data lakes easily at scale. Customers are struggling with “role explosion” and need to manage hundreds or even thousands of user permissions to control data access. For example, for an account with 1,000 resources and 100 principals, the data steward would have to create and manage up to 100,000 policy statements. Furthermore, as new principals and resources get added or deleted, these policies have to be updated to keep the permissions current.

Lake Formation Tag-based access control solves this problem by allowing data stewards to create LF-tags (based on their data classification and ontology) that can then be attached to resources. You can create policies on a smaller number of logical tags instead of specifying policies on named resources. LF-tags enable you to categorize and explore data based on taxonomies, which reduces policy complexity and scales permissions management. You can create and manage policies with tens of logical tags instead of the thousands of resources. LF-tags access control decouples policy creation from resource creation, which helps data stewards manage permissions on a large number of databases, tables, and columns by removing the need to update policies every time a new resource is added to the data lake. Finally, LF-tags access allows you to create policies even before the resources come into existence. All you have to do is tag the resource with the right LF-tags to ensure it is managed by existing policies.

This post focuses on managing permissions on data lakes at scale using LF-tags in Lake Formation. When it comes to managing data lake catalog tables from AWS Glue and administering permission to Lake Formation, data stewards within the producing accounts have functional ownership based on the functions they support, and can grant access to various consumers, external organizations, and accounts. You can now define LF-tags; associate at the database, table, or column level; and then share controlled access across analytic, machine learning (ML), and extract, transform, and load (ETL) services for consumption. LF-tags ensures that governance can be scaled easily by replacing the policy definitions of thousands of resources with a small number of logical tags.

LF-tags access has three main components:

• Tag ontology and classification – Data stewards can define a LF-tag ontology based on data classification and grant access based on LF-tags to AWS Identity and Access Management (IAM) principals and SAML principals or groups
• Tagging resources – Data engineers can easily create, automate, implement, and track all LF-tags and permissions against AWS Glue catalogs through the Lake Formation API
• Policy evaluation – Lake Formation evaluates the effective permissions based on LF-tags at query time and allows access to data through consuming services such as Amazon Athena, Amazon Redshift Spectrum, Amazon SageMaker Data Wrangler, and Amazon EMR Studio, based on the effective permissions granted across multiple accounts or organization-level data shares

Solution overview

The following diagram illustrates the architecture of the solution described in this post.

In this post, we demonstrate how you can set up a Lake Formation table and create Lake Formation tag-based policies using a single account with multiple databases. We walk you through the following high-level steps:

1. The data steward defines the tag ontology with two LF-tags: Confidential and Sensitive. Data with “Confidential = True” has tighter access controls. Data with “Sensitive = True” requires specific analysis from the analyst.
2. The data steward assigns different permission levels to the data engineer to build tables with different LF-tags.
3. The data engineer builds two databases: tag_database and col_tag_database. All tables in tag_database are configured with “Confidential = True”. All tables in the col_tag_database are configured with “Confidential = False”. Some columns of the table in col_tag_database are tagged with “Sensitive = True” for specific analysis needs.
4. The data engineer grants read permission to the analyst for tables with specific expression condition “Confidential = True” and  “Confidential = False , Sensitive = True”.
5. With this configuration, the data analyst can focus on performing analysis with the right data.

Provision your resources

This post includes an AWS CloudFormation template for a quick setup. You can review and customize it to suit your needs. The template creates three different personas to perform this exercise and copies the nyc-taxi-data dataset to your local Amazon Simple Storage Service (Amazon S3) bucket.

To create these resources, complete the following steps:

1. Sign in to the AWS CloudFormation console in the us-east-1 Region.
2. Choose Launch Stack:
   
3. Choose Next.
4. In the User Configuration section, enter password for three personas: DataStewardUserPassword, DataEngineerUserPassword and DataAnalystUserPassword.
5. Review the details on the final page and select I acknowledge that AWS CloudFormation might create IAM resources.
6. Choose Create.

The stack takes up to 5 minutes and creates all the required resources, including:

• An S3 bucket
• The appropriate Lake Formation settings
• The appropriate Amazon Elastic Compute Cloud (Amazon EC2) resources
• Three user personas with user ID credentials:
  • Data steward (administrator) – The lf-data-steward user has the following access:
    • Read access to all resources in the Data Catalog
    • Can create LF-tags and associate to the data engineer role for grantable permission to other principals
  • Data engineer – The lf-data-engineer user has the following access:
    • Full read, write, and update access to all resources in the Data Catalog
    • Data location permissions in the data lake
    • Can associate LF-tags and associate to the Data Catalog
    • Can attach LF-tags to resources, which provides access to principals based on any policies created by data stewards
  • Data analyst – The lf-data-analyst user has the following access:
    • Fine-grained access to resources shared by Lake Formation Tag-based access policies

    

Register your data location and create an LF-tag ontology

We perform this first step as the data steward user (lf-data-steward) to verify the data in Amazon S3 and the Data Catalog in Lake Formation.

1. Sign in to the Lake Formation console as lf-data-steward with the password used while deploying the CloudFormation stack.
2. In the navigation pane, under Permissions¸ choose Administrative roles and tasks.
3. For IAM users and roles, choose the user lf-data-steward.
4. Choose Save to add lf-data-steward as a Lake Formation admin.
   
   Next, we will update the Data catalog settings to use Lake Formation permission to control catalog resources instead of IAM based access control.
5. In the navigation pane, under Data catalog¸ choose Settings.
6. Uncheck Use only IAM access control for new databases.
7. Uncheck Use only IAM access control for new tables in new databases.
8. Click Save.
   
   Next, we need to register the data location for the data lake.
9. In the navigation pane, under Register and ingest, choose Data lake locations.
10. For Amazon S3 path, enter s3://lf-tagbased-demo-<<Account-ID>>.
11. For IAM role¸ leave it as the default value AWSServiceRoleForLakeFormationDataAccess.
12. Choose Register location.
    Next, we create the ontology by defining a LF-tag.
13. Under Permissions in the navigation pane, under Administrative roles, choose LF-Tags.
14. Choose Add LF-tags.
    
15. For Key, enter Confidential.
16. For Values, add True and False.
17. Choose Add LF-tag.
    
18. Repeat the steps to create the LF-tag Sensitive with the value True.
    You have created all the necessary LF-tags for this exercise.Next, we give specific IAM principals the ability to attach newly created LF-tags to resources.
19. Under Permissions in the navigation pane, under Administrative roles, choose LF-tag permissions.
20. Choose Grant.
21. Select IAM users and roles.
22. For IAM users and roles, search for and choose the lf-data-engineer role.
23. In the LF-tag permission scope section, add the key Confidential with values True and False, and the key Sensitive with value True.
24. Under Permissions¸ select Describe and Associate for LF-tag permissions and Grantable permissions.
25. Choose Grant.
    
    Next, we grant permissions to lf-data-engineer to create databases in our catalog and on the underlying S3 bucket created by AWS CloudFormation.
26. Under Permissions in the navigation pane, choose Administrative roles.
27. In the Database creators section, choose Grant.
28. For IAM users and roles, choose the lf-data-engineer role.
29. For Catalog permissions, select Create database.
30. Choose Grant.
    
    Next, we grant permissions on the S3 bucket (s3://lf-tagbased-demo-<<Account-ID>>) to the lf-data-engineer user.
31. In the navigation pane, choose Data locations.
32. Choose Grant.
    
33. Select My account.
34. For IAM users and roles, choose the lf-data-engineer role.
35. For Storage locations, enter the S3 bucket created by the CloudFormation template (s3://lf-tagbased-demo-<<Account-ID>>).
36. Choose Grant.
    Next, we grant lf-data-engineer grantable permissions on resources associated with the LF-tag expression Confidential=True.
37. In the navigation pane, choose Data permissions.
38. Choose Grant.
39. Select IAM users and roles.
40. Choose the role lf-data-engineer.
41. In the LF-tag or catalog resources section, Select Resources matched by LF-Tags.
42. Choose Add LF-Tag.
43. Add the key Confidential with the values True.
    
44. In the Database permissions section, select Describe for Database permissions and Grantable permissions.
45. In the Table and column permissions section, select Describe, Select, and Alter for both Table permissions and Grantable permissions.
46. Choose Grant.
    Next, we grant lf-data-engineer grantable permissions on resources associated with the LF-tag expression Confidential=False.
47. In the navigation pane, choose Data permissions.
48. Choose Grant.
49. Select IAM users and roles.
50. Choose the role lf-data-engineer.
51. Select Resources matched by LF-tags.
52. Choose Add LF-tag.
53. Add the key Confidential with the values False.
    
54. In the Database permissions section, select Describe for Database permissions and Grantable permissions.
55. In the Table and column permissions section, do not select anything.
56. Choose Grant.
    Next, we grant lf-data-engineer grantable permissions on resources associated with the LF-tag expression Confidential=False and Sensitive=True.
57. In the navigation pane, choose Data permissions.
58. Choose Grant.
59. Select IAM users and roles.
60. Choose the role lf-data-engineer.
61. Select Resources matched by LF-tags.
62. Choose Add LF-tag.
63. Add the key Confidential with the values False.
64. Choose Add LF-tag.
65. Add the key Sensitive with the values True.
    
66. In the Database permissions section, select Describe for Database permissions and Grantable permissions.
67. In the Table and column permissions section, select Describe, Select, and Alter for both Table permissions and Grantable permissions.
68. Choose Grant.
    

Create the Lake Formation databases

Now, sign in as lf-data-engineer with the password used while deploying the CloudFormation stack. We create two databases and attach LF-tags to the databases and specific columns for testing purposes.

Create your database and table for database-level access

We first create the database tag_database, the table source_data, and attach appropriate LF-tags.

1. On the Lake Formation console, choose Databases.
2. Choose Create database.
3. For Name, enter tag_database.
4. For Location, enter the S3 location created by the CloudFormation template (s3://lf-tagbased-demo-<<Account-ID>>/tag_database/).
5. Deselect Use only IAM access control for new tables in this database.
6. Choose Create database.

Next, we create a new table within tag_database.

7. On the Databases page, select the database tag_database.
8. Choose View Tables and click Create table.
9. For Name, enter source_data.
10. For Database, choose the database tag_database.
    
11. For Data is located in, select Specified path in my account.
12. For Include path, enter the path to tag_database created by the CloudFormation template (s3://lf-tagbased-demo-<<Account-ID>>/tag_database/).
13. For Data format, select CSV.
    
14. Under Upload schema, enter the following schema JSON:

    [
                   {
                        "Name": "vendorid",
                        "Type": "string"
                        
                        
                   },
                   {
                        "Name": "lpep_pickup_datetime",
                        "Type": "string"
                        
                        
                   },
                   {
                        "Name": "lpep_dropoff_datetime",
                        "Type": "string"
                        
                        
                   },
                      {
                        "Name": "store_and_fwd_flag",
                        "Type": "string"
                        
                        
                   },
                      {
                        "Name": "ratecodeid",
                        "Type": "string"
                        
                        
                   },
                      {
                        "Name": "pulocationid",
                        "Type": "string"
                        
                        
                   },
                   {
                        "Name": "dolocationid",
                        "Type": "string"
                        
                        
                   },
                      {
                        "Name": "passenger_count",
                        "Type": "string"
                        
                        
                   },
                   {
                        "Name": "trip_distance",
                        "Type": "string"
                        
                        
                   }, 
                      {
                        "Name": "fare_amount",
                        "Type": "string"
                        
                        
                   },
                   {
                        "Name": "extra",
                        "Type": "string"
                        
                        
                   },
                      {
                        "Name": "mta_tax",
                        "Type": "string"
                        
                        
                   },
                   {
                        "Name": "tip_amount",
                        "Type": "string"
                        
                        
                   },
                      {
                        "Name": "tolls_amount",
                        "Type": "string"
                        
                        
                   },
                   {
                        "Name": "ehail_fee",
                        "Type": "string"
                        
                        
                   }, 
                   {
                        "Name": "improvement_surcharge",
                        "Type": "string"
                        
                        
                   },
                   {
                        "Name": "total_amount",
                        "Type": "string"
                        
                        
                   },
                   {
                        "Name": "payment_type",
                        "Type": "string"
                        
                        
                   }
     
    ]
    

15. Choose Upload.
    

After uploading the schema, the table schema should look like the following screenshot.

16. Choose Submit.

Now we’re ready to attach LF-tags at the database level.

22. On the Databases page, find and select tag_database.
23. On the Actions menu, choose Edit LF-tags.
24. Choose Assign new LF-tag.
25. For Assigned keys¸ choose the Confidential LF-tag you created earlier.
26. For Values, choose True.
27. Choose Save.
    

This completes the LF-tag assignment to the tag_database database.

Create your database and table for column-level access

Now we repeat these steps to create the database col_tag_database and table source_data_col_lvl, and attach LF-tags at the column level.

1. On the Databases page, choose Create database.
2. For Name, enter col_tag_database.
3. For Location, enter the S3 location created by the CloudFormation template (s3://lf-tagbased-demo-<<Account-ID>>/col_tag_database/).
4. Deselect Use only IAM access control for new tables in this database.
5. Choose Create database.
   
6. On the Databases page, select your new database (col_tag_database).
7. Choose View tables and Click Create table.
8. For Name, enter source_data_col_lvl.
9. For Database, choose your new database (col_tag_database).
   
10. For Data is located in, select Specified path in my account.
11. Enter the S3 path for col_tag_database (s3://lf-tagbased-demo-<<Account-ID>>/col_tag_database/).
12. For Data format, select CSV.
    
13. Under Upload schema, enter the following schema JSON:

    [
                   {
                        "Name": "vendorid",
                        "Type": "string"
                        
                        
                   },
                   {
                        "Name": "lpep_pickup_datetime",
                        "Type": "string"
                        
                        
                   },
                   {
                        "Name": "lpep_dropoff_datetime",
                        "Type": "string"
                        
                        
                   },
                      {
                        "Name": "store_and_fwd_flag",
                        "Type": "string"
                        
                        
                   },
                      {
                        "Name": "ratecodeid",
                        "Type": "string"
                        
                        
                   },
                      {
                        "Name": "pulocationid",
                        "Type": "string"
                        
                        
                   },
                   {
                        "Name": "dolocationid",
                        "Type": "string"
                        
                        
                   },
                      {
                        "Name": "passenger_count",
                        "Type": "string"
                        
                        
                   },
                   {
                        "Name": "trip_distance",
                        "Type": "string"
                        
                        
                   }, 
                      {
                        "Name": "fare_amount",
                        "Type": "string"
                        
                        
                   },
                   {
                        "Name": "extra",
                        "Type": "string"
                        
                        
                   },
                      {
                        "Name": "mta_tax",
                        "Type": "string"
                        
                        
                   },
                   {
                        "Name": "tip_amount",
                        "Type": "string"
                        
                        
                   },
                      {
                        "Name": "tolls_amount",
                        "Type": "string"
                        
                        
                   },
                   {
                        "Name": "ehail_fee",
                        "Type": "string"
                        
                        
                   }, 
                   {
                        "Name": "improvement_surcharge",
                        "Type": "string"
                        
                        
                   },
                   {
                        "Name": "total_amount",
                        "Type": "string"
                        
                        
                   },
                   {
                        "Name": "payment_type",
                        "Type": "string"
                        
                        
                   }
     
    ]
    

14. Choose Upload.
    

After uploading the schema, the table schema should look like the following screenshot.

15. Choose Submit to complete the creation of the table

Now you associate the  Sensitive=True LF-tag to the columns vendorid and fare_amount.

21. On the Tables page, select the table you created (source_data_col_lvl).
22. On the Actions menu, choose Edit Schema.
23. Select the column vendorid and choose Edit LF-tags.
24. For Assigned keys, choose Sensitive.
25. For Values, choose True.
26. Choose Save.
    

Repeat the steps for the Sensitive LF-tag update for fare_amount column.

27. Select the column fare_amount and choose Edit LF-tags.
28. Add the Sensitive key with value True.
29. Choose Save.
    
30. Choose Save as new version to save the new schema version with tagged columns.The following screenshot shows column properties with the LF-tags updated.
    Next we associate the Confidential=False LF-tag to col_tag_database. This is required for lf-data-analyst to be able to describe the database col_tag_database when logged in from Athena.
31. On the Databases page, find and select col_tag_database.
32. On the Actions menu, choose Edit LF-tags.
33. Choose Assign new LF-tag.
34. For Assigned keys¸ choose the Confidential LF-tag you created earlier.
35. For Values, choose False.
36. Choose Save.

Grant table permissions

Now we grant permissions to data analysts for consumption of the database tag_database and the table col_tag_database.

1. Sign in to the Lake Formation console as lf-data-engineer.
2. On the Permissions page, select Data Permissions
3. Choose Grant.
4. Under Principals, select IAM users and roles.
5. For IAM users and roles, choose lf-data-analyst.
   
6. Select Resources matched by LF-tags.
7. Choose Add LF-tag.
8. For Key, choose Confidential.
9. For Values¸ choose True.
10. For Database permissions, select Describe
    
11. For Table permissions, choose Select and Describe.
12. Choose Grant.
    
    This grants permissions to the lf-data-analyst user on the objects associated with the LF-tag Confidential=True (Database : tag_database)  to describe the database and the select permission on tables.Next, we repeat the steps to grant permissions to data analysts for LF-tag expression for Confidential=False . This LF-tag is used for describing the col_tag_database and the table source_data_col_lvl when logged in as lf-data-analyst from Athena. And so, we only grant describe access to the resources through this LF-tag expression.
13. Sign in to the Lake Formation console as lf-data-engineer.
14. On the Databases page, select the database col_tag_database.
15. Choose Action and Grant.
16. Under Principals, select IAM users and roles.
17. For IAM users and roles, choose lf-data-analyst.
    
18. Select Resources matched by LF-tags.
19. Choose Add LF-tag.
20. For Key, choose Confidential.
21. For Values¸ choose False.
22. For Database permissions, select Describe.
    
23. For Table permissions, do not select anything.
24. Choose Grant.
    
    Next, we repeat the steps to grant permissions to data analysts for LF-tag expression for Confidential=False and Sensitive=True. This LF-tag is used for describing the col_tag_database and the table source_data_col_lvl (Column level) when logged in as lf-data-analyst from Athena.
25. Sign in to the Lake Formation console as lf-data-engineer.
26. On the Databases page, select the database col_tag_database.
27. Choose Action and Grant.
28. Under Principals, select IAM users and roles.
29. For IAM users and roles, choose lf-data-analyst.
30. Select Resources matched by LF-tags.
31. Choose Add LF-tag.
32. For Key, choose Confidential.
33. For Values¸ choose False.
34. Choose Add LF-tag.
35. For Key, choose Sensitive.
36. For Values¸ choose True.
37. For Database permissions, select Describe.
    
38. For Database permissions, select Describe.
39. For Table permissions, select Select and Describe.
40. Choose Grant.

Run a query in Athena to verify the permissions

For this step, we sign in to the Athena console as lf-data-analyst and run SELECT queries against the two tables (source_data and source_data_col_lvl). We use our S3 path as the query result location (s3://lf-tagbased-demo-<<Account-ID>>/athena-results/).

1. In the Athena query editor, choose tag_database in the left panel.
2. Choose the additional menu options icon (three vertical dots) next to source_data and choose Preview table.
3. Choose Run query.

The query should take a few minutes to run. The following screenshot shows our query results.

The first query displays all the columns in the output because the LF-tag is associated at the database level and the source_data table automatically inherited the LF-tag from the database tag_database.

4. Run another query using col_tag_database and source_data_col_lvl.

The second query returns just the two columns that were tagged (Non-Confidential and Sensitive).

As a thought experiment, you can also check to see the Lake Formation Tag-based access policy behavior on columns to which the user doesn’t have policy grants.

When an untagged column is selected from the table source_data_col_lvl, Athena returns an error. For example, you can run the following query to choose untagged columns geolocationid:

SELECT geolocationid FROM "col_tag_database"."source_data_col_lvl" limit 10;

Extend the solution to cross-account scenarios

You can extend this solution to share catalog resources across accounts. The following diagram illustrates a cross-account architecture.

We describe this in more detail in a subsequent post.

Clean up

To help prevent unwanted charges to your AWS account, you can delete the AWS resources that you used for this walkthrough.

1. Sign in as lf-data-engineer Delete the databases tag_database and col_tag_database
2. Now, Sign in as lf-data-steward and clean up all the LF-tag Permissions, Data Permissions and Data Location Permissions that were granted above that were granted lf-data-engineer and lf-data-analyst.
3. Sign in to the Amazon S3 console as the account owner (the IAM credentials you used to deploy the CloudFormation stack).
4. Delete the following buckets:
   1. lf-tagbased-demo-accesslogs-<acct-id>
   2. lf-tagbased-demo-<acct-id>
5. On the AWS CloudFormation console, delete the stack you created.
6. Wait for the stack status to change to DELETE_COMPLETE.

Conclusion

In this post, we explained how to create a LakeFormation Tag-based access control policy in Lake Formation using an AWS public dataset. In addition, we explained how to query tables, databases, and columns that have LakeFormation Tag-based access policies associated with them.

You can generalize these steps to share resources across accounts. You can also use these steps to grant permissions to SAML identities. In subsequent posts, we highlight these use cases in more detail.

About the Authors

Sanjay Srivastava is a principal product manager for AWS Lake Formation. He is passionate about building products, in particular products that help customers get more out of their data. During his spare time, he loves to spend time with his family and engage in outdoor activities including hiking, running, and gardening.

Nivas Shankar is a Principal Data Architect at Amazon Web Services. He helps and works closely with enterprise customers building data lakes and analytical applications on the AWS platform. He holds a master’s degree in physics and is highly passionate about theoretical physics concepts.

Pavan Emani is a Data Lake Architect at AWS, specialized in big data and analytics solutions. He helps customers modernize their data platforms on the cloud. Outside of work, he likes reading about space and watching sports.

Post Syndicated from Noritaka Sekiyama original https://aws.amazon.com/blogs/big-data/effective-data-lakes-using-aws-lake-formation-part-5-secure-data-lakes-with-row-level-access-control/

Increasingly, customers are looking at data lakes as a core part of their strategy to democratize data access across the organization. Data lakes enable you to handle petabytes and exabytes of data coming from a multitude of sources in varying formats, and gives users the ability to access it from their choice of analytics and machine learning tools. Fine-grained access controls are needed to ensure data is protected and access is granted to only those who require it.

AWS Lake Formation is a fully managed service that helps you build, secure, and manage data lakes, and provide access control for data in the data lake. Lake Formation row-level permissions allow you to restrict access to specific rows based on data compliance and governance policies. Lake Formation also provides centralized auditing and compliance reporting by identifying which principals accessed what data, when, and through which services.

This post demonstrates how row-level access controls work in Lake Formation, and how to set them up.

If you have large fact tables storing billions of records, you need a way to enable different users and teams to access only the data they’re allowed to see. Row-level access control is a simple and performant way to protect data, while giving users access to the data they need to perform their job. In the retail industry for instance, you may want individual departments to only see their own transactions, but allow regional managers access to transactions from every department.

Traditionally you can achieve row-level access control in a data lake through two common approaches:

• Duplicate the data, redact sensitive information, and grant coarse-grained permissions on the redacted dataset
• Load data into a database or a data warehouse, create a view with a WHERE clause to select only specific records, and grant permission on the resulting view

These solutions work well when dealing with a small number of tables, principals, and permissions. However, they make it difficult to audit and maintain because access controls are spread across multiple systems and methods. To make it easier to manage and enforce fine-grained access controls in a data lake, we announced a preview of Lake Formation row-level access controls. With this preview feature, you can create row-level filters and attach them to tables to restrict access to data for AWS Identity and Access Management (IAM) and SAMLv2 federated identities.

How data filters work for row-level security

Granting permissions on a table with row-level security (row filtering) restricts access to only specific rows in the table. The filtering is based on the values of one or more columns. For example, a salesperson analyzing sales opportunities should only be allowed to see those opportunities in their assigned territory and not others. We can define row-level filters to restrict access where the value of the territory column matches the assigned territory of the user.

With row-level security, we introduced the concept of data filters. Data filters make it simpler to manage and assign a large number of fine-grained permissions. You can specify the row filter expression using the WHERE clause syntax described in the PartiQL dialect.

Example use case

In this post, a fictional ecommerce company sells many different products, like books, videos, and toys. Customers can leave reviews and star ratings for each product, so other customers can make informed decisions about what they should buy. We use the Amazon Customer Reviews Dataset, which includes different products and customer reviews.

To illustrate the different roles and responsibilities of a data owner and a data consumer, we assume two personas: a data lake administrator and a data analyst. The administrator is responsible for setting up the data lake, creating data filters, and granting permissions to data analysts. Data analysts residing in different countries (for our use case, the US and Japan) can only analyze product reviews for customers located in their own country and for compliance reasons, shouldn’t be able to see data for customers located in other countries. We have two data analysts: one responsible for the US marketplace and another for the Japanese marketplace. Each analyst uses Amazon Athena to analyze customer reviews for their specific marketplace only.

Set up resources with AWS CloudFormation

This post includes an AWS CloudFormation template for a quick setup. You can review and customize it to suit your needs.

The CloudFormation template generates the following resources:

• An AWS Lambda function (for Lambda-backed AWS CloudFormation custom resources). We use the function to copy sample data files from the public S3 bucket to your Amazon Simple Storage Service (Amazon S3) bucket.
• An S3 bucket to serve as our data lake.
• IAM users and policies:
  • DataLakeAdmin
  • DataAnalystUS
  • DataAnalystJP
• An AWS Glue Data Catalog database, table, and partition.
• Lake Formation data lake settings and permissions.

When following the steps in this section, use either us-east-1 or us-west-2 Regions (where the preview functionality is currently available).

Before launching the CloudFormation template, you need to ensure that you disabled Use only IAM access control for new databases/tables by following steps:

1. Sign in to the Lake Formation console in the us-east-1 or us-west-2 Region.
2. Under Data catalog, choose Settings.
3. Deselect Use only IAM access control for new databases and Use only IAM access control for new tables in new databases.
4. Choose Save.

To launch the CloudFormation stack, complete the following steps:

1. Sign in to the CloudFormation console in the same Region.
2. Choose Launch Stack:
   
3. Choose Next.
4. For DatalakeAdminUserName and DatalakeAdminUserPassword, enter the user name and password you want for the data lake admin IAM user.
5. For DataAnalystUsUserName and DataAnalystUsUserPassword, enter the user name and password you want for the data analyst user who is responsible for the US marketplace.
6. For DataAnalystJpUserName and DataAnalystJpUserPassword, enter the user name and password you want for the data analyst user who is responsible for the Japanese marketplace.
7. For DataLakeBucketName, enter the name of your data lake bucket.
8. For DatabaseName and TableName, leave as the default.
9. Choose Next.
10. On the next page, choose Next.
11. Review the details on the final page and select I acknowledge that AWS CloudFormation might create IAM resources.
12. Choose Create.

Stack creation can take about 1 minute.

Query without data filters

After you set up the environment, you can query the product reviews table. Let’s first query the table without row-level access controls to make sure we can see the data. If you’re running queries in Athena for the first time, you need to configure the query result location.

Sign in to the Athena console using the DatalakeAdmin user, and run the following query:

SELECT * 
FROM lakeformation_tutorial_row_security.amazon_reviews
LIMIT 10

The following screenshot shows the query result. This table has only one partition, product_category=Video, so each record is a review comment for a video product.

Let’s run an aggregation query to retrieve the total number of records per marketplace:

SELECT marketplace, count(*) as total_count
FROM lakeformation_tutorial_row_security.amazon_reviews
GROUP BY marketplace

The following screenshot shows the query result. The marketplace column has five different values. In the subsequent steps, we set up row-based filters using the marketplace column.

Set up data filters

Let’s start by creating two different data filters, one for the analyst responsible for the US marketplace, and another for the one responsible for the Japanese marketplace. The we grant the users their respective permissions.

Create a filter for the US marketplace data

Let’s first set up a filter for the US marketplace data.

1. As the DatalakeAdmin user, open the Lake Formation console.
2. Choose Data filters.
3. Choose Create new filter.
4. For Data filter name, enter amazon_reviews_US.
5. For Target database, choose the database lakeformation_tutorial_row_security.
6. For Target table, choose the table amazon_reviews.
7. For Column-level access, leave as the default.
8. For Row filter expression, enter marketplace='US'.
9. Choose Create filter.
   

Create a filter for the Japanese marketplace data

Let’s create another data filter to restrict access to the Japanese marketplace data.

1. On the Data filters page, choose Create new filter.
2. For Data filter name, enter amazon_reviews_JP.
3. For Target database, choose the database lakeformation_tutorial_row_security.
4. For Target table, choose the table amazon_reviews.
5. For Column-level access, leave as the default.
6. For Row filter expression, enter marketplace='JP'.
7. Choose Create filter.

Grant permissions to the US data analyst

Now we have two data filters. Next, we need to grant permissions using these data filters to our analysts. We start by granting permissions to the DataAnalystUS user.

1. On the Data permissions page, choose Grant.
2. For Principals, choose IAM users and roles, and choose the user DataAnalystUS.
3. For Policy tags or catalog resources, choose Named data catalog resources.
4. For Database, choose the database lakeformation_tutorial_row_security.
5. For Table, choose the table amazon_reviews.
6. For Table permissions, select Select.
7. For Data permissions, select Advanced cell-level filters.
8. Select the filter amazon_reviews_US.
9. Choose Grant.

The following screenshot show the available data filters you can attach to a table when configuring permissions.

Grant permissions to the Japanese data analyst

Next, complete the following steps to configure permissions for the user DataAnalystJP:

1. On the Data permissions page, choose Grant.
2. For Principals, choose IAM users and roles, and choose the user DataAnalystJP.
3. For Policy tags or catalog resources, choose Named data catalog resources.
4. For Database, choose the database lakeformation_tutorial_row_security.
5. For Table, choose the table amazon_reviews.
6. For Table permissions, select Select.
7. For Data permissions, select Advanced cell-level filters.
8. Select the filter amazon_reviews_JP.
9. Choose Grant.

Query with data filters

With the data filters attached to the product reviews table, we’re ready to run some queries and see how permissions are enforced by Lake Formation. Because row-level security is in preview as of this writing, we need to create a special Athena workgroup named AmazonAthenaLakeFormationPreview, and switch to using it. For more information, see Managing Workgroups.

Sign in to the Athena console using the DataAnalystUS user and switch to the AmazonAthenaLakeFormationPreview workgroup. Run the following query to retrieve a few records, which are filtered based on the row-level permissions we defined:

SELECT * 
FROM lakeformation.lakeformation_tutorial_row_security.amazon_reviews
LIMIT 10

Note the prefix of lakeformation. before the database name; this is required for the preview only.

The following screenshot shows the query result.

Similarly, run a query to count the total number of records per marketplace:

SELECT marketplace, count(*) as total_count
FROM lakeformation.lakeformation_tutorial_row_security.amazon_reviews
GROUP BY marketplace 

The following screenshot shows the query result. Only the marketplace US shows in the results. This is because our user is only allowed to see rows where the marketplace column value is equal to US.

Switch to the DataAnalystJP user and run the same query:

SELECT * 
FROM lakeformation.lakeformation_tutorial_row_security.amazon_reviews
LIMIT 10

The following screenshot shows the query result. All of the records belong to the JP marketplace.

Run the query to count the total number of records per marketplace:

SELECT marketplace, count(*) as total_count
FROM lakeformation.lakeformation_tutorial_row_security.amazon_reviews
GROUP BY marketplace

The following screenshot shows the query result. Again, only the row belonging to the JP marketplace is returned.

Clean up

Now to the final step, cleaning up the resources.

1. Delete the CloudFormation stack.
2. Delete the Athena workgroup AmazonAthenaLakeFormationPreview.

Conclusion

In this post, we covered how row-level security in Lake Formation enables you to control data access without needing to duplicate it or manage complicated alternatives such as views. We demonstrated how Lake Formation data filters can make creating, managing, and enforcing row-level permissions simple and easy.

When you want to grant permission on specific cell, you can include or exclude columns in the data filters in addition to the row filter expression. You can learn more about the cell filters in Part 4: Implementing cell-level and row-level security.

You can get started with Lake Formation today by visiting the AWS Lake Formation product page. If you want to try out row-level security, as well as the other exciting new features like ACID transactions and acceleration currently available for preview in the US East (N. Virginia) and the US West (Oregon) Regions, sign up for the preview.

About the Authors

Noritaka Sekiyama is a Senior Big Data Architect on the AWS Glue and AWS Lake Formation team. He has 11 years of experience working in the software industry. Based in Tokyo, Japan, he is responsible for implementing software artifacts, building libraries, troubleshooting complex issues and helping guide customer architectures.

Sanjay Srivastava is a Principal Product Manager for AWS Lake Formation. He is passionate about building products, in particular products that help customers get more out of their data. During his spare time, he loves to spend time with his family and engage in outdoor activities including hiking, running, and gardening.
 

Post Syndicated from Deenbandhu Prasad original https://aws.amazon.com/blogs/big-data/part-4-effective-data-lakes-using-aws-lake-formation-part-4-implementing-cell-level-and-row-level-security/

We announced the preview of AWS Lake Formation transactions, cell-level and row-level security, and acceleration at AWS re: Invent 2020. In Parts 1 , 2, and 3 of this series, we explained how to set up governed tables, add streaming and batch data to them, and use ACID transactions. In this post, we focus on cell-level and row-level security and show you how to enforce business needs by restricting access to specific rows.

The goal of modern data lakes is to democratize access to broad datasets to empower data analysts and business users. In these scenarios, data lake security becomes more important than ever. Enterprises want to share their data across groups, departments, and organizations, while balancing their compliance and security needs. A common paradigm used by many enterprises is to restrict data access to limit scope based on the user profile or the organizations to which they belong. Previously, you had to enforce this by duplicating the original data or creating materialized and non-materialized views of the data based on filtered datasets. However, these solutions often break the concept of a single source of truth and result in write amplification, which doubles or triples storage. The large number of copies required also increases the management effort required due to their complexity.

Lake Formation supports simple row-level security and cell-level security:

• Basic row-level security allows you to specify filter expressions that limit access to specific rows of a table to a user.
• Cell-level security builds on row-level security by allowing you to hide or show specific columns along with providing access to specific rows.

In this post, we consider an example use case in which Company A does business across the United States and Canada. They have built an enterprise customer data hub on Amazon Simple Storage Service (Amazon S3), which sources customers from both countries into this central location for company-wide analytics and marketing.

The marketing department of Company A is responsible for organizing promotion campaigns and developing communications content to promote services and product to prospects. The team consumes the data from this enterprise customer data hub to create the targeted campaign.

The marketing team data analysts are divided by country, and the requirement is to restrict analyst access to customer data from their country—analysts from the United States can see only customers from the United States, and analysts from Canada can only access customers from Canada. Additionally, analysts from Canada aren’t allowed to see the date of birth (DoB column) due to local company policy (this restriction is an example of cell-level security).

The dataset used for this post is synthetically generated; the following screenshot shows an example of the data.

Solution overview

Let’s see how you can use the Lake Formation row-level security feature to enforce Company A’s requirements on the data in the S3 data lake. You can apply row-level security to a governed table or to a standard table in Lake Formation. In this post, you apply row-level security on a standard Lake Formation table; you can follow a similar process for a governed table.

We walk through the following high-level steps:

1. Create a database (lf_rls_blog) and table (customer).
2. Grant select (row and column) permissions to the users lf-rls-blog-analyst-us and lf-rls-blog-analyst-ca.
3. Run queries in Amazon Athena as the US and Canada analysts to verify that you only see rows from the user’s respective country (and the appropriate columns).

Set up resources with AWS CloudFormation

This post includes an AWS CloudFormation template for a quick setup. You can review and customize it to suit your needs.

The CloudFormation template generates the following resources:

• AWS Identity and Access Management (IAM) users, roles, and policies:
  • The three users include lf-rls-blog-manager (data lake administrator), lf-rls-blog-analyst-us (US data analyst), and lf-rls-blog-analyst-ca (Canada data analyst)
• Lake Formation data lake settings and resources:
  • This includes an S3 bucket with the prefix lf-rowlevel-security-blog-* and the customer data files used in this post

As of this writing, these Lake Formation preview features are available only in us-east-1 and us-west-2. When following the steps in this post, use Region us-east-1. Check the availability of the features in other Regions in the future.

To create your resources, complete the following steps:

1. Sign in to the AWS CloudFormation console in the us-east-1 Region.
2. Choose Launch Stack:
   
3. Create passwords for the three users.
4. Review the details on the page and select I acknowledge that AWS CloudFormation might create IAM resources.
5. Choose Create stack.
   

Create a database and table

To create your database and table, complete the following steps:

1. Sign in to the AWS Management Console as the data lake administrator (lf-rls-blog-manager).
2. On the Lake Formation console, choose Databases in the navigation pane.
3. Choose Create database.
   
4. For Name, enter lf_rls_blog.
5. If Use only IAM access control for new tables in this database is selected, uncheck it.
6. Choose Create database.
   

Next, you create a new data lake table.

7. In the navigation pane, choose Tables.
8. Choose Create table.
   
9. For Name, enter customer.
10. For Database, choose the database you just created (lf_rls_blog).
    
11. Browse to the customers folder under datafiles in the S3 bucket (starting with lf-rowlevel-security-blog-*) created by the CloudFormation template.
    
12. For Classification, select CSV.
13. For Delimiter, choose Comma:
14. Choose Upload Schema.
    
15. Enter the following JSON code:

    [
    {
    "Name": "customer_id",
    "Type": "bigint"
    },
    {
    "Name": "prefix",
    "Type": "string"
    },
    {
    "Name": "first_Name",
    "Type": "string"
    },
    {
    "Name": "middle_Name",
    "Type": "string"
    },
    {
    "Name": "last_Name",
    "Type": "string"
    },
    {
    "Name": "suffix",
    "Type": "string"
    },
    {
    "Name": "gender",
    "Type": "string"
    },
    {
    "Name": "dob",
    "Type": "string"
    },
    {
    "Name": "phone",
    "Type": "string"
    },
    {
    "Name": "building_number",
    "Type": "bigint"
    },
    {
    "Name": "street_Name",
    "Type": "string"
    },
    {
    "Name": "city",
    "Type": "string"
    },
    {
    "Name": "cust_zip",
    "Type": "bigint"
    },
    {
    "Name": "country",
    "Type": "string"
    }
    ]
    

16. Choose Upload.
    
17. Choose Submit.
    

Create data filters and grant permissions

To implement column-level, row-level, and cell-level security, first you create data filters. Then you choose that data filter while granting the select Lake Formation permission on tables. For this use case, you create two data filters: one for the US data analyst and one for the Canada data analyst.

Permitted filter expressions are predicates that obey a subset of the WHERE clause grammar in PartiQL. You can use comparison operators to compare columns with constants. The following are the supported operators:

• Comparison operators – =, >, <, >=, <=, <>, BETWEEN, IN, LIKE
• Logical operators – AND, OR

Let’s first create the data filter for the US analyst.

1. On the Lake Formation console, choose Data filters in the navigation pane.
2. Choose Create new filter.
   
3. For Data filter name, enter US Filter.
4. For Target database, choose the lf_rls_blog database.
5. For Target table, choose the customer table.
6. For Column-level access, select Access to all columns.
7. For Row filter expression, enter country='US'.
8. Choose Create filter.
   

The US analyst has access to all the columns of US customers only.

Now let’s create a data filter for the Canada analyst.

9. On the Data filters page, choose Create new filter.
10. For Data filter name, enter Canada Filter.
11. For Target database, choose the lf_rls_blog database.
12. For Target table, choose the customer table.
13. For Column-level access, select Exclude columns.
14. For Select columns, choose the dob column.
15. For Row filter expression, enter country='Canada'.
16. Choose Create filter.
    

The Canada analyst now has access to all the columns except dob (date of birth) of Canadian customers only.

Verify both data filters are created by checking the Data filters page.

Now we can grant table and column permissions.

17. On the Tables page, select the customer table.
18. On the Actions menu, choose Grant.
    

19. For IAM users and roles, choose lf-rls-blog-analyst-us.
20. Choose Named data catalog resources
21. For Databases, choose lf_rls_blog.
22. For Tables, choose customer.
    
23. For Table and column permissions, choose Select.
24. Under Data permissions¸ select Advanced cell-level filters.
25. Select US Filter.
26. Choose Grant.
    
27. Repeat these steps for the lf-rls-blog-analyst-ca user, choosing the lf_rls_blog database and customer table and granting Select permissions.
    
28. Select Advanced cell-level filters.
29. In the Data permissions section, select Canada Filter.
30. Choose Grant.
    

Run queries to test permission levels

To utilize Lake Formation preview features in Athena, you need to create a new workgroup named AmazonAthenaLakeFormationPreview and switch to that workgroup before running queries. For more information, see Managing Workgroups. Additionally, for preview you use the lakeformation qualifier for the database and table name, as shown in the following example:

select * from lakeformation.<databasename>.<tablename>

Lake Formation implicitly grants all permissions to the table creator. In this use case, lf-rls-blog-manager has SELECT permissions on all rows and columns of the customer table. Let’s first verify permissions for lf-rls-blog-manager by querying the customer table using Athena.

1. On the Athena console (in Region us-east-1), open the query editor.
2. Choose set up a query result location in Amazon S3.
   
3. Navigate to the S3 bucket starting with lf-rowlevel-security-blog-* and select the folder anthenaqueryresults.
4. Choose Save.
5. In the query editor, for Data source, choose AWSDataCatalog.
6. For Database, choose lf_rls_blog.
7. Create and switch to the AmazonAthenaLakeFormationPreview workgroup.

You can see the customer table under Tables.

8. Enter the following query:

   SELECT * FROM lakeformation."lf_rls_blog"."customer"

9. Choose Run query.

Rows from both countries are displayed to the lf-rls-blog-manager users.

Next, lets verify permission of the lf-rls-blog-analyst-us and lf-rls-blog-analyst-ca users on this table.

10. Sign in to the console as lf-rls-blog-analyst-us.
11. Repeat the previous steps on the Athena console (in us-east-1) to set up the query result location.
12. Switch to the AmazonAthenaLakeFormationPreview workgroup.
13. Run the following query:

    SELECT * FROM lakeformation."lf_rls_blog"."customer"

Only US customers are shown in the result for the US data analyst.

Now you verify the same for the Canada data analyst.

14. Sign in to the console as lf-rls-blog-analyst-ca.
15. Repeat the previous steps on the Athena console (in us-east-1) to set up the query result location.
16. Switch to AmazonAthenaLakeFormationPreview workgroup.
17. Run the following query:

    SELECT * FROM lakeformation."lf_rls_blog"."customer"

Only customers from Canada are visible to the Canada data analyst. Furthermore, the lf-rls-blog-analyst-ca user doesn’t have access to the dob column.

Clean up

For the final step, clean up the resources you created:

1. Sign in to the console as lf-rls-blog-manager.
2. On the Lake Formation console, choose Databases in the navigation pane.
3. Select the database lf_rls_blog.
4. On the Action menu, choose Delete.
5. Delete anthenaqueryresults folder from the Amazon S3 bucket with prefix lf-rowlevel-security-blog-*.
6. Login as the user that launched the CloudFormation stack in this post.
7. On the AWS CloudFormation console, delete the stack lf-rowlevel-security-blog.

When you delete the stack, the resources it created are automatically deleted.

Conclusion

In this post, you learned how to implement fine-grained access control on a data lake table using the new row-level security feature of Lake Formation. This feature allows you to easily enforce privacy regulations or corporate governance data access rules on your data lake.

Learn more about Lake Formation and start using these features to build and secure your data lake on Amazon S3 using Lake Formation today, and leave your thoughts and questions in the comments.

About the Authors

Deenbandhu Prasad is a Senior Analytics Specialist at AWS, specializing in big data services. He is passionate about helping customers build modern data platforms on the AWS Cloud. Deenbandhu has helped customers of all sizes implement master data management, data warehouse, and data lake solutions.

Noritaka Sekiyama is a Senior Big Data Architect at AWS Glue and AWS Lake Formation. His passion is for implementing software artifacts for building data lakes more effectively and easily. During his spare time, he loves to spend time with his family, especially hunting bugs—not software bugs, but bugs like butterflies, pill bugs, snails, and grasshoppers.