I had a few questions come up lately that involved proxy users and some of the answers were surprising. To illustrate the scenarios I’ll create a couple of users, one with a password, one without, and a user that will proxy into each of them. I’ll also grant the users privileges to look at some of the data dictionary to see how their activity is tracked.
Setup
CREATE USER testuser1 IDENTIFIED BY "ProxyThis123"; CREATE USER testuser2 NO AUTHENTICATION; GRANT CREATE SESSION TO testuser1; GRANT CREATE SESSION TO testuser2; GRANT SELECT ON dba_users TO testuser1; GRANT SELECT ON dba_users TO testuser2; GRANT AUDIT_VIEWER TO testuser1; GRANT AUDIT_VIEWER TO testuser2; CREATE USER proxyuser IDENTIFIED BY "ProxyPa55w0rd"; ALTER USER testuser1 GRANT CONNECT THROUGH proxyuser; ALTER USER testuser2 GRANT CONNECT THROUGH proxyuser;
When has the user logged in?
First question, “has this user logged in at all?” In this case the account had a password so it could have logged in directly or via proxy. Since I just created them, I know they haven’t logged in yet, so I’ll proxy in to each user and check the LAST_LOGIN value from DBA_USERS to see how they show up.
SQL> connect proxyuser[testuser1]/ProxyPa55w0rd@testdb
Connected.
SQL> connect proxyuser[testuser2]/ProxyPa55w0rd@testdb
Connected.
SQL> select username, last_login from dba_users where username in ('TESTUSER1','TESTUSER2','PROXYUSER');
USERNAME LAST_LOGIN
____________ ______________________________________________
PROXYUSER 2026-10-13 16:47:08.000000 AMERICA/NEW_YORK
TESTUSER1
TESTUSER2
3 rows selected.
Right off the bat, we can see that logging in by proxy doesn’t update the proxy targets, but rather only the PROXYUSER has its last login updated. This can give the impression the user has never been used. Some might think this a way to sneak in to a database undetected. But, looking at the audit trail we can see the connections were established and tracked by the proxy user and the target user.
SQL> SELECT event_timestamp, action_name, dbusername, dbproxy_username, current_user
2 FROM unified_audit_trail
3 WHERE dbusername IN ('TESTUSER1', 'TESTUSER2', 'PROXYUSER') AND action_name = 'LOGON'
4* ORDER BY event_timestamp;
EVENT_TIMESTAMP ACTION_NAME DBUSERNAME DBPROXY_USERNAME CURRENT_USER
_____________________________ _______________________ _____________ ___________________ _______________
2026-10-13 16:47:08.816668 LOGON TESTUSER1 PROXYUSER TESTUSER1
2026-10-13 16:47:25.680804 LOGON TESTUSER2 PROXYUSER TESTUSER2
Interestingly though, the DBA_USERS.LAST_LOGIN doesn’t reflect the last time it logged in via proxy. It did capture the TESTUSER1 time (47:08), but not the TESTUSER2 time (47:25). I don’t know if this is a bug or an intentional feature.
Dropping Proxy Users
Even though the auditing tracks a single session, if you try to drop either the proxying user or the proxy target, they are both considered to be connected. From the previous example, I’m still connected by proxy into TESTUSER2. Trying to drop either of the users then fails.
SQL> drop user proxyuser; drop user proxyuser * ERROR at line 1: ORA-01940: cannot drop a user who is currently connected Help: https://docs.oracle.com/error-help/db/ora-01940/ SQL> drop user testuser2; drop user testuser2 * ERROR at line 1: ORA-01940: cannot drop a user who is currently connected Help: https://docs.oracle.com/error-help/db/ora-01940/
Changing target user’s password
Proxy users are typically used when you don’t have the target user password; either because the password is unknown or the user is defined with NO AUTHENTICATION and thus, has no password. So the next question was, can the proxy user change the password of its targets?
SQL> connect proxyuser[testuser1]/ProxyPa55w0rd@testdb Connected. SQL> alter user testuser1 identified by "MyNewPa55w0rd" replace "ProxyThis123"; User TESTUSER1 altered. SQL> connect testuser1/MyNewPa55w0rd@testdb Connected.
So, yes, when you proxy into user’s account that has a password, you can assign that user a new password but only if you know the existing one. But, what if there is no complexity function defined for the user’s profile? I’ll login as an administrative user and create a new profile without a complexity function and assign it to testuser1.
SQL> CREATE PROFILE no_verify LIMIT PASSWORD_VERIFY_FUNCTION NULL; Profile created. SQL> ALTER USER testuser1 PROFILE no_verify; User altered.
Now I’ll try again as PROXYUSER to login to TESTUSER1 and change the password without needing to know the old one.
SQL> connect proxyuser[testuser1]/ProxyPa55w0rd@testdb Connected. SQL> alter user testuser1 identified by "NoReplaceNeeded"; User TESTUSER1 altered.
That worked, and provides another good reason to use complexity on your user profiles for additional protection. Finally, can the proxy user create a password for a user that doesn’t have one?
SQL> connect proxyuser[testuser2]/ProxyPa55w0rd@testdb Connected. SQL> alter user testuser2 identified by "NewPa55w0rd"; Error starting at line : 1 in command - alter user testuser2 identified by "NewPa55w0rd" Error report - ORA-01031: insufficient privileges
The answer here is “no”, even if the profile has no complexity function. In order for TESTUSER2 to change from NO AUTHENTICATION to having a password, the user doing so must have the ALTER USER privilege. That could include TESTUSER2 itself or, more likely, an administrative user.
I was not aware of all of these features I began my investigation. I hope you found it interesting. Questions and comments, as always, are welcome.