Enabling Security and Auditing Options

[mscim]

Hi,

Can anyone give me a general overview how I can enable Oracle Server Security and Auditing features. I want to keep a log of

a: Login and logout time of each user of the database

b: Keep a record of each time certain fields in a table is updated, the time and user who updated it.

Thanks in advance.

 

[mike1953]

There is a command in SQL called 'audit'. I use 'audit connect' to keep a record of logins, logouts, and failed connect attempts. Be aware that a command like this can cause your SYS.AUD$ table to grow prodigiously.
I have not investigated whether the audit command can track to the field level, but you can use a table trigger to perform this level of capture.

HTH,
Mike

 

[lbreimyer]

There is also a lot of 3rd party audit software that can alleviate the often tedious process of sorting through your audit logs for desired information. I use SecurityExpressions. Sometimes it's worth investing in a timesaver product...

 

[mscim]

As with tracking DML changes to certain column, I have created a trigger to track down the users.

select terminal into v_host
from DBA_AUDIT_SESSION
where sessionid = sys_context('USERENV','SESSIONID');

insert into audit_mytable values
( ld.name, ld.job, ld.salary, ld.address,
:new.name, :new.job, ld.salary, :new.address,
sysdate, user, v_host
);
End;

How can I improve this trigger by capturing the user OS login ?