4 minute read · October 12, 2023

Table-Driven Access Policies Using Subqueries

Albert Vernon

Albert Vernon · Senior Product Manager, Dremio


User-defined functions (UDFs) are callable routines that make it easier for you to write and reuse SQL logic across queries. In Dremio, you can use UDFs as row-access and column-masking policies.

Dremio Cloud and Dremio Software v24.1+ allow you to run subqueries from UDFs. Subqueries are queries nested inside other queries and are often used with the IN and EXISTS operators.

The remainder of this article describes how to create table-driven access policies in Dremio using UDFs and subqueries.

Table-Driven Access Policies Using Subqueries

As described in New: Row-Level and Column-Level Access Controls, UDFs can serve as access-control policies. The examples in that blog article used QUERY_USER or IS_MEMBER to control access to rows and columns depending on who is running the query. The hide_undercover UDF hard-coded role names into its function body:

WHEN IS_MEMBER('chief') OR IS_MEMBER('captain')

However, it can be convenient to control access dynamically by inserting and removing rows from a table. Being able to call subqueries from UDFs allows you to test for the existence of rows in a table. What follows is a reworked example from the above blog article that uses a subquery instead of IS_MEMBER.

As in the blog article's example, there is a table named officers with a column called assignment. The UDF hide_undercover protects the identities of undercover officers unless the current user is permitted to view them. Another table, authorized_users, lists which users are allowed to see the undercover officers.

-- Set up the authorized users list.
CREATE TABLE $scratch.authorized_users (name VARCHAR);
INSERT INTO $scratch.authorized_users VALUES ('chief'), ('captain');
-- Create UDF to serve as the row-access policy.
            WHEN USER() IN (SELECT name FROM $scratch.authorized_users) THEN TRUE
            WHEN val = 'undercover' THEN FALSE
            ELSE TRUE
-- Set the UDF as a policy when creating a table.
CREATE TABLE $scratch.officers (
  	name VARCHAR,
  	assignment VARCHAR)
    ROW ACCESS POLICY hide_undercover(assignment);
-- You can also set the UDF as a policy when modifying a table.
ALTER TABLE $scratch.officers ADD ROW ACCESS POLICY hide_undercover(assignment);
-- Insert some test data.
INSERT INTO $scratch.officers VALUES ('Deckard', 'undercover'), ('Murphy', 'patrol');
-- [email protected]
-- Current user is not authorized, so the undercover officer is not in the results.
SELECT * from $scratch.officers;
-- Murphy, patrol
-- Authorizing the current user shows all officers in the results.
INSERT INTO $scratch.authorized_users VALUES ('[email protected]');
SELECT * from $scratch.officers;
-- Deckard, undercover
-- Murphy, patrol

Get Started with Dremio Cloud – It’s Free!

Dremio Cloud: The easy and open, fully managed data lakehouse platform.

Sign Up Now

Everything you need to build, automate, and query your data lakehouse in production.

Ready to Get Started?

Bring your users closer to the data with organization-wide self-service analytics and lakehouse flexibility, scalability, and performance at a fraction of the cost. Run Dremio anywhere with self-managed software or Dremio Cloud.