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:
>