Обсуждение: simulating row ownership

On Fri, Jan 07, 2005 at 11:52:07AM -0500, Rick Schumeyer wrote:

> I have a table where I want everyone to be able to be able to insert and
> select.
> 
> But they should only be able to update and delete rows that they "own".  The
> table has a column indicating the owner.

What does the owner column refer to?  A database user?  If so, then
you could use a trigger that checks CURRENT_USER or SESSION_USER
and raises an exception if the user doesn't have permission to
update or delete the affected row.  In the PostgreSQL documentation,
see the "Triggers" chapter and the "Trigger Procedures" section of
the "PL/pgSQL - SQL Procedural Language" chapter.

-- 
Michael Fuhr
http://www.fuhr.org/~mfuhr/

On Fri, Jan 07, 2005 at 11:52:07AM -0500, Rick Schumeyer wrote:

> I have a table where I want everyone to be able to be able to insert
> and select.

> But they should only be able to update and delete rows that they
> "own".  The table has a column indicating the owner.

> What is the best way to accomplish this?  I'm not real familiar with
> rules, but it seems that I can do this with rules for update and
> delete applied to the table.

Using rules, you could do something like the following:

CREATE TABLE test ( aname TEXT PRIMARY KEY
);

INSERT INTO test ( aname ) VALUES ( 'aaa' );
INSERT INTO test ( aname ) VALUES ( 'yourusername' );

CREATE RULE lock_test_user_update
AS ON UPDATE TO test
WHERE old.aname = CURRENT_USER
DO INSTEAD nothing;

CREATE RULE lock_test_user_delete
AS ON DELETE TO test
WHERE old.aname = CURRENT_USER
DO INSTEAD nothing;

-- 
Ron Peterson
Network & Systems Manager
Mount Holyoke College
http://www.mtholyoke.edu/~rpeterso

On Tue, Jan 11, 2005 at 08:38:21AM -0500, rpeterso wrote:

> CREATE RULE lock_test_user_update
> AS ON UPDATE TO test
> WHERE old.aname = CURRENT_USER
> DO INSTEAD nothing;
> 
> CREATE RULE lock_test_user_delete
> AS ON DELETE TO test
> WHERE old.aname = CURRENT_USER
> DO INSTEAD nothing;

For your example, these rules should say !=, of course...

-- 
Ron Peterson
Network & Systems Manager
Mount Holyoke College
http://www.mtholyoke.edu/~rpeterso