Data filters in Lake Formation

You can implement column-level, row-level, and cell-level security by creating data filters. You select a data filter when you grant the SELECT Lake Formation permission on tables. If your table contains nested column structures, you can define a data filter by including or excluding the child columns and define row-level filter expressions on nested attributes.

Each data filter belongs to a specific table in your Data Catalog. A data filter includes the following information:

Filter name
The Catalog IDs of the table associated with the filter
Table name
Name of the database that contains the table
Column specification – a list of columns and nested columns (with struct datatypes) to include or exclude in query results.
Row filter expression – an expression that specifies the rows to include in query results. With some restrictions, the expression has the syntax of a WHERE clause in the PartiQL language. To specify all rows, choose Access to all rows under Row-level access in the console or use AllRowsWildcard in API calls.

For more information about what is supported in row filter expressions, see PartiQL support in row filter expressions.

The level of filtering that you get depends on how you populate the data filter.

When you specify the "all columns" wildcard and provide a row filter expression, you are establishing row-level security (row filtering) only.
When you include or exclude specific columns and nested columns, and specify "all rows" using the all-rows wildcard, you are establishing column-level security (column filtering) only.
When you include or exclude specific columns and also provide a row filter expression, you are establishing cell-level security (cell filtering).

The following screenshot from the Lake Formation console shows a data filter that performs cell-level filtering. For queries against the orders table, it restricts access to the customer_name column and the query results return only rows where the product_type column contains 'pharma'.

The data filter window contains these fields, arranged vertically: Data filter name;
Target database; Target table; Option button group with the options Access to all columns,
Include columns, and Exclude columns; Select columns (drop-down list); Row filter
expression (multi-line text box). The Exclude columns option is selected, the
customer_name column is selected for exclusion, and the Row filter expression field
contains 'product_type='pharma'.

Note the use of single quotes to enclose the string literal, 'pharma'.

You can use the Lake Formation console to create this data filter, or you can supply the following request object to the CreateDataCellsFilter API operation.


{
     "Name": "restrict-pharma",
     "DatabaseName": "sales",
     "TableName": "orders",
     "TableCatalogId": "111122223333",      
     "RowFilter": {"FilterExpression": "product_type='pharma'"},
     "ColumnWildcard": {
         "ExcludedColumnNames": ["customer_name"]
     }
}

You can create as many data filters as you need for a table. In order to do so, you require SELECT permission with the grant option on a table. Data Lake Administrators by default have the permission to create data filters on all tables in that account. You typically only use a subset of the possible data filters when granting permissions on the table to a principal. For example, you could create a second data filter for the orders table that is a row-security-only data filter. Referring to the preceding screenshot, you could choose the Access to all columns option and include a row filter expression of product_type<>pharma. The name of this data filter could be no-pharma. It restricts access to all rows that have the product_type column set to 'pharma'.

The request object for the CreateDataCellsFilter API operation for this data filter is the following.


{
     "Name": "no-pharma",
     "DatabaseName": "sales",
     "TableName": "orders",
     "TableCatalogId": "111122223333",      
     "RowFilter": {"FilterExpression": "product_type<>'pharma'"},
     "ColumnNames": ["customer_id", "customer_name", "order_num"
          "product_id", "purchase_date", "product_type", 
          "product_manufacturer", "quantity", "price"]
}

You could then grant SELECT on the orders table with the restrict-pharma data filter to an administrative user, and SELECT on the orders table with the no-pharma data filter to non-administrative users. For users in the healthcare sector, you would grant SELECT on the orders table with full access to all rows and columns (no data filter), or perhaps with yet another data filter that restricts access to pricing information.

You can include or exclude nested columns when specifying column-level and row-level security within a data filter. In the following example, access to the product.offer field is specified using qualified column names (wrapped in double quotes). This is important for nested fields in order to avoid errors occurring when column names contain special characters, and to maintain backward compatibility with top level column-level security definitions.


{
     "Name": "example_dcf",
     "DatabaseName": "example_db",
     "TableName": "example_table",
     "TableCatalogId": "111122223333",      
     "RowFilter": { "FilterExpression": "customer.customerName <> 'John'" },
     "ColumnNames": ["customer", "\"product\".\"offer\""]
}

Data filters in Lake Formation

See also