Friday, October 31, 2014

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
 

No comments: