CODEWITHSUNDEEP
oracle-database
Kiran
Kiran

Reputation: 366

GRANT SELECT does not work for a sequence

I have created xyz sequence under owner account and granted select privilege for 'abc' Database user but, when I tried to select sequence it says sequence does not exists using abc user.

Owner account 

CREATE SEQUENCE XYZ MINVALUE 0 START WITH 1 INCREMENT BY 1;

GRANT SELECT ON XYZ TO ABC;

ABC account

select XYZ.nextval from dual;

I should access XYZ.nextval using ABC account. Any help on this is so much appreciated on how to fix this

Upvotes: 2

Views: 11061

Answers (1)

Littlefoot
Littlefoot

Reputation: 142788

It appears that you're missing the sequence owner name.

An example (that actually works).

SQL> show user
USER is "SCOTT"
SQL>
SQL> create sequence seq;

Sequence created.

SQL> grant select on seq to hr;

Grant succeeded.

SQL> connect hr/hr
Connected.
SQL>
SQL> select scott.seq.nextval from dual;      --> owner.sequence name.nextval

   NEXTVAL
----------
         1

SQL>

A synonym:

SQL> connect hr/hr
Connected.
SQL> create synonym seq for scott.seq;

Synonym created.

SQL> select seq.nextval from dual;

   NEXTVAL
----------
         2

SQL>

A public synonym:

SQL> connect scott/tiger
Connected.
SQL> create public synonym seq for seq;

Synonym created.

SQL> connect hr/hr
Connected.
SQL> select seq.nextval from dual;

   NEXTVAL
----------
         3

SQL>

Upvotes: 3

Related Questions