How to grant the select privilege ?

[bang_dba]

Hi,
I have a table emp in SCOTT schema. A view emp_v is created based on emp in BLAKE schema.
A public synonym emp_v is created based on view in BLAKE schema.

When i select the view emp_v (or rather the public synonym) from a
3 rd schema..say Mark, i get the error Table or view doesnot exsit.

Here is the steps what i have done. Can some body tell me what grant i am misssing ???
-----------------

connect internal;

create user scott identified by tiger
/
create user blake identified by blake
/
create user mark identified by mark
/
grant connect,resource to scott,blake,mark
/
connect scott/tiger;

create table emp(enum number, ename varchar2(10) )
/
grant select on emp to public with grant option
/
connect blake/blake;

create view emp_v as select * from scott.emp
/
grant select on emp_v to public with grant option
/

connect internal;

create public synonym emp_v for balke.emp_v
/
connect mark/mark;

select * from emp_v
/


i am gettting ....ERROR table or view doesn't exist


Can somebody tell me what i'm missing.....

[davey23uk]

works for me on 9i

why not post your exact output feom sql*plus not just what you think you typed in

[bang_dba]

SQL> connect mark/mark
Connected.

SQL> select * from emp_v;
select * from emp_v
*
ERROR at line 1:
ORA-00942: table or view does not exist



I am using Oracle8i Enterprise Edition Release 8.1.7.4.0 - Production

[abhaysk]

i doubt you have created EMP_V public synonym correctly.

can u query in dba_synonyms & post us here?

[bang_dba]

select owner,object_name,object_type,status from dba_objects where object_name like 'EMP%'


OWNER OBJECT_NAME OBJECT_TYPE STATUS

PUBLIC EMP_V SYNONYM VALID
BLAKE EMP_V VIEW VALID
SCOTT EMP TABLE VALID




SELECT * FROM DBA_SYNONYMS WHERE SYNONYM_NAME LIKE 'EMP_V%'

OWNER SYNONYM_NAME TABLE_OWNER TABLE_NAME DB_LINK
PUBLIC EMP_V BALKE EMP_V

[davey23uk]

ok I thought it was a typo in your original statement but you create the public synonym on balke not blake

[hrishy]

Originally posted by bang_dba
Hi,
I have a table emp in SCOTT schema. A view emp_v is created based on emp in BLAKE schema.
A public synonym emp_v is created based on view in BLAKE schema.

When i select the view emp_v (or rather the public synonym) from a
3 rd schema..say Mark, i get the error Table or view doesnot exsit.

Here is the steps what i have done. Can some body tell me what grant i am misssing ???
-----------------

connect internal;

create user scott identified by tiger
/
create user blake identified by blake
/
create user mark identified by mark
/
grant connect,resource to scott,blake,mark
/
connect scott/tiger;

create table emp(enum number, ename varchar2(10) )
/
grant select on emp to public with grant option
/
connect blake/blake;

create view emp_v as select * from scott.emp
/
grant select on emp_v to public with grant option
/

connect internal;

create public synonym emp_v for balke.emp_v
/
connect mark/mark;

select * from emp_v
/


i am gettting ....ERROR table or view doesn't exist


Can somebody tell me what i'm missing.....

well the statement

create public synonym emp_v for balke.emp_v


should really be

create public synonym emp_v for BLAKE.emp_v

there is spelling mistake..:-)

oversight i guess

regards
Hrishy

[bang_dba]

Hi,

Thanx Hrishy and dave.

But strange; oracle lets u create a synonym even though the user/object doesn't exist.

[abhaysk]

Hrishy / Davey23uk :

If synonym is created for non existent object, wont that generate "ORA-00980: synonym translation is no longer valid" on querying?

But instead he's getting "ORA-00942: table or view does not exist"...

I would suspect some user by BALKE exists and no privs for table/view for the user 3..

Abhay.

[davey23uk]

not true ...

[oracle@cim4 oracle]$ sqlplus "/ as sysdba"
 
SQL*Plus: Release 9.2.0.1.0 - Production on Tue Dec 9 11:48:49 2003
 
Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.
 
 
Connected to:
Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production
With the Partitioning option
JServer Release 9.2.0.1.0 - Production
 
SQL> create public synonym emp_v for blah.emp;
 
Synonym created.
 
SQL> select username from dba_users;
 
USERNAME
------------------------------
SYS
SYSTEM
OUTLN
MZADMIN
DBSNMP