Tuesday, November 10, 2015

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>

No comments: