Re: Record Level Security

From: John Bell (jrm_bell@hotmail.com)
Date: 01/05/03 

From: "John Bell" <jrm_bell@hotmail.com>
Date: Sun, 5 Jan 2003 11:38:04 -0000

Hi

Thanks for the information, I am grateful for your response.

I didn't think about exposing user_name as I saw no reason to display or
update it!

John

"Umachandar Jayachandran" <please_reply_to_newsgroup_only> wrote in message
news:#6rbdTJtCHA.2168@TK2MSFTNGP12...
> 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. )
>
>