Column permissions

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Vik - 30 Oct 2006 13:37 GMT

1. I denied some user the select permissions on some columns in a table.
However, this user is able to see these columns through the views (SQL
Server 2000). Is there a way to automatically deny the permissions on the
same columns in all the views or it's necessary to set the permissions in
every view?

2. If a column is unchecked in the permissions window, is this the same as
permission granted?

3. I've read somewhere that users should not have access to the tables, only
have access to the views. What is a reason for this recommendation?

Thanks.

Reply to this Message

Eric Johnson - 30 Oct 2006 18:08 GMT

1) Because of ownership chaining, if the user can read the column in
the view, they can read the column in the table, despite the
permissions being denied.

2) If the column is unchecked, that is permissions revoked, if it is
checked that is permission granted and the red X is denied.

3) This recommendation is based on a few things. First, the problem you
are seeing is remedied by using only views. You don't have to manage
certain permissions twice if everything is in views. Additionally, this
creates an abstraction layer, where the table can be changed and as
long as you update the view, the user never knows about the change.
This also applies to applications. Additionally, direct table access
opens up holes that a DBA may forget to plug. For instance, adding a
new "confidential" column and not denying permission on it. If you
are using views, (not views that just select * from a table), then the
new column will not be available until you explicitly make it so.

Eric Johnson
Consortio Services

> 1. I denied some user the select permissions on some columns in a table.
> However, this user is able to see these columns through the views (SQL
[quoted text clipped - 9 lines]
>
> Thanks.

Reply to this Message