Understanding an Audit Trail
Definition of an Audit Trail
The audit trail is an optional SAS file that you can create in order 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.
Many businesses and organizations require an audit trail for security reasons. The audit trail maintains historical information about the data, which gives you the opportunity to develop usage statistics and patterns. The historical information enables you to track individual pieces of data from the moment they enter the data file to the time they leave.
The audit trail is also the only facility in SAS that stores observations from failed append operations and that were rejected by integrity constraints. (The integrity constraints feature is described in "Understanding Integrity Constraints" on page 505.) The audit trail enables you to write a DATA step to extract the failed or rejected observations, use information describing why the observations failed to correct them, and then reapply the observations to the data file.
Audit Trail Description
The audit trail is created by the default Base SAS engine and has the same libref and member name as the data file, but has a data set type of AUDIT. It 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 optional variables you can define to collect modification data.
The _AT*_ variables are described in the following table.
| _AT*_ Variable | Description |
|---|---|
| _ATDATETIME_ | Stores the date and time of a modification |
| _ATUSERID_ | Stores the logon userid that is associated with a modification |
| _ATOBSNO_ | Stores the observation number that is 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 that describes the type of modification |
The _ATOPCODE_ values are listed in the following table.
| Code | Modification |
|---|---|
| AL | Auditing is resumed |
| AS | Auditing is suspended |
| 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 |
| EU | Observation update failed |
The type of entries stored in the audit trail, along with their corresponding _ATOPCODE_ values, are determined by the options specified in the LOG statement when the audit trail is initiated. Note that if the LOG statement is omitted when the audit trail is initiated, the default behavior is to log all images.
-
The A operation codes are controlled by the ADMIN_IMAGE option.
-
The DR operation code is controlled by the BEFORE_IMAGE option.
-
All other D operation codes are controlled with the DATA_IMAGE option.
-
The E operation codes are controlled by the ERROR_IMAGE option.
The user variable is a variable that associates data values with the data file without making them part of the data file. That is, the data values are stored in the audit file, but you update them in the data file like any other variable. You may want to define a user variable to enable end users to enter a reason for each update.
User variables are defined at audit trail initiation with the USER_VAR statement. For example, the following code initiates an audit trail and creates a user variable REASON_CODE for data file MYLIB.SALES:
proc datasets lib=mylib; audit sales; initiate; user_var reason_code $ 20; run;
After the audit trail is initiated, the Base SAS engine retrieves the user variables from the audit trail and displays them when the data file is opened for update. You can enter data values for the user variables as you would for any data variable. The data values are saved 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. However, to rename a user variable or modify its attributes, you modify the data file, not the audit file. The following example uses PROC DATASETS to rename the user variable:
proc datasets lib=mylib; modify sales; rename reason_code = Reason; run; quit;
You must also define attributes such as format and informat in the data file with PROC DATASETS. If you define user variables, you must store values in them for the variables to be meaningful.
A data file can have one audit file, and the audit file must reside in the same SAS library as the data file.
Operation in a Shared Environment
The audit trail operates similarly in local and remote environments. The only difference for applications and users networking 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 that the transaction is logged might be different than the user's SAS session.
Performance Implications
Because each update to the data file is also written to the audit file, the audit trail can negatively impact system performance. You might 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.
Preservation by Other Operations
The audit trail is not recommended for data files that are copied , moved, sorted in place, replaced , or transferred to another operating environment, 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 about generation data sets, see "Understanding Generation Data Sets" on page 499.
Programming Considerations
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.
Other Considerations
Data values that are 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. To terminate the audit trail for a generation data set, use the GENNUM= data set option in the AUDIT statement. You cannot initiate an audit trail for a generation data set.
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 Base SAS Procedures Guide .
Initiating an Audit Trail
You initiate an audit trail in the DATASETS procedure with the AUDIT statement. Consult the "PROC DATASETS AUDIT Statement" in the Base SAS Procedures Guide for syntax information.
The audit file will use the SAS password assigned to its associated data file; 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.
Controlling the Audit Trail
Once active, you can suspend and resume logging, and terminate (delete) the audit trail. The syntax for controlling the audit trail is described in the PROC DATASETS AUDIT statement documentation. Note that replacing the associated data file will also delete the audit trail.
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 TYPE= data set option. For example, to view the contents of the audit trail, issue the following statement. Note that the parentheses around the TYPE= option are required.
proc contents data=mylib.sales (type=audit); run;
The PROC CONTENTS output is shown below. Notice that the listing contains all of the variables from the corresponding data file, the _AT*_ variables, and the user variable.
 |
