-
How to grant the select privilege ?
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.....
-
works for me on 9i
why not post your exact output feom sql*plus not just what you think you typed in
-
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
-
i doubt you have created EMP_V public synonym correctly.
can u query in dba_synonyms & post us here?
funky...
"I Dont Want To Follow A Path, I would Rather Go Where There Is No Path And Leave A Trail."
"Ego is the worst thing many have, try to overcome it & you will be the best, if not good, person on this earth"
-
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
Last edited by bang_dba; 12-08-2003 at 06:09 AM.
-
ok I thought it was a typo in your original statement but you create the public synonym on balke not blake
-
Re: How to grant the select privilege ?
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
-
Hi,
Thanx Hrishy and dave.
But strange; oracle lets u create a synonym even though the user/object doesn't exist.
-
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.
funky...
"I Dont Want To Follow A Path, I would Rather Go Where There Is No Path And Leave A Trail."
"Ego is the worst thing many have, try to overcome it & you will be the best, if not good, person on this earth"
-
not true ...
Code:
[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
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|