Nov 11, 2018.
Setting the AUDIT_SYSLOG_LEVEL Parameter. Oracle 11g. REHL6 (Onwards).
Oracle Database – Enterprise Edition – Version 10.2.0.1 to 188.8.131.52 [Release 10.2 to 11.2]
Because of Infosec dictate we are required to port/export our DB logs to OS rsyslog.
Example: If the ‘Connect’ audit trail is enabled in the DB, the requirement would be to write these connect logs to OS rsyslogs.
AUDIT_SYSLOG_LEVEL parameter. When the AUDIT_TRAIL parameter is set to OS, writes DB audit records to the system audit log using the rsyslog utility.
To enable syslog auditing for all the users (privileged or not privileged), you assign a value of OS to the AUDIT_TRAIL initialization parameter, as described in “Setting the AUDIT_TRAIL Initialization Parameter”. You assign to the AUDIT_SYSLOG_LEVEL parameter a facility and priority in the format AUDIT_SYSLOG_LEVEL=facility.priority. The facility argument describes the part of the operating system that is logging the message while the priority argument defines the severity of the message. The syslog daemon compares the value assigned to the facility argument of the AUDIT_SYSLOG_LEVEL parameter with the rsyslog.conf file in order to determine where to log information. For example, the following statement identifies the facility as local1 with a priority level of warning:
Setting the AUDIT_SYSLOG_LEVEL initialization parameter to the default value
(NONE) will result in DBAs gaining access to the OS audit records.
To enable syslog auditing, follow these steps:
Assign a value of OS to the AUDIT_TRAIL initialization parameter:
SQL> ALTER SYSTEM SET AUDIT_TRAIL=OS SCOPE=SPFILE;
Options available for us:
Disables standard auditing. This value is the default if the AUDIT_TRAIL parameter was not set in the initialization parameter file or if you created the database using a method other than Database Configuration Assistant. If you created the database using Database Configuration Assistant, then the default is db.
Directs all audit records to an operating system file. Oracle recommends that you use the os setting, particularly if you are using an ultra-secure database configuration.
Directs audit records to the database audit trail (the SYS.AUD$ table), except for records that are always written to the operating system audit trail. Use this setting for a general database for manageability.
If the database was started in read-only mode with AUDIT_TRAIL set to db, then Oracle Database internally sets AUDIT_TRAIL to os. Check the alert log for details.
Performs all actions of AUDIT_TRAIL=db, and also populates the SQL bind and SQL text CLOB-type columns of the SYS.AUD$ table, when available. These two columns are populated only when this parameter is specified.
If the database was started in read-only mode with AUDIT_TRAIL set to db, extended, then Oracle Database internally sets AUDIT_TRAIL to os. Check the alert log for details.
Writes to the operating system audit record file in XML format. Records all elements of the AuditRecord node except Sql_Text and Sql_Bind to the operating system XML audit file.
Performs all actions of AUDIT_TRAIL=xml, and includes SQL text and SQL bind information in the audit trail.
Also set the AUDIT_SYSLOG_LEVELparameter.
SQL> ALTER SYSTEM SET AUDIT_SYSLOG_LEVEL=”local1.warning” SCOPE=SPFILE;
Set the AUDIT_SYSLOG_LEVEL parameter to specify a facility and priority in the format AUDIT_SYSLOG_LEVEL=facility.priority.
facility: Describes the part of the operating system that is logging the message. Accepted values are user, local0–local7, syslog, daemon, kern, mail, auth, lpr, news,uucp, andcron.
The local0–local7 values are predefined tags that enable you to sort the syslog message into categories. These categories can be log files or other destinations that the syslog utility can access.
priority: Defines the severity of the message. Accepted values are notice, info, debug, warning, err, crit, alert, and emerg.
The syslog daemon compares the value assigned to the facility argument of the AUDIT_SYSLOG_LEVEL parameter with the syslog.conf file to determine where to log the information. The decision where to write the syslog entries does not belong to the Oracle services, but to the syslog daemon.
For example, the following statement identifies the facility as local1 with a priority level of warning:
Add the audit file destination to the rsyslog configuration file /etc/rsyslog.conf.
For example, assuming you had set the AUDIT_SYSLOG_LEVEL to local1.warning, enter the following:
This setting logs all warning messages to the /var/log/audit.log file.
Comment: separate the entries in syslogd.conf by using TAB rather than spaces, otherwise it may not work for all syslogd versions, so the above would really be:
Also pre-create the file as follows (as root):
# touch /var/log/audit.log
The facility line for your messages in the file rsyslog.conf should appear before the “catch all” setting and you should include appropriate .none entry to “catch all” also.
Once the changes are made to the rsyslog.conf, restart the rsyslog service.
#systemctl restart rsyslog.servic
If you get a message like so:
Redirecting to /bin/systemctl restart syslog.service
Failed to restart syslog.service: Unit not found.
Then you are actually running rsyslog.
Make sure the changes are made to /etc/rsyslog.conf
#service rsyslog restart