Oracle Database Auditing/Logging

[Former Member]

Im working in a banking environment. There is a banking system that runs on Oracle version 9.2.0.4. The connection to the database is as below:

1000 Users----

---

>Application -

---

>Database

There are about 1000 users that logon to the application. All their activities are logged by the application. However, the application connects to the database via a user called SYSTEM. There a VERY high volumes of transactions passed onto the database generated by the users.

Now, there are DBAs that access the database directly and not through the application. Is there a way i can audit or log all their activity such as changing records, deleting appending etc without logging changes to the database that come from the users? We tried logging/auditing that but it also captured all the updates to the database that were being made by the users through the application (the very many transactions) and this slowed the system down drastically. Is there a way we can audit/log activity of just the people (DBA) that log directly to the database? Please assist urgently. Without their logging, there is a great risk of fraud as they can alter data directly.

[sebastian_sebaraj]

If you look at the Program column in v$session view, it shows what type of program is being used to connect to the database.

I am sure the Program has a value different than the tools used by other DBAs.

I do not know what is the value for the Program if the user connects to the database. Either you can audit all the connections other than the application's Program Name or you can monitor program like SQLPLUS/TOAD/etc/etc.

Hope it helps

[Former Member]

I think people got the question wrong. Its not SAP its an application called IFLEX. What i want to audit is the people taht access the database but NOT through the application. Those that change the tables and data directly and NOT through the application. I have about 3 people that can do this. Im not worried about the users using the application because they are logged by the application. Its the DBAs that im worried about. I want to be able to monitor their activity in the database. Want to know what data they change, etc.

[markus_doehr2]

> Want to know what data they change, etc

No program can help you if everyone is logging on with the same user ID (means effectively user "system").

Markus

[sebastian_sebaraj]

have you looked program column in v$session. Please read my earlier update

[sebastian_sebaraj]

with teh combination of DB triggers and program column in v$session you should be able to do what you are looking for

[former_member204746]

if not using SAP, ask this elsewhere. not in SDN.

[former_member204746]

SAP does not uses Oracle user SYSTEM, it uses one of these users:

SAPR3

SAPSR3

SAP[SID]

OPS$ users

that is it.

Again, you are asking a question that do not seem to be related to SAP. What SAP product are you using?

[Former Member]

Hi

Oracle auditing can be enabled for users who are accessing the system.

A basic guide to auditing can be found at

http://www.oracle-base.com/articles/10g/Auditing_10gR2.php

You can use various options to set it up for users accessing the Database either from the application or directly into the Database.

Rishi

[markus_doehr2]

Rishi,

Oracle auditing can be enabled for users who are accessing the system.

that is exactly his problem. Everyone (users and administrators) use the same user (namely "system") to do things on the database.

So that auditing won´t help him.

Markus

[markus_doehr2]

Im working in a banking environment. There is a banking system that runs on Oracle version 9.2.0.4. The connection to the database is as below:

1000 Users----

-

---

>Application -

-

---

>Database

There are about 1000 users that logon to the application. All their activities are logged by the application. However, the application connects to the database via a user called SYSTEM. There a VERY high volumes of transactions passed onto the database generated by the users.

Is that application somehow SAP related?

From a logical point of view there is no possibility to make a difference between "dba using system" and "user using system" - how should the system distinguish that?

It´s a VERY bad application design using the system user to run.

Markus