Writing a SPLUNK alert for SYSTEM logon to an Oracle database with oracle auditing turned on. Issue is that on the unix server box all users login with there id to the box and then "su - oracle" to connect to the id that runs the oracle instance. When you run the alert in SPLUNK it shows the user as ORACLE which is the correct command returned from "whoami". However what I would like is to have the command returned from "who am i" as this is the real user logged into the box that is the ORACLE account. Is this possible to do?
rjk5555 pts/10 May 13 13:50 (mypc.domain.com)
(index=oracle OR index=dblogs)
("<Action>100</Action>" OR "<Action>129</Action>")
| eval Rpt_Time = strftime (_time , "%Y/%m/%d %H:%M:%S %a")
| eval DB_Server = host
| eval empid = OS_User
| rename Action AS Action_ID
| lookup oracle-action-map Action_ID OUTPUT Action_Name
I get it; I work in an environment where these sort of questions come up all the time. We can't change the way Oracle does auditing, but we can enforce policy that ensures user actions are auditable as much as possible. I would advise the following:
I didn't think it was possible since it is how oracle does auditing.
Each user does have an individual account that they are supposed to be using to connect into the database that would provide the auditing information in detail that would be required. However we all know that not all users will use this method and "su - oracle" to get to the database occasionally. What we are trying to do is report on those occasions.
As far as the SYSTEM account, yes each user is supposed to be using their individual created account that has the a default role and dba role that they can set if needed. We wanted this alert though to see if they are using SYSTEM instead of their individual account.
You're talking about changing the actual database audit data where the OS user is recorded, not just how Splunk reports on it. Unfortunately I don't think this is something that can be changed, as it is internal to the way Oracle records audit data.
The problem I see is that under your current arrangement you effectively have users connecting to a group DB account from a group OS account, which makes accurate auditing pretty much impossible.
A better policy would be to have users connect directly from their own accounts instead of using "su - oracle" first. The oracle OS account should really only be used for software maintenance and certain types of batch automation at most. That would allow the OS userid recorded in the audit trail to accurately reflect the true user.
The same is true of the SYSTEM database account; it really shouldn't be used for much of anything. Consider granting necessary privileges (and ONLY the minimum necessary privileges) to individual user accounts, if possible, and locking the SYSTEM account entirely. If users really must connect to a group database account, then consider using proxy connections as described here. That way the XML audit trail will record the proxy username as well as the database account username, plus users don't have to know the actual password of the group account.