Audit Trail

Definition of an Audit Trail

The audit trail is an optional SAS file that you can create to log modifications to a SAS data file. Each time an observation is added, deleted, or updated, information is written to the audit trail about who made the modification, what was modified, and when.

Benefits of an Audit Trail

Many businesses and organizations require an audit trail for security reasons. The audit trail maintains a historical record of the data that enables you to trace a piece of data from the moment it enters the data file to the time it leaves.

An audit trail provides useful information from which to develop usage statistics. For example, for master data files that are updated by multiple applications and users, the audit trail can show which applications and users made updates and what updates were made.

The audit trail is also the only place in the SAS System that stores observations from failed appends and observations that were rejected by integrity constraints. The integrity constraints feature is described in Integrity Constraints. You can write a DATA step to extract the failed or rejected observations from the audit trail, use information describing why they failed to correct them, and then reapply the observations to the data file.

Audit Trail Description

The audit trail is a SAS file created by the SAS base engine with the same libref and member name as the data file, and a data set type of AUDIT. The audit trail replicates the variables in the data file and additionally stores two types of audit variables:

_AT*_ variables, which automatically store modification data
"user" variables, which are special variables you can optionally define when you initiate the audit trail.

The _AT*_ variables are described in the following table.

_AT* Variables
_AT*_ Variable Description

_ATDATETIME_ Stores the date and time of a modification

_ATUSERID_ Stores the logon userid associated with a modification

_ATOBSNO_ Stores the observation number affected by the modification, except when REUSE=YES (because the observation number is always 0)

_ATRETURNCODE_ Stores the event return code

_ATMESSAGE_ Stores the SAS log message at the time of the modification

_ATOPCODE_ Stores a code describing the type of modification

***_AT* Variables***
*_AT_ Variable**	Description
_ATDATETIME_	Stores the date and time of a modification
_ATUSERID_	Stores the logon userid associated with a modification
_ATOBSNO_	Stores the observation number affected by the modification, except when REUSE=YES (because the observation number is always 0)
_ATRETURNCODE_	Stores the event return code
_ATMESSAGE_	Stores the SAS log message at the time of the modification
_ATOPCODE_	Stores a code describing the type of modification

The _ATOPCODE_ values are listed in the following table.

_ATOPCODE_ Values
Code Modification

DA Added data record image

DD Deleted data record image

DR Before-update record image

DW After-update record image

EA Observation add failed

ED Observation delete failed

EW Observation update failed

***_ATOPCODE_ Values***
Code	Modification
DA	Added data record image
DD	Deleted data record image
DR	Before-update record image
DW	After-update record image
EA	Observation add failed
ED	Observation delete failed
EW	Observation update failed

The log settings at audit trail initiation determine which _ATOPCODE_ values are logged:

the "DR" operation code is controlled with the LOG statement BEFORE_IMAGE option
other operations codes that begin with a "D" are controlled with the DATA_IMAGE option
operation codes that begin with an "E" are controlled with the ERROR_IMAGE option.

For instructions on specifying log settings, refer to Initiating an Audit Trail. The default behavior is to log all images.

The user variables are unique in the SAS System because they are stored in one file (the audit file) and opened for update in another file, the data file. This enables you to associate data values with the data file without making them part of the data file. For example, you could define a user variable that enables users to enter a "reason for the modification."

The user variables are processed as follows:

You define the variables as part of the audit trail specification.
The base engine retrieves the variables from the audit trail and displays them when the data file is opened for update.
The users can enter data values for the user variables as they would for any data variable.
The data values are written to the audit trail as each observation is saved. In applications like FSEDIT, which save observations as you scroll through them, it may appear that the data values have disappeared.
The user variables are not available when the data file is opened for browsing or printing.
You modify user variables in the data file. That is, to rename a user variable or modify its attributes, you modify the data file, not the audit file.

For information about defining user variables, see Defining User Variables. If you define user variables, you must store values in them for the variables to be meaningful.

The audit trail must reside in the same SAS library as its associated data file, and a data file can have only one audit file.

Operation

