Dynamic Views for Access Control

1. User-Specific Permissions with current_user()

While group-based permissions are efficient, some security rules need to be specific to the individual user. For example, a rule might state, "Sales reps can only see the customer accounts they personally manage."

This is where the current_user() function comes in. It's a simple yet powerful function that returns the email address of the user executing the query.

A common and robust pattern is to use current_user() to look up a user's permissions in a separate metadata table. This decouples your security logic from your views, making it easier to manage permissions by simply updating the metadata table.

Let's watch a detailed walkthrough of this pattern.

This approach of using a mapping table is highly flexible. When a user's access rights change, you only need to INSERT, UPDATE, or DELETE a row in your permissions table, and all the views that use the logic will reflect the change immediately without needing to be redefined.

2. Understanding is_member() vs. is_account_group_member()

In the previous lesson, we used is_account_group_member(). You may also encounter is_member(). It's crucial to understand the difference.

• is_account_group_member('group_name'): Checks if the user is in an account-level group. These groups are defined in the account console and can span multiple workspaces. This is the recommended function for Unity Catalog, as UC is an account-level service.
• is_member('group_name'): Checks if the user is in a workspace-level group (sometimes called a "local" group). This function is provided for compatibility with older Databricks environments that used the Hive Metastore.

You should always prefer is_account_group_member() for new security policies in Unity Catalog to ensure your governance model is consistent and portable across your entire Databricks account.

Although is_account_group_member() is preferred, the mechanics of using is_member() in a query are identical. The following example demonstrates a common pattern where the group name itself is stored as a column in the data.

3. Hands-On Practice: Combining Logic

Now, let's combine these concepts to build a view with a more complex security policy. In this exercise, you will create a view for a tasks table with the following rules:

1. All users can see tasks that are assigned directly to them.
2. Members of the project_managers group can see all tasks for all projects.

Instructions:

1. In a new SQL notebook, ensure you are using a catalog and schema where you have permission to create objects.

2. Setup: Run the following SQL to create a sample table and an account-level group.

   -- Create a sample tasks table
   CREATE OR REPLACE TABLE tasks (
     task_id INT,
     project_name STRING,
     task_description STRING,
     assigned_to STRING -- User email
   );
   
   -- Insert sample data. Replace 'your.email@example.com' with your actual Databricks user email.
   INSERT INTO tasks VALUES
     (101, 'Project Alpha', 'Design database schema', 'data.arch@example.com'),
     (102, 'Project Alpha', 'Develop ETL pipeline', 'your.email@example.com'),
     (103, 'Project Beta', 'Create dashboard mockups', 'ui.ux@example.com'),
     (104, 'Project Alpha', 'Deploy to production', 'devops@example.com'),
     (105, 'Project Beta', 'User acceptance testing', 'your.email@example.com');
     
   -- Create the group in your account console if you are an admin.
   -- If you are not an admin, you can simulate this by asking an admin to create the group
   -- and add you to it.
   -- In the Account Console > User management > Groups, create a group named 'project_managers'.
   -- Add your user to this group.
   

   Note: If you don't have permissions to create account-level groups, you can still write the query. For testing, you can temporarily remove the is_account_group_member condition to verify that the current_user() logic works.

3. Task: Create the Dynamic View
   Create a view named project_tasks_secure that implements the two security rules described above. Use an OR condition in your WHERE clause.

   Click here for the solution

   CREATE OR REPLACE VIEW project_tasks_secure AS
   SELECT
     *
   FROM
     tasks
   WHERE
     -- Rule 1: Users can see tasks assigned to them
     assigned_to = current_user()
     OR
     -- Rule 2: Project managers can see all tasks
     is_account_group_member('project_managers');
   

4. Verification:
   Query your new view: SELECT * FROM project_tasks_secure;

   • Since you are a member of the project_managers group, you should see all 5 tasks.
   • If you were to be removed from the project_managers group (you can ask an admin to do this for testing), and then re-ran the query, you would only see the two tasks assigned to your email address (task_ids 102 and 105).

Conclusion

In this lesson, you've mastered the use of current_user() and is_member() to implement sophisticated, fine-grained access control with dynamic views. This level of control is essential for building a secure and compliant data lakehouse.

Key Takeaways

• The current_user() function is your tool for implementing security rules based on an individual's identity, which is perfect for "user-can-see-their-own-data" scenarios.
• Using current_user() with a separate mapping table creates a flexible and easily manageable security system.
• is_account_group_member() checks against account-level groups and is the recommended function for Unity Catalog.
• is_member() checks against workspace-local groups and is mainly for backward compatibility.
• You can combine these functions using logical operators (AND, OR) in a WHERE clause to enforce complex business rules.

Next Steps

Now that we know how to grant permissions and define fine-grained access policies on our data, the next logical question is: "How did this data get here, and what has happened to it?" The next lesson, Trace data lineage for tables and columns using the Unity Catalog UI, will answer exactly that. We'll explore how Unity Catalog automatically captures and visualizes the lineage of your data assets.