I'm trying to get a better handle on what locked out a specific user when multiple Oracle databases are involved using database links.
Setup:
sqlplus kjohnston@db2
create user testuser identified by "Dummypass1";
grant create session to testuser;
exit;
sqlplus kjohnston@db1
create public database link testlink using 'DB2';
create user testuser identified by "Differentpass1";
grant create session to testuser;
exit;
sqlplus testuser@db1
Run the following a few times until testuser@db2 is locked.
SELECT * FROM dual@testlink;
On DB2, I can query on the following:
SELECT users.username, users.lock_date, aud.os_username, aud.userhost, aud.sessionid, aud.os_process
FROM dba_users users
JOIN dba_audit_trail aud
ON aud.username = users.username
AND aud.timestamp = users.lock_date
WHERE users.account_status in ('LOCKED', 'LOCKED(TIMED)')
AND aud.action = 100
AND aud.returncode = 1017;
USERNAME LOCK_DATE OS_USERNAME USERHOST SESSIONID OS_PROCESS
TESTUSER 09-MAR-17 09.39.14 SYSTEM VM1 10484322 404:5808
In this case, it's obvious that this lock originated from a database link since the OS_USERNAME is SYSTEM and the USERHOST is VM1, which is a database server (so few people have access to it anyways) where database DB1 is running from.
However, i'm unsure where to go from here in my analysis. Sure, I have the SESSIONID and the OS_PROCESS from dba_audit_trail (as shown above), but I've been unable to successfully query the appropriate table on DB1 in order to narrow down what was running and who was running what on DB1.
I've tried querying v$session
(on SID and AUDSID columns) and v$active_session_history
(on SESSION_ID) on both DB1 and DB2 in an attempt to get more information. However, all attempts to query these tables on either database using either SESSIONID or OS_PROCESS values have failed (0 results returned).
Since there are no private database links on DB1 that connects directly to testuser, it looks like this probably originated from a public proxy database link, but i'm unsure how to find what testuser@db1 was actually running at the time that testuser@db2 got locked out.
Also, consider the possibility that testuser doesn't equate to a specific person, but it is an application schema that many different programmers may have access to.
Also, the logged in user that ran the dblink connection may not even be TESTUSER@DB1 since a user with create any procedure privilege could have:
create procedure testuser.testproc
v_var varchar2(1);
begin
SELECT dummy
INTO v_var
FROM dual@db2;
end testproc;
exec testuser.testproc;
Any ideas how I can track down what locked out testuser@db2 (the SQL that was running, preferably) given my supplied example?