The audit trail operates similarly in local and remote environments. The only difference for applications and users networked with SAS/CONNECT and SAS/SHARE is that the audit trail logs events when the observation is written to permanent storage; that is, when the data is written to the remote SAS session or server. Therefore, the time the transaction is logged may be different than the user's SAS session.

Performance

Because each update to the data file is also written to the audit file, the audit trail can negatively impact system performance. You may want to consider suspending the audit trail for large, regularly scheduled batch updates. Note that the audit variables are unavailable when the audit trail is suspended.

Reading and Determining the Status of the Audit Trail

The audit trail is read-only. You can read the audit trail with any component of SAS that reads a data set. To refer to the audit trail, use the data set TYPE= option. For example, to print the audit trail, you would issue the statement:

proc print data=libref.member-name (type=audit);
  title "Data in the Audit File";
run;

If an audit trail exists, PROC CONTENTS reports the audit status and records image settings when it is invoked on its associated data file. You can also use your favorite reporting tool -- PROC REPORT or PROC TABULATE, for example -- on the audit trail.

Limitations

The audit trail is not recommended for SAS data files that are copied, moved, sorted in place, replaced, or transferred to another operating system because those operations do not preserve the audit trail. In a copy operation on the same host, you can preserve the data file and audit trail by renaming them using the Generation Data Sets feature; however, logging will stop because neither the auditing process nor the Generation Data Sets feature saves the source program that caused the replacement. For more information, see Generation Data Sets.

For data files whose audit file contains user variables, the variable list is different when browsing and updating the data file. The user variables are selected for update but not for browsing. You should be aware of this difference when you are developing your own full-screen applications.

Data values entered for user variables are not stored in the audit trail for delete operations.

If the audit file becomes damaged, you will not be able to process the data file until you terminate the audit trail. Then you can initiate a new audit trail or process the data file without one.

The Audit Trail and Fast-Append

In indexed data sets, the fast-append feature may cause some observations to be written to the audit trail twice, first with a DA operation code and then with an EA operation code. The observations with EA represent those rejected by index restrictions. For more information, see "Appending to an Indexed Data Set" in the PROC DATASETS APPEND statement documentation in the SAS Procedures Guide.

Initiating an Audit Trail

You initiate the audit trail in PROC DATASETS with the AUDIT statement. The syntax for initiating the audit trail is:

PROC DATASETS LIB=libref;

AUDIT SAS-file <SAS-password>;

INITIATE;

<LOG <BEFORE_IMAGE=YES|NO><DATA_IMAGE=YES|NO> <ERROR_IMAGE=YES|NO>>;

USER_VAR specification-1 <...specification-n>;

where:

SAS-file specifies the SAS data file in the procedure input library that you want to audit.

SAS-password is the SAS password of the data file, if one exists.

The INITIATE statement creates the audit trail.

The LOG statement specifies the data images, or events, to be logged on the audit trail.

BEFORE_IMAGE=YES|NO controls storage of before-update record images.

DATA_IMAGE=YES|NO controls storage of after-update record images.

ERROR_IMAGE=YES|NO controls storage of unsuccessful update record images.

If the LOG statement is omitted, the default setting for all images is YES.

The USER_VAR statement optionally defines user variables to be logged to the audit trail with each update to an observation. Syntax details are provided in Defining User Variables.

The audit file will use the SAS password assigned to the associated data file, and therefore it is recommended that the data file have an ALTER password. An ALTER-level password restricts read and edit access to SAS files. If a password other than ALTER is used, or no password is used, the software will generate a warning message that the files are not protected from accidental update or deletion.

Defining User Variables

You define user variables at audit trail initiation with the USER_VAR statement. The syntax for the USER_VAR statement is:

USER_VAR= variable-name <$><length><LABEL= "variable-label">
<...variable-name-n <$><length><LABEL= "variable-label">>;

where:

variable-name is a name for the user variable.

$ indicates the variable is a character value. If $ is not specified, the default is numeric.

length specifies the length of the variable. If a length is not specified, the default is 8 characters.

