Effective data lakes using AWS Lake Formation, Part 1: Implementing cell-level and row-level security

July 2023: This post was reviewed for accuracy.

We announced the general availability of AWS Lake Formation transactions, cell-level and row-level security, and acceleration at AWS re: Invent 2021. 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

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. 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 – optional, choose customer.
    23. For Data filters – optional¸ select US Filter.

19. For Data filter permissions, choose Select.
20. Choose Grant.
    
21. Repeat these steps for the lf-rls-blog-analyst-ca user, choosing the lf_rls_blog database, customer table, Canada Filter and granting Select permissions.
    
22. Choose Grant.
    

Run queries to test permission levels

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.

You can see the customer table under Tables.

7. Enter the following query:

   SELECT * FROM "lf_rls_blog"."customer"

8. 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.

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

    SELECT * FROM "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.

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

    SELECT * FROM "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.