I want to tell you about something I came across a while ago then I was testing Azure SQL. I’m talking about Row-Level Security or RLS.
RLS was GA on 19 Aug 2015 for Azure SQL v12 and is a security feature for filtering SQL data on row level… As you probably already figured out *facepalm*.
This article helped me to get started and It’s super helpful, but I think I made RLS even more super awesome.
https://msdn.microsoft.com/en-us/library/dn765131.aspx
In one of the examples in the article, you get to filter a table using a column that holds the username. If the user who is logged on matches the value in the row, the row is displayed for the user.
CREATE SCHEMA Security; GO CREATE FUNCTION Security.fn_securitypredicate(@SalesRep AS sysname) RETURNS TABLE WITH SCHEMABINDING AS RETURN SELECT 1 AS fn_securitypredicate_result WHERE @SalesRep = USER_NAME() OR USER_NAME() = 'Manager';
CREATE SECURITY POLICY SalesFilter ADD FILTER PREDICATE Security.fn_securitypredicate(SalesRep) ON dbo.Sales WITH (STATE = ON);
Give me all the rows where SalesRep column is “logged on user name” and apply it I the Sales table. If the logged on user is “Manager” the filter is going to return the rows independently of the “SalesRep” value.
Simple, cool, works, end of story.
No wait! I don’t usually have a column called “User” that holds a username for which I planned to use for RLS in the future. What I had was a bunch of GUID’s. Multiple GUID’s to each user and different column name for them in each table.
Let’s create these table.
But first we will create a S0 database on a previous configured v12 database in azure.
I use SQL Server 2016 CTP2 Management Studio at this time, it saved a lot of time when working with Azure SQL, because older versions lack support for v12.
CREATE DATABASE testDB ( EDITION = 'Standard', SERVICE_OBJECTIVE='S0' )
Add some tables and some data.
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Table_1](
[ID] [int] IDENTITY(1,1) NOT NULL,
[SubscriptionGuid] [nvarchar](50) NULL,
[Data1] [nchar](10) NULL
)
GO
INSERT INTO [dbo].[Table_1]
([SubscriptionGuid],[Data1])
VALUES
('a45d72d4-5554-409c-88a2-5c0808f8de20','d1fa1'), --Account1
('a45d72d4-5554-409c-88a2-5c0808f8de20','d2fa1'),
('a45d72d4-5554-409c-88a2-5c0808f8de20','d3fa1'),
('a45d72d4-5554-409c-88a2-5c0808f8de20','d4fa1'),
('69f58d63-e9a1-4ddf-af10-abfaa24e2e55','d5fa1'), --Account1 Guid2
('69f58d63-e9a1-4ddf-af10-abfaa24e2e55','d6fa1'),
('69f58d63-e9a1-4ddf-af10-abfaa24e2e55','d7fa1'),
('9d996b3b-7c69-45a8-a1f4-39668ac9e776','d1fa2'), --Account2
('9d996b3b-7c69-45a8-a1f4-39668ac9e776','d2fa2'),
('8c90e38c-3d1c-4fda-9963-01a71e0bc7b0','d1fa3'), --Account3
('8c90e38c-3d1c-4fda-9963-01a71e0bc7b0','d2fa3')
GO
</pre>
<pre>SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Table_2](
[TenantName] [nvarchar](50) NOT NULL,
[FluxCapasitorValue] [int] NULL,
[Data1] [nchar](10) NULL,
CONSTRAINT [PK_Table_2] PRIMARY KEY CLUSTERED
(
[TenantName] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
)
GO
INSERT INTO [dbo].[Table_2]
([TenantName]
,[FluxCapasitorValue]
,[Data1])
VALUES
('a45d72d4-5554-409c-88a2-5c0808f8de20',24,'true'), --Account1
('69f58d63-e9a1-4ddf-af10-abfaa24e2e55',21,'true'), --Account1 GUID2
('9d996b3b-7c69-45a8-a1f4-39668ac9e776',20,'false'), --Account2
('a45d72d4-ffff-ffff-ffff-5c0808f8de20',99,'error') --Unused Account
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Table_3](
[ID] [int] IDENTITY(1,1) NOT NULL,
[SubscriptionGuid] [nvarchar](50) NULL,
[DataF] [nchar](10) NULL
)
GOINSERT INTO [dbo].[Table_3]
([SubscriptionGuid],[DataF])
VALUES
('eaf164a8-ade4-4db4-9016-NOFILTER','NOFILTER')
GO
I know which user owns which GUID so I make a relation table.
CREATE TABLE [dbo].[Accounts]( [Guid] [nvarchar](50) NOT NULL, [Name] [sysname] NOT NULL, PRIMARY KEY CLUSTERED ( [Guid] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) )
Account1 owns two GUIDs and Account2 and 3 only owns one each.
INSERT INTO [dbo].[Accounts]
([Guid],[Name])
VALUES
('a45d72d4-5554-409c-88a2-5c0808f8de20','Account1'),
('69f58d63-e9a1-4ddf-af10-abfaa24e2e55','Account1'),
('9d996b3b-7c69-45a8-a1f4-39668ac9e776','Account2'),
('8c90e38c-3d1c-4fda-9963-01a71e0bc7b0','Account3')
To be able to filter this we need to modify the security function from the article.
CREATE SCHEMA Security;
GO
CREATE FUNCTION Security.fn_securitypredicate_Guid(@GUID AS sysname)
RETURNS TABLE
WITH SCHEMABINDING
AS
RETURN SELECT 1 AS fn_securitypredicate_result
WHERE ((SELECT a.Name from dbo.Accounts as a where a.Guid = @GUID)) = USER_NAME() or 'dbo' = USER_NAME();
Instead of matching the username to a parameter from the table. I create a sub query that matches all the account name (username) from the “accounts” table using the GUID as a parameter from the filtered table.
Now I can use this function on my different tables and choose to use different column names for each table.
CREATE SECURITY POLICY Table1Filter ADD FILTER PREDICATE Security.fn_securitypredicate_Guid(SubscriptionGuid) ON dbo.Table_1 WITH (STATE = ON); CREATE SECURITY POLICY Table2Filter ADD FILTER PREDICATE Security.fn_securitypredicate_Guid(TenantName) ON dbo.Table_2 WITH (STATE = ON);
*Note that we use a different input parameter on the policy filters. In table_1 the guid column was named ‘SubscriptionGuid’ and in table_2 the column name was ‘TenantName’
Let’s create the users and grant them some access.
--Run this on the master DB CREATE LOGIN Account1 WITH PASSWORD = 'P@ssword' GO CREATE LOGIN Account2 WITH PASSWORD = 'P@ssword' GO CREATE LOGIN Account3 WITH PASSWORD = 'P@ssword' GO
--Run this on the User DB CREATE USER Account1 FOR LOGIN Account1 CREATE USER Account2 FOR LOGIN Account2 CREATE USER Account3 FOR LOGIN Account3 grant select ON [Table_1] TO Account1 grant select ON [Table_2] TO Account1 grant select ON [Table_3] TO Account1 grant select ON [Table_1] TO Account2 grant select ON [Table_2] TO Account2 grant select ON [Table_3] TO Account2 grant select ON [Table_1] TO Account3 grant select ON [Table_2] TO Account3 grant select ON [Table_3] TO Account3
If we test this by running a select on all tables as an administrator…
SELECT * FROM [dbo].[Table_1] SELECT * FROM [dbo].[Table_2] SELECT * FROM [dbo].[Table_3]
We will get all the rows. Obviously?!
Yeah but, it’s actually because we added this :
‘dbo’=USER_NAME();
In the security function earlier.
If we instead run the same query as one of our new users, we will get a different result.
EXECUTE AS USER = 'Account1'; SELECT * FROM [dbo].[Table_1] SELECT * FROM [dbo].[Table_2] SELECT * FROM [dbo].[Table_3]
Same goes for Account2 and so forth.
EXECUTE AS USER = 'Account2'; SELECT * FROM [dbo].[Table_1] SELECT * FROM [dbo].[Table_2] SELECT * FROM [dbo].[Table_3]
And as you probably noticed “Table 3” always return one row. That is because we never applied any security policy to “Table 3”.
By using RLS we have now a centralized table for managing which users sees which rows. You can thank me later.
Jon Jander @Meapax