LABEL="variable-label" specifies a label for the variable.

You can define attributes such as format and informat in the data file with PROC DATASETS.

Controlling the Audit Trail

Once the audit trail is established, you can change which record images are logged, suspend and resume logging, and terminate (delete) the audit file. The syntax for controlling the audit trail is:

PROC DATASETS LIB= libref;

AUDIT SAS-file <SAS-password>;

LOG | SUSPEND | RESUME | TERMINATE;

Replacing the associated data file will also delete the audit trail.

Example of Initiating an Audit Trail

The following example creates and initiates an audit trail for data file MYLIB.SALES, which stores fictional invoice and renewal figures for SAS products. The audit trail will record all events and store one user variable, REASON_CODE, for users to enter a reason for the update.

Subsequent examples will illustrate the affect of a data file update on the audit trail and how to use audit variables to capture observations that are rejected by integrity constraints. The system option LINESIZE is set in advance for the integrity constraints example. A large LINESIZE value is recommended to display the content of the _ATMESSAGE_ variable. The output examples have been modified to fit on the page.

options linesize=250;
   /*------------------------------------*/
   /* Create SALES data set.             */
   /*------------------------------------*/

data mylib.sales;
  length product  $9;
  input product invoice renewal;
cards;
FSP        1270.00        570
SAS        1650.00        850
STAT       570.00         0
STAT       970.82         600
OR         239.36         0
SAS        7478.71        1100
SAS        800.00         800
;


   /*----------------------------------*/
   /* Create an audit trail with a     */
   /* user variable.                   */
   /*----------------------------------*/

proc datasets lib=mylib;
  audit sales;
    initiate;
    user_var reason_code $ 20;
run;

   /*-------------------------------------*/
   /* Issue proc contents to view the     */
   /* audit file.                         */
   /* ------------------------------------*/
proc contents data=mylib.sales (type=audit); run;

PROC CONTENTS of MYLIB.SALES

The CONTENTS Procedure Data Set Name: MYLIB.SALES Observations: 0 Member Type: AUDIT Variables: 10 Engine: V8 Indexes: 0 Created: 10:51 Thursday, September 30, 1999 Observation Length: 111 Last Modified: 10:51 Thursday, September 30, 1999 Deleted Observations: 0 Protection: Compressed: NO Data Set Type: AUDIT Sorted: NO Label: ... The CONTENTS Procedure -----Alphabetic List of Variables and Attributes----- # Variable Type Len Pos Format ------------------------------------------------------- 5 _ATDATETIME_ Num 8 45 DATETIME. 10 _ATMESSAGE_ Char 8 103 6 _ATOBSNO_ Num 8 53 9 _ATOPCODE_ Char 2 101 7 _ATRETURNCODE_ Num 8 61 8 _ATUSERID_ Char 32 69 2 invoice Num 8 0 1 product Char 9 16 4 reason_code Char 20 25 3 renewal Num 8 8

Example of a Data File Update

The following example inserts an observation into MYLIB.SALES.DATA and prints the update data in the MYLIB.SALES.AUDIT.

   /*----------------------------------*/
   /* Do an update.                    */
   /*----------------------------------*/
 proc sql;
   insert into mylib.sales
       set product = 'AUDIT',
           invoice = 2000,
           renewal = 970,
       reason_code = "Add new product";
quit;

   /*----------------------------------------*/
   /* Print the audit trail. */
   /*----------------------------------------*/
proc sql;
  select product,
         reason_code,
         _atopcode_,
         _atuserid_ format=$6.,
         _atdatetime_
         from mylib.sales(type=audit);
quit;

Updated Data in MYLIB.SALES.AUDIT

product reason_code _ATOPCODE_ _ATUSERID_ _ATDATETIME_ ------------------------------------------------------------------------- AUDIT Add new product DA xxxxxx 30SEP99:10:30:18

Example of Using the Audit Trail to Capture Rejected Observations

The following example adds integrity constraints to MYLIB.SALES.DATA and records observations that are rejected as a result of the integrity constraints in MYLIB.SALES.AUDIT.

   /*----------------------------------*/
   /* Create integrity constraints.    */
   /*----------------------------------*/
