SQL Anywhere Studio Security Guide
Auditing
This page describes operation of Adaptive Server Anywhere in a manner equivalent to a C2-security-certified configuration. It does not provide general-purpose information on the topic.
This section identifies the different audit records that may be generated by the engine or server, the information contained in the record, and when the record is generated. Descriptions of the audit records generated by the three database utilities dblog, dbtran, and dbwrite in the .alg file appear in Auditing of database utilities.
| Type | Information | Use |
|---|---|---|
| Attempting Operation | date/time, SQL of attempted operation |
This record displays the operation being attempted. This is necessary because of the way the transaction log works.
The transaction log contains SQL to replicate changes made to the database data or schema if recovery becomes necessary. Audit records become part of this log so that each permission check is recorded as it happens, and so that the activity on the database can be recreated later. However, if a permissions check fails, then the operation being attempted doesn't actually happen, and therefore doesn't get logged. In this case, there is no way of knowing what was being attempted. This is especially important when a non-DBA user attempts something that requires DBA authority. For this reason, all DDL statements (and a few other statements as well) are recorded before they are attempted. |
| Operation Succeeded / Failed | date/time, success or failure | This record indicates the success or failure of the most recent Operation Attempt, Attempting to set public option, or Attempting SETUSER record for the same connection ID. |
| Checking permission | date/time, type of permission / authority, table name (if applicable), column name (if applicable), procedure / function name (if applicable) |
This record indicates that a permission or authority check of some kind took place. The permission in question is indicated, and can be one of:
DBA / Resource authority Insert / Update / Select / Delete / Alter / Resource permission on a table Update / Select / Resource permission on a table and column Grant Insert / Update / Select / Delete / Alter / Resource permission on a table Grant Update / Select / Resource permission on a table and column Execute permission on a procedure or function Grant Execute permission on a procedure or function |
| Checking user | date/time, user name | This record indicates that a user check took place. This can help determine ownership of objects, for example, user bob owns table T. If an insert is attempted on table T, we must check to see if the current user is user bob. The text of the record is Checking to see if user is user name. |
| Set Public Option | date/time, name of option | This record indicates that a user attempted to set an option owned by the PUBLIC user. Only users with DBA authority are allowed to do this, so this check will always be followed by a DBA authority check. An Operation Succeeded/Failed record indicates success or failure. |
| Auditing Enabled / Disabled | date/time | This record indicates that the auditing public option has been changed. This record will always follow a Set Public Option record. This record is generated whether auditing is turned on or off. However, this record will not be generated if the user sets the auditing variable to ON when auditing is already on, or if the user sets the variable to OFF when auditing is already off. |
| Attempting SETUSER | date/time, name of user | This record indicates that a user has attempted a SETUSER command with a parameter. Only users with DBA authority are allowed to do this, so this record will always be followed by a DBA authority check. An Operation Succeeded/Failed record indicates success or failure. Note that the SETUSER command with no arguments is neither audited nor logged, since any user can execute that statement. |
| Attempting Connection | date/time, user name (if successful), machine address (local if the same machine), port type, success or failure | This record indicates that a connection attempt took place. |
| Trigger firing / finishing | date/time, name of trigger | This record indicates that a trigger has fired or finished executing. All audit records for the same connection in between these two records are auditing the trigger execution. Note that triggers execute with the permission of the table owner, not the caller, so any permission checks audited in between Trigger firing and Trigger finishing records are done with respect to the table owner. Examining the SQL statement that caused the trigger to fire will reveal the table owner. Look at the SQL statement for the same connection immediately preceding the Trigger firing record. It will be an insert, update, or delete on a table. The table name will be in the format owner.table. |
| String | date/time, string | Records of this type can be inserted into the audit trail using a system stored procedure called sa_audit_string. This procedure is executable only by users with DBA authority. Any string (up to 128 characters) can be specified. |
Table 6.2 - Format of audit records - fixed
Table 6.3 - Format of audit records - variable by type