Audit File Facility

Overview

SPD Server supports SQL audit logging of submitted SQL queries and proxy auditing of access to SPD Server resources. SPD Server proxy auditing and SQL audit logging (spdsaud) are enabled when the server is started using the -AUDITFILE or -SQLAUDITFILE parameters. You can enable proxy auditing, SQL audit logging, or both.
SPD Server auditing logs access to SPD Server resources. Auditing also logs implicit or explicit SQL pass-through queries that are submitted to SPD Server. Separate audit logs are created for proxy auditing and SQL audit logging. SPD Server includes three SAS programs (auditwithwhere.sas, auditraw.sas, and auditsql.sas) in the /samples directory of your SPD Server installation. You can use these programs to input the audit logs in to SAS tables. You can then query the SAS tables to determine access to SPD Server tables and resources.

Proxy Auditing

Proxy auditing helps you determine access to SPD Server resources. The audit record contains the following information:
  • the activity timestamp
  • the primary path of the domain that contains the resource
  • the LIBNAME of the domain
  • the user ID of the SPD Server user that is accessing the resource
  • the resource name
  • the resource type
  • the SPD user ID of the resource
  • the SPD group ID of the resource
  • the resource operation type for librefs:
    • ASSIGN
  • the resource operation type for tables:
    • DELETE
    • RENAME
    • OPEN
    • REOPEN
    • REPAIR
    • TRUNC
  • the resource operation type for clusters:
    • CREATE
    • UNDOCL
    • ADDCL
  • the resource operation type for a WHERE clause:
    • WHERE
  • the resource operation mode for librefs:
    • ACCESS
  • the resource operation mode for tables and clusters:
    • OUTPUT
    • INPUT
    • UPDATE
    • UTILITY
  • access requested to a resource by a user
  • access granted to a resource for the user
  • the ACLs that are associated with a resource

WHERE Clause Auditing

WHERE clause auditing provides an audit record that contains the following information:
  • the length of the WHERE clause
  • the contents of the WHERE clause
You enable WHERE clause auditing by using the WHEREAUDIT option. The maximum size that can be allocated to WHERE clauses is controlled by the WHAUDLEN option. For more information about these options, see Setting Server Operational Parameters.

SQL Query Auditing

SQL audit logging provides a record of the SQL queries that were submitted to the SPD Server server. The SQL audit record contains the following information:
  • the SQL query timestamp
  • the type of the SQL query
    • 1=SELECT
    • 2=DROP
    • 3=ALTER
    • 4=CREATE
    • 5=DESCRIBE
    • 6=UPDATE
    • 7=DELETE
    • 18=RESET
    • 19=BEGIN ASYNC
    • 20=END ASYNC
  • the number of rows that were returned for an SQL SELECT statement
  • the elapsed time, in seconds, required to process the SQL query
  • the user ID of the user that submitted the query
  • the group ID of the user that submitted the query
  • the default LIBNAME for the query, used for any table that is not referenced by a two-part name
  • the number of characters in the query
  • the text of the submitted SQL query
You control the maximum size that can be allocated in the SQL log for an SQL statement by using the SQLAUDLEN option. For more information, see Setting Server Operational Parameters.
Last updated: February 3, 2017