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 22.214.171.124.0 - Production on Tue Oct 26 19:45:49 2004
Copyright (c) 2004 PeteFinnigan.com Limited. All rights reserved.
NAME OF OBJECT TO CHECK [USER_OBJECTS]: DBMS_SYSTEM
OWNER OF THE OBJECT TO CHECK [USER]: SYS
OUTPUT METHOD Screen/File [S]: S
FILE NAME FOR OUTPUT [priv.lst]:
OUTPUT DIRECTORY [DIRECTORY or file (/tmp)]:
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 http://www.petefinnigan.com/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.