Results 1 to 9 of 9
  1. #1
    Join Date
    Dec 2002
    Location
    Brazil
    Posts
    59

    Unanswered: How to select an object belonging to another user without defining the owner?

    Hi,

    I have an user called 'produc' which is the owner of all my objects production database.
    I changed the produc's password for security issues.
    Now I created a read-only user called 'sistemas' with read acess in produc's objects.
    But, my developers are reclaiming that from now one they need to change all their scripts inserting the owner in front of the the objects.
    For instance:
    Connect sistemas/password
    Select * from produc.tablename;
    How can I solve this problem?
    I want user 'sistemas' to proceed the command like this:
    Select * from tablename;

    Waiting for some answer....
    Nadia

  2. #2
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    There is a simple solution. Create local synonyms in the sistemas schema. For example

    create SYNONYM tablename for produc.tablename;

    They can then issue there "select * from tablename" and everything works. You will have to make synonyms for every object in produc.
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  3. #3
    Join Date
    May 2004
    Location
    BA [ARG]
    Posts
    137
    What about creating an ON_LOGON trigger for sistemas that has the following command:
    Code:
    execute immediate ('alter session set current_schema=produc');
    This way, you'll be able to use "select * from tablename;" with the privileges of sistemas only.

    That is actually the way we work.

    Saludos...
    Germán.

  4. #4
    Join Date
    Apr 2003
    Location
    Greenville, SC (USA)
    Posts
    1,155
    I don't believe you really want to use alter session ... all you are looking for is to select from the table without having to specify the user ... As beilstwh
    stated ... USE SYNONYMS !!!

  5. #5
    Join Date
    Apr 2004
    Posts
    246
    why bother with a simple solution like synonyms, when a much more complicated, over-kill solution can be found. and who cares what happens later on when the sistemas schema wants to have it's own objects, and the trigger causes problems. yup, no reason to do things the simple way when we can do it the hard way and set ourselves up for later failure.
    Give a man a fish, you feed him for a day. Club him over the head with a fish, he'll leave you alone.

  6. #6
    Join Date
    Apr 2003
    Location
    Greenville, SC (USA)
    Posts
    1,155
    shoblock, you got me .... I can't come up with any good argument against that ... I guess everything is relative ... The post didn't specify whether they were looking for an easy or dangerous solution ...

  7. #7
    Join Date
    Dec 2002
    Location
    Brazil
    Posts
    59
    Well, I am still in doubt.
    For instance, the trigger solution is more pratice. If I use it, no more changes will be required.
    But, synonym solution is more flexible... But I will have lots of work in creating synonyms for the 1530 tables I have in my database production. And any new table created will require the synonym creation so it's more difficult to provide the maintenance.
    In this situation, what do you advice me to do?????
    Nadia

  8. #8
    Join Date
    Apr 2004
    Posts
    246
    I prefer the synonym solution. Creating the 1530 synonyms is a one time thing (don't forget the grants as well), and then it's just creating them one by one as you add tables.

    set pages 0
    set echo off
    spool sistemas_syns.sql
    select 'create synonym sistemas.' || object_name|| ' for produc.'||object_name || ';'
    from dba_objects
    where owner='PRODUC'
    and object_Type in ('TABLE','VIEW','SEQUENCE','PACKAGE',
    'PROCEDURE','FUNCTION');
    spool off

    @sistemas_syns
    Give a man a fish, you feed him for a day. Club him over the head with a fish, he'll leave you alone.

  9. #9
    Join Date
    Dec 2002
    Location
    Brazil
    Posts
    59
    Thank you!
    You convinced me.....
    I wiil use synonyms.....
    Nadia

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •