Skip to content
Cheat sheets

Data Engineer Associate · Domain 7: Governance and Security · 15% of the exam

Task 7.3: Understand column-level masking and row-level security to restrict data visibility based on user groups.

SQL user-defined functions as row filters and column masks, is_account_group_member, applying them with ALTER TABLE, and dynamic views.

Study it

  • Row filters, column masks and dynamic views

    Lesson coming

Sample questions

Try each one before opening the answer. Every option is explained, with the Databricks documentation page that proves it.

Question 1 · choose 1

A table owner creates one function for several regional tables: CREATE FUNCTION main.sec.region_rf(region STRING, allowed STRING) RETURN region = allowed; The table main.sales.orders_eu must return only rows whose region is 'EU', and main.sales.orders_us only rows whose region is 'US', both from this one function. Which statement applies it to main.sales.orders_eu?

  1. AALTER TABLE main.sales.orders_eu SET ROW FILTER main.sec.region_rf ON (region)
  2. BALTER TABLE main.sales.orders_eu SET ROW FILTER main.sec.region_rf ON (region, 'EU')
  3. CALTER TABLE main.sales.orders_eu SET ROW FILTER main.sec.region_rf('EU') ON (region)
  4. DALTER TABLE main.sales.orders_eu ADD CONSTRAINT eu_only CHECK (main.sec.region_rf(region, 'EU'))
Show the answer and why

  • AALTER TABLE main.sales.orders_eu SET ROW FILTER main.sec.region_rf ON (region)

    Incorrect

    The ON list must provide as many arguments as the function's signature requires; region_rf takes two parameters and this passes one.

  • BALTER TABLE main.sales.orders_eu SET ROW FILTER main.sec.region_rf ON (region, 'EU')

    Correct

    The ON list can mix column names and constant literals whose type matches a function parameter, so region binds to the column and 'EU' to allowed.

  • CALTER TABLE main.sales.orders_eu SET ROW FILTER main.sec.region_rf('EU') ON (region)

    Incorrect

    The clause names the function without arguments; every argument, column or constant literal alike, goes in the ON list.

  • DALTER TABLE main.sales.orders_eu ADD CONSTRAINT eu_only CHECK (main.sec.region_rf(region, 'EU'))

    Incorrect

    A CHECK constraint tests rows that are written, not rows that are read, and its expression cannot use user-defined functions.

A row filter is a SQL UDF that returns BOOLEAN, applied with ALTER TABLE ... SET ROW FILTER ... ON (...). Each entry in the ON list is a column name or a constant literal of a supported type, so one function can serve several tables with a different constant on each: ON (region, 'EU') here and ON (region, 'US') on the other table.

Databricks documentation

Task 7.3Report a problem with this question

Question 2 · choose 1

The ssn column of main.hr.people must show the full value to members of the account group hr and '***-**-****' to everyone else. The table owner has created main.sec.mask_ssn(ssn STRING), which returns ssn for hr members and the mask otherwise. Which statement applies it?

  1. AALTER TABLE main.hr.people SET ROW FILTER main.sec.mask_ssn ON (ssn)
  2. BALTER TABLE main.hr.people ADD CONSTRAINT ssn_masked CHECK (main.sec.mask_ssn(ssn) IS NOT NULL)
  3. CALTER TABLE main.hr.people DROP COLUMN ssn
  4. DALTER TABLE main.hr.people ALTER COLUMN ssn SET MASK main.sec.mask_ssn
Show the answer and why

  • AALTER TABLE main.hr.people SET ROW FILTER main.sec.mask_ssn ON (ssn)

    Incorrect

    A row filter removes rows; it needs a boolean function and does not change column values.

  • BALTER TABLE main.hr.people ADD CONSTRAINT ssn_masked CHECK (main.sec.mask_ssn(ssn) IS NOT NULL)

    Incorrect

    CHECK constraints validate writes, cannot call user-defined functions and do not change what readers see.

  • CALTER TABLE main.hr.people DROP COLUMN ssn

    Incorrect

    Dropping the column removes the data for hr members as well.

  • DALTER TABLE main.hr.people ALTER COLUMN ssn SET MASK main.sec.mask_ssn

    Correct

    SET MASK binds the function to the column, so each query returns the function's result for the reader.

