Re: ORA-00942: How can access a table without pre-fixing the schema name

baka wrote:
> Hello ORACLE GURU's
> I have a simple problem.
> would like to access oracle table without prefixing schema owner name
> for the read only user. (please look at step 6 for the error)
> How can i do this without using the oracle synonym.
> Either by hook or crook or by hacking i would like to access
> like Step 6:
> (i have so many table and procedure,function )
> Thanks in advance,
> baka
>
> Image of my problem
>
> -- saigen kankyou ha kokokara
>
>
> /* Step 1:connect to dba user */
>
> conect dbausr/dbauser_at_connect9i
> /* Step 2:create tables and insert sample data */
> create table a
> (
> a_one varchar(21)
> );
> insert into a(a_one ) values ('a fine with dbauser');
> create table b
> (
> b_one varchar(21)
> );
> insert into b(b_one ) values ('b fine with dbauser');
>
> /* Step 3: create role */
>
> create role read_role;
> grant select on a to read_role;
> grant select on b to read_role;
>
> /* Step 4:create user for select purpose */
>
> create user ruser identified by ruser;
> grant connect,read_role to ruser;
> alter user ruser default role read_role;
> grant create session,read_role to ruser;
>
> /* Step 5:select table as an owner of the schema */
> SQL>
> SQL> select * from a;
>
> A_ONE
> ------------------------------------------
> a fine with dbauser
>
> SQL>
>
> /* Step 6:connect read only user and hit the select statement*/
> SQL> connect ruser/ruser_at_scognos;
> Connected
> SQL> select * from a;
> select * from a
> *
> ERROR at line 1:
> ORA-00942: table or view does not exist.
>
> SQL>
>
> /*Step 7: select the table with prefixing the schema name with read
> only user */
>
> SQL> select * from dbausr.a;
>
> A_ONE
> ------------------------------------------
> a fine with dbauser
>
> SQL>
>

alter session set current_schema=dbausr;

Best regards

Maxim Received on Thu Jul 14 2005 - 02:18:35 CDT