Auditing database interactions provides the ability to see who did what and when they did it in the database. This is useful in scenarios where the data stored is sensitive (eg. salary information, proprietary recipes) and you need to track who has accessed the data over a specific period of time. It can also be useful when trying to mitigate a breach or to determine what happened in the case of data that is changed/deleted improperly. SQL Anywhere auditing (when enabled) tracks all activity performed on a database, including:
- login attempts (including the user information for both successes and failures),
- timestamps of all events,
- permissions checks (including object information for both successes and failures) and
- all actions that require system privileges.
- Custom audit strings added to the audit log via the sa_audit_string(…) stored procedure
What exactly is audited can be managed using the sa_enable_auditing_type(…) system procedure. Auditing can be enabled and disabled at login time by setting the “conn_auditing” option. All of the auditing information is stored in the transaction log in version 16.
Version 17 introduces a new option, AUDIT_LOG, which allows you to specify one or more different targets for the auditing information. Potential targets include:
- TRANSLOG (default) – as in version 16, audit data is stored in the transaction log
- SYSLOG – audit information is logged to the system event tracing log (eg. the Windows event log on windows, and syslog on Linux/Unix)
- FILE – audit information is logged to the file specified (the server must have write access to it) using the server ETD (Event Trace Data) format
You may choose to use a different target for many reasons. For example, if you do not wish to use a transaction log for the database, if you have existing tools for the examination of the operating system logs, or if you simply wish to separate the auditing information about database operations from the actual recording of the execution of the operations.
Setting the audit_log option requires the SET ANY SECURITY OPTION privilege. If there are any problems with auditing targets, the server does its best to continue auditing, reverting to using the transaction log if other targets are unavailable.
Here is an example of how one might use the audit_log option and what the resulting logs would look like.
First we connect as a user with appropriate security administration privileges and turn on auditing and set the audit_log option to log audit details to a file:
SET OPTION PUBLIC.audit_log='FILE(filename_prefix=C:\work\issues\v17testing\auditing\)';
SET OPTION PUBLIC.auditing = 'On';
We can log a custom message to the audit log at any time, using the sa_audit_string() stored procedure:
CALL sa_audit_string( 'Auditing has begun.' );
GRANT CONNECT TO testuser identified by testuser;
Next, we connect as the newly created testuser, and execute the following statement, which will fail with a permissions error:
SELECT * FROM groupo.Employees;
Switching back to our original connection, we will turn off auditing, and log a corresponding message to the audit log.
CALL sa_audit_string( 'Auditing is ending.' );
SET OPTION PUBLIC.auditing = 'Off';
After completing the above, we have an audit file generated in the specified directory, called “_0.etd” by default
We can run:
dbmanageetd –o auditlog.out _0.etd
This creates a file “auditlog.out” that will contain a section that looks similar to the following:
First we see our custom audit string, which we can use to bookmark our test:
[2015-09-08T10:28:10.254-04:00] SYS_Audit_String text=[Auditing has begun.] connid=25 username=[DBA] recnum=17
Next we see various permissions checks related to calls made by DBISQL:
[2015-09-08T10:28:10.301-04:00] SYS_Audit_PermCheck success=1 perm_type=[Execute] detail1=[dbo.sa_locks] detail2=[NULL] connid=25 username=[DBA] recnum=18
[2015-09-08T10:28:10.301-04:00] SYS_Audit_PermCheck success=1 perm_type=[Execute] detail1=[dbo.sa_locks] detail2=[NULL] connid=25 username=[DBA] recnum=19
[2015-09-08T10:28:10.301-04:00] SYS_Audit_PermCheck success=1 perm_type=[MONITOR] detail1=[NULL] detail2=[NULL] connid=25 username=[DBA] recnum=20
Next we see various permissions checks related to calls made by DBISQL from the testuser connection:
[2015-09-08T10:28:12.064-04:00] SYS_Audit_PermCheck success=1 perm_type=[Execute] detail1=[dbo.sa_locks] detail2=[NULL] connid=8 username=[testuser] recnum=21
[2015-09-08T10:28:12.064-04:00] SYS_Audit_PermCheck success=0 perm_type=[MONITOR] detail1=[NULL] detail2=[NULL] connid=8 username=[testuser] recnum=22
[2015-09-08T10:28:12.064-04:00] SYS_Audit_PermCheck success=1 perm_type=[Execute] detail1=[dbo.sa_locks] detail2=[NULL] connid=8 username=[testuser] recnum=23
[2015-09-08T10:28:12.064-04:00] SYS_Audit_PermCheck success=0 perm_type=[MONITOR] detail1=[NULL] detail2=[NULL] connid=8 username=[testuser] recnum=24
Now we have the permissions failure for the select statement (signified by “success=0” in the audit record)
[2015-09-08T10:28:13.094-04:00] SYS_Audit_PermCheck success=0 perm_type=[Select] detail1=[GROUPO.Employees] detail2=[NULL] connid=8 username=[testuser] recnum=25
[2015-09-08T10:28:13.094-04:00] SYS_Audit_PermCheck success=0 perm_type=[Select] detail1=[GROUPO.Employees] detail2=[***] connid=8 username=[testuser] recnum=26
Finally, we see the custom message for the end of our audit:
[2015-09-08T10:28:19.256-04:00] SYS_Audit_PermCheck success=1 perm_type=[Execute] detail1=[dbo.sa_audit_string] detail2=[NULL] connid=25 username=[DBA] recnum=35
[2015-09-08T10:28:19.256-04:00] SYS_Audit_PermCheck success=1 perm_type=[MANAGE AUDITING] detail1=[NULL] detail2=[NULL] connid=25 username=[DBA] recnum=36
[2015-09-08T10:28:19.256-04:00] SYS_Audit_String text=[Auditing is ending.] connid=25 username=[DBA] recnum=37
