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. See for example the audit functionality. 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. You can find the code here.
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
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;
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' );
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.
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)
Test the Java code
Now check your local syslog (often /var/log/messages) for entries like
Oct 26 14:31:22 oracle-19c-vagrant Syslogtest: Hi there
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.