Proxy Login Time Not Recorded in dba_users (Doc ID 2711279.1)

Last updated on APRIL 17, 2023

Applies to:

Oracle Database - Enterprise Edition - Version 12.2.0.1 and later
Information in this document applies to any platform.

Symptoms

Creating a client user to connect as proxy as show below.

create user proxy_testingfortchar identified externally as 'proxy_testingfortchar@AD.COM';

ALTER USER proxy_testingfortchar grant connect through 'server@AD.COM';

alter user client grant connect through proxy;
Henceforth referred to client and proxy respectively (like the naming used in the above alter statement)

client = proxy_testingfortchar

proxy = server@AD.COM

ALTER USER proxy_testingfortchar grant connect through 'server@AD.COM';

-bash-4.1$
-bash-4.1$ sqlplus [proxy_testingfortchar]/@orcl12201

SQL*Plus: Release 12.2.0.1.0 Production on Wed Sep 9 07:12:35 2020

Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

SQL> show user;
USER is "PROXY_TESTINGFORTCHAR"
SQL>
SQL>

########## check for client user ####

select spare6 from user$ where user#=108;

select last_login from dba_users where username='PROXY_TESTINGFORTCHAR';

SQL>
SQL>
SQL> select spare6 from user$ where user#=108;

SPARE6
---------

SQL>
SQL> select last_login from dba_users where username='PROXY_TESTINGFORTCHAR';

LAST_LOGIN
---------------------------------------------------------------------------

SQL>

SQL> select * from proxy_users;

PROXY CLIENT AUT FLAGS
-------------------------------------------------------------------------------------------------------------------------------- -------------------------------------------------------------------------------------------------------------------------------- --- -----------------------------------
server@AD.COM PROXY_TESTINGFORTCHAR NO PROXY MAY ACTIVATE ALL CLIENT ROLES

SQL>
SQL>

where as for proxy user we can see last_login column updated in dba_users;

SQL> select USER_ID,USERNAME from dba_users;

USER_ID USERNAME
---------- --------------------------------------------------------------------------------------------------------------------------------

106 server@AD.COM

37 rows selected.

SQL>
SQL>

SQL> select spare6 from user$ where user#=106;

SPARE6
---------
09-SEP-20

SQL>
SQL>

SQL> select last_login from dba_users where username='server@AD.COM';

LAST_LOGIN
---------------------------------------------------------------------------
09-SEP-20 03.40.37.000000000 AM +00:00

SQL>
SQL>