SPD Server Auditing

Overview of SPD Server Auditing

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 or SQL audit logging, or both. For more information about start-up options, see the Help section in the SPD Server Administrator's Guide on SPD Server Host Commands .
SPD Server auditing provides a way to log access to SPD Server resources, or to log 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. These programs enable you to input the audit logs into SAS tables. Then, you can query the SAS tables to determine access to SPD Server tables and resources.

Proxy Auditing

Proxy auditing provides a means to 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 SPD Server resource by an SPD user
  • access granted to the SPD Server resource for the SPD 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
WHERE clause auditing is enabled using the WHEREAUDIT option. The maximum size that can be allocated WHERE clauses is controlled by the WHAUDLEN option. For more information, see SPD Server Parameter File Configurations for Auditing.

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 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 SPD Server user that submitted the query
  • the group ID of the SPD Server 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 length of the query in characters
  • the text of the submitted SQL query
The maximum size that can be allocated in the SQL log for an SQL statement is controlled by the SQLAUDLEN option. For more information, see SPD Server Parameter File Configurations for Auditing.