A column mask is a SQL function that returns the original or a masked value of the column's type, applied with ALTER COLUMN ... SET MASK.

Databricks documentation

Task 7.3Report a problem with this question

Question 3 · choose 1

A row filter function on a Unity Catalog table must check whether the querying user belongs to the finance group, which is managed at the account level. Which function should it call?

  1. Ais_member('finance')
  2. Bis_account_group_member('finance')
  3. Ccurrent_user() = 'finance'
  4. Dsession_user() = 'finance'
Show the answer and why

  • Ais_member('finance')

    Incorrect

    is_member tests groups as the workspace sees them; Databricks advises is_account_group_member for testing account-level membership.

  • Bis_account_group_member('finance')

    Correct

    This returns true when the session user is a direct or indirect member of the account-level group.

  • Ccurrent_user() = 'finance'

    Incorrect

    current_user returns the user's name, which never equals a group name.

  • Dsession_user() = 'finance'

    Incorrect

    session_user also returns the connected user, not their groups.

For Unity Catalog row filters, column masks and dynamic views, test group membership with is_account_group_member.

Databricks documentation

Task 7.3Report a problem with this question

Question 4 · choose 1

A partner group must see a joined, curated version of two tables, with email addresses redacted unless the user is in auditors. The group must not be able to read the two base tables. Which approach fits?

  1. ARow filters and column masks on both base tables, with SELECT on the tables granted to the group
  2. BAn ABAC column mask policy on the catalog, with SELECT on the base tables granted to the group
  3. CA dynamic view over the join using is_account_group_member, granted to the group
  4. DA nightly job that copies the joined data, with emails masked, into a new table
Show the answer and why

  • ARow filters and column masks on both base tables, with SELECT on the tables granted to the group

    Incorrect

    The group would then have access to the base tables, which the requirement rules out.

  • BAn ABAC column mask policy on the catalog, with SELECT on the base tables granted to the group

    Incorrect

    ABAC also protects base tables that the group would still be able to query.

  • CA dynamic view over the join using is_account_group_member, granted to the group

    Correct

    Dynamic views expose a curated or joined version of data with masking logic, to users who lack access to the underlying tables.

  • DA nightly job that copies the joined data, with emails masked, into a new table

    Incorrect

    A copy goes stale between runs and duplicates the data.

Dynamic views suit fine-grained access over several tables or reshaped data; row filters and column masks protect individual tables in place.

Databricks documentation

Task 7.3Report a problem with this question

Question 5 · choose 1

A table owner ran DROP FUNCTION on a function that was still set as a table's row filter. Now every query on the table fails. How should the owner fix the table?

  1. ARun ALTER TABLE ... DROP ROW FILTER on the table
  2. BRun UNDROP FUNCTION on the function
  3. CRecreate the table with CREATE OR REPLACE TABLE
  4. DRevoke EXECUTE on the function from the account users group
Show the answer and why

  • ARun ALTER TABLE ... DROP ROW FILTER on the table

    Correct

    Dropping the orphaned row filter reference makes the table accessible again; the filter should be dropped before the function.

  • BRun UNDROP FUNCTION on the function

    Incorrect

    UNDROP has no form for functions, so the dropped function cannot be brought back this way.

  • CRecreate the table with CREATE OR REPLACE TABLE

    Incorrect

    Replacing a table keeps its existing row filter, so the broken reference would remain.

  • DRevoke EXECUTE on the function from the account users group

    Incorrect

    The function no longer exists, and privileges do not repair the table's reference to it.

Remove a row filter with ALTER TABLE ... DROP ROW FILTER before dropping its function; doing it in the other order leaves the table inaccessible.

Databricks documentation

Task 7.3Report a problem with this question

Practise domain 7 →Practise all domains →