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.


Column Level Security Examples
Published: Dec 01, 2020
I’ve done a few posts about Row Level Security so I thought I would make one about Column Level as well as, many times, people want to restrict access to columns as well and believe the only way is through re-architecting their tables or hiding everything behind a view layer.

Now, as a starting point I’ll happily point out that I have no issue with using a View layer, I just feel this is cleaner as views are extra objects, require extra consideration, extra access rights, along with maintenance and possible recompilation around releases etc.

But, for completeness, let’s have a look at a couple of quick ways to remove column access before we go into “proper” column level security…

For all examples we will be using the following subset of sales.salesOrderHeader and we wish to remove creditCardID and CreditCardApprovalCode:

drop table if exists salesOrderHeaderTemp
go

select orderDate, salesOrderNumber, customerID, CreditCardID,
              
CreditCardApprovalCode, SubTotal, TotalDue
into salesOrderHeaderTemp
from sales.SalesOrderHeader
go



We have a user called “bob” who isn’t allowed to see the credit card information:

create user bob without login
go


Here are some options:

Views

This is an incredibly simple way to control access… we simply limit the view and give Bob access to the view and only the view:

drop view if exists vwSalesOrderHeaderTemp
go

create view vwSalesOrderHeaderTemp
as
       select
orderDate, salesOrderNumber,
                    
customerID, SubTotal, TotalDue
      
from salesOrderHeaderTemp
go

grant select on vwSalesOrderHeaderTemp to bob
go


Now we can see that our user can only see the columns we want:

execute as user = 'bob';

select *
from vwSalesOrderHeaderTemp

revert
go



Therefore views achieve our goal nicely.

Functions

This is exactly the same as with the view, except that we would use a function such as:

drop function if exists dbo.fnSalesOrderHeaderTemp
go

create function dbo.fnSalesOrderHeaderTemp()
      
returns table
as
       return
      
(
              
select orderDate, salesOrderNumber,
                          
customerID, SubTotal, TotalDue
              
from salesOrderHeaderTemp
      
)
go

grant select on dbo.fnSalesOrderHeaderTemp to bob
go

execute as user = 'bob';

select *
from dbo.fnSalesOrderHeaderTemp()

revert
go



Now, as you can see, each of the above works perfectly well and would do the job. The downsides are that we are creating new objects in the database, are setting individual security, have more to maintain during releases etc. So is there another way?

Native

Although I know a few people have issues with this method it is by far the one I most prefer because it’s simple, effective, and it enforces good coding practice. I’ll explain with a quick example…

First we’ll grant our test user “bob” select access to the sales.SalesOrderHeader table and then we’ll remove his access to see credit card information in the table:

grant select on sales.salesOrderHeader to bob
deny select on sales.salesOrderHeader (creditCardID, creditCardApprovalCode) to bob


Now we can test this as follows:

execute as user = 'bob';

select *
from sales.salesOrderHeader

revert
go



As you can see, this has revoked the relevant column access.

Now, I know some people don’t like this because whereas the view or function simply hide the columns, permissioning will actually throw an error when using *. However, I actually prefer this because it enforces proper coding practice and ensures that people use a column list and not * when writing their queries. Also, it’s always far too easy to have a change to a view or function accidentally slip through the net in a release and suddenly you’re in hot water for a permissions breach. Therefore I would always recommend using the native permissions.

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.