Row Level Security on In-Memory Tables
Published: Nov 28, 2021
Quick note to combine the last few sets of posts… can you use Row Level Security on in-memory tables?

We all know that in-memory tables do tend to have a lot of quirks and many things we might want to use don’t seem to, but in this case we’re all good… row level security does in fact work on in-memory tables:

drop table if exists dbo.myTestInMem
go

create table dbo.myTestInMem
(
id int identity not null primary key nonclustered,
userName varchar(10) not null,
myData int,
index ix_mem nonclustered hash(userName) with(bucket_count = 10)
)
with (memory_optimized = on)
go

insert into dbo.myTestInMem select 'bob', 1
insert into dbo.myTestInMem select 'bob', 2
insert into dbo.myTestInMem select 'fred', 3
insert into dbo.myTestInMem select 'fred', 4
insert into dbo.myTestInMem select 'bob', 5
go

if exists (select * from sys.sysusers where name = 'bob')
drop user bob;

if exists (select * from sys.sysusers where name = 'fred')
drop user fred;

create user bob without login
create user fred without login;

grant select on dbo.myTestInMem to bob
grant select on dbo.myTestInMem to fred;

if exists(select * from sys.security_policies where name = 'secTestPolicy')
drop security policy secTestPolicy;

drop function if exists dbo.secTestFunction
go

create function dbo.secTestFunction(@userName varchar(10))
returns table
with
schemabinding, native_compilation
as
return
(
select 1 as pass
where @userName = user_name()
)
go

create security policy secTestPolicy
add filter predicate dbo.secTestFunction(userName)
on dbo.myTestInMem
with (state = on)
go

execute as user = 'bob'

select *
from dbo.myTestInMem

revert
go

execute as user = 'fred'

select *
from dbo.myTestInMem

revert
go



As you can see, it’s exactly as we expected.

There is one thing to note though, and that’s the fact that you MUST natively compile your security function in order to use it with an in-memory table. Therefore you will notice that the function states:

with schemabinding, native_compilation


Without this you will get an error:


Otherwise there should be no issues at all.

Comments:
NB: Comments will only appear once they have been moderated.