Re: Record Level Security

From: Umachandar Jayachandran (please_reply_to_newsgroup_only)
Date: 01/05/03 

From: "Umachandar Jayachandran" <please_reply_to_newsgroup_only>
Date: Sun, 5 Jan 2003 00:34:21 -0800

    Since you are not exposing the USER_NAME column in the UDF or view,
there is no problem I think. The issue if you have to include the USER_NAME
column also in the view or UDF like below. Then any user can insert data for
any other user via the UDF that doesn't qualify while reading data.

ALTER FUNCTION fn_ShowMyEntries ()
RETURNS table
AS
RETURN (
        SELECT [Item], [OtherAttribute], [RowCounter], [USER_NAME]
        FROM [dbo].[table1]
        WHERE [User_name] = Current_User
       )
GO

ALTER VIEW dbo.vw_table1
AS
SELECT Item, OtherAttribute, RowCounter
FROM dbo.table1
WHERE (User_Name = CURRENT_USER)
 WITH CHECK OPTION
GO

-- Logged in as dbo, this will fail:
insert into vw_table1 ( Item, OtherAttribute, User_Name )
values( 'CC', '212', 'TestUser' )

-- Logged in as dbo, this will succeed:
insert into fn_ShowMyEntries() ( Item, OtherAttribute, User_Name )
values( 'CC', '212', 'TestUser' ) 

--
Umachandar Jayachandran
SQL Resources at http://www.umachandar.com/resources.htm
( Please reply only to newsgroup. )

Relevant Pages

  • Re: Record Level Security
    ... > Since you are not exposing the USER_NAME column in the UDF or view, ... > column also in the view or UDF like below. ... > ALTER FUNCTION fn_ShowMyEntries ... > insert into fn_ShowMyEntries(Item, OtherAttribute, User_Name) ...
    (microsoft.public.sqlserver.security)
  • display View output in DataGridView
    ... a fixed name UDF dragged onto the DBML referencing the fixed name VIEW; ... Even though there is no change in its code ALTER FUNCTION also has to be run ...
    (microsoft.public.dotnet.languages.csharp)
  • Change UDF Owner
    ... Can I change the owner of a UDF? ... USE master ... EXEC sp_changeobjectowner 'fn_replgetagentcommandlinefromjobid', 'sa' ... I want dbo to own all the objects in my database - just ...
    (microsoft.public.sqlserver.security)
  • Help with UDF using OPENROWSET to EXECUTE sproc
    ... Here is the UDF I am trying to create: ... ALTER FUNCTION dbo.TieredAccessCounties ... Must declare the variable '@SourceTable'. ... [OLE/DB provider returned message: ...
    (microsoft.public.sqlserver.server)