The CONTENTS Procedure Data Set Name MYLIB.SALES.AUDIT Observations 0 Member Type AUDIT Variables 10 Engine V9 Indexes 0 Created 12:34 Wednesday, January 22, 2003 Observation Length 111 Last Modified 12:34 Wednesday, January 22, 2003 Deleted Observations 0 Protection Compressed NO Data Set Type AUDIT Sorted NO Label Data Representation WINDOWS Encoding Default Engine/Host Dependent Information Data Set Page Size 4096 Number of Data Set Pages 1 First Data Page 1 Max Obs per Page 33 Obs in First Data Page 0 Number of Data Set Repairs 0 File Name C:\My Documents\myfiles\sales.sas7baud Release Created 9.0000A0 Host Created WIN_NT Alphabetic List of Variables and Attributes # Variable Type Len Format 5 _ATDATETIME_ Num 8 DATETIME19. 10 _ATMESSAGE_ Char 8 6 _ATOBSNO_ Num 8 9 _ATOPCODE_ Char 2 7 _ATRETURNCODE_ Num 8 8 _ATUSERID_ Char 32 2 invoice Num 8 1 product Char 9 4 reason_code Char 20 3 renewal Num 8
| |
You can also use your favorite reporting tool, such as PROC REPORT or PROC TABULATE, on the audit trail.
Examples of Using Audit Trails
Example of Initiating an Audit Trail
The following example shows the data and code that are used to create and initiate an audit trail for the data file MYLIB.SALES that is used in earlier examples in this section. MYLIB.SALES stores fictional invoice and renewal figures for SAS products. The audit trail will record all events and will store one user variable, REASON_CODE, for users to enter a reason for the update.
Subsequent examples will illustrate the effect 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 ; input product invoice renewal; datalines; 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 nolist; audit sales; initiate; user_var reason_code $ 20; run;
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=., _atdatetime_ from mylib.sales(type=audit); quit;
| |
The SAS System product reason_code _ATOPCODE_ _ATUSERID_ _ATDATETIME_ ________________________________________________________________________________ AUDIT Add new product DA xxxxxx 31OCT2002:11:24:32
| |
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. For more information about integrity constraints, see "Understanding Integrity Constraints" on page 505.
/*----------------------------------*/ /* 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_ .; 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_ 0.; var product invoice renewal _atmessage_ ; title 'Rejected Records'; run;
Output 28.3 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. Output 28.4 prints information about the rejected observations on the audit trail.
| |
Contents of the Audit Trail Obs product reason_code _ATOPCODE_ _ATUSERID_ _ATDATETIME_ 1 AUDIT Add new product DA xxxxxx 22JAN2003:12:43:27 2 SAS DR xxxxxx 22JAN2003:13:02:02 3 SAS 10% price cut DW xxxxxx 22JAN2003:13:02:02 4 SAS DR xxxxxx 22JAN2003:13:02:02 5 SAS 10% price cut DW xxxxxx 22JAN2003:13:02:02 6 AUDIT DR xxxxxx 22JAN2003:13:02:02 7 AUDIT 10% price cut DW xxxxxx 22JAN2003:13:02:02 8 AUDIT Add new product EA xxxxxx 22JAN2003:13:02:02 9 AUDIT Add new product DA xxxxxx 22JAN2003:13:02:02 10 AUDIT Add new product EA xxxxxx 22JAN2003:13:02:02
| |
| |
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.
| |
EAN: 2147483647
Pages: 258