Auditing authentication issues in a distributed environment

Question

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?

Answer 1

As per the definition of your database link, it's a fixed user database link.

If a link includes a fixed user, the fixed user's username and password are used to connect to the remote database.

For example, you have connected as kjohnston and created a fixed user private database link, If kjohnston uses the fixed user link in a query, then kjohnston is the user on the local database, but you connect to the remote database as testuser.

So you can't get the session information of testuser in the local database. DB one is just acting as client tool for the testuser.

SQL> 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;  2    3    4    5    6    7    8  

USERNAME               LOCK_DATE
------------------------------ ---------
OS_USERNAME
--------------------------------------------------------------------------------
USERHOST
--------------------------------------------------------------------------------
 SESSIONID OS_PROCESS
---------- ----------------
TESTUSER               10-MAR-17
oracle
testserver1
   1590012 3348

You can get the locked username using the above query.

On DB1, you can query the dba_db_links view to know who is the owner of that dblink.

SQL> select * from dba_db_links where username='TESTUSER';

And you can get all the SQL fired by that user using either auditing or tracing.

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).

You can't get information from these views on DB1 because that information is belongs to testuser which has created session on DB2.