If a tree falls in a forest and no one is around to hear it, does it make a sound?
Problem statement. Filtering data based on our needs is a fundamental requirement in many applications. We rarely need to show all the data. Filtering is done by applying logical conditions on top of the data either on the application side (not recommended) or as part of SQL while retrieving the data. Filter logic is applied based on a particular task and often based on system and user interaction. For example, if a user wants data for a given year that (s)he enters into the application or a system requests logs for the last minute we apply correspondent filters into the query. In some cases, we also need extra filters that tied to the context, for example, user, application, date range, configuration etc. Those filters are then applied in addition to user/system conditions.
Here I show three examples of queries against three views. Highlighted red are conditions that are requested by the applications, where the remaining of the condition depends on the context of the request. Queries for the first example would look like
SELECT * FROM VIEW WHERE Year = 2018 AND LastUpdateDateTime BETWEEN DATEADD(day,DATEDIFF(day,0,GETDATE()),0) AND DATEADD(second,-1,DATEADD(day,DATEDIFF(day,0,GETUTCDATE())+1,0))
One common feature of these examples is that they all depend on some kind of anchor which defined as part session/transaction: current date, a user who made the request, application who made the request.
The problem is not that we cannot add these conditions. The problems are that
- we may need to duplicate it in more than one instance, hence violating the DRY principle.
- it is error-prone as the main view may return data for a larger result set that is required, so if somebody misses it or adds incorrect condition the result will be incorrect
In this post, we will review how can we hide the implementation of the context behind the view. I have described a more general pattern of abstraction here.
Contextual View. Contextual views return data that are not deterministic and depends on another state/anchor/context. So depending on implementation, the same view can return different data from user to user, from day to day, from database to database even if the underlined data has not been modified. That is the main premise. Filter conditions depend on the state of the session.
Ideally, I would like to see queries above as following with an assumption that data has not been updated between request.
This may be a bit counter-intuitive, however, I think, Oracle, for example, may be using similar, if not the same, technique to provide access to the schema specific data dictionaries.
Implementation is quite easy. All we need to do it to apply the filter logic that is tied to the state (using one of the contextual function, see the section below for full list) of the session. The rest is limited by the imagination of the developer.
It is important to mention that SQL Server has a powerful implementation of this pattern on engine level using Row-Level Security, which could be used to achieve similar results.
Demo. I have used this technique to hide sensitive data from the requester based on his/her branch of the organization (s)he worked. Higher the position, more data they can see. Below is a simple implementation of this pattern. First thing first we are going to setup table that represents organization structure, for simplicity I will skip full implementation of the entity and keep all the entities with bare minimum information.
--DROP TABLE BusinessUnit CREATE TABLE BusinessUnit (BusinessUnitId INT IDENTITY (1,1) NOT NULL, BusinessUnitName VARCHAR(255) NOT NULL, ParentBusinessUnitId INT NULL, CONSTRAINT PK_BusinessUnit PRIMARY KEY CLUSTERED (BusinessUnitId), CONSTRAINT PK_BusinessUnit_REF_BusinessUnit FOREIGN KEY (ParentBusinessUnitId) REFERENCES BusinessUnit(BusinessUnitId), CONSTRAINT UK_BusinessUnit UNIQUE (BusinessUnitName) ); CREATE FUNCTION fn_BusinessUnit_GetBusinessUnitId (@BusinessUnitName VARCHAR(255)) RETURNS INT AS BEGIN RETURN ( SELECT BusinessUnitId FROM BusinessUnit WHERE BusinessUnitName = @BusinessUnitName); END; INSERT INTO BusinessUnit(BusinessUnitName, ParentBusinessUnitId) VALUES ('Corporate', NULL); INSERT INTO BusinessUnit(BusinessUnitName, ParentBusinessUnitId) VALUES ('Midwest', dbo.fn_BusinessUnit_GetBusinessUnitId ('Corporate')); INSERT INTO BusinessUnit(BusinessUnitName, ParentBusinessUnitId) VALUES ('East', dbo.fn_BusinessUnit_GetBusinessUnitId ('Corporate')); INSERT INTO BusinessUnit(BusinessUnitName, ParentBusinessUnitId) VALUES ('Illinoice', dbo.fn_BusinessUnit_GetBusinessUnitId ('Midwest')); INSERT INTO BusinessUnit(BusinessUnitName, ParentBusinessUnitId) VALUES ('Indiana', dbo.fn_BusinessUnit_GetBusinessUnitId ('Midwest')); SELECT * FROM BusinessUnit
The next let’s create a table with users. Each user belongs to a single and only single business unit. I will also provide a database username associated with the user. In this case it is SQL user, however, if you have AD (or any other user management service) setup then this could be AD account.
--DROP TABLE ApplicationUser CREATE TABLE ApplicationUser( ApplicationUserId INT IDENTITY (1,1) NOT NULL, FirstName NVARCHAR(255) NOT NULL, LastName NVARCHAR(255) NULL, UserName VARCHAR(50) NOT NULL, BusinessUnitId INT NOT NULL, CONSTRAINT PK_ApplicationUser PRIMARY KEY (ApplicationUserId), CONSTRAINT PK_ApplicationUser_REF_BusinessUnit FOREIGN KEY (BusinessUnitId) REFERENCES BusinessUnit(BusinessUnitId), CONSTRAINT UK_ApplicationUser UNIQUE (UserName) ); INSERT INTO ApplicationUser ( FirstName, LastName, UserName, BusinessUnitId) VALUES ('Jane', 'Doe', 'jdoe01', dbo.fn_BusinessUnit_GetBusinessUnitId ('Corporate')), ('John', 'Doe', 'jdoe02', dbo.fn_BusinessUnit_GetBusinessUnitId ('Midwest')), ('John', 'Roe', 'jroe01', dbo.fn_BusinessUnit_GetBusinessUnitId ('Midwest')), ('Baby', 'Doe', 'bdoe01', dbo.fn_BusinessUnit_GetBusinessUnitId ('Illinoice')), ('Baby', 'Roe', 'broe01', dbo.fn_BusinessUnit_GetBusinessUnitId ('Illinoice')) SELECT * FROM ApplicationUser;
Now we have all the information to create our contextual view. Let’s say our goal is to provide access to the application user data, however, each user can only see the data either on the same node of organization hierarchy or below, but not above or sibling/peer branches. In order to do so using the contextual views pattern, we can create a view.
CREATE VIEW vwUserApplicationUser AS WITH cte_BusinessUnit as( SELECT BU.BusinessUnitId, BU.BusinessUnitName, 0 HierarchyLevel FROM BusinessUnit BU WHERE EXISTS ( SELECT * FROM ApplicationUser AU WHERE AU.BusinessUnitId = BU.BusinessUnitId AND AU.UserName = SUSER_SNAME()) UNION ALL SELECT BU.BusinessUnitId, BU.BusinessUnitName, CBU.HierarchyLevel + 1 HierarchyLevel FROM BusinessUnit BU INNER JOIN cte_BusinessUnit CBU ON CBU.BusinessUnitId = BU.ParentBusinessUnitId) SELECT AU.FirstName, AU.LastName, AU.UserName, AU.BusinessUnitId, BU.BusinessUnitName, BU.HierarchyLevel FROM ApplicationUser AU INNER JOIN cte_BusinessUnit BU ON BU.BusinessUnitId = AU.BusinessUnitId WHERE BU.HierarchyLevel > 0 OR AU.UserName = SUSER_SNAME()
I am using SUSER_SNAME build-in function to anchor the view result to the requester. in order to test this view, let’s create a user for each level of the hierarchy and execute the view.
USE [master] CREATE LOGIN [jdoe01] WITH PASSWORD=N'pwd', DEFAULT_DATABASE=[master], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF CREATE LOGIN [jdoe02] WITH PASSWORD=N'pwd', DEFAULT_DATABASE=[master], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF CREATE LOGIN [bdoe01] WITH PASSWORD=N'pwd', DEFAULT_DATABASE=[master], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF USE [BLOG] CREATE USER [jdoe01] FOR LOGIN [jdoe01] WITH DEFAULT_SCHEMA=[dbo] CREATE USER [jdoe02] FOR LOGIN [jdoe02] WITH DEFAULT_SCHEMA=[dbo] CREATE USER [bdoe01] FOR LOGIN [bdoe01] WITH DEFAULT_SCHEMA=[dbo] GRANT SELECT ON vwUserApplicationUser TO [jdoe01] GRANT SELECT ON vwUserApplicationUser TO [jdoe02] GRANT SELECT ON vwUserApplicationUser TO [bdoe01] EXECUTE AS LOGIN = 'jdoe01'; select * from vwUserApplicationUser REVERT; EXECUTE AS LOGIN = 'jdoe02'; select * from vwUserApplicationUser REVERT; EXECUTE AS LOGIN = 'bdoe01'; select * from vwUserApplicationUser REVERT;
Notice that the view and filter (or absence of the filter condition) is the same, however, it returns different result depending who is connected to the database – this view is requester aware. We can use the same technique to filter data in other tables that should implement requester aware logic.
Similar way we can use other functions to make views aware of the current date using GET_UTCDATE(), the current application using APP_NAME or other scenarios
Context-specific functions. This post is mostly about SQL Server, so all the example here are based on the t-SQL syntax and the SQL Server built-in functions. SQL Server provides a variety of built-in functions out of the box that provides information about configuration, environment, and session.
Configuration functions can be used in cases when the same code base deployed to multiple instances with different configurations and behavior of the code will depend on the host database setting. Here are the functions that can potentially be used
- @@Version – need extra parsing.
I have not had a case of using Cryptographic functions, however, theoretically, all of then can be used as well.
Date and Time functions, that do not accept any parameters are one of the most commonly used functions set to enable time aware objects
Another large category is Metadata functions, specifically.
- SERVERPROPERTY – need to pass specific parameter name that is part of the logic
- SESSIONPROPERTY – expects an option
- CONNECTIONPROPERTY – expects an option
We can technically also use PUBLISHINGSERVERNAME replication function.
Special case. The implementation possibilities are limited by available built-in functions. I had once a requirement to build a query that depended on the global configuration that was set by the administrator. This setting was driven by the business team and did not have specific logic. I have used the configuration service instead of built-in functions in conjunction with this pattern to change the logic of view filters.
Summary. In this post, we have reviewed the concept of context-aware views and provided a small demo of requester aware view.