8i | 9i | 10g | 11g | 12c | 13c | 18c | 19c | 21c | 23c | Misc | PL/SQL | SQL | RAC | WebLogic | Linux

Proxy User Authentication and Connect Through in Oracle Databases

Connect to other database users without knowing their passwords using proxy users.

Why Proxy?
Proxy User and Connect Through
Identify Proxy Users
Grants for Proxy Users
Password Reset (Pre-9iR2)

Why Proxy?

There are a two main reasons for using proxy users.

Some DBA 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.
You have multiple developers working in a shared schema. Letting multiple people share the same credentials represents a security risk. Instead you create a separate proxy user for each individual, allowing them to connect to the schema owner with their own credentials. If a user leaves a project, you simply lock or drop their user, and they no longer have access to the shared schema.

Proxy User and Connect Through

Since Oracle 9i Release 2 it has been possible to create proxy users, allowing us to access a schema via a different username/password combination. This is done by using the GRANT CONNECT THROUGH clause on the destination user.

Create some test users.

-- Connect to a privileged user.
conn sys/SysPassword1@//localhost:1521/pdb1 as sysdba

--drop user schema_owner cascade;
--drop user my_user_1 cascade;


-- Create SCHEMA_OWNER.
create user schema_owner identified by SecretPassword1;
grant create session to schema_owner;


-- Create proxy user.
create user my_user_1 identified by MyPassword1;
grant create session to my_user_1;

All the MY_USER_1 user to make a proxy connection to the SCHEMA_OWNER user.

alter user schema_owner grant connect through my_user_1;

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

SQL> conn my_user_1[schema_owner]/MyPassword1@//localhost:1521/pdb1
SQL> show user
USER is "SCHEMA_OWNER"
SQL>

The proxy authentication can be revoked using the following command.

alter user schema_owner revoke connect through my_user_1;

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

There are a lot of other things we can do with proxy users. These may be useful for developers accessing the data via the middle tier, but may be less useful to DBAs.

-- Normal proxy.
alter user schema_owner grant connect through my_user_1;

-- Limit privileges to a specific role granted to the destination user.
alter user schema_owner grant connect through my_user_1 with role test_role;

-- Force authentication for authenticated roles.
alter user schema_owner grant connect through my_user_1 with role test_role authentication required;

-- Disable a specific role from the destination user.
alter user schema_owner grant connect through my_user_1 with role all except test_role;

-- Disable all roles from the destination user.
alter user schema_owner grant connect through my_user_1 with no roles;

Identify Proxy Users

Proxy users can be identified using the PROXY_USERS view.

select * from proxy_users;

PROXY                          CLIENT                         AUT FLAGS
------------------------------ ------------------------------ --- -----------------------------------
MY_USER_1                      SCHEMA_OWNER                   NO  PROXY MAY ACTIVATE ALL CLIENT ROLES

SQL>

The V$SESSION view only reports the destination user in the USERNAME column, so we can't see which users are direct connections and which users are proxy connections. Joining to the V$SESSION_CONNECT_INFO view gives us access to the AUTHENITCATION_TYPE column, which contains the value "PROXY" for proxy connections. This way we can query just the sessions that are using proxy authentication. Check out the proxy_sessions.sql script.

select s.sid, s.serial#, s.username, s.osuser, sci.authentication_type
from   v$session s,
       v$session_connect_info sci
where  s.sid = sci.sid
and    s.serial# = sci.serial#
and    sci.authentication_type = 'PROXY';

The proxy user details are part of the unified audit trail, so you can see both the client and the proxy user in the audit records.

select dbusername,
       dbproxy_username
from   unified_audit_trail
where  dbproxy_username is not null;

DBUSERNAME           DBPROXY_USERNAME
-------------------- --------------------
SCHEMA_OWNER         MY_USER_1

SQL>

When connected as a proxy user the SYS_CONTEXT function gives you access to the following parameters.

column session_user format a20
column session_schema format a20
column current_schema format a20
column proxy_user format a20

select sys_context('userenv','session_user') as session_user, 
       sys_context('userenv','session_schema') as session_schema,
       sys_context('userenv','current_schema') as current_schema,
       sys_context('userenv','proxy_user') as proxy_user
from   dual;

SESSION_USER         SESSION_SCHEMA       CURRENT_SCHEMA       PROXY_USER
-------------------- -------------------- -------------------- --------------------
SCHEMA_OWNER         SCHEMA_OWNER         SCHEMA_OWNER         MY_USER_1

SQL>

Grants for Proxy Users

Proxy users often confuse people when they are thinking about grants. Just remember that a proxy user is an alternate way to log into the destination user. Once logged in, you are running as the destination user, so all grants need to be applied to that user, not your user or the proxy user. For example.

Logged In As	Destination User	Apply Grants To
MY_USER_1	MY_USER_1	MY_USER_1
MY_USER_1[SCHEMA_OWNER]	SCHEMA_OWNER	SCHEMA_OWNER
SCHEMA_OWNER	SCHEMA_OWNER	SCHEMA_OWNER

Password Reset (Pre-9iR2)

Prior to introduction of proxy users in Oracle 9i Release 2, DBAs would often use the following trick to connect as other users when they didn't know the password. Please don't do this anymore, as proxy users solve this problem.

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.

For more information see:

Hope this helps. Regards Tim...

Back to the Top.