Thread: User privileges on sequences

I have a db in which users can view and update data only via views.
It is my understanding that the privileges of the view/rule creator do not
extend to sequences. Therefore, I have to give users permissions to
sequences, just to the extent that they could add records. What is the
minimum set of privileges for this? Are SELECT privileges on sequences
enough? What happens actually when a user issues a statement: SELECT
nextval("sequence")? What about setval()? Do these involve only a SELECT to
the sequence, or is it an UPDATE, or is it a DELETE and INSERT, or just an
INSERT?

>>>>> "MG" == Mihai Gheorghiu <tanethq@earthlink.net> writes:

MG> nextval("sequence")? What about setval()? Do these involve only a SELECT to
MG> the sequence, or is it an UPDATE, or is it a DELETE and INSERT, or just an
MG> INSERT?

On my sequences, I have these privs:

GRANT INSERT,UPDATE,DELETE,SELECT on some_seq to THEUSER;

I never bothered to try limiting them.  There are only 16 combos to
try, so try them!  I think you definitely need select and update.
Don't know about the others.

--
=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
Vivek Khera, Ph.D.                Khera Communications, Inc.
Internet: khera@kciLink.com       Rockville, MD       +1-240-453-8497
AIM: vivekkhera Y!: vivek_khera   http://www.khera.org/~vivek/

Mihai Gheorghiu writes:

> Therefore, I have to give users permissions to sequences, just to the
> extent that they could add records. What is the minimum set of
> privileges for this?

You need UPDATE privileges to be able to execute nextval() and currval().
I assume this would include setval() as well, though I don't see it in the
docs offhand.

--
Peter Eisentraut   peter_e@gmx.net   http://funkturm.homeip.net/~peter