Oracle database....who am i

[Kuberski]

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?

whoami

oracle

who am i

rjk5555 pts/10 May 13 13:50 (mypc.domain.com)

==============================================
(index=oracle OR index=dblogs)
sourcetype=oracle:audit
<DB_User>SYSTEM</DB_User>
("<Action>100</Action>" OR "<Action>129</Action>")
| xmlkv
| 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

| TABLE OS_User, DB_User, oracle_sid, DB_Server, Userhost, Rpt_Time, Action_ID, Action_Name

[pmdba]

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:

1. Since you're using *NIX, you can remove the password on the oracle OS account and use "sudo" to authorize individual users to access the account through "su - oracle" as root, which doesn't require a password. This will eliminate anyone sharing passwords from being able to access the account, and prevent remote network access to the account.
2. Move user access to another system other than the database server, and limit db server access to the couple of system and database admins who really need it. That way "su - oracle" isn't even an option.
3. Lock all shared database accounts like SYSTEM that shouldn't be connected to directly. If someone screams, tell them it's policy.
4. Change the passwords on all shared database accounts that are connected to by users or applications, and set up proxy privileges to allow users to access them using their personal credentials.
5. Configure Splunk to alert any time a restricted account is accessed; if the connection comes over the network you have a better chance of identifying the end user than if they are using one or more accounts on the server.

[Kuberski]

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.

[pmdba]

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.