Re: Object level Authorisation

From: Fulvio (fulvio_at_nssitaly.com)
Date: 03/26/05 

Date: Sat, 26 Mar 2005 20:07:31 +0100

Thanks
That works fine -:)

"Dan Guzman" <guzmanda@nospam-online.sbcglobal.net> ha scritto nel messaggio
news:eImZMTjMFHA.2704@TK2MSFTNGP10.phx.gbl...
> You can use views to selectively expose data for SELECT queries based on
> your security requirements. For example, you can create a role named
> 'PriceRole', add authorized users to that role and then create a view to
> expose the column based on role membership.
>
> CREATE VIEW dbo.Tps_View
> AS
> SELECT
> Tps_Id,
> Tps_Name,
> CASE IS_MEMBER('PriceRole')
> WHEN 1 THEN Tps_Price ELSE NULL END AS Tps_Price
> FROM dbo.Tps
> GO
>
> However, INSERT and UPDATE statements using Tps_Price data are problematic
> because the computed value can't be updated. These statements will need
> to use the underlying table directly, although DELETEs can use either the
> view or table.
>
>> I'd prefere not to change VB code by looking for user's role and
>> differently query DB
>
> It's best to consider security requirements in the design phase rather
> than late in development or after implementation. You could rename the
> table to another name and then name the view 'Tps' so that SELECT and
> DELETE statements work as desired without changes. However, you'll still
> have the issue with Tps_Price UPDATEs and /INSERTs when the view is used
> with Tps_Price data. If you use only stored procedures for DML, changes
> should be minimal.
>
> --
> Hope this helps.
>
> Dan Guzman
> SQL Server MVP
>
> "Fulvio" <fulvio@nssitaly.com> wrote in message
> news:u8Xtx%23gMFHA.3336@TK2MSFTNGP09.phx.gbl...
>> Hi all,
>>
>> is there a way to tell Sql Server to return Null values if user has no
>> SELECT authorisation on a column ?
>>
>> let's imagine a table structure like:
>>
>> Tps_Id,
>> Tps_Name,
>> Tps_Price
>>
>> Now in my VB application i use 'Select * from Tps'
>>
>> All works fine for users in roles that can select data on that table. But
>> if i have a role which can't select column Tps_Price i get error 230.
>> I'd prefere not to change VB code by looking for user's role and
>> differently query DB
>>
>> Thanks for your help
>>
>> --
>>
>> Fulvio
>>
>
>

Relevant Pages

  • Re: Object level Authorisation
    ... You can use views to selectively expose data for SELECT queries based on ... It's best to consider security requirements in the design phase rather than ... "Fulvio" wrote in message ... > SELECT authorisation on a column? ...
    (microsoft.public.sqlserver.security)
  • How to deploy a package to client PC ?
    ... As the security requirements, We have a ftp download package which must run ... on another PC from SQL Server, ... When I deploy a package to client, ...
    (microsoft.public.sqlserver.dts)