Home » RDBMS Server » Security » expanding audit trail information (Oracle 10gR2 RHEL4 linux)
expanding audit trail information [message #345208] Tue, 02 September 2008 10:52 Go to next message
earniep
Messages: 4
Registered: September 2008
Location: Aruba
Junior Member

Hi all,

my name is earnie, and i'm a oracle dba.
The company for which i work for gave me recently the task to setup a auditing trail for all users who log into the database server.So i did that and set a auditing on all sessions.

The kind of auditing is operating system based, meaning that every thing is being logged to the syslog server locally witch in turn sends all information to a remote syslog server.

Every thing works fine, but the only problem is that the audit trail functionality that oracle uses doesn't log wich program the user (client) uses to connect to the database.
The question that i have is if there is a way i can include this information whatsoever to the auditing trail log?? i noticed that the sys.aud$ doesn't include a 'PROGRAM' column.

An example of the logs I'm getting u can see below


Sep 2 10:05:33 servername user:alert Oracle Audit[598190]: SESSIONID: "2786212"
ENTRYID: "1"
STATEMENT: "1"
USERID: "EARNIEP"
USERHOST: "client"
TERMINAL: "pts/0"
ACTION: "100"
RETURNCODE: "0"
COMMENT$TEXT: "Authenticated by: DATABASE"
OS$USERID: "oracle"
PRIV$USED: 5

but no PROGRAM to be seen...

thank you for your help in advanced

regards,

Earnie
Re: expanding audit trail information [message #345212 is a reply to message #345208] Tue, 02 September 2008 10:59 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
If you want to add some data that are not in standard audit trail you have to use a (after logon) trigger.

Why do you want to record program name?

Regards
Michel
Re: expanding audit trail information [message #345215 is a reply to message #345208] Tue, 02 September 2008 11:06 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
earniep wrote on Tue, 02 September 2008 08:52

but no PROGRAM to be seen...



The program name is under control of the client & can easily be spoofed.
Re: expanding audit trail information [message #345218 is a reply to message #345215] Tue, 02 September 2008 11:12 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
./fa/1637/0/ That was my next answer.

Regards
Michel
Re: expanding audit trail information [message #345237 is a reply to message #345218] Tue, 02 September 2008 12:55 Go to previous messageGo to next message
earniep
Messages: 4
Registered: September 2008
Location: Aruba
Junior Member

Hey peeps,

You told me that i can use a after logon trigger, but that doesn't solve the fact that it must log it to the syslog..how can i do that simultaneously...

The commands a used so far are..

audit_syslog_level string
SQL> ALTER SYSTEM SET audit_trail=OS SCOPE=SPFILE;

System altered.

SQL> ALTER SYSTEM SET audit_syslog_level='USER.ALERT' SCOPE=SPFILE;


audit insert, update, delete on sys.aud$ by access;
audit session ;
audit create user by access;
audit alter user by access;
audit profile by access;
audit system grant by access;

How can i put the audit session to log programs etc..

I'm not a expert for that matter..

thank you
Re: expanding audit trail information [message #345240 is a reply to message #345237] Tue, 02 September 2008 13:06 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
How can i put the audit session to log programs etc.

You can't.

You didn't answer the question.

Another one, what's the need of "audit insert, update, delete on sys.aud$ by access;"?

Also, please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code (See SQL Formatter).
Use the "Preview Message" button to verify.

Regards
Michel
Re: expanding audit trail information [message #345242 is a reply to message #345240] Tue, 02 September 2008 13:16 Go to previous messageGo to next message
earniep
Messages: 4
Registered: September 2008
Location: Aruba
Junior Member

This :
'audit insert, update, delete on sys.aud$ by access '
was done because our company internal auditor told me so, to prevent anyone to temper with the table.( i know sounds stupid but i just had to do it!!)

So, going back to my question ;

1. it is not possible to log user program information using the basic audit trail functionality
2. it is also not possible to 'Spoof' the program information out of the v$session table with a 'trigger' and push this to the standard audit trail
3. The only method that i can let oracle talk to my syslog server using is by using the inbuilt audit trail functionality

so...

i'm doomed...this can't be done?? Sad
Re: expanding audit trail information [message #345246 is a reply to message #345242] Tue, 02 September 2008 13:37 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
1. Right
2. Wrong, you can extract it from v$session but you have to use your owne trigger and this information is not reliable as client can put it what it wants (so my question you still didn't answer)
3. Did you search another way?

Regards
Michel

[Updated on: Tue, 02 September 2008 13:39]

Report message to a moderator

Re: expanding audit trail information [message #345254 is a reply to message #345246] Tue, 02 September 2008 14:04 Go to previous messageGo to next message
earniep
Messages: 4
Registered: September 2008
Location: Aruba
Junior Member

Michel,

I did try a auditing kind of trigger which logs all clients info before and after logging to a table and it worked. this also includes the program information (wich is not my idea but from those stupid AUDITORS i'm working with Mad )

The main problem was to get that information out of the table and send to the local sys log server real time.

So I'm stuck.

I know u don't know all the answers either, but i appreciate the help and support gave me so far

regards,

Earnie (frustated dba'er)



Re: expanding audit trail information [message #345262 is a reply to message #345254] Tue, 02 September 2008 14:31 Go to previous message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I don't know the answer but they are many Java classes on the web to manage syslog, you can embed them into Oracle.
For instance:
http://www.devdaily.com/java/jwarehouse/syslog-1.2/com/ice/syslog/Syslog.java.shtml
http://www.loganalysis.org/sections/syslog/syslog-programming/index.html

Regards
Michel
Previous Topic: migrate oracle 8i to 10g
Next Topic: View package body role
Goto Forum:
  


Current Time: Sat Dec 10 11:12:06 CST 2016

Total time taken to generate the page: 0.21865 seconds