Re: Change returned data in a view based on security role?
From: Steve Kass (skass_at_drew.edu)
Date: 11/22/04
- Next message: Leon: "Insert SP from multiple tables"
- Previous message: David Portas: "Re: building a table"
- In reply to: Dooler: "Re: Change returned data in a view based on security role?"
- Next in thread: Dooler: "Re: Change returned data in a view based on security role?"
- Messages sorted by: [ date ] [ thread ]
Date: Mon, 22 Nov 2004 17:18:25 -0500
Clayton,
It may sound like I'm evading the question a little, but in my
opinion, SQL Server security is fine. The SQL Server security model
does not include row-level security, and views can't add this feature in
a foolproof way, though I'm not aware of any potential disclosure if the
TOP clause I suggested is used. I think in this regard, Microsoft is
probably in better stead than some of the companies that implement
row-level security with query predicates and offer it as a feature. I
can't point you towards much else on this subject beyond a brief mention
at http://www.sommarskog.se/dyn-search.html and a couple of newsgroup
threads
(http://groups.google.com/groups?q=row-level+security+kass+sqlserver&hl=en).,
but there may be some better links within a few months.
SK
Dooler wrote:
>> Unfortunately, this solution does not provide foolproof
>>security. If a SELECT query is issued against a table
>>containing sensitive information, partial or full disclosure
>>of the sensitive information, even to a non-privileged user,
>>may be possible. This can be the case even if the result set
>>of the query contains no sensitive information.
>>
>>
>
>Ok, that one threw me. So if SQL locks down permissions on if they
>can select from a view or not and what information is returned based
>on their role (what I am trying to figure out how to do), what you are
>saying is I cannot trust security in sql to work correctly?
>
>
>
>> Whether and to what extent privileged information can
>>be disclosed in this situation depends on quite
>>a few things, but if no breach can be risked, and
>>using a view predicate like WHERE suser_sid() = T.user_id
>>is an ideal solution otherwise, something must be added
>>that forces the view to be materialized, such as
>>
>>select top 2000000000 * from
>>...
>>order by <whatever is likely to be fastest>
>>
>>(If the table in question may have more than
>>two billion rows, you can take a chance with
>>the undocumented SELECT TOP 99.99999999999999
>>PERCENT .. ORDER BY ...)
>>
>> The risk of disclosure may still exist if the view
>>is replaced by a table valued function.
>>
>>-- Steve Kass
>>-- Drew University
>>-- Ref: 1D282213-25D5-49C4-8541-A7BE10F8E8FB
>>
>>
>>
>>Dooler wrote:
>>
>>
>>
>>>I have a table for storing data (credit card info) and have two roles:
>>>nonAccounting and Accounting. What I would like to do is create a
>>>single view that everyone uses, but the data returned changes based on
>>>role person is in. IE: Accounting would see 1234-4567-9012-3456,
>>>but nonAccounting would only see XXXX-XXXX-XXXX-3456. I need save
>>>view due to the application everyone uses goes through odbc to access
>>>tables. Added to this, I will assign Select and Insert only to
>>>nonAccounting and full rights to Accounting for
>>>Selecting/Inserting/Updating the view.
>>>
>>>Any ideas how to perform this?
>>>
>>>Thanks in Advance,
>>>Clayton
>>>
>>>
>>>
>>>
>
>
>
- Next message: Leon: "Insert SP from multiple tables"
- Previous message: David Portas: "Re: building a table"
- In reply to: Dooler: "Re: Change returned data in a view based on security role?"
- Next in thread: Dooler: "Re: Change returned data in a view based on security role?"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
