Re: Object level Authorisation

From: Dan Guzman (guzmanda_at_nospam-online.sbcglobal.net)
Date: 03/26/05

  • Next message: Fulvio: "Re: Object level Authorisation" 
    Date: Sat, 26 Mar 2005 12:51:17 -0600

    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
>
  • Next message: Fulvio: "Re: Object level Authorisation"