MS SQL Server security - Security of views compared to non-views

Author

Security of views compared to non-views

Sam Hobbs

2005-12-11, 3:23 am

When I say "non-view" I think I mean DML; correct me if I am wrong.

We are trying to retrofit another vendor's software to use better security.
I think that the software uses views to process it's data. For security
purposes, is there any significant difference between non-views and views?
In otehr words, is the security used for views equivalent to security used
for non-views?

I hope that the terminology I have used is adequate to describe the prolem.

If there is not a significant difference, then I can give a lower priority
to determination if the software is using views, and I will give a lower
priority to looking for the documentation specific to Views.

Kalen Delaney

2005-12-11, 3:23 am

Hi Sam

The security benefit of views is that you can allows someone to access the
view, without giving them blanket permission to the base tables, so that
they can ONLY see the data that is included in the view.

For example, if I have a view that is something like this:

CREATE VIEW v1 AS
SELECT col1, col2 FROM t1
WHERE col3 = 'non secure info'

I can give some SELECT permission on the view, and then they can only see 2
columns in the table, and only for those rows where col3 = 'non secure info'

Without the view, I would have to give someone select permission on the
table t1, and then they could see ALL the rows.

--
HTH
Kalen Delaney, SQL Server MVP
www. solidqualitylearning
.com

"Sam Hobbs" <samuel@social.rr. com_change_social_to
_socal> wrote in message
news:%23$3K2yf$FHA.140@TK2MSFTNGP12.phx.gbl...
>
> When I say "non-view" I think I mean DML; correct me if I am wrong.
>
> We are trying to retrofit another vendor's software to use better
> security. I think that the software uses views to process it's data. For
> security purposes, is there any significant difference between non-views
> and views? In otehr words, is the security used for views equivalent to
> security used for non-views?
>
> I hope that the terminology I have used is adequate to describe the
> prolem.
>
> If there is not a significant difference, then I can give a lower priority
> to determination if the software is using views, and I will give a lower
> priority to looking for the documentation specific to Views.
>
>
>

Sam Hobbs

2005-12-11, 11:23 am

Thank you Kalen, that seems obvious but I did not realize it. You did
explain it very well; I sure understand now.

Assuming the other application uses views for all it's processing, it should
be possible to make the data quite secure.

"Kalen Delaney" < replies@public_newsg
roups.com> wrote in message
news:enhNa2f$FHA.912@TK2MSFTNGP11.phx.gbl...
>
> Hi Sam
>
> The security benefit of views is that you can allows someone to access the
> view, without giving them blanket permission to the base tables, so that
> they can ONLY see the data that is included in the view.
>
> For example, if I have a view that is something like this:
>
> CREATE VIEW v1 AS
> SELECT col1, col2 FROM t1
> WHERE col3 = 'non secure info'
>
> I can give some SELECT permission on the view, and then they can only see
> 2 columns in the table, and only for those rows where col3 = 'non secure
> info'
>
> Without the view, I would have to give someone select permission on the
> table t1, and then they could see ALL the rows.
>
> --
> HTH
> Kalen Delaney, SQL Server MVP
> www. solidqualitylearning
.com
>
>
> "Sam Hobbs" <samuel@social.rr. com_change_social_to
_socal> wrote in message
> news:%23$3K2yf$FHA.140@TK2MSFTNGP12.phx.gbl...
>
>
>

Sponsored Links