Oracle Database: Write arbitrary log messages to the syslog from PL/SQL

Syslog is a standard for message logging, often employed in *NIX environments. It allows separation of the software that generates messages, the system that stores them, and the software that reports and analyzes them. Each message is labeled with a facility code, indicating the software type generating the message, and assigned a severity level.

In *NIX systems syslog messages often end up in /var/log/messages. You can configure these messages to be forwarded to remote syslog daemons. Also a pattern which often seen is that the local log files are monitored and processed by an agent.

Oracle database audit information can be send to the syslog daemon. If you however want to use a custom format in the syslog or write an entry to the syslog which is not related to an audit action, this functionality will not suffice. How to achieve this without depending on the audit functionality is described in this blog post. PL/SQL calls database hosted Java code. This code executes an UDP call to the local syslog.

Syslog functionality

There are different ways to send data to the syslog.

◉ By using the logger command
◉ Using TCP
◉ Using UDP

You can execute shell commands from the Oracle database by wrapping them in Java or C or by using DBMS_PIPE (see here). When building a command-line however to log an arbitrary message, there is the danger that the message will contain characters which might break your logger command or worse, do dangerous things on your OS as the user running your database. You can first write a file to a local directory from the database and send that using the logger command, but this is a roundabout way. Using UDP and TCP is more secure and probably also performs better (although I haven’t tested this).

TCP in contrast to UDP works with an acknowledgement of a message. This is done in order to provide the sender some confirmation the packet has been received. With UDP, it is ‘fire-and-forget’ for the sender and you do not know if the receiver has received the packet. UDP is faster as you can imagine since no confirmation is send.

In this example I will be using UDP to send a message to the local syslog. In order to allow this, rsyslog needs to be installed.

For Fedora this can be done with:

dnf install rsyslog

Next configure UDP access by uncommenting the below two lines in /etc/rsyslog.conf

$ModLoad imudp
$UDPServerRun 514

If the daemon is not running, start it with:

systemctl start rsyslog

If you want to start it on boot, do:

systemctl enable rsyslog

You might have to configure your firewall to allow access from localhost/127.0.0.1 to localhost/127.0.0.1 UDP port 514

Java in the Oracle Database

The Oracle database has out of the box packages to do TCP (DBMS_TCP). However there is no such functionality for UDP available. In order to provide this, I’ve written a small Java class. It can be installed using just PL/SQL code. I’ve tried this on Oracle DB 19c (using the following Vagrant box) but it is likely to work on older versions.

Create a testuser

First create a testuser and grant it the required permissions:

create user testuser identified by Welcome01;
/
grant connect,dba,resource to testuser;
/
begin
dbms_java.grant_permission( 'TESTUSER', 'SYS:java.net.SocketPermission', 'localhost:0', 'listen,resolve' );
dbms_java.grant_permission( 'TESTUSER', 'SYS:java.net.SocketPermission', '127.0.0.1:514', 'connect,resolve' );
end;
/

Register the Java code

Now create the Java code under the user TESTUSER. The below code is PL/SQL which can be executed in the database to store and compile the Java code.

SET DEFINE OFF
create or replace and compile
 java source named "SysLogger"
 as

import java.io.*;
import java.net.*;

