SELECT ANY SEQUENCE

I used to think that a user with SELECT ANY TABLE and SELECT ANY DICTIONARY could see anything in a database. I found out today that these 2 privileges do not allow you to SELECT from another user’s sequence. You can see what I mean in the example below, which I tested in an Oracle 11.1 database. First I created a user to own a sequence:

 

SQL> conn / as sysdba

Connected.

SQL> create user user1

  2  identified by user1

  3  /

 

User created.

 

SQL> grant create session, create sequence

  2  to user1

  3  /

 

Grant succeeded.

 

SQL>

 

Then I created a second user to SELECT from the first user’s sequence:

 

SQL> create user user2

  2  identified by user2

  3  /

 

User created.

 

SQL> grant create session,

  2  select any table,

  3  select any dictionary to user2

  4  /

 

Grant succeeded.

 

SQL>

 

USER1 then created a sequence:

 

SQL> conn user1/user1

Connected.

SQL> create sequence sequence1

  2  /

 

Sequence created.

 

SQL> select sequence1.nextval from dual

  2  /

 

   NEXTVAL

----------

         1

 

SQL>

 

… but USER2 could not SELECT from it:

 

SQL> conn user2/user2

Connected.

SQL> select user1.sequence1.nextval from dual

  2  /

select user1.sequence1.nextval from dual

             *

ERROR at line 1:

ORA-01031: insufficient privileges

 

SQL>

 

One way round this is to GRANT the SELECT ANY SEQUENCE privilege like this:

 

SQL> conn / as sysdba

Connected.

SQL> grant select any sequence to user2

  2  /

 

Grant succeeded.

 

SQL> conn user2/user2

Connected.

SQL> select user1.sequence1.nextval from dual

  2  /

 

   NEXTVAL

----------

         2

 

SQL>