# Data filtering and cell-level security in Lake Formation
When you grant Lake Formation permissions on a Data Catalog table, you can include data filtering specifications to restrict access to certain data in query results and engines integrated with Lake Formation. Lake Formation uses data filtering to achieve column-level security, row-level security, and cell-level security. You can define and apply data filters on nested columns if your source data contains nested structures.
With the data filtering capabilities of Lake Formation, you can implement the following levels of data security.
**Column-level security**
Granting permissions on a Data Catalog table with column-level security (column filtering) allows users to view only specific columns and nested columns that they have access to in the table. Consider a `persons` table that is used in multiple applications for a large multi-region communications company. Granting permissions on Data Catalog tables with column filtering can restrict users who don't work in the HR department from seeing personally identifiable information (PII) such as a social security number or birth date. You can also define security policies and grant access to only partial sub-structures of nested columns.
**Row-level security**
Granting permissions on a Data Catalog table with row-level security (row filtering) allows users to view only specific rows of data that they have access to in the table. The filtering is based on the values of one or more columns. You can include nested column structures when defining row-filter expressions. For example, if different regional offices of the communications company have their own HR departments, you can limit the person records that HR employees can see to only records for employees in their region.
**Cell-level security**
Cell-level security combines row filtering and column filtering for a highly flexible permissions model. If you view the rows and columns of a table as a grid, by using cell-level security, you can restrict access to individual elements (cells) of the grid anywhere in the two dimensions. That is, you can restrict access to different columns depending on the row. This is illustrated by the following diagram, in which restricted columns are shaded.
![\[A grid is shown with 5 rows and 6 columns. The rows and columns have headers like Col1, Col2, Row1, Row2, and so on. The grid cells with the following coordinates are shaded: R3,C1; R3,C2; R3,C3; R5,C1; R5;C2; R5,C5; R5,C6.\]](http://docs.aws.amazon.com/lake-formation/latest/dg/images/cells-diagram.png)
Continuing the example of the persons table, you can create a *data filter* at the cell-level that restricts access to the street address column if the row has the country column set to "UK", but allows access to the street address column if the row has the country column set to "US".
Filters apply only to read operations. Therefore, you can grant only the `SELECT` Lake Formation permission with filters.
**Cell-level security on nested columns**
Lake Formation allows you to define and apply data filters with cell-level security on nested columns. However, the integrated analytical engines such as Amazon Athena, Amazon EMR, and Amazon Redshift Spectrum support executing queries against Lake Formation managed nested tables with row and column-level security.
For limitations, see [Data filtering limitations](data-filtering-notes.md).
**Topics**
+ [Data filters in Lake Formation](#data-filters-about)
+ [PartiQL support in row filter expressions](partiql-support.md)
+ [Permissions required for querying tables with cell-level filtering](row-filtering-prereqs.md)
+ [Managing data filters](managing-filters.md)
## 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](partiql-support.md).
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'.\]](http://docs.aws.amazon.com/lake-formation/latest/dg/images/data-filter-sample-pharma.png)
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\""]
}
```
**See also**
[Managing data filters](managing-filters.md)
# PartiQL support in row filter expressions
You can construct row filter expressions using a subset of PartiQL data types, operators, and aggregations. Lake Formation does not allow any user defined or standard partiQL functions in the filter expression. You can use comparison operators to compare columns with constants (for example, `views >= 10000`), but you can't compare columns with other columns.
A Row filter expression may be a simple expression or a composite expression. Total length of the expression must be less than 2048 characters.
**Simple expression**
A simple expression will be of the format:` `
+ **Column name**
It can either a top level data column, a partition column, or a nested column present in the table schema and must belong to the [Supported data types](#row-filter-supported-datatypes) listed below.
+ **Comparison operator**
The following are the supported operators: `=, >, <, >=, <=, <>,!=, BETWEEN, IN, LIKE, NOT, IS [NOT] NULL`
+ All string comparisons and `LIKE` pattern matches are case-sensitive. You can't use IS [NOT] NULL operator on partition columns.
+ **Column value**
The Column value must match the data type of the column name.
**Composite expression**
A composite expression will be of the format: `( ) ()`. Composite expressions can be further combined using logical operators `AND/OR`.
## Supported data types
Row filters that refer to an AWS Glue Data Catalog table that contains an unsupported data types will result in an error. The following are the supported data types for table columns and constants, which are mapped to Amazon Redshift data types:
+ `STRING, CHAR, VARCHAR`
+ `INT, LONG, BIGINT, FLOAT, DECIMAL, DOUBLE`
+ `BOOLEAN`
+ `STRUCT`
For more information about data types in Amazon Redshift, see [Data types](https://docs.aws.amazon.com/redshift/latest/dg/c_Supported_data_types.html) in *Amazon Redshift Database Developer Guide*.
## Row filter expressions
**Example**
The following are examples of valid row filter expressions for a table with columns: ` country (String), id (Long), year (partition column of type Integer), month (partition column of type Integer)`
+ `year > 2010 and country != 'US'`
+ `(year > 2010 and country = 'US') or (month < 8 and id > 23)`
+ `(country between 'Z' and 'U') and (year = 2018)`
+ `(country like '%ited%') and (year > 2000)`
**Example**
The following is a valid examples of row filter expressions for a table with nested columns: `year > 2010 and customer.customerId <> 1 `
Nested fields under partition columns should not be referenced when defining nested row-level expressions.
String constants must be enclosed in single-quotes.
## Reserved keywords
If your row filter expression contains PartiQL keywords, you will receive a parsing error as column names may conflict with the keywords. When this happens, escape the column names by using double quotes. Some examples of reserved keywords are “first”, “last”, “asc”, “missing”. See PartiQL specification for a list of reserved keywords.
## PartiQL reference
For more information about PartiQL, see [https://partiql.org/](https://partiql.org/).
# Permissions required for querying tables with cell-level filtering
The following AWS Identity and Access Management (IAM) permissions are required to run queries against tables with cell-level filtering.
------
#### [ JSON ]
****
```
{
"Version":"2012-10-17",
"Statement": [
{
"Effect": "Allow",
"Action": [
"lakeformation:StartQueryPlanning",
"lakeformation:GetQueryState",
"lakeformation:GetWorkUnits",
"lakeformation:GetWorkUnitResults"
],
"Resource": "*"
}
]
}
```
------
For more information about Lake Formation permissions, see [Lake Formation personas and IAM permissions reference](permissions-reference.md).
# Managing data filters
To implement column-level, row-level, and cell-level security, you can create and maintain data filters. Each data filter belongs to a Data Catalog table. You can create multiple data filters for a table, and then use one or more of them when granting permissions on the table. You can also define and apply data filters on nested columns that have `struct` datatypes allowing users to access only sub-structures of nested columns.
You require `SELECT` permission with the grant option to create or view a data filter. To allow principals in your account to view and use a data filter, you can grant the `DESCRIBE` permission on it.
**Note**
Lake Formation doesn't support granting `Describe` permission on a data filter, which is shared from another account.
You can manage data filters by using the AWS Lake Formation console, the API, or the AWS Command Line Interface (AWS CLI).
For information about data filters, see [Data filters in Lake Formation](data-filtering.md#data-filters-about)
# Creating a data filter
You can create one or more data filters for each Data Catalog table.
**To create a data filter for a Data Catalog table (console)**
1. Open the Lake Formation console at [https://console.aws.amazon.com/lakeformation/](https://console.aws.amazon.com/lakeformation/).
Sign as a data lake administrator, the target table owner, or a principal who has a Lake Formation permission on the target table.
1. In the navigation pane, under **Data catalog**, choose **Data filters**.
1. On the **Data filters** page, choose **Create new filter**.
1. In the **Create data filter** dialog box, enter the following information:
+ Data filter name
+ Target database – Specify the database that contains the table.
+ Target table
+ Column-level access – Leave this set to **Access to all columns** to specify row filtering only. Choose **Include columns** or **Exclude columns** to specify column or cell filtering, and then specify the columns to include or exclude.
Nested columns – If you're applying the filter on a table that contains nested columns, you can explicitly specify sub-structures of the nested struct columns within a data filter.
When you grant SELECT permission to a principal on this filer, the principal executing the following query, will only see the data for `customer.customerName` and not `customer.customerId`.
```
SELECT "customer" FROM "example_db"."example_table";
```
![\[Column-level access settings with options to include specific columns and filter rows.\]](http://docs.aws.amazon.com/lake-formation/latest/dg/images/nested-column-filter.png)
When you grant permissions to the `customer` column, the principal receives the access to the column and the nested fields under the column (`customerName` and `customerID`).
+ Row filter expression – Enter a filter expression to specify row or cell filtering. For supported data types and operators, see [PartiQL support in row filter expressions](partiql-support.md). Choose **Access to all rows** to grant access to all .
You can include partial column structs from nested columns in a row filter expression to filter rows that contain specific value.
When a principal is granted permissions to a table with a row filter expression `Select * from example_nestedtable where customer.customerName <>'John'`, and **Column-level** access is set to **Access to all columns**, the query results shows only rows where `customerName <>'John'` evaluates to true.
The following screenshot shows a data filter that implements cell filtering. In queries against the `orders` table, it denies access to the `customer_name` column and shows only rows that have 'pharma' in the `product_type` column.
![\[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'.\]](http://docs.aws.amazon.com/lake-formation/latest/dg/images/data-filter-sample-pharma.png)
1. Choose **Create filter**.
**To create a data filter with cell-filter policies on a nested field**
This section uses the following sample schema to show how to create a data cells filter:
```
[
{ name: "customer", type: "struct" },
{ name: "customerApplication", type: "struct" },
{ name: "product", type: "struct,type:string>" },
{ name: "purchaseId", type: "string" },
]
```
1. On the **Create a data filter**, page enter a name for the data filter.
1. Next, use the drop-down to choose a database name and table name.
1. In the **Column-level access** section, choose Included columns, and select a nested column (`customer.customerName`).
1. In the **Row-level access** section, choose the **Access to all rows** option.
1. Choose **Create filter**.
When you grant `SELECT` permission on this filter, the principal gets access to all rows in the `customerName` column.
1. Next, define another data filter for the same database/table.
1. In the **Column-level access** section, choose Included columns, and select another nested column (`customer.customerid`).
1. In the **Row-level access** section, choose **Filter rows**, and enter a **Row filter expression** (`customer.customerid <> 5`).
1. Choose **Create filter**.
When you grant `SELECT` permission on this filter, the principal receives access to all rows in the `customerName`, and `customerId` fields except the cell where the value is 5 in the `customerId` column.
# Granting data filter permissions
You can grant the `SELECT`, `DESCRIBE` and `DROP` Lake Formation permissions on data filters to principals.
At first, only you can view the data filters that you create for a table. To enable another principal to view a data filter and grant Data Catalog permissions with the data filter, you must either:
+ Grant `SELECT` on a table to the principal with the grant option, and apply the data filter to the grant.
+ Grant the `DESCRIBE` or `DROP` permission on the data filter to the principal.
You can grant the `SELECT` permission to an external AWS account. A data lake administrator in that account can then grant that permission to other principals in the account. When granting to an external account, you must include the grant option so that administrator of the external account can further cascade the permission to other users in his/her account. When granting to a principal in your account, granting with the grant option is optional.
You can grant and revoke permissions on data filters by using the AWS Lake Formation console, the API, or the AWS Command Line Interface (AWS CLI).
------
#### [ Console ]
1. Sign in to the AWS Management Console and open the Lake Formation console at [https://console.aws.amazon.com/lakeformation/](https://console.aws.amazon.com/lakeformation/).
1. In the navigation pane, under **Permissions**, choose **Data lake permissions**.
1. On the **Permissions** page, in the **Data permissions** section, choose **Grant**.
1. On the **Grant data permissions** page, choose the principals to grant the permissions to.
1. In the LF-Tags or catalog resources section, choose **Named data catalog resources**. Then choose the database, table, and data filter for which you want to grant permissions.
![\[\]](http://docs.aws.amazon.com/lake-formation/latest/dg/images/grant-data-filter-perms-step2.png)
1. In the **Data filter permissions** section, choose the permissions you want to grant to the selected principals.
![\[\]](http://docs.aws.amazon.com/lake-formation/latest/dg/images/grant-perms-on-filters.png)
------
#### [ AWS CLI ]
+ Enter a `grant-permissions` command. Specify `DataCellsFilter` for the `resource` argument, and specify `DESCRIBE` or `DROP` for the `Permissions` argument and, optionally, for the `PermissionsWithGrantOption` argument.
The following example grants `DESCRIBE` with the grant option to user `datalake_user1` on the data filter `restrict-pharma`, which belongs to the `orders` table in the `sales` database in AWS account 1111-2222-3333.
```
aws lakeformation grant-permissions --cli-input-json file://grant-params.json
```
The following are the contents of file `grant-params.json`.
```
{
"Principal": {"DataLakePrincipalIdentifier": "arn:aws:iam::111122223333:user/datalake_user1"},
"Resource": {
"DataCellsFilter": {
"TableCatalogId": "111122223333",
"DatabaseName": "sales",
"TableName": "orders",
"Name": "restrict-pharma"
}
},
"Permissions": ["DESCRIBE"],
"PermissionsWithGrantOption": ["DESCRIBE"]
}
```
------
# Granting data permissions provided by data filters
Data filters represent a subset of data within a table. To provide data access to principals, `SELECT` permissions need to be granted to those principals. With this permission the principals can:
+ View the actual table name in list of tables shared with their account.
+ Create data filters on the shared table and grant permissions to their users on those data filters.
------
#### [ Console ]
**To grant SELECT permissions**
1. Go to the **Permissions** page in the Lake Formation console, and then choose **Grant**.
![\[\]](http://docs.aws.amazon.com/lake-formation/latest/dg/images/permissions-grant-action.png)
1. Select the principals you want to provide access to, and select **Named data catalog resources**.
![\[\]](http://docs.aws.amazon.com/lake-formation/latest/dg/images/grant-data-filter-perms-step2.png)
1. To provide access to the data that the filter represents, choose **Select** under **Data filter permissions**.
![\[\]](http://docs.aws.amazon.com/lake-formation/latest/dg/images/grant-data-filter-perms-step3.png)
------
#### [ CLI ]
Enter a `grant-permissions` command. Specify `DataCellsFilter` for the resource argument, and specify `SELECT` for the Permissions argument.
The following example grants `SELECT` with the grant option to user `datalake_user1` on the data filter `restrict-pharma`, which belongs to the `orders` table in the `sales` database in AWS account `1111-2222-3333`.
```
aws lakeformation grant-permissions --cli-input-json file://grant-params.json
```
The following are the contents of file `grant-params.json`.
```
{
"Principal": {
"DataLakePrincipalIdentifier": "arn:aws:iam::111122223333:user/datalake_user1"
},
"Resource": {
"DataCellsFilter": {
"TableCatalogId": "111122223333",
"DatabaseName": "sales",
"TableName": "orders",
"Name": "restrict-pharma"
}
},
"Permissions": ["SELECT"]
}
```
------
# Viewing data filters
You can use the Lake Formation console, AWS CLI, or the Lake Formation API to view data filters.
To view data filters, you must be a Data Lake administrator or have the required permissions on the data filters.
------
#### [ Console ]
1. Sign in to the AWS Management Console and open the Lake Formation console at [https://console.aws.amazon.com/lakeformation/](https://console.aws.amazon.com/lakeformation/).
1. In the navigation pane, under **Data catalog**, choose **Data filters**.
The page displays the data filters you have access to.
![\[The Data filters page displays the available data filters with the following columns: Filter name, Table, Database, and Table catalog ID. The screenshot shows a single data filter with the following values: test-df, cloudtrailtest_cloudtrail, lakeformation_cloudtrail, redacted account ID. Above the table there are four buttons (from left to right): Refresh/reload, View (grayed out), Delete (grayed out), and "Create new filter". There is also a search field, which is empty.\]](http://docs.aws.amazon.com/lake-formation/latest/dg/images/list-data-filters.jpg)
1. To view the data filter details, choose the data filter, and then choose View. A new window appears with the data filter detailed information.
![\[The "View data filter" window shows additional information about the selected data filter. The information displayed includes the name, database, table, column-level access setting, row filter expression, and the columns.\]](http://docs.aws.amazon.com/lake-formation/latest/dg/images/list-data-filters-details.jpg)
------
#### [ AWS CLI ]
Enter a `list-data-cells-filter` command and specify a table resource.
The following example lists the data filters for the `cloudtrailtest_cloudtrail` table.
```
aws lakeformation list-data-cells-filter --table '{ "CatalogId":"123456789012",
"DatabaseName":"lakeformation_cloudtrail", "Name":"cloudtrailtest_cloudtrail"}'
```
------
#### [ API/SDK ]
Use the `ListDataCellsFilter` API and specify a table resource.
The following example uses Python to list the first 20 data filters for the `myTable` table.
```
response = client.list_data_cells_filter(
Table = {
'CatalogId': '111122223333',
'DatabaseName': 'mydb',
'Name': 'myTable'
},
MaxResults=20
)
```
------
# Listing data filter permissions
You can use the Lake Formation console to view the permissions granted on data filters.
To view permissions on a data filter, you must be a Data Lake administrator or have the required permissions on the data filter.
------
#### [ Console ]
1. Sign in to the AWS Management Console and open the Lake Formation console at [https://console.aws.amazon.com/lakeformation/](https://console.aws.amazon.com/lakeformation/).
1. In the navigation pane, under **Permissions**, choose **Data permissions**.
1. On the **Data Permissions** page, click or tap in the search field, and on the **Properties** menu, choose **Resource type**.
1. On the **Resource type** menu, choose **Resource type: Data cell filter**.
The data filters that you have permissions on are listed. You might have to scroll horizontally to see the **Permissions** and **Grantable** columns.
![\[The Data Permissions page displays a table of permissions with the following columns: Principal, Resource type, Database, Table, Resource, Catalog, and Permissions. The Resource type column shows "Data cell filter" in all four rows. The permissions for the first and second rows are Describe, Drop, and Select. The permissions for the third row is Describe. Above the table is a Clear filter button and a tile indicating that the current search is for Resource type: Data cell filter. Above those is a search (text) field, and above that are Refresh, Revoke, and Grant buttons.\]](http://docs.aws.amazon.com/lake-formation/latest/dg/images/data-permissions-cell-filters.png)
------
#### [ AWS CLI ]
+ Enter a `list-permissions` command. Specify `DataCellsFilter` for the `resource` argument, and specify `DESCRIBE` or `DROP` for the `Permissions` argument and, optionally, for the `PermissionsWithGrantOption` argument.
The following example lists `DESCRIBE` permissions with the grant option on the data filter `restrict-pharma`. The results are limited to permissions granted for the principal `datalake_user1` and the `orders` table in the `sales` database in AWS account 1111-2222-3333.
```
aws lakeformation list-permissions --cli-input-json file://list-params.json
```
The following are the contents of file `grant-params.json`.
```
{
"Principal": {"DataLakePrincipalIdentifier": "arn:aws:iam::111122223333:user/datalake_user1"},
"Resource": {
"DataCellsFilter": {
"TableCatalogId": "111122223333",
"DatabaseName": "sales",
"TableName": "orders",
"Name": "restrict-pharma"
}
},
"Permissions": ["DESCRIBE"],
"PermissionsWithGrantOption": ["DESCRIBE"]
}
```
------