2

I have a grant alter sequence to a user. But I have to specify the schema name to alter the sequence otherwise it comes back with error sequence does not exist. Is it possible to do the grant in a way so I don't have to specify the schema name? I can do select/insert/update without specifying the schema name.

NullUserException
  • 83,810
  • 28
  • 209
  • 234
Eqbal
  • 4,722
  • 12
  • 38
  • 47

3 Answers3

6

Name resolution and access privileges are separate concepts. The grant gives you permission to alter the schema, but you're still constrained by the namespace you're in. There are four ways to make a name in one schema resolve in another:

  1. Reference the schema with the object name:
    select schema_name.sequence_name.nextval from dual;
  2. Alter the current session to resolve names as if you were in the other schema:
    alter session set current_schema = schema_name;
  3. Create a synonym for the object in the current schema:
    create synonym sequence_name for schema_name.sequence_name;
  4. Create a public synonym for the object:
    create public synonym sequence_name for schema_name.sequence_name;
Allan
  • 17,141
  • 4
  • 52
  • 69
  • I found out we already have a public synonym and `alter sequence` does not work still, I get `sequence does not exist` error when I try to alter it. – Eqbal Jun 29 '11 at 17:28
2

It sounds like you're connected to your database as a different user than the one that owns the sequence. In that case, you will need to preface the sequence with the schema, otherwise it's ambiguous as to what sequence you're talking about.

If you are connected to your database as the owning user, you do not need the schema qualifier:

SQL> create sequence foo;

Sequence created.

SQL> grant alter on foo to hr;

Grant succeeded.
eaolson
  • 14,717
  • 7
  • 43
  • 58
0

you may:

ALTER SESSION SET CURRENT_SCHEMA myschema..

then you can reference all items without the qualifier.

Randy
  • 16,480
  • 1
  • 37
  • 55