Results 1 to 9 of 9
  1. #1
    Join Date
    May 2004
    Location
    BA [ARG]
    Posts
    137

    Unanswered: db link & alter session

    Hi all,

    I'm having a problem with database links. I work with a public database link (with info about the connection, server, SID) and a private database link (with the schema name).

    I'm trying to connect with an user (USER_A) that has many privileges (almost a DBA) and make an "alter session set current_schema=xxx" to another schema (USER_B) that has the private database link.

    The question is: Is that USER_A able to use the private db link that USER_B owns?

    Thanks in advance...
    Saludos...

  2. #2
    Join Date
    Jun 2003
    Location
    West Palm Beach, FL
    Posts
    2,713

    Cool

    No can do.
    The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

  3. #3
    Join Date
    May 2004
    Location
    BA [ARG]
    Posts
    137
    Why the laughing???
    I'm pretty fucked up now!!

    Well, thanks anyway... I will have to rethink some things.

    Saludos...

  4. #4
    Join Date
    May 2004
    Location
    BA [ARG]
    Posts
    137
    This thing is driving me nuts...

    I focused the problem using a different approach.

    Supose I have USER_A, USER_B and USER_C
    > USER_A and USER_B are in the same database, USER_C in another.
    > USER_A has a private db link (with user and pass) to USER_C and there is another public db link with the host.
    > USER_B has a select any table privilege.

    I have to able to select a table that belongs to USER_C connecting as USER_B.

    What I tried was to create synonyms inside USER_A for some tables from USER_B. This synonyms include the db link, i.e. "create synonym table_a for table_a@dblink"

    Now, if I try to run a select connecting as USER_B and altering session to USER_A I get an error (ORA-00942: table or view does not exist), but this is no privilege problem as far as I see.

    Is it possible to make that select??

    Thanks in advance!!

  5. #5
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >> USER_B has a select any table privilege.
    This fact is irrelevent WRT user C; since he is in a different DB.

    Think about this.
    I have "full DBA" privs in my DB,
    do you think this should be necessary & sufficient to diddle any data in your DB?

    A shovel is a great tool for creating a hole in the ground,
    but only if the "correct end" of the shovel comes into contact wit the Earth.
    You are using the "wrong end" of Oracle.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  6. #6
    Join Date
    May 2004
    Location
    BA [ARG]
    Posts
    137
    I truly cannot believe it.

    I added an after logon trigger to USER_B with an "execute immediate 'alter session set current_schema=USER_A'" and it worked!!!

    Why is it possible that making a plain 'alter session set current_schema=USER_A' after connecting with USER_B doesn't work without the trigger?

  7. #7
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    Who owns the trigger (SYS?)?
    Does User_B have ALTER SESSION priv?
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  8. #8
    Join Date
    Jun 2003
    Location
    West Palm Beach, FL
    Posts
    2,713

    Cool

    Quote Originally Posted by DKG
    What I tried was to create synonyms inside USER_A for some tables from USER_B. This synonyms include the db link, i.e. "create synonym table_a for table_a@dblink"
    Another workaround is:

    1) Create VIEWS on remote tables using USER_A (the db link id).
    2) GRANT select or whatever to USER_B.
    3) Create (or not) synonyms for USER_A.views on USER_B.

    The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

  9. #9
    Join Date
    May 2004
    Location
    BA [ARG]
    Posts
    137
    Quote Originally Posted by anacedent
    Who owns the trigger (SYS?)?
    Does User_B have ALTER SESSION priv?
    USER_B owns the trigger, since it has CREATE ANY TRIGGER priv, and it also has ALTER SESSION priv.

    I encourage you to try this, it cannot be possible that works using a trigger and not with an alter session.

    Also it is not necessary to create synonyms, with that trigger USER_B can make a select from a table from the db link.

    Ripley's... Believe it... or not (and if you don't, try it)

    Thanks, guys.

Posting Permissions

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