[SQL] Controlling access to Sequences

[Matthew Horoschun]

Hi All,

I'm implementing a system where I need to be reasonably careful about security. One thing that worries me is SEQUENCES.

My understanding is that I need to GRANT the UPDATE privilege on the SEQUENCE if I want a user to be able to to use nextval() on it. The trouble is, if they can do a nextval() they can also do a setval() which would allow them to set the sequence to any value they felt like and potentially cause problems for other user's use of the SEQUENCE.

I've considered forcing the user to use the nextval() by using a RULE on a VIEW, but it appear as though the nextval() function still runs as the logged-in user not the owner of the VIEW.

Is there any effective method for controlling access to a SEQUENCE? or should I do something like in the view:

INSERT INTO x VALUES ( ( SELECT MAX( id ) + 1 FROM x ), some_field );

Is this safe to do? Or might this lead to undesirable problems if called concurrently?

Any assistance would be appreciated.

Matthew.


---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly