August 1, 2012 at 2:52 pm
I've been reading about denying users direct access to data and using techniques such as views or procs with EXECUTE AS instead.
I'm not sure through why this would usually be desireable. Assuming that a database does not contain tables or columns that certain users who need other data cannot be allowed to view, why is putting users in the db_datareader role not secure enough?
I can see usings procs/EXECUTE AS for inserts\updates\deletes since you'll be able to better control what changes to the data are made, although you'll need to anticipate all types of changes that are needed and design a proc for each. But for selects, I've worked with a few applications with "query builder" functions and you couldn't anticipate every query someone might need to do.
Also, vendor applications I've used tend to use vendor-created SQL logins with broad rights, and all DB access is via these logons. So I'm guessing this kind of granular security management is based more for homegrown solutions and people using Access/Excel front ends rather than vendor packages.
Thoughts?
August 1, 2012 at 3:35 pm
The advice on SELECT generally refers to OLTP environments. For reporting, usually users have access either to the tables or through views if row-level security is being applied.
The reason for blocking this in OLTP environments is simple: a user with a poorly written query could block your transactional system when it tries to make a data change to the table(s) in question. That's not desired behavior.
K. Brian Kelley
@kbriankelley
August 1, 2012 at 4:11 pm
dan-572483 (8/1/2012)
I've been reading about denying users direct access to data and using techniques such as views or procs with EXECUTE AS instead.I'm not sure through why this would usually be desireable. Assuming that a database does not contain tables or columns that certain users who need other data cannot be allowed to view, why is putting users in the db_datareader role not secure enough?
Thoughts?
I think you answered your own question for a lot of cases. Often times tables are wide and have sensitive information you don't want everyone in the organization to view.
August 1, 2012 at 6:44 pm
That reminds me, as a security person I hate the db_datareader and db_datawriter roles. They provide implicit permissions to tables and views. That means that I not only have to query the appropriate permission catalog views, but I also have to query these two role memberships to determine who has what access.
Avoid these two roles like the plague. Create your own roles, explicitly define permissions to those roles. Put users in those roles. Those who have to do auditing will thank you (you may find yourself thanking yourself).
K. Brian Kelley
@kbriankelley
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply