Re: Record Level Security

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

From: "John Bell" <jrm_bell@hotmail.com>
Date: Fri, 3 Jan 2003 13:04:44 -0000

Hi Umanchander

I can not re-create your security problem and the following test case
behaves as I would expect. Could you please show how to simulate the problem
and indicate why base table permission are necessary?

ProductVersion ProductLevel Edition
EngineType
------------------------------ ------------------------------ --------------
---------------- -------------------
8.00.665 SP2 Developer
Edition Enterprise

CREATE TABLE [dbo].[table1] (
 [Item] [char] (2) COLLATE Latin1_General_CI_AS NULL ,
 [OtherAttribute] [char] (3) COLLATE Latin1_General_CI_AS NULL ,
 [RowCounter] [int] NULL ,
 [User_Name] [varchar] (128) COLLATE Latin1_General_CI_AS NULL
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[table1] WITH NOCHECK ADD
 CONSTRAINT [DF_table1_User_Name] DEFAULT (user_name()) FOR [User_Name]
GO

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

GRANT SELECT , UPDATE , INSERT , DELETE ON [dbo].[fn_ShowMyEntries] TO
[TestUser]
GO

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

INSERT INTO [dbo].[table1] ( Item, OtherAttribute, RowCounter, User_Name )
VALUES ( 'AA', '123',NULL,'dbo')
INSERT INTO [dbo].[table1] ( Item, OtherAttribute, RowCounter, User_Name )
VALUES ( 'AA', '456',NULL,'dbo')
INSERT INTO [dbo].[table1] ( Item, OtherAttribute, RowCounter, User_Name )
VALUES ( 'BB', '458',NULL,'TestUser')

Thanks

John

"Umachandar Jayachandran" <please_reply_to_newsgroup_only> wrote in message
news:OadkOdbsCHA.1632@TK2MSFTNGP12...
> You don't want to use a UDF for this. There is no reason to do that.
> Just define a view with the required filters in place. This is more
portable
> & easy to use/optimize. Additionally, you can put the WITH CHECK OPTION on
> the view that will prevent users without permission to see other data from
> updating it. With the UDF, there is a security hole & I can update other
> users' data if I have permissions on the tables. With UDFs, you also need
to
> be careful about how you write it. If you change it to non-inline, then
you
> will have severe performance problems.
>
> --
> Umachandar Jayachandran
> SQL Resources at http://www.umachandar.com/resources.htm
> ( Please reply only to newsgroup. )
>
>

Relevant Pages

  • Re: PATCH: cdrecord: avoiding scsi device numbering for ide devices
    ... > then there is no security problem. ... the device without even having write permission. ... Yes, it breaks user-space programs, and yes, the kernel is to blame ... send the line "unsubscribe linux-kernel" in ...
    (Linux-Kernel)
  • Re: Please help - weird problem
    ... This is a security problem. ... You need to grant read permission for ... Eliyahu ...
    (microsoft.public.dotnet.framework.aspnet)
  • Re: mplayer encounts premission problem of directfb
    ... can't do it and mplayer complains that permission of /dev/tty0. ... whether it will cause security problem? ... I have installed the package, but it seems that it doesn't work if just ... To UNSUBSCRIBE, email to debian-user-REQUEST@xxxxxxxxxxxxxxxx ...
    (Debian-User)
  • Re: mplayer encounts premission problem of directfb
    ... can't do it and mplayer complains that permission of /dev/tty0. ... whether it will cause security problem? ... I have installed the package, but it seems that it doesn't work if just ... To UNSUBSCRIBE, email to debian-user-REQUEST@xxxxxxxxxxxxxxxx ...
    (Debian-User)
  • Tomcat5, postgres.jar agh
    ... has occured to cause the driver to fail. ... I was told I had a security problem so I added a file called ... permission java.security.AllPermission; ... To UNSUBSCRIBE, email to debian-user-REQUEST@xxxxxxxxxxxxxxxx ...
    (Debian-User)