proc datasets lib=mylib;
   modify sales;
   ic create null_renewal = not null (invoice)
             message = "Invoice must have a value.";
   ic create invoice_amt = check (where=((invoice > 0) and
               (renewal <= invoice)))
             message = "Invoice and/or renewal are invalid.";
run;

   /*----------------------------------*/
   /* Do some updates.                 */
   /*----------------------------------*/
 proc sql; /* this update works */
    update mylib.sales
      set invoice = invoice * .9,
      reason_code = "10% price cut"
      where renewal > 800;

 proc sql;  /* this update fails */
    insert into mylib.sales
       set product = 'AUDIT',
           renewal = 970,
       reason_code = "Add new product";

 proc sql;  /* this update works */
    insert into mylib.sales
       set product = 'AUDIT',
           invoice = 10000,
           renewal = 970,
       reason_code = "Add new product";

proc sql;  /* this update fails */
    insert into mylib.sales
       set product = 'AUDIT',
           invoice = 100,
           renewal = 970,
       reason_code = "Add new product";
 quit;

   /*----------------------------------------*/
   /* Print the audit trail. */
   /*----------------------------------------*/
proc print data=mylib.sales(type=audit);
  format _atuserid_ $6.;
  var product reason_code _atopcode_ _atuserid_ _atdatetime_;
title  'Contents of the Audit Trail';
run;

   /*----------------------------------------*/
   /* Print the rejected records.            */
   /*----------------------------------------*/
proc print data=mylib.sales(type=audit);
  where _atopcode_ eq "EA";
  format _atmessage_ $250.;
  var product invoice renewal _atmessage_ ;
title  'Rejected Records';
run;

Contents of MYLIB.SALES.AUDIT after an Update with Integrity Constraints shows the contents of MYLIB.SALES.AUDIT after several updates of MYLIB.SALES.DATA were attempted. Integrity constraints were added to the file, then updates were attempted. Rejected Records on the Audit Trail prints information about the rejected observations on the audit trail.

Contents of MYLIB.SALES.AUDIT after an Update with Integrity Constraints

Contents of the Audit Trail Obs product reason_code _ATOPCODE_ _ATUSERID_ _ATDATETIME_ 1 AUDIT Add new product DA xxxxxx 30SEP99:10:30:18 2 AUDIT Add new product DA xxxxxx 30SEP99:10:32:00 3 SAS DR xxxxxx 30SEP99:10:46:26 4 SAS 10% price cut DW xxxxxx 30SEP99:10:46:26 5 SAS DR xxxxxx 30SEP99:10:46:26 6 SAS 10% price cut DW xxxxxx 30SEP99:10:46:26 7 AUDIT DR xxxxxx 30SEP99:10:46:26 8 AUDIT 10% price cut DW xxxxxx 30SEP99:10:46:26 9 AUDIT DR xxxxxx 30SEP99:10:46:26 10 AUDIT 10% price cut DW xxxxxx 30SEP99:10:46:26 11 AUDIT Add new product EA xxxxxx 30SEP99:10:46:32 12 AUDIT Add new product EA xxxxxx 30SEP99:10:46:38 13 AUDIT Add new product DA xxxxxx 30SEP99:10:46:44

Rejected Records on the Audit Trail

Rejected Records Obs product invoice renewal _ATMESSAGE_ 1 AUDIT . 970 ERROR: Invoice must have a value. Add/Update failed for data set MYLIB.SALES because data value(s) do not comply with integrity constraint null_renewal. 2 AUDIT 100 970 ERROR: Invoice and/or renewal are invalid. Add/update failed for data set MYLIB.SALES because data value(s) do not comply with integrity constraint invoice_amt.

Chapter Contents
Previous
Next
Top of Page

	variable-name is a name for the user variable.
	$ indicates the variable is a character value. If $ is not specified, the default is numeric.
	length specifies the length of the variable. If a length is not specified, the default is 8 characters.
	LABEL="variable-label" specifies a label for the variable.