How to grant the select privilege ?

Printable View

12-06-2003, 09:22 AM
bang_dba

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.....
12-06-2003, 09:58 AM
davey23uk

works for me on 9i

why not post your exact output feom sql*plus not just what you think you typed in
12-08-2003, 05:12 AM
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
12-08-2003, 05:46 AM
abhaysk

i doubt you have created EMP_V public synonym correctly.

can u query in dba_synonyms & post us here?
12-08-2003, 06:04 AM
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
12-08-2003, 07:50 AM
davey23uk

ok I thought it was a typo in your original statement but you create the public synonym on balke not blake
12-08-2003, 08:01 AM
hrishy

Re: How to grant the select privilege ?

Quote:

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
12-09-2003, 06:08 AM
bang_dba

Hi,

Thanx Hrishy and dave.

But strange; oracle lets u create a synonym even though the user/object doesn't exist.
12-09-2003, 07:05 AM
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..:rolleyes: :rolleyes:

Abhay.
12-09-2003, 07:50 AM
davey23uk

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
12-09-2003, 08:09 AM
Thomasps

Code:

SQL> create synonym xx for th.ddf; Synonym created. SQL> select * from xx; select * from xx * ERROR at line 1: ORA-00980: synonym translation is no longer valid

Abhay is correct.
12-09-2003, 08:19 AM
davey23uk

ok thats true, but if you to desc xx you get a different message 'object th.dff does not exist'

Which is still actually different than ORA-00942 so I gues the OP either had the user there or gave us the wrong error message as Abhay said
12-09-2003, 10:40 AM
abhaysk

Quote:

Originally posted by davey23uk
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

Wass not true...& i have no clue why you have posted the part of the code? :confused: :confused:

Did i ever tell, its not possible to create a synonym for NON existent object??

Well, i said the error he is getting is not what he shud have GOT, if he is getting that error then there should be some user by the TPYO and table/view by that name and no privs for user querying for that synonym...

Is ther any thing where i am not being logical in my approch??

Abhay.
12-09-2003, 11:43 AM
bang_dba

Quote:

Originally posted by Thomasps

Code:

SQL> create synonym xx for th.ddf; Synonym created. SQL> select * from xx; select * from xx * ERROR at line 1: ORA-00980: synonym translation is no longer valid

Abhay is correct.

I Dont had any user by name BALKE....

You get "ORA-00942: table or view does not exist"
when u create a PUBLIC synonym

create public synonym xxx for th.ddf

SELECT * FROM XXX

ORA-00942: table or view does not exist
12-09-2003, 07:41 PM
davey23uk

Quote:

Wass not true...& i have no clue why you have posted the part of the code?

Did i ever tell, its not possible to create a synonym for NON existent object??

Well, i said the error he is getting is not what he shud have GOT, if he is getting that error then there should be some user by the TPYO and table/view by that name and no privs for user querying for that synonym...

Is ther any thing where i am not being logical in my approch??

Abhay.

thats why I corrected myself after - i apologise
12-10-2003, 03:44 AM
abhaysk

Quote:

Originally posted by bang_dba
I Dont had any user by name BALKE....

You get "ORA-00942: table or view does not exist"
when u create a PUBLIC synonym

create public synonym xxx for th.ddf

SELECT * FROM XXX

ORA-00942: table or view does not exist

Yes indeed on 8.x it throws this misleading error. But 9.x no probs, the error is relevent.

Abhay.