 |
|
| SAS Data Files |
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.) 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 user ID 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 might 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, SAS 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 that save observations as you scroll through them, it might 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 from 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 stops 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.
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 cannot 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 can 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 the observations 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 Base SAS Procedures Guide for syntax information.
The audit file uses 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 generates 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 also deletes 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.
PROC CONTENTS Listing for Data File MYLIB.SALES
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 24, 2007 Observation Length 111
Last Modified 12:34 Wednesday, January 24, 2007 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.2000A0
Host Created XP_PRO
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.
| Audit Trails and CEDA Processing |
When a SAS data file requires processing with CEDA, audit trails are not supported. For example, if you transfer a SAS data file with an initiated audit trail from one operating environment such as Windows to a different operating environment such as UNIX, CEDA translates the file for you, but the audit trail is not available. For information on CEDA processing, see Processing Data Using Cross-Environment Data Access (CEDA).
The MIGRATE procedure preserves audit trails when migrating data files. For more information, see the MIGRATE Procedure in Base SAS Procedures Guide. The CPORT and CIMPORT procedures preserve audit trails when transporting SAS data files from one operating environment to another operating environment. The CPORT procedure makes a copy of the data file in a transportable format, and the CIMPORT procedure reads the transport file and creates a new host-specific copy of the data file. For more information, see the CPORT Procedure and CIMPORT Procedure in Base SAS Procedures Guide.
- CAUTION:
- If your data files contain audit trails, do not use your
operating environment commands to copy, move, or delete your data files.
![[cautionend]](../../../../common/63294/HTML/default/images/cautend.gif)
| 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 records all events and stores one user variable, REASON_CODE, for users to enter a reason for the update.
Subsequent examples 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 $9; 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=$6., _atdatetime_ from mylib.sales(type=audit); quit;
Updated Data in MYLIB.SALES.AUDIT
The SAS System
product reason_code _ATOPCODE_ _ATUSERID_ _ATDATETIME_
________________________________________________________________________________
AUDIT Add new product DA xxxxxx 31OCT2007: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.
/*----------------------------------*/ /* 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 22JAN2007:12:43:27
2 SAS DR xxxxxx 22JAN2007:13:02:02
3 SAS 10% price cut DW xxxxxx 22JAN2007:13:02:02
4 SAS DR xxxxxx 22JAN2007:13:02:02
5 SAS 10% price cut DW xxxxxx 22JAN2007:13:02:02
6 AUDIT DR xxxxxx 22JAN2007:13:02:02
7 AUDIT 10% price cut DW xxxxxx 22JAN2007:13:02:02
8 AUDIT Add new product EA xxxxxx 22JAN2007:13:02:02
9 AUDIT Add new product DA xxxxxx 22JAN2007:13:02:02
10 AUDIT Add new product EA xxxxxx 22JAN2007:13:02:02
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)a do not comply with
integrity constraint invoice_amt.
|
|
Copyright © 2010 by SAS Institute Inc., Cary, NC, USA. All rights reserved.