Accessing Table of other owner
DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 13

Thread: Accessing Table of other owner

  1. #1
    Join Date
    Jun 2005
    Location
    Brazil / MG
    Posts
    6

    Accessing Table of other owner

    Hi,
    I using Oracle 8.1.7 and have the following problem:
    I Have two users, User1 and User2, and a have one tablespace TS_DATA.
    User1 have full access on TS_DATA (Create Tables, Drop Tables and others). User1 have created the table below:

    CREATE TABLE USER1.TB_TEST1
    ( CODE NUMBER NOT NULL PRIMARY KEY,
    DESC VARCHAR2(50) NOT NULL);

    How can I grant permission to USER2 to do SELECT on TB_TEST1 without put the owner before the table name?
    For example:
    SELECT * FROM TB_TEST1
    Thiago Campos Pereira

  2. #2
    Join Date
    Jan 2002
    Location
    Up s**t creek
    Posts
    1,525
    Create a synonym for the table.
    Jim
    Oracle Certified Professional
    "Build your reputation by helping other people build theirs."

    "Sarcasm may be the lowest form of wit but its still funny"

    Click HERE to vist my website!

  3. #3
    Join Date
    Jun 2005
    Location
    Brazil / MG
    Posts
    6
    I have 10 user and 250 tables. I have to create 2500 synonym. This solution donít solve my problem.
    I need other solution that donít use synonym.
    Thiago Campos Pereira

  4. #4
    Join Date
    Sep 2002
    Location
    England
    Posts
    7,333
    there is not other solution, create synonyms

  5. #5
    Join Date
    Jan 2002
    Location
    Up s**t creek
    Posts
    1,525
    Create public synonyms, that way you only need to create 250 and why does that not solve the problem?
    Jim
    Oracle Certified Professional
    "Build your reputation by helping other people build theirs."

    "Sarcasm may be the lowest form of wit but its still funny"

    Click HERE to vist my website!

  6. #6
    Join Date
    Nov 2002
    Location
    New Delhi, INDIA
    Posts
    1,796
    Originally posted by tpereira
    I have 10 user and 250 tables. I have to create 2500 synonym. This solution donít solve my problem.
    I need other solution that donít use synonym.
    What Jim meant was create public synonyms which is accessible to all. Additionally create a role, grant select on user1's tables to that role and grant that role to the other 10 users.
    Last edited by adewri; 06-15-2005 at 08:03 AM.
    Amar
    "There is a difference between knowing the path and walking the path."

    Amar's Blog  Get Firefox!

  7. #7
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    You have two options (apart the one mentioned by jovery):

    - create PUBLIC synonyms for that tables (so you'll have only 250 synonyms, not 2500)
    - allow your users to perform the following : ALTER SESSION SET CURRENT_SCHEMA = USER1;
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

  8. #8
    Join Date
    Sep 2002
    Location
    England
    Posts
    7,333
    Originally posted by jmodic
    You have two options (apart the one mentioned by jovery):

    - create PUBLIC synonyms for that tables (so you'll have only 250 synonyms, not 2500)
    - allow your users to perform the following : ALTER SESSION SET CURRENT_SCHEMA = USER1;
    does that then allow users to change data in tables in that schema?

  9. #9
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    Originally posted by davey23uk
    does that then allow users to change data in tables in that schema?
    If they have permission to, yes.

    As for public synonyms, I would really stay away from them unless you know this is the ONLY application that will ever be on that database.
    Jeff Hunter
    marist89@yahoo.com
    http://marist89.blogspot.com/
    Get Firefox!
    "I pledge to stop eating sharks fin soup and will not do so under any circumstances."

  10. #10
    Join Date
    Jan 2002
    Location
    Up s**t creek
    Posts
    1,525
    Originally posted by marist89
    If they have permission to, yes.

    As for public synonyms, I would really stay away from them unless you know this is the ONLY application that will ever be on that database.
    Totally agree Jeff but as the original poster did not say whether the user would/would not have objects of there own I offered the synonym route.
    Jim
    Oracle Certified Professional
    "Build your reputation by helping other people build theirs."

    "Sarcasm may be the lowest form of wit but its still funny"

    Click HERE to vist my website!

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