In my last post, I talked about the concept of row level security impelementation. Performance issue will gradually arise while number of rows in MyData increases. This is because is_member function evaluate every row in MyData table and check whether it’s the row should be returned. In order to make index becomes Search-able, we will have to change the structure of the view.

Now let me assume that all the objects created in my last post exist in the database. Let’s add one more table to associate RoleID with database principles and modify the view

/*create table MyData
(
	MyDataID int identity(1,1) primary key,
	Description varchar(100),
	RoleID int
)
go*/
create table RowPrincipleMap
(
	RoleID int identity(1,1) primary key,
	PrincipleID int not null unique
)
set identity_insert RowPrincipleMap on
go
insert into RowPrincipleMap(RoleID, PrincipleID) values(5,5)
insert into RowPrincipleMap(RoleID, PrincipleID) values(6,6)
go
set identity_insert RowPrincipleMap off
go
alter view vMyData
as
select d.*
from MyData d
	inner join RowPrincipleMap r on d.RoleID = r.RoleID
where is_member(user_name(r.PrincipleID)) =1
go

The intention of this is to let is_member function to run against very small table, RowPrincipleMap rather than a table potentially huge in the future. Let’s verify validity of this change

execute as user = 'user1'
select * from vMyData
/*
MyDataID    Description RoleID
----------- ----------- -----------
2           value1      5

(1 row(s) affected)

*/
revert
go
execute as user = 'user2'
select * from vMyData
/*
MyDataID    Description  RoleID
----------- ------------ -----------
1           value1       6

(1 row(s) affected)

*/
revert
go
execute as user = 'user3'
select * from vMyData
/*
MyDataID    Description RoleID
----------- ----------- -----------
1           value1      6
2           value1      5

(2 row(s) affected)

*/
revert
go
execute as user = 'user4'
select * from vMyData
/*
MyDataID    Description RoleID
----------- ----------- -----------
1           value1      6
2           value1      5

(2 row(s) affected)

*/
revert
go

cool! everything is as excepted.

Now we will have the full control over the data reading from the view. What about writing?

execute as user = 'user1'
select * from vMyData
update vMyData set description = 'zzz'
select * from vMyData
/*
MyDataID    Description RoleID
----------- ----------- -----------
2           zzz         5

(1 row(s) affected)

(1 row(s) affected)

MyDataID    Description RoleID
----------- ----------- -----------
2           zzz         5

(1 row(s) affected)
*/

revert

Ok, we can update the table from the view. This definitely is an enhancement on the view. In previous version of SQL server, probably SQL Server 2000, if a view has a inner join or outer join, the view will not be updatable. Now let’s do another test

execute as user = 'user1'
select * from vMyData
update vMyData set RoleID = 6
select * from vMyData
/*
MyDataID    Description RoleID
----------- ----------- -----------
2           zzz         5

(1 row(s) affected)

(1 row(s) affected)

MyDataID    Description RoleID
----------- ----------- -----------

(1 row(s) affected)
*/

revert

execute as user = 'user2'
select * from vMyData
/*
MyDataID    Description RoleID
----------- ----------- -----------
1           value1      6
2           zzz         6

(2 row(s) affected)
*/
-- set it back
update vMyData set RoleID = 5 where MyDataID = 2
revert

I update the RoleID under user1 security context to a RoleID that user1 should not have access to, which lead the row to be removed from the view. This can be either a feature or an unwanted bug. If it’s considered as a bug, we should prevent this from happening. The simplest way of doing that is to place WITH CHECK OPTION in the view definition.

execute as user = 'user1'
--select * from vMyData
update vMyData set RoleID = 6
/*
Msg 550, Level 16, State 1, Line 1
The attempted insert or update failed because the target view either specifies WITH CHECK OPTION or spans a view that specifies WITH CHECK OPTION and one or more rows resulting from the operation did not qualify under the CHECK OPTION constraint.
The statement has been terminated.

*/
revert
go

See, this problem is resolved by built-in functionalities without any envolvement of customized codes. For update, WITH CHECT OPTION prevents rows invisible. View itself can prevent deleting rows outside of the view. What about insert? When inserting a row through the view, you will have to provide a RoleID. If you have access to the data from multiple departments, what RoleID you would pick for the new record? Let’s talk about it in my next post.