Auditing Categories - Implementing Database Security and Auditing - page 360

do not correspond. If you are logging all errors, you can identify this situa-
tion and react. Failed logins are another good example of an error that
needs to be logged and monitored, even if you are not auditing logins to the
database. Finally, any failed attempt to elevate privileges is a strong indica-
tor that an attack may be in progress.
Errors are also important from a quality perspective, and this also maps
well to compliance. Production applications that are causing errors because
of bugs and application issues should be identified and fixed. Logging SQL
errors is often a simple way to identify these problems. Therefore, even
when your primary concern is a security initiative, providing this informa-
tion to the application owners can make you a hero, because no one likes
running code that still has issues that can usually be easily resolved. If you're
lucky, these errors might even point you in the direction of problems that
affect response time and availability.
Detailed error auditing is supported by some of the database vendors,
and you can refer to the reference guide of your environment to see how to
do this. In Oracle you can again use system triggers:
create table error_audit
(
user_id varchar2(30),
session_id number(8),
host varchar2(30),
error_date date,
error varchar2(100)
);
Next, create the trigger to be fired when an error occurs:
create or replace trigger
audit_errors_trigger
AFTER SERVERERROR ON DATABASE
BEGIN
insert into error_audit values(
user,
sys_context('USERENV','SESSIONID'),
sys_context('USERENV','HOST'),
sysdate,
dbms_standard.server_error(1)
);
COMMIT;
END;