Data filtering limitations
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.
Keep in mind the following notes and restrictions for row-level and cell-level filtering.
-
Cell-level security is not supported on nested columns, views, and resource links.
-
All expressions that are supported on top level columns are also supported on nested columns. However, nested fields under partition columns should NOT be referenced when defining nested row-level expressions.
-
Cell-level security is available in all regions when using Athena engine version 3 or Amazon Redshift Spectrum. For other services, cell-level security is only available in the regions mentioned on the Supported Regions.
-
SELECT INTO
statements are not supported. -
The
array
, andmap
data types aren't supported in row filter expressions. Thestruct
data type is supported. -
There is no limit to the number of data filters that can be defined on a table, but there is a limit of 100 data filter
SELECT
permissions for a single principal on a table. -
The maximum number of data filters that can be included in a grant on a table is 10.
-
To apply a data filter with a row filter expression, you must have
SELECT
with the grant option on all table columns. This restriction doesn't apply to administrators in external accounts when the grant was made to the external account. -
If a principal is a member of a group and both the principal and the group are granted permissions on a subset of rows, the principal's effective row permissions are the union of the principal's permissions and the group's permissions.
-
The following column names are restricted in a table for row-level and cell-level filtering:
ctid
oid
xmin
cmin
xmax
cmax
tableoid
insertxid
deletexid
importoid
redcatuniqueid
-
If you apply the all-rows filter expression on a table concurrently with other filter expressions with predicates, the all-rows expression will prevail over all other filter expressions.
-
When permissions on a subset of rows are granted to an external Amazon account and the data lake administrator of the external account grants those permissions to a principal in that account, the principal's effective filter predicate is the intersection of the account's predicate and any predicate that was directly granted to the principal.
For example, if the account has row permissions with the predicate
dept='hr'
and the principal was separately granted permission forcountry='us'
, the principal has access only to rows withdept='hr'
andcountry='us'
.
For more information about cell-level filtering, see Data filtering and cell-level security in Lake Formation.