Worth Corner, Crawley, RH10 7SL

Support Hours - Mon-Sat, 8.00-18.00

Welcome to our SQL Server blog

This blog was created in order to pass on some of our knowledge and advice in the hope that the community might find it useful.

Obviously Microsoft SQL Server is our area of expertise and therefore this is our most thorough blog with everything from the basic through to the the incredibly advanced and, as such, we should have something for everyone within our pages.


Row Level Security on In-Memory Tables
Published: Nov 26, 2020
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.

Leave a Comment
Your email address will not be published. All fields are mandatory.
NB: Comments will only appear once they have been moderated.

SQL  World  CEO
Kevin  Urquhart

iPhone Selfie

I am a SQL Server DBA, Architect, Developer, Trainer, and CEO of SQL World. This is my blog in which I’m simply trying to share my SQL knowledge and experiences with the world.

Categories


© Copyright 2020 SQLTraining Ltd.