public class Syslog {

// Priorities.
public static final int LOG_EMERG = 0; // system is unusable
public static final int LOG_ALERT = 1; // action must be taken immediately
public static final int LOG_CRIT = 2; // critical conditions
public static final int LOG_ERR = 3; // error conditions
public static final int LOG_WARNING = 4; // warning conditions
public static final int LOG_NOTICE = 5; // normal but significant condition
public static final int LOG_INFO = 6; // informational
public static final int LOG_DEBUG = 7; // debug-level messages
public static final int LOG_PRIMASK = 0x0007; // mask to extract priority

// Facilities.
public static final int LOG_KERN = (0 << 3); // kernel messages
public static final int LOG_USER = (1 << 3); // random user-level messages
public static final int LOG_MAIL = (2 << 3); // mail system
public static final int LOG_DAEMON = (3 << 3); // system daemons
public static final int LOG_AUTH = (4 << 3); // security/authorization
public static final int LOG_SYSLOG = (5 << 3); // internal syslogd use
public static final int LOG_LPR = (6 << 3); // line printer subsystem
public static final int LOG_NEWS = (7 << 3); // network news subsystem
public static final int LOG_UUCP = (8 << 3); // UUCP subsystem
public static final int LOG_CRON = (15 << 3); // clock daemon
// Other codes through 15 reserved for system use.
public static final int LOG_LOCAL0 = (16 << 3); // reserved for local use
public static final int LOG_LOCAL1 = (17 << 3); // reserved for local use
public static final int LOG_LOCAL2 = (18 << 3); // reserved for local use
public static final int LOG_LOCAL3 = (19 << 3); // reserved for local use
public static final int LOG_LOCAL4 = (20 << 3); // reserved for local use
public static final int LOG_LOCAL5 = (21 << 3); // reserved for local use
public static final int LOG_LOCAL6 = (22 << 3); // reserved for local use
public static final int LOG_LOCAL7 = (23 << 3); // reserved for local use

public static final int LOG_FACMASK = 0x03F8; // mask to extract facility

// Option flags.
public static final int LOG_PID = 0x01; // log the pid with each message
public static final int LOG_CONS = 0x02; // log on the console if errors
public static final int LOG_NDELAY = 0x08; // don't delay open
public static final int LOG_NOWAIT = 0x10; // don't wait for console forks

private static final int DEFAULT_PORT = 514;

/// Use this method to log your syslog messages. The facility and
// level are the same as their Unix counterparts, and the Syslog
// class provides constants for these fields. The msg is what is
// actually logged.
// @exception SyslogException if there was a problem
@SuppressWarnings("deprecation")
public static String syslog(String hostname, Integer port, String ident, Integer facility, Integer priority, String msg) {
try {
InetAddress address;
if (hostname == null) {
address = InetAddress.getLocalHost();
} else {
address = InetAddress.getByName(hostname);
}

if (port == null) {
port = new Integer(DEFAULT_PORT);
}
if (facility == null) {
facility = 1; // means user-level messages
}
if (ident == null)
ident = new String(Thread.currentThread().getName());

int pricode;
int length;
int idx;
byte[] data;
String strObj;

pricode = MakePriorityCode(facility, priority);
Integer priObj = new Integer(pricode);

length = 4 + ident.length() + msg.length() + 1;
length += (pricode > 99) ? 3 : ((pricode > 9) ? 2 : 1);

data = new byte[length];

idx = 0;
data[idx++] = '<';

strObj = Integer.toString(priObj.intValue());
strObj.getBytes(0, strObj.length(), data, idx);
idx += strObj.length();

data[idx++] = '>';

ident.getBytes(0, ident.length(), data, idx);
idx += ident.length();

data[idx++] = ':';
data[idx++] = ' ';

msg.getBytes(0, msg.length(), data, idx);
idx += msg.length();

data[idx] = 0;

DatagramPacket packet = new DatagramPacket(data, length, address, port);
DatagramSocket socket = new DatagramSocket();
socket.send(packet);
socket.close();
} catch (IOException e) {
return "error sending message: '" + e.getMessage() + "'";
}
return "";
}

private static int MakePriorityCode(int facility, int priority) {
return ((facility & LOG_FACMASK) | priority);
}
}
/

Make the Java code available from PL/SQL

create or replace

procedure SYSLOGGER(p_hostname in varchar2, p_port in number, p_ident in varchar2, p_facility in number, p_priority in number, p_msg in varchar2)

as

language java

name 'Syslog.syslog(java.lang.String,java.lang.Integer,java.lang.String,java.lang.Integer,java.lang.Integer,java.lang.String)';

Test the Java code

DECLARE

  P_HOSTNAME VARCHAR2(200);

  P_PORT NUMBER;

  P_IDENT VARCHAR2(200);

  P_FACILITY NUMBER;

  P_PRIORITY NUMBER;

  P_MSG VARCHAR2(200);

BEGIN

  P_HOSTNAME := NULL;

  P_PORT := NULL;

  P_IDENT := 'Syslogtest';

  P_FACILITY := NULL;

  P_PRIORITY := 1;

  P_MSG := 'Hi there';

  SYSLOGGER(

    P_HOSTNAME => P_HOSTNAME,

    P_PORT => P_PORT,

    P_IDENT => P_IDENT,

    P_FACILITY => P_FACILITY,

    P_PRIORITY => P_PRIORITY,

    P_MSG => P_MSG

  );

END;

Now check your local syslog (often /var/log/messages) for entries like

Oct 26 14:31:22 oracle-19c-vagrant Syslogtest: Hi there

Considerations

TCP instead of UDP

This example uses UDP. UDP does not have guaranteed delivery. You can just as well implement this with TCP. Using TCP you do not require custom Java code in the database but you do require Access Control List (ACL) configuration and have to write PL/SQL (using UTL_TCP) to do the calls to rsyslog. An example on how this can be implemented, can be found here.

Custom audit logging to syslog

Using the Oracle feature Fine Grained Auditing (FGA), you can configure a handler procedure which is called when a policy is triggered. Within this procedure you can call the PL/SQL which does syslog logging. The PL/SQL procedure has a SYS_CONTEXT available which contains information like the user, proxy user and even the SQL query and bind variables which triggered the policy (when using DB+EXTENDED logging).

If you want to store what a certain user has seen, you can use Flashback Data Archive (FDA) in addition to FGA. This feature is available for free in Oracle DB 12c and higher. In older versions this depends on the Advanced Compression option. If you combine the FDA and the FGA, you can execute the original query on the data at a certain point in time (on historic data). You can even store the SYS_CONTEXT in the FDA which allows for a more accurate reproduction of what happened in the past. When using these options, mind the performance impact and create specific tablespaces for the FDA and FGA data.

Source: technology.amis.nl