SQL 2016 -Row level security misuse: – Visual Totals in SSAS

Ever since I heard of Row level security I have been wanting to try it out. Not the usual stuff that I was sure MS would have already handled and tested for but the more unusual security loop holes in it one of which I have mentioned at the very bottom of this post. Those of us who have worked in Analysis Services know that we have been able to achieve row level filters (in a much more perfect way than in DB Engine) for many years now. The more important aspect of how it was achieved in SSAS was the ability to control visual totals. What this means is that in SSAS when aggregating the default member ALL was used to show grand totals this meant that the report would look something like this.

This lets the India VP know that he contributes 50% of the total sales and thus has power over the others, by simply checking the box for visual totals for the user permissions in SSAS the grand totals reflect accurately as $250,000 and the VP has no clue where he stands among the others.

I was interested in seeing if this would be possible in SQL 2016 and created the below scripts.

Create the test tables and populate with data

CREATE SCHEMA rls
GO
-- creating a table to test rls
CREATE TABLE rls.rlstbl
( id int ,
name varchar(128) ,
DOB datetime,
emailid varchar(128))
GO
-- inserting test data into table

GO

Next we creat the users who will fetch data from the above table.


-- creating users based on the data
CREATE USER [a@j.com] WITHOUT LOGIN;
CREATE USER [e@b.com] WITHOUT LOGIN;
CREATE USER [h@c.com] WITHOUT LOGIN;
GO
-- granting permissions to users based on the data
GRANT SELECT ON rls.rlstbl to [a@j.com]
GRANT SELECT ON rls.rlstbl to [e@b.com]
GRANT SELECT ON rls.rlstbl to [h@c.com]
GRANT UPDATE ON rls.rlstbl to [h@c.com]
GRANT INSERT ON rls.rlstbl to [h@c.com]
GRANT DELETE ON rls.rlstbl to [h@c.com]
GRANT DELETE ON rls.rlstbl to [e@b.com]
GO


The next thing we need to do is create a table valued function ,for DBA the closest thing this comes to is the Classifier function used with Resource Governor, the table valued function is joined internally with the base table to filter rows belonging to the user. ( this aspect is hidden from the user and visible via the execution plan). In my function below I have created 4 common scenarios I come across.

• The managerwho wants to view the date for his entire team but cannot view others data
• The Super user who can view data for all teams but is not a DBA
• The DBA
• The Lowly user who can view just his data and nothing else.

CREATE FUNCTION rls.rlspredicate(@emailid AS sysname)
RETURNS TABLE
WITH SCHEMABINDING
AS
RETURN SELECT 1 AS rlspredicate_op
WHERE 1= case when USER_NAME()=@emailid then 1
when USER_NAME() ='e@b.com' and @emailid like '%@b.com%' then 1
when USER_NAME() like '%@j.com%' then 1
when user_name() ='dbo' then 1
else 0
end
-- Applying the secuity policy
CREATE SECURITY POLICY rowFilter
ADD FILTER PREDICATE rls.rlspredicate(emailid)
ON rls.rlstbl
WITH (STATE = ON);

GO

Now we test the standard Select , insert update and delete statments and they all work as expected.

EXECUTE AS USER = 'a@j.com';
SELECT *, user_name() FROM rls.rlstbl;
REVERT;

SELECT * FROM rls.rlstbl

Till here everything works as expected and now I try running the below query to view how much I or my team contribute to the total Sales of the company by simply running the query below, you will notice that running this query doesnt require any special permissions

EXECUTE AS USER = 'e@b.com';

DECLARE @int FLOAT = (SELECT ROWS FROM sys.partitions WHERE object_id = object_id('rls.rlstbl'))
select count(*)/@int from rls.rlstbl
REVERT

Screenshot below

I honestly feel that security policies for row level security should ignore system catalogs since the practical usage of this features is for application functionality. In all fairness MS has called out advanced querying as a potential loop hole but I hardly consider this advanced querying.

Please Consider Subscribing

Subscribe