Skip to main content
Back to path

Welcome to a Grasp lesson

Create a free account to keep studying and create your own study path.

Lesson 52

Column-Level Security with Lake Formation

Recommended resources

Hello! Welcome back to our course on preparing for the AWS Data Engineer - Associate certification.

In our last lesson, we established the foundation of data lake security by granting permissions at the database and table levels. We explored the two main approaches: the direct Named Resource method and the scalable Tag-Based Access Control (LF-TBAC).

Today, we're taking the next logical step to implement even more granular security. This is crucial for protecting sensitive data like Personally Identifiable Information (PII) or confidential financial details. By the end of this 60-minute lesson, you will be able to implement fine-grained access control using Lake Formation's column-level security. This capability is a cornerstone of modern data governance and a key topic on the certification exam.

We'll examine the two primary ways to achieve this, which mirror the methods we learned in the previous lesson, but applied at the column level:

  1. Direct Column Filtering using include/exclude lists.
  2. Scalable Column Tagging using Lake Formation Tags (LF-Tags).

Method 1: Direct Column Filtering

The most straightforward way to restrict column access is to specify exactly which columns a principal can or cannot see when you grant them SELECT permission on a table. Lake Formation allows you to grant access to a table while explicitly including or excluding certain columns.

This is best understood with a demonstration. The following video segment shows a Data Lake Administrator granting an analyst access to an orders table but hiding the sensitive customer_name column.

Deep Dive Into AWS Lake Formation - Level 300 (United States)

Watch this segment from the 'Deep Dive Into AWS Lake Formation' video by AWS Events. It provides a practical demonstration of granting table access while excluding a specific, sensitive column.

Watch from 16:40 to 20:47. Pay close attention to: The process of granting SELECT permission to the 'analyst' user. How the 'customer_name' column is specifically excluded from the grant. The final result from the analyst's perspective in Athena, where even a SELECT * query does not reveal the hidden column.

As you saw in the video, the analyst is completely unaware that the customer_name column even exists. This filtering is enforced transparently by Lake Formation's integration with query engines like Athena.

How It Works: Data Filters and the Vending Model

So, how does Lake Formation actually enforce this? The mechanism is a powerful combination of metadata filtering and credential vending. This short video segment explains the process.

Deep Dive Into AWS Lake Formation - Level 300 (United States)

This final clip from the 'Deep Dive' video explains the mechanics of how Lake Formation enforces permissions with integrated services like Athena.

Watch from 20:47 to 21:45. Focus on the sequence of events: Athena checks with Lake Formation, Lake Formation provides temporary scoped-down credentials, and Athena filters out unauthorized columns before returning results.

The key steps are:

  1. A user queries a table via Athena.
  2. Athena asks Lake Formation what permissions the user has for that table.
  3. Lake Formation checks its policies and returns temporary, short-lived S3 credentials that are scoped only to the specific S3 prefixes and objects the user is allowed to access.
  4. Athena uses these credentials to read the data from S3.
  5. Crucially, before returning the results to the user, Athena's query engine applies the column-level policy, filtering out any columns the user is not permitted to see.
AWS Data Lake Architecture with Lake Formation for Column-Level Security
This diagram shows the overall flow. Data in S3 is cataloged by Glue, and permissions are centrally managed by Lake Formation. When Athena (the query engine) requests data, it adheres to the fine-grained permissions defined in Lake Formation, including column-level security.

In Lake Formation, this combination of column and/or row restrictions is formally encapsulated in an object called a Data Filter.

Data filtering and cell-level security in Lake Formation

The official AWS documentation provides the formal definition of Data Filters and how they enable different levels of security. Let's read through it to solidify these concepts.

Please read the following sections: Start at the top of the page, reading the introduction and the subsection on 'Column-level security'. Then, skip down to the section titled 'Data filters in Lake Formation' to understand how these filters are constructed. This will give you the formal terminology used by AWS, which is important for the exam.

Test your understanding!

An analyst has been granted SELECT permission on a sales table in Lake Formation. The grant includes all columns except for the customer_pii column. The analyst connects to Athena and runs the query SELECT customer_pii FROM sales;. What is the expected outcome?

Show answer

The query will fail. Athena, being integrated with Lake Formation, will receive an error indicating that the customer_pii column does not exist or that the user does not have permission to access it. From the analyst's perspective, the column is completely invisible.

Method 2: Scalable Column Security with LF-Tags

While direct column filtering is effective for one-off rules, it doesn't scale well. Imagine you have 50 tables, each with PII columns, and 10 different analyst roles. Managing individual column-level grants for every combination would be a nightmare.

This is where LF-Tag-Based Access Control (LF-TBAC) for columns becomes essential. We introduced LF-TBAC in the last lesson for databases and tables; the principle is the same for columns.

The workflow is:

  1. Define LF-Tags: Create tags that represent data sensitivity, like Classification:PII or AccessLevel:Confidential.
  2. Assign LF-Tags to Columns: Go to the table schema and attach the relevant tags directly to the columns (e.g., tag the ssn, email, and phone_number columns with Classification:PII).
  3. Grant Permissions on Tags: Grant principals (users/roles) SELECT permissions on resources that match certain tag expressions. For example, grant the hr-analysts role access to all columns tagged with Classification:PII.

This approach decouples permissions from the resources themselves, making governance vastly simpler and more scalable.

AWS Lake Formation Column-Level Security with LF-Tags
This diagram illustrates the power of LF-TBAC for column security. A central governance account defines an LF-Tag ontology (e.g., PII, Sensitive). These tags are applied to columns in tables owned by different data producers. Data consumers are then granted access based on these tags, ensuring consistent policy enforcement across the data lake.

The following video gives a great conceptual overview of using LF-tags for column-level security.

Lake Formation: Managing Permissions Through LF-Tags

This video from 'DataEng Uncomplicated' clearly explains how LF-Tags can be applied at the column level for more scalable and manageable security.

Watch from 00:37 to 01:22, and then from 03:58 to 05:11. Focus on the core idea: instead of specifying column names in a policy, you apply tags to the columns and grant permissions on the tags.

To see how this is implemented in practice, the AWS documentation provides a detailed tutorial. You don't need to perform the steps, but understanding the flow is critical.

Managing a data lake using Lake Formation tag-based access ...

This AWS tutorial demonstrates the end-to-end process of setting up column-level security with LF-Tags. We'll focus on the key steps related to defining, assigning, and granting permissions on tags.

Review the following steps from the tutorial to understand the process: Step 2: Focus on the part where LF-Tags ('Confidential', 'Sensitive') are created under 'Register a data location and define LF-Tag ontology'. Step 3: Skim this step to see how tables are created. The important part is at the end, under 'Create your database and table for column-level access', where the Sensitive=True LF-Tag is associated with the vendorid and fare_amount columns. Step 4: Observe how permissions are granted to the lf-data-analyst not on the table directly, but on LF-Tag expressions like Confidential=False and Sensitive=True. Step 5: See how the analyst's query in Athena can only see the columns that match the tags they have permission for. This confirms the policy is being enforced.

Extending to Cell-Level Security

Lake Formation can get even more granular. Cell-level security is not a distinct feature but rather the result of combining column-level and row-level security.

Imagine you want an analyst to see the commission column, but only for sales that occurred in their own region. This requires both a column filter (include commission) and a row filter (e.g., WHERE region = 'North America').

You achieve this by creating a single Data Filter that contains both a column specification (include/exclude list) and a row filter expression.

Data filtering and cell-level security in Lake Formation

Let's return to the AWS documentation on Data Filtering to see how cell-level security is defined as a combination of row and column filters.

Read the subsections 'Cell-level security' and the paragraph that follows describing how a Data Filter with both column and row specifications establishes cell-level security. Note the console screenshot showing a filter that restricts both a column (customer_name) and rows (product_type = 'pharma').

For the exam, remember that if a scenario requires restricting certain columns for certain rows, the answer involves a Lake Formation Data Filter with both column and row filter expressions.

Conclusion

In this lesson, we've drilled down into the fine-grained access control capabilities of AWS Lake Formation. You now have the knowledge to secure data at the most granular level required by enterprise governance policies.

Key Takeaways:

  • Column-level security is essential for protecting sensitive data elements within your tables.
  • It can be implemented via two methods:
    • Direct Column Filtering: Simple for specific, ad-hoc rules. Implemented via Data Filters with include/exclude column lists.
    • LF-Tag Based Access Control (LF-TBAC): The scalable, recommended best practice. Involves tagging columns and granting permissions on those tags.
  • Cell-level security is achieved by combining column filters and row filters within a single Data Filter.
  • Query engines like Athena seamlessly enforce these permissions, making the filtering transparent to end-users.

Next Up:
We've now mastered securing data within a single AWS account. However, data engineering often involves collaboration across business units or even partner organizations, each with their own AWS account. In our next lesson, we'll address this by learning how to design cross-account data sharing patterns using Lake Formation.