One project every month - making stuff better ...

PLSQL, syslog and the story of Bolas spiders

So I am setting up a server for a bigger project that I'm playing with at the moment, and I wanted a quick and easy correlated way of logging messages/errors locally on the machine as well as using my airbrake utility. So I wanted all my components to write to the syslog on the linux server I'm setting up, even my database.

So the very first thing you want to read, whenever you want to implement any protocol/service/functionality, is the RFC if one exists. Syslog RFC number is 3164.

One really cool thing about RFC's, is that they are very informal and sometimes contains little nuggets of gold, in the form of jokes/stories/easter-eggs etc, and syslog is not without this. Go and read section 6 Security Considerations in the syslog RFC, and learn something about Bolas Spiders.

So reading this RFC, we can see that we need to be able to write the syslog package to the syslog server, and that the format of the actual message is this:

<pri>[timestamp] [server] [tag]: [message]

pri is a combination of the facility (service reporting the error message) and the severity of the message. Each are assigned a number, and then you take the number for the facility, multiply by 8, and then add the numeric value for the severity. So for example, if we want to log a message from a user-level (numeric value 1) with a severity of error (numeric value of 3) the pri should be 1*8+3=11. The timestamp is just the local timestamp of the logging device/program in the this format: Mon dd hh24:mi:ss. Server is of course the server name, and tag is usually the program logging the message. The message is of course the text we want to log.

So if we imagine that we wanted to log a user-level error from the localhost from within the Oracle database with the message ORA-00001 Unique constraint violated, our message would look like this:

<11>Dec 09 22:54:17 localhost orcl: ORA-00001 Unique constraint violated

So to send the message we need to connect to the syslog tcp port (defaults to 601) and send that packet. For that we can utilize utl_tcp for the connection. So I have created a package called syslog_ninja that does exactly this.

NOTE: If you are running Oracle Linux, then you are most likely running Rsyslogd, and that service listens to a device socket directly on the host instead of a port. To change this, simply edit the rsyslog.conf file in /etc and uncomment the lines "$Modload imtcp" and "$InputTCPServerRun 601". After saving those changes restart the rsyslog service

To install the packages, download the following files from my github repository:

  • syslog_ninja.package.sql
  • syslog_ninja.package body.sql

The privileges required to run this package is "create procedure", "execute on utl_tcp" and if you are using 11g or later, we need a network acl to allow the user to connect to the localhost or the server where the syslog deamon is running. For an example of an ACL setup look at the acl.sql in the repository. Please note, that you need to be logged in as sysdba to create the ACL.

So once you have installed the package in a schema, the usage is quite simple. You can either use the function lf or the procedure lp (short for LoggerFunction and LoggerProcedure). By default you only need to sepcify the message, as the rest has default values. Facility is default "user-level", the severity is default "warning" and the tag is defaulted to the database name.

So here are a couple of examples:

-- Default invocation of function
select syslog_ninja.lf('Just a function warning') from dual;

-- Default invocation of procedure
exec syslog_ninja.lp('A default procedure warning');

-- Setting the severity to critical:
  syslog_ninja.lp(message => 'A critical message', severity => syslog_ninja.s_critical);

-- Changing the tag to current schema
  syslog_ninja.lp(message => 'Tagged with schema instead of database name', tag => syslog_ninja.t_current_schema);

Tagged in : UTL_TCP