This article continues where the last one left off. If you’d like and go back and read that one you can find it here.

In that article, an updatable view was created to replace an existing table. The table had been named myData and was renamed myDataBaseTable. The view is named myData so that all existing client-side code continues to work without change. This has performance advantages over the solution from two issues (Vol 2 #36) ago which used a UDF in a computed column to achieve the same functionality.

We started with data that looked like this:

SELECT * from myData
GO

Results:

Key Name Code CorrespondingValue
1 Andy D 123
2 Rebecca D 123
3 Violet E 0
4 Eric A 3
5 Tommy F 6
6 Christine C 84

At the end of the last article, we saw that the view myData was updatable and that the existing client-side code would continue to work. Here’s the sample update of myData:

UPDATE myData SET [Name] = ‘Andy N’ WHERE [Key] =1SELECT * from myData
GO

Results:

Key Name Code CorrespondingValue
1 Andy N D 123
2 Rebecca D 123
3 Violet E 0
4 Eric A 3
5 Tommy F 6
6 Christine C 84

So we can change columns in myData just as we were able to before the switch from a UDF to a view. But there’s a complication. It turns out that the myData.CorrespondingValue column, which comes from table LookUp1, is updatable as shown by this query:

UPDATE myDAta SET CorrespondingValue = 37 WHERE [Key] = 1
GOSELECT * FROM myData
go

Results:

Key Name Code CorrespondingValue
1 Andy N D 37
2 Rebecca D 37
3 Violet E 0
4 Eric A 3
5 Tommy F 6
6 Christine C 84

Ooooooops! The update to the CorrespondingValue column for key=1 changed the CorrespondingValue for Key=2 as well. That’s because the update changed the LookUp1 table as seen in this query:

SELECT * from LookUp1
GO

Results:

Code CorrespondingValue
A 3
B 91
C 84
D 37
E 0
F 6

It’s as if we hand done this:

Update LookUp1 set CorrespondingValue = 37 Where Code = ‘D’

But we didn’t. I guess there can always be unintended consequences when implementing a clever solution. But our solution (the view) is worth using because it solves an important problem for the application. So what should we do?

In many situations, we could just live with the problem. After all there may not be any code that updates myData.CorrespondingValue in the application. But my opinion is that the database schema should go pretty far to enforce (and therefore document) the business rules of the application. Allowing updates to myData.CorrespondingValue shouldn’t be allowed once it comes form a join on LookUp1.

The solution is to use column permissions on the view. Column permissions are granted by including a comma-separated list of columns to the right of the View name surrounded by parentheses. Here’s the script to revoke update permission on myData. Here’s a script that revokes UPDATE permission from PUBLIC and then grants UPDATE permission only on selected columns to PUBLIC:

revoke update on myData to public
GO
grant update on myData([key], [name], code) to public
GO

Now you’ve got to STOP!

Yes STOP!

And login as a user who isn’t sysadmin. The reason is that we haven’t revoked permission to select the data from you, with sysadmin permissions. For testing permissions, I use a SQL Login that I name Limited– USER, who is only a member of PUBLIC. So go ahead and create such a login and give it access to the database that you’re running the scripts in. I use a database named Scratch for these purposes.

Now here’s the update script when the login is Limited– USER:

select user_name() as [Current Login]
go
UPDATE mydata SET CorrespondingValue = 999 WHERE [Key]=1
GO

Results:

Current Login
Limited– USER
Server: Msg 230, Level 14, State 1, Line 1
UPDATE permission denied on column ‘CorrespondingValue’ of object ‘myDAta’,
database ‘Scratch’, owner ‘dbo’.

So users with normal levels of permission can no longer update the CorrespondingValue column of myData because it’s protected. This prevents the unintended consequence of changing the CorrespondingValue for all users with the same value of myData.Code.

I said earlier that one of the objectives was to not break client side code and allow it to work unchanged. What would happen if client side code tried to update myData.CorrespondingValue? It would break due to lack of permission. In this case, that’s what I want to happen. If client side code was able to update myData.Corresponding before the switch to a View, it has to be changed because the CorrespondingValue now comes from LookUp1.

This is the first time that I’ve found a use for column permissions that I thought was worthwhile enough to implement. I’m sure that I’ll find more someday.