Pete Finnigan's Oracle Security Weblog

I found the Amis blog posting titled "Addition to LOG4PLSQL to make log level adjustments at runtime" very interesting the other day and worth some comment here.

I came across the LOG4PLSQL software quite a long time ago for the first time when I was adding some instrumentation to a PL/SQL program that generated a large set of PL/SQL packages and triggers that when run generated SQL INSERT and UPDATE statements. I do like programs that generate programs and even better when you can generate a program that generates a program that generates a program..:-).

Anyways I needed to log what was generated in the triggers so I was looking around at what was available and came across LOG4PLSQL. I ended up instead using a simple trace package that I had originally written around 1998 instead but I was quite impressed with LOG4PLSQL. My own package was much simpler but offered the ability to generate log lines to the screen or file and also it had levels. e.g. I could add log statements to various parts of the programs and assign a level, such as 0 - 9, FLOW to ERROR logging. These levels could then be manipulated at run time by telling the session to log levels 0 - 4 for instance or 0 - 7 or whatever.

I will tidy up my code sometime and add it to my site tools page if it would be useful to readers. I have used it many times and although it’s simple it’s useful.

What has this got to do with security? - Well I always like the idea of using instrumentation in programs I write that can be turned on or off at run time and compiled out if possible
(I have C #define versions as well that can be compiled out if necessary - again I will add them to my site at some point). Logging is useful, very useful for run time analysis of why a program is misbehaving. It is great to be able to tell a client to turn on the trace and to be able to tell them why there is an error due to application misuse or data corruption. Logging is also useful for auditing and for detecting application misuse and privilege misuse. Logging to a file is more secure than writing logs to database tables as its likely to be harder for a hacker to erase a log file if he only has database access.

Back to the Amis blog entry. In his entry Dick is talking about changing the logging levels at runtime and an issue he had whereby he could not make code changes to change the DEFAULT_LEVEL parameter. Dick describes code changes he made to LOG4PLSQL to change the DEFAULT_LEVEL parameter to a function that returns the level which is stored in the database. Also there is a periodic read of the variable and only sessions started after a change take on the change. This is a useful change to this already useful and rich software.