COM security Policy

I am trying to create a data extension and this extension has to access a
COM+ application in order to run as a domain user to access files on the
network but every time I try running this code it errors out with a security
permission error. How do I setup permission to access the COM+ application?
--
RYAN SCHOUTENNever mind this is not an issue
--
RYAN SCHOUTEN
"RYAN SCHOUTEN" <ryanttr@.yahoo.com> wrote in message
news:uyU736ERFHA.2744@.TK2MSFTNGP10.phx.gbl...
> I am trying to create a data extension and this extension has to access a
> COM+ application in order to run as a domain user to access files on the
> network but every time I try running this code it errors out with a
security
> permission error. How do I setup permission to access the COM+
application?
> --
> RYAN SCHOUTEN
>

Column-level Security

Does anyone know of any design patterns, best practice or common
dos-and-dont's for Column-level security in SQL 2005?
I've read the Row-level / cell-level paper on msdn recommending encryption,
and it's not quite what I'm after.
eg might this be implemented through views with role-based security
or completely locking tables down and using stored procedures only for data
access?
Thanks
wBobDamien
You can create a VIEW with only columns that users need to see and GRANT
select permission on view. The same thing you can do with stored procedures
to give tje user an EXECUTE permissions.
http://vyaskn.tripod.com/sql_server...t_practices.htm --sec
urity
best practices

"Damien" <Damien@.discussions.microsoft.com> wrote in message
news:B711D4F2-D50A-4130-A80C-595D168DF782@.microsoft.com...
> Does anyone know of any design patterns, best practice or common
> dos-and-dont's for Column-level security in SQL 2005?
> I've read the Row-level / cell-level paper on msdn recommending
> encryption,
> and it's not quite what I'm after.
> eg might this be implemented through views with role-based security
> or completely locking tables down and using stored procedures only for
> data
> access?
> Thanks
> wBob|||Hi Uri,
what if I wanted to present a consistent interface to the users? ie, if I
SELECT from a view but don't have permissions on a column, I currently get a
n
error, whereas I would prefer a NULL. Is there any way to avoid this?
Thanks
"Uri Dimant" wrote:

> Damien
> You can create a VIEW with only columns that users need to see and GRANT
> select permission on view. The same thing you can do with stored procedure
s
> to give tje user an EXECUTE permissions.
> http://vyaskn.tripod.com/sql_server...t_practices.htm --s
ecurity
> best practices
>
>
>
>
>
>
> "Damien" <Damien@.discussions.microsoft.com> wrote in message
> news:B711D4F2-D50A-4130-A80C-595D168DF782@.microsoft.com...
>
>|||Well , looks strange , but I think you have to deal with such requirement on
the client side. I'm still saying that you have run a view with only columns
that you have permission to and for NULL's check on the client side
"Bob" <Bob@.discussions.microsoft.com> wrote in message
news:1CB3949E-9153-43D9-9467-5C4C6895E97C@.microsoft.com...[vbcol=seagreen]
> Hi Uri,
> what if I wanted to present a consistent interface to the users? ie, if I
> SELECT from a view but don't have permissions on a column, I currently get
> an
> error, whereas I would prefer a NULL. Is there any way to avoid this?
> Thanks
> "Uri Dimant" wrote:
>|||Bob,
You can craft your view something like this:
Select T.ThingID,
T.ThingName,
CASE (Is_Member('SalaryRightsRole'))
WHEN 1 THEN T.SalaryAmount
ELSE NULL
END As SalaryAmount,
..
FROM Thing T
Role membership would control columns to view. In this case, if the user is
a member of SalaryRightsRole, he will get the value, but others would get
NULL.
You can control the columns to be viewed in a number of ways, with your own
security table, etc.
RLF
"Bob" <Bob@.discussions.microsoft.com> wrote in message
news:1CB3949E-9153-43D9-9467-5C4C6895E97C@.microsoft.com...[vbcol=seagreen]
> Hi Uri,
> what if I wanted to present a consistent interface to the users? ie, if I
> SELECT from a view but don't have permissions on a column, I currently get
> an
> error, whereas I would prefer a NULL. Is there any way to avoid this?
> Thanks
> "Uri Dimant" wrote:
>|||I couldn't get that to work Russell. It looks like if the user does not hav
e
SELECT permission on a column then you can't use it, even in an expression.
Msg 230, Level 14, State 1, Line 2
The SELECT permission was denied on the column ...
Does yours work? Maybe I've missed something ...
wBob
"Russell Fields" wrote:

> Bob,
> You can craft your view something like this:
> Select T.ThingID,
> T.ThingName,
> CASE (Is_Member('SalaryRightsRole'))
> WHEN 1 THEN T.SalaryAmount
> ELSE NULL
> END As SalaryAmount,
> ...
> FROM Thing T
> Role membership would control columns to view. In this case, if the user
is
> a member of SalaryRightsRole, he will get the value, but others would get
> NULL.
> You can control the columns to be viewed in a number of ways, with your ow
n
> security table, etc.
> RLF
> "Bob" <Bob@.discussions.microsoft.com> wrote in message
> news:1CB3949E-9153-43D9-9467-5C4C6895E97C@.microsoft.com...
>
>|||Bob,
OK, the thing with this approach is that the _view_ is the security. For
this to work your system would:
1 - Disallow any user access to the table.
2 - Provide user access through these secured views created WITH
VIEW_METADATA.
3 - Would not use SQL Server's column level security.
On SQL Server 2005 at least, my tests indicate that my WHERE only sees the
data that the view is presenting. (Test it yourself using a high-powered and
low-powered user.) So, I have not been able to ask questions of the NULLed
columns in order to discover hidden information.
I mention this because there have some things written about the risk of
discovery with this approach.
RLF
"Bob" <Bob@.discussions.microsoft.com> wrote in message
news:6253CD4E-ED79-4CB5-92D1-D8DE5E04DEFE@.microsoft.com...[vbcol=seagreen]
>I couldn't get that to work Russell. It looks like if the user does not
>have
> SELECT permission on a column then you can't use it, even in an
> expression.
> Msg 230, Level 14, State 1, Line 2
> The SELECT permission was denied on the column ...
> Does yours work? Maybe I've missed something ...
>
> wBob
> "Russell Fields" wrote:
>

Column level security

How can I impose column level security?You can set up security on column level:

GRANT
{ ALL [ PRIVILEGES ] | permission [ ,...n ] }
{
[ ( column [ ,...n ] ) ] ON { table | view }
| ON { table | view } [ ( column [ ,...n ] ) ]
| ON { stored_procedure | extended_procedure }
| ON { user_defined_function }
}
TO security_account [ ,...n ]
[ WITH GRANT OPTION ]
[ AS { group | role } ]

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