What you will be able to do
- Explain how a row filter UDF and a column mask UDF decide what a user sees at query time
- Write filter and mask functions that change visibility by group membership with IS_ACCOUNT_GROUP_MEMBER
- Apply, replace and remove table-level filters and masks with ALTER TABLE, in the correct order and with the correct privileges
- Spot the data-type mismatch that silently disables a row filter
Key concept
Row filters and column masks — Two Unity Catalog controls, each backed by a SQL UDF, that the engine applies every time a table is queried. A row filter decides which rows the user gets back. A column mask decides whether the user sees a column's real value or a masked one. The table data itself is never changed.
1.Two UDF contracts: one decides rows, one decides values
Unity Catalog has two controls for fine-grained visibility, and both work the same basic way. You write a SQL user-defined function (UDF), register it in Unity Catalog, and attach it to a table. The function then runs at query time for whoever is querying. It does not run once when the data is loaded. So one table can show different results to different users without any copies of the data.
A row filter is how you get row-level security. Its UDF looks at each row and returns a boolean, and any row where it returns FALSE is dropped. Typical uses limit users to the records for one region, department or account. A table can have only one row filter. The function takes zero or more parameters, and each one is bound to a column of the table.
A column mask is how you get column-level masking. Its UDF takes the column value as its input and returns either the original value or a masked version. The return type must match the column's data type or be castable to it. Each column can have only one mask. A mask can also take other columns as extra inputs, so its output can depend on more than one attribute of the row.
| Aspect | Row filter | Column mask |
|---|---|---|
| What the UDF returns | A boolean; rows returning FALSE are excluded | The original value or a masked version, castable to the column type |
| How many per object | One per table | One per column |
| Bind to an existing table | ALTER TABLE ... SET ROW FILTER | ALTER TABLE ... ALTER COLUMN ... SET MASK |
| Typical use | Row-level security by region, department or account | Redacting sensitive values in specific columns |
Checkpoint 1 of 5· Check yourself
A data engineer wants to attach a second row filter to a table that already has one, so that one filter handles region and the other handles department. What does Unity Catalog allow?
A table can have only one row filter. To use several conditions, combine them inside one UDF, for example with AND.
“Each table can have only one row filter.”Source: docs.databricks.com
Checkpoint 2 of 5· Exam question
A data engineer must ensure that sales representatives in the `orders` table only see rows for the region stored in their Unity Catalog group membership, while managers see all regions. What is the correct way to implement this with a per-table row filter?
Correct answer: A — Create a `BOOLEAN` UDF checking `is_account_group_member()` against the row's region, then apply it with `ALTER TABLE orders SET ROW FILTER region_filter ON (region)`
- A. This is the documented pattern for Unity Catalog row filters: the UDF must return `BOOLEAN`, evaluating `is_account_group_member()` for the caller against the row's region, and `SET ROW FILTER` attaches it to the table so rows failing the check are excluded.
- B. A dynamic view can approximate row-level filtering, but it requires managing a separate view object and re-granting privileges, which is not the native row-filter mechanism the exam scope calls for and adds ongoing maintenance overhead.
- C. Returning the region as a `STRING` and applying `SET MASK` invokes the column-masking mechanism, which transforms a column's displayed value; it does not filter out entire rows, so unauthorized rows would still be returned with their region value unmasked.
- D. Unity Catalog `CHECK` constraints validate data on write and cannot reference the querying user's identity or group membership, so they cannot implement per-user row visibility at query time.
Sources1
2.Making visibility depend on who is asking
A filter or mask only restricts visibility by user group if the function checks who is calling it. Inside the function body you can use any built-in function. Two matter most here: session_user() returns the identity of the invoking user, and is_account_group_member() tests whether that user belongs to a group. The usual pattern is an IF that returns full access to a privileged group and restricted output to everyone else.
In the row filter below, members of the admin group get true for every row, so they see the whole table. Everyone else falls through to region='US' and sees only US rows.
Checkpoint 3 of 5· Fill the gap
Which function completes this row filter so that admins see every row and everyone else sees only US rows?
CREATE FUNCTION us_filter(region STRING)
RETURN IF( ? ('admin'), true, region='US');IS_ACCOUNT_GROUP_MEMBER('admin') returns true when the invoking user is in the admin group, which short-circuits the filter to true for every row. SESSION_USER returns a user name, not a boolean group test.
Source: docs.databricks.comColumn masks follow the same pattern. The first parameter of a masking function is always the masked column itself. To pass in anything more, such as another column or a constant, add USING COLUMNS when you attach the mask. The example below works out the group name from the row's own country value. A member of US_address_viewers sees US addresses in full, and every other address comes back as REDACTED.
CREATE FUNCTION mask_address_by_country(address STRING, country STRING, group_suffix STRING DEFAULT '_address_viewers')
RETURN IF(
is_account_group_member(country || group_suffix),
address,
'REDACTED'
);Some rules don't fit in a few group names, such as organizational hierarchies or security models copied from another system. For these, the docs suggest a mapping table: an access-control list stating which rows each user or group may see, which the filter function checks with EXISTS against SESSION_USER(). Filters run with the definer's rights. The exception is the functions that check user context, such as SESSION_USER and IS_ACCOUNT_GROUP_MEMBER, which run as the invoker.
The function you attach must be a SQL UDF. If you want Python logic, write a SQL UDF that wraps the Python UDF and attach the SQL wrapper.
It fails with a [ROUTINE_NOT_FOUND] error. Create a SQL UDF (for example email_mask_sql) that calls the Python function, and apply the SQL wrapper as the column mask.
Checkpoint 4 of 5· Exam question
An engineer needs to mask the `ssn` column in the `employees` table so that only members of the `hr_admin` group see the full value, while everyone else sees the last four digits redacted with asterisks. Which approach correctly implements this?
Correct answer: A — Define a UDF returning `ssn` unmasked for `hr_admin` members, otherwise a redacted string, applied with `ALTER COLUMN ssn SET MASK ssn_mask`
- A. This correctly uses a column mask: the UDF returns a value of the same type as `ssn`, conditionally revealing or redacting it based on group membership, and `SET MASK` attaches that transformation to the column for every query against the table.
- B. `SET ROW FILTER` expects a `BOOLEAN`-returning UDF and removes whole rows rather than transforming a column's value, so it cannot selectively redact digits within the `ssn` column while keeping the row visible.
- C. Unity Catalog privileges are granted at the table level, not per column, so `GRANT`/`DENY` cannot expose a column to one group while hiding it from another on the same table.
- D. A separate hashed materialized view produces an irreversible value for everyone, so `hr_admin` users could no longer retrieve the real SSN, and it requires users to remember to query a different object entirely.
Sources2
3.Applying, replacing and removing filters and masks safely
You attach a filter or mask with ALTER TABLE on an existing table, or with WITH ROW FILTER / MASK inside CREATE TABLE. Catalog Explorer can do the same from the table's Overview tab. To assign the function you need EXECUTE on it, plus USE SCHEMA and USE CATALOG on its parents. To add or remove a filter or mask on an existing table, you must be the table owner or hold both MANAGE and SELECT on the table.
Reading a protected table also depends on the compute. A SQL warehouse works. So does standard access mode on Databricks Runtime 12.2 LTS or above, and dedicated access mode on 15.4 LTS or above. Dedicated compute on 15.3 or below cannot read these tables.
The controls are designed to stay in place. If you run REPLACE TABLE, the row filter is kept even when the schema changes. Masks are kept for any column whose name survives the replace. Removal, though, has to happen in a fixed order.
Checkpoint 5 of 5· Put it in order
Put these steps in order for creating, applying and later fully deleting a row filter
- 1.ALTER TABLE sales SET ROW FILTER us_filter ON (region);
- 2.ALTER TABLE sales DROP ROW FILTER;
- 3.DROP FUNCTION us_filter;
- 4.CREATE FUNCTION us_filter(region STRING) ...
The function has to exist before you can bind it. When deleting, detach the filter from the table first. Dropping a function that a table still references leaves the table inaccessible.
“You must perform the ALTER TABLE ... DROP ROW FILTER command before you drop the function.”Source: docs.databricks.com
-- Bug: parameter type is INT, but the column is STRING
CREATE FUNCTION salary_filter(dept INT) RETURNS BOOLEAN
RETURN dept IS NULL;
ALTER TABLE employees SET ROW FILTER salary_filter ON (department);The fix is to declare dept STRING so the parameter type matches the column. Databricks also recommends turning on ANSI mode (spark.sql.ansi.enabled = true). Then a failed cast raises an error you can see, instead of turning into a NULL that quietly disables your security.
Sources2
Exam traps
Each one states something that sounds right. Open it to see what is actually true.
1.To remove a row filter, it's enough to drop the UDF that implements it.Why is that wrong?
Detach the filter with ALTER TABLE ... DROP ROW FILTER first. Dropping the function while the table still references it leaves the table inaccessible.
Covered in Applying, replacing and removing filters and masks safely
2.A Python UDF can be attached directly as a column mask.Why is that wrong?
Only a SQL UDF can be attached. Wrap the Python UDF in a SQL UDF; attaching the Python UDF directly fails.
Covered in Making visibility depend on who is asking
3.A type mismatch between a filter parameter and its column always raises an error.Why is that wrong?
With ANSI mode off, values that cannot be cast silently become NULL. The filter can then return the wrong rows without any error.
Covered in Applying, replacing and removing filters and masks safely
Sources
Every claim above is drawn from one of these pages, quoted as it was written on the date shown.
- 1.
“The mask is a SQL UDF that takes the column value as input and returns the original value or a masked version.”
↩︎ Two UDF contracts: one decides rows, one decides values“Each column can have one mask.”
↩︎ Two UDF contracts: one decides rows, one decides values“Row filters and column masks are Unity Catalog access controls that restrict the rows and column values a user can see at query time”
↩︎ Key concept“With ANSI mode disabled (spark.sql.ansi.enabled = false), uncastable values are silently converted to NULL, no error is raised”
↩︎ Exam trap 3“Rows where the function returns FALSE are excluded from query results.”
↩︎ Prediction“such as a row filter that returns all rows instead of filtering them”
↩︎ Prediction - 2.https://docs.databricks.com/aws/en/data-governance/unity-catalog/filters-and-masks/manually-applyOfficial docs
“members of the admin group can access all records in the table”
↩︎ Making visibility depend on who is asking“The first parameter of the masking function always maps to the masked column itself.”
↩︎ Making visibility depend on who is asking“All filters run with definer's rights except for functions that check user context”
↩︎ Making visibility depend on who is asking“A comprehensive mapping table encodes which data rows in the original table are accessible to certain users or groups.”
↩︎ Making visibility depend on who is asking“you must be the table owner, or have both the MANAGE and SELECT privileges on the table.”
↩︎ Applying, replacing and removing filters and masks safely“you must have the EXECUTE privilege on the function, USE SCHEMA on the schema, and USE CATALOG on the parent catalog.”
↩︎ Applying, replacing and removing filters and masks safely“Dedicated access mode (formerly single user access mode) on Databricks Runtime 15.4 LTS or above.”
↩︎ Applying, replacing and removing filters and masks safely“If you run REPLACE TABLE, any existing row filter is retained regardless of schema changes.”
↩︎ Applying, replacing and removing filters and masks safely“If you don't, the table will be in an inaccessible state.”
↩︎ Exam trap 1“If you try to use the Python UDF (email_mask_python) directly as a column mask, you will receive a [ROUTINE_NOT_FOUND] error.”
↩︎ Exam trap 2“Each table can have only one row filter.”
↩︎ Checkpoint“You must perform the ALTER TABLE ... DROP ROW FILTER command before you drop the function.”
↩︎ Checkpoint