Column Level Permissions Security Issue

SQL2K5 SP2
I’m trying to lock down PCI sensitive data columns in some product databas
es
from our developers. We need developers to have data reader rights to the
production database to perform general maintenance and troubleshooting of th
e
application BUT minus the specific secured columns like credit card number,
etc.
I have a user role setup called RWE created by:
CREATE ROLE RWE
GRANT SELECT TO RWE
GRANT INSERT TO RWE
GRANT UPDATE TO RWE
GRANT DELETE TO RWE
GRANT EXECUTE TO RWE
A standard SQL user is placed inside this role allowing them full read,
write, and execute rights on everything in the DB which is fine.
BUT, now we want those same rights except for the sensitive data files so I
updated the rule with the following script:
DENY SELECT ON [dbo].[TableNameHere] ([strCC]) TO [RWE]
Loggin in a developer and doing a SELECT * FROM TableNameHere throws a
permission error as expected, so far so good.
But, I did a SELECT * FROM ViewThatContainsField_strCC and shows them the
denied field. Oh, oh! I also did EXEC spProcThatShows_strCC and again it
shows the denied credit card field. Again, oh, oh.
So, long story short, I’m stuck. My understanding of SQL permissions, I wa
s
under the understanding that placing a deny at the lowest level (column)
should throw errors in all methods of access that column (either by direct
select, or select through views, procs, etc).
Any suggestions or any hints on what I’m missing here?
Thx ahead of time!Ross Nornes (RossNornes@.discussions.microsoft.com) writes:
> A standard SQL user is placed inside this role allowing them full read,
> write, and execute rights on everything in the DB which is fine. BUT,
> now we want those same rights except for the sensitive data files so I
> updated the rule with the following script:
> DENY SELECT ON [dbo].[TableNameHere] ([strCC]) TO [RWE]
> Loggin in a developer and doing a SELECT * FROM TableNameHere throws a
> permission error as expected, so far so good.
> But, I did a SELECT * FROM ViewThatContainsField_strCC and shows them the
> denied field. Oh, oh! I also did EXEC spProcThatShows_strCC and again it
> shows the denied credit card field. Again, oh, oh.
Ownership chaining I suppose. That is, the procedure and the view are owned
by the same database user that owns the table. In such case the permissions
of the owner applies.
You could deny permission on the view column. You should probably deny
execution on procedures that disclose sensitive data as well.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Thx for the reply Erland!
All objects are owned by DBO.
Ya, I could deny access on the view column and the procs, if I have to I
will, but the issue is these are legacy applications and will take HOURS and
HOURS to dig through all the code to determine all places where sensitive
data fields are located and grant specific permissions on each object.
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 anyth
ing
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. 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.
Is there a better way to achieve this same protection without the huge hours
of item by item code review? Whats the point of deny permissions if this is
how they function?
I really hope I'm just missing something here, but at this point I pretty
frustrated with this aspect of the security system right now...
Ross
"Erland Sommarskog" wrote:

> Ross Nornes (RossNornes@.discussions.microsoft.com) writes:
> Ownership chaining I suppose. That is, the procedure and the view are owne
d
> by the same database user that owns the table. In such case the permission
s
> of the owner applies.
> You could deny permission on the view column. You should probably deny
> execution on procedures that disclose sensitive data as well.
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/pr...oads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodin...ions/books.mspx
>|||Ross Nornes (RossNornes@.discussions.microsoft.com) 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@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx