Proxy User Authentication and Connect Through in Oracle Databases

This article describes two methods a database administrator can use to log on to a user when the password is not known.

Password Reset
Proxy User and Connect Through

Password Reset

Certain tasks, like creating private database links or setting up jobs using the DBMS_JOB package, require the administrator to log in as a specific user. This can present a problem if the administrator doesn't know the password. One workaround for this was to save the users current password hash, change the password, logon and perform the task, then change the password back to the hashed value.

-- Get the current password hash.
CONN / AS SYSDBA

SELECT password
FROM   dba_users
WHERE  username = 'SCOTT';

PASSWORD
------------------------------
F894844C34402B67

1 row selected.

SQL>

-- Reset the password to a known value.
ALTER USER scott IDENTIFIED BY DummyPassword1;

-- use the known password to connect to the user and perform the task.
CONN scott/DummyPassword1;

-- #### Do the task now. ####

-- Reset the password using the hash.
CONN / AS SYSDBA

ALTER USER scott IDENTIFIED BY VALUES 'F894844C34402B67';

In 11g the PASSWORD column was removed from the DBA_USERS view, but you can still retrieve it from the USER$ table.

SELECT password
FROM   sys.user$
WHERE  name = 'SCOTT';

PASSWORD
------------------------------
F894844C34402B67

1 row selected.

SQL>

The logon_as_user_orig.sql and logon_as_user.sql scripts make it a little easier to retrieve the relevant information to perform these actions.

This method works fine, but it does mean there is a period of time when the password is incorrect, which may affect users and application servers trying to make new connections.

Proxy User and Connect Through

Since Oracle 9i Release 2 it has been possible to create proxy users, allowing you to access a schema via a different username/password combination. This is done by using the GRANT CONNECT THROUGH clause on the destination user. Assuming we have a user called SCOTT and we want to create a proxy user to allow someone to connect to SCOTT without knowing the password, we could do the following.

CONN / AS SYSDBA
CREATE USER test_user IDENTIFIED BY test_user;

ALTER USER scott GRANT CONNECT THROUGH test_user;

We can now connect to the SCOTT user, using the credentials of the proxy user.

SQL> CONN test_user[scott]/test_user
SQL> SHOW USER
USER is "SCOTT"
SQL>

Proxy users can be identified using the PROXY_USERS view.

SELECT * FROM proxy_users;

PROXY                          CLIENT                         AUT FLAGS
------------------------------ ------------------------------ --- -----------------------------------
TEST_USER                      SCOTT                          NO  PROXY MAY ACTIVATE ALL CLIENT ROLES

SQL>

The proxy authentication can be revoked using the following command.

ALTER USER scott REVOKE CONNECT THROUGH test_user;

Using this method the administrator can now set up their privileged account have connect through access to any other user, allowing them to perform tasks as that user, without having to alter the user's password.

For more information see:

Creating Proxy User Accounts and Authorizing Users to Connect Through Them

Hope this helps. Regards Tim...

Back to the Top.