0

I do have an Azure SQL Database running. In there I do have 3 Tables

  • Users
  • UserProject
  • Projects

A normal many-to-many relationship. A user can have multiple projects and a project can have multiple users. The rows are linked by IDs and ForeignKey-Constraitns are set accordingly.

I do want to enable Row Level Security for the Projects-table. This is what I did:

CREATE FUNCTION dbo.fn_predicate(@tenant varchar(25))     
    RETURNS TABLE     
    WITH SCHEMABINDING
AS
    RETURN SELECT 1 AS fn_accessResult
        WHERE SESSION_CONTEXT(N'Tenant') = @tenant ;
GO

Then I created the policy:

CREATE SECURITY POLICY mandantPolicy
    ADD FILTER PREDICATE dbo.fn_predicate(Tenant) ON dbo.Projects,
    ADD BLOCK  PREDICATE dbo.fn_predicate(Tenant) ON dbo.Projects
GO

I then enabled the policy and it works fine. This means I can only see projects of a tenant for which I've set the SESSION_CONTEXT. And also the insert is blocked accordingly..

For example:

-- Works as expected (new project inserted)
EXEC SP_SET_SESSION_CONTEXT @key=N'Tenant', @value='foo'
INSERT INTO Projects (Name, Tenant) VALUES ('Dummy', 'foo')

-- Works as expected (insert blocked)
EXEC SP_SET_SESSION_CONTEXT @key=N'Tenant', @value='bar'
INSERT INTO Projects (Name, Tenant) VALUES ('Dummy', 'foo')

What I would expect is, that this row level security is also enforced on the relationship table UserProject. For example:

-- Insert a new project for tenant 'foo'
-- This results in a new project with id 1
EXEC SP_SET_SESSION_CONTEXT @key=N'Tenant', @value='foo'
INSERT INTO Projects (Name, Tenant) VALUES ('Dummy', 'foo')

-- Try to link the project from tenant foo with a user (id 5) but as tenant 'bar'
EXEC SP_SET_SESSION_CONTEXT @key=N'Tenant', @value='bar'
INSERT INTO UserProject (ProjectId, UserId) VALUES (1, 5)

In my opinion this last INSERT should be blocked, as the tenant is set to 'bar' and therefore the project which was previously added as tenant 'foo' should not be visible to the user 'bar'.

Do I miss any configuration here? Or is this just not supported by RLS?

Stefan
  • 1,007
  • 1
  • 11
  • 32
  • 1
    I'm going for "just not supported/this is a bug". The reads produced for a foreign key insert check are not subsequently filtered through the security policy present on the table. This is true even if the foreign key actually includes the columns supposedly filtered (i.e. make the primary key `ID`, `Tenant`). While RLS is known to be vulnerable through side channel attacks that indirectly reveal that the data is present, this sounds like the kind of thing that's worthwhile to add as a feature. You could [suggest it](https://feedback.azure.com/forums/908035-sql-server). – Jeroen Mostert Jan 16 '19 at 13:21
  • You have to define the SECURITY POLICY and the PREDICATE function for each related table. Security Policy works on a single table. – PSK Jan 31 '19 at 07:40

0 Answers0