Re: Column Level Permissions Security Issue
- From: Erland Sommarskog <esquel@xxxxxxxxxxxxx>
- Date: Fri, 9 Nov 2007 22:47:07 +0000 (UTC)
Ross Nornes (RossNornes@xxxxxxxxxxxxxxxxxxxxxxxxx) writes:
All objects are owned by DBO.
I'm hoping I just have a huge laps in my understanding here, but, if a
role is granted SELECT rights as in "GRANT SELECT TO RWE", then a DENY
is placed on a table or column, it should not matter how the table is
called, the deny should be enforced. Since the deny works if I hit table
directly with a SELECT * FROM [TableNameHere], why would a view, proc,
function or anything else not obey the deny permission? This seems to be
a HUGE hold in basic security enforcement unless I'm totally missing how
this works.
When working with security in SQL Server it's imperative to understand
how ownership chaining works. I have an article that among other things
discusses ownership chaining: http://www.sommarskog.se/dynamic_sql.html.
There are also topics in Books Online that are good reading
If this is true, shops like my shop that need to allow developers read
access to their databases but also need to not allow them access to
secure files like credit card numbers, we are going to have to code
review ever single view, proc, function, etc, thats being moved out
since those appear to not obey the deny permissions.
My thought is that since these views and procedures expose this
sensitive data, there are users who have permission to access this
data. I don't know how you grant permission to users, but I would
expect you to be very restrictive with giving permissions to views
and procs that expose sensitive data. And unless you are very
restrictive with granting access to the database as a whole, you would
need to have in place a mechanism where you determine whether a view
or procedure is only for a chosen few or for the general masses.
If you are on SQL 2005, and you think ownership chaining gets in the
way, you can transfer ownership of the objects to a different user.
But that is likely to have repercussions not only for the developers,
but also the users of the system.
--
Erland Sommarskog, SQL Server MVP, esquel@xxxxxxxxxxxxx
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
.
- References:
- Re: Column Level Permissions Security Issue
- From: Erland Sommarskog
- Re: Column Level Permissions Security Issue
- From: Ross Nornes
- Re: Column Level Permissions Security Issue
- Prev by Date: Re: deny users from accessing server from Management Studio
- Next by Date: Re: sa password
- Previous by thread: Re: Column Level Permissions Security Issue
- Next by thread: guideline for service account user
- Index(es):
Relevant Pages
|
|
