How to grant the select privilege ?
DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 16

Thread: How to grant the select privilege ?

  1. #1
    Join Date
    Sep 2002
    Posts
    376

    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.....

  2. #2
    Join Date
    Sep 2002
    Location
    England
    Posts
    7,333
    works for me on 9i

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

  3. #3
    Join Date
    Sep 2002
    Posts
    376
    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

  4. #4
    Join Date
    Dec 2002
    Location
    Bangalore ( India )
    Posts
    2,434
    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"

  5. #5
    Join Date
    Sep 2002
    Posts
    376
    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 05:09 AM.

  6. #6
    Join Date
    Sep 2002
    Location
    England
    Posts
    7,333
    ok I thought it was a typo in your original statement but you create the public synonym on balke not blake

  7. #7
    Join Date
    Jan 2001
    Posts
    2,828

    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

  8. #8
    Join Date
    Sep 2002
    Posts
    376
    Hi,

    Thanx Hrishy and dave.

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

  9. #9
    Join Date
    Dec 2002
    Location
    Bangalore ( India )
    Posts
    2,434
    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"

  10. #10
    Join Date
    Sep 2002
    Location
    England
    Posts
    7,333
    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
  •  



Click Here to Expand Forum to Full Width