Table-Driven Access Policies Using Subqueries

Introduction

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.
CREATE FUNCTION hide_undercover (val VARCHAR) RETURNS BOOLEAN RETURN
    SELECT
        CASE
            WHEN USER() IN (SELECT name FROM $scratch.authorized_users) THEN TRUE
            WHEN val = 'undercover' THEN FALSE
            ELSE TRUE
        END;
-- 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');
SELECT USER();
-- [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.