This discussion is archived
4 Replies Latest reply: Jun 18, 2013 10:55 AM by Zoran Pavlovic RSS

Authentication for connected user database links (release 11.x)

JohnWatson Guru
Currently Being Moderated

I know how to use database links in various forms, but I've been trying to think through how the authentication works for a connected user link in 11g. If I create the link like this,
create public database link using 'orcl';
then any user can use the link, provided they have an identical username/password in the two databases. With pre-11g passwords, it was understandable: the password was salted with the username, so the hash of the password would be the same in both databases, and I assumed that the logon through the link used some sort of IDENTIFIED BY VALUES mechanism. But in 11g, the salt will different in the two databases. So the hash will be different. And of course Oracle never stores the actual password. So I don't see how the authentication works. Can anyone explain?
This doesn't have any practical value at the moment, but I would be grateful for any insight.


  • 1. Re: Authentication for connected user database links (release 11.x)
    mtefft Journeyer
    Currently Being Moderated

    I believe the password is retained for the session, and that is the password that is used to make the connection. It is not dependent on retrieving the password or its hash from the dictionary. In fact, with situations like globally-identified users it would not be there anyway.

  • 2. Re: Authentication for connected user database links (release 11.x)
    JohnWatson Guru
    Currently Being Moderated

    Thank you for replying. That sounds plausible.

    Globally identified users (which would be a "current user" database link) are I think straightforward.

  • 3. Re: Authentication for connected user database links (release 11.x)
    mtefft Journeyer
    Currently Being Moderated

    You could try a test like this:

    • Create a user on both the local database and the remote database.
    • Set the password on the local database to LOCAL_PW and on the remote to REMOTE_PW
    • Create a database link to the remote database without credentials (the 'connected  user' link)
    • Connect to the local database as this test user.
    • Query a table (like DUAL) on the remote database via the link. The connection will be unsuccessful.

    {code}

    ORA-01017: invalid username/password; logon denied

    ORA-02063: preceding line from [remote database]

    {code}

    • Without disconnecting (but from a different session) change the user's password on the local database to REMOTE_PW. The passwords are now the same on both instances.
    • Query a table (like DUAL) on the remote database via the link. The connection will again be unsuccessful. This demonstrates that the connection is not using the password from the local database's dictionary.
    • On the remote database, change the user's password to LOCAL_PW. Note that the passwords no longer match.
    • Query a table (like DUAL) on the remote database via the link. This time, the connection will be successful, because the credentials that created teh session can be successfully authenticated on the remote database.
  • 4. Re: Authentication for connected user database links (release 11.x)
    Zoran Pavlovic Explorer
    Currently Being Moderated

    Yes, password is retained in session (I am not sure, but I think that it's stored encrypted as Authentication Data in USERENV Context).

     

    - Zoran

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points