Call: +44 (0)1904 557620 Call

Pete Finnigan's Oracle Security Weblog

This is the weblog for Pete Finnigan. Pete works in the area of Oracle security and he specialises in auditing Oracle databases for security issues. This weblog is aimed squarely at those interested in the security of their Oracle databases.

[Previous entry: "2 new books on Oracle security"] [Next entry: "Allowing a user read-only access to stored procedure source code"]

Writing to the alert log

I found a post on the ORACLE-L list a couple of days ago where the poster asked if there is anyway to intentionally write to the alert log. This is an interesting question. There is a way to write to the alert log and also to trace files or even both at the same time. This was explained by Jonathan Lewis and others in a reply to the post.

The functions to use are as follows:

dbms_system.ksdwrt(1,'xxxx') -- writes to the alert log
dbms_system.ksdwrt(2,'xxxx') -- writes to a trace file for the current session
dbms_system.ksdwrt(3,'xxxx') -- writes to both the alert log and a trace file
dbms_system.ksdddt -- used to write a date / time stamp
dbms_system.ksdind(n) -- indent the output using ":" characters
dbms_system.ksdfls -- flushes the output to file.

So what is the Oracle security angle here?

There are two considerations. The first is that the package DBMS_SYSTEM is quite a powerful package. It can be used to write to the alert log and trace files as we have seen but it can also be used to set events, set trace, wait for an event and even set boolean and integer parameters for the current session. As we will have seen already in part 1 and shortly in part 2 of my short article discussing why allowing users to set trace can be a security risk allowing users access to this package is also a risk because of the functionality available. It is not totally out of the question to prevent users or rather admin staff to write to the alert log but do not simply grant access to this package as it allows other functionality as we have seen.

Before allowing access to this package you should also be asking the question why. Why does a user need to write his or her own text messages to the alert log?

The default permissions granted on DBMS_SYSTEM in a 9iR2 installation are as follows:

who_can_access: Release - Production on Tue Oct 26 19:45:49 2004
Copyright (c) 2004 Limited. All rights reserved.

OUTPUT METHOD Screen/File [S]: S

Checking object => SYS.DBMS_SYSTEM

Object type is => PACKAGE (TAB)
Privilege => EXECUTE is granted to =>
Role => OEM_MONITOR (ADM = NO) which is granted to =>
User => SYS (ADM = YES)

PL/SQL procedure successfully completed.

For updates please visit /tools.htm


You can see how useful the script who_can_access.sql is for this type of permission check. Use the script to check which users and roles have access to this built in package in your database and revoke it from those who do not need it.

The second issue with this package is that it can be abused by a hacker to cause the database to crash resulting in a Denial of Service. This issue was discovered by Alexander Kornbrust and is fixed in the patch for alert #68. By sending a specifically crafted parameter to the DBMS_SYSTEM.KSDWRT function it is possible to crash the database. See Alex's alert, again the same advice is given to prevent this attack which is to revoke access to this package. Of course apply the alert #68 patch if it has not been applied already.

There has been 2 Comments posted on this article

November 1st, 2004 at 10:16 am

Pete Finnigan says:

Hi! Pete,

Just pointing out a typo. Is it not 3 as the first parameter to ksdwrt to write to both alert and trace files?


November 1st, 2004 at 10:35 am

Pete Finnigan says:

Hi Vasan,

Thanks very much for pointing that out, yes you are right it was a typo. I have ammended the entry now.