Simplest way to log in as Oracle user different from schema user and still see all tables,… of schema

oracleschemausers

What is the simplest way to log in as user X and see all tables,… of schema Y as if they belonged to X?

With an Oracle 11g database, I would like to log into the database using user X but all the data (tables, views, procedures, …) would be stored in Schema Y.

All the code that accesses the tables is guaranteed to use unqualified names (TBL_NAME, not Y.TBL_NAME)

Searching for "schema alias" points to CREATE SYNONYM, but that would have to be done for each DML object, right? And since I only have unqualified calls, it seems a login trigger with ALTER SESSION SET CURRENT_SCHEMA = ...; should do the trick?

Any other options?

Best Answer

ALTER SESSION SET CURRENT_SCHEMA

will work, but you still need to do all of the grants.

– Phil Oct 29 at 23:33

Related Solutions

Changing the default schema of a user

Sounds like you don't have SELECT access on the sequence. Ask the DBA to run:

GRANT SELECT ON SCHEMA_OWNER.MY_SEQ TO APP_USER;

Can you edit your post with the full error message you're receiving?

Regarding your comment. I suspect you've got SELECT ANY TABLE etc privs. These don't apply to sequences, as demonstrated below:

PHIL@PHILL11G2 > create user test1 identified by test1;

User created.

PHIL@PHILL11G2 > grant connect,select any table to test1;

Grant succeeded.

PHIL@PHILL11G2 > create sequence testseq;

Sequence created.

PHIL@PHILL11G2 > conn test1/test1;
Connected.
Loading glogin.sql

Session altered.

TEST1@PHILL11G2 > select count(*) from phil.bigtable;

  COUNT(*)
----------
   2300640

TEST1@PHILL11G2 > 

TEST1@PHILL11G2 > select phil.testseq.nextval from dual;
select phil.testseq.nextval from dual
            *
ERROR at line 1:
ORA-01031: insufficient privileges

Why does “GRANT SELECT … TO Role;” not allow members of Role to SELECT

In addition to ensuring you're in the right schema, try this one;
Once logged in as Alice, do: SET ROLE Viewer IDENTIFIED BY pwdviewer;

For more see here

Best Answer

Related Solutions

Changing the default schema of a user

Why does “GRANT SELECT … TO Role;” not allow members of Role to SELECT

Related Question