ORA-02205

I found some notes from a course I took in 1990. They said that it was only possible to GRANT ALTER or GRANT SELECT on a sequence. This seemed reasonable to me but I wanted to check if it was still the case. I did this test on Oracle 12.1. First I created a user who would own a sequence:

SQL> create user u1 identified by pw1

  2  /

 

User created.

 

SQL> grant create session, create sequence to u1

  2  /

 

Grant succeeded.

SQL>

Then I created a user who would be granted access to the sequence:

SQL> create user u2 identified by pw2

  2  /

 

User created.

SQL>

The first user created a sequence then did a GRANT ALL on it to the second user:

SQL> conn u1/pw1

Connected.

SQL> create sequence s1

  2  /

 

Sequence created.

 

SQL> grant all on s1 to u2

  2  /

 

Grant succeeded.

SQL>

I looked for the privileges which had been given to the second user but only found ALTER and SELECT. This confirmed what I had read in my notes:

SQL> select privilege from all_tab_privs

  2  where grantor = 'U1'

  3  and grantee = 'U2'

  4  and table_name = 'S1'

  5  /

 

PRIVILEGE                                             

----------------------------------------              

ALTER                                                 

SELECT

SQL>

Finally I tried to GRANT UPDATE on the sequence but this failed with an ORA-02205:

SQL> grant update on s1 to u2

  2  /

grant update on s1 to u2

                *

ERROR at line 1:

ORA-02205: only SELECT and ALTER privileges are valid

for sequences

 

SQL>