Re: Controlling access to Sequences

On Fri, Jan 31, 2003 at 23:47:27 +1100,
Matthew Horoschun <mhoroschun(at)canprint(dot)com(dot)au> wrote:
> 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:

You can limit access to nextval by only allowing access through a user
defined function that runs as the definer. If you only want them to use
the function when doing an insert into a specific table, then I am not
sure if you can do this easily. At worst you could write a function
that does the insert.

Note that there is a real issue with letting people have access to setval
over nextval. nextval can only increase the value of the sequence, while
setval can decrease it (in additon to being able to increase it). In some
cases decreasing a sequence might cause a lot more problems then a large
increase would.