Wednesday, April 25, 2018

Auditing RACF Using Microsoft Access or any SQL Database

Executive Summary

Aside from being difficult, auditing RACF requires special skills. It needs understanding of RACF concepts plus understanding of the other subsystems being audited. A comprehensive manual audit took four auditors two months to complete. This can make the audit report irrelevant by the time it is released because errors may have been corrected already - making the auditors look incompetent - or worse, security breach may have occurred without the auditors detecting it.

Added to this is that the process will probably cover only samples of the entire system. This is why a tool is needed to simplify the audit procedure to make the process more reliable and complete. One of the tools we used was Microsoft Access. Microsoft Access is a relational database on the personal computer that allows the user to create and maintain a database. Its graphical interface allows a user with no background in SQL to create useful queries to produce reports for analysis. With the implementation of the tool, the audit process took two auditors two days to finish. Since the data used is the entire RACF database, reports were more comprehensive and more reliable. The short turnaround time for the audit also ensures that discrepancies are detected and presented for correction.


Auditing is a tedious and boring task. Being so, it is prone to errors when done manually. Auditing RACF accesses can be more difficult as it requires knowledge in RACF principles as well as an understanding of the business process and accesses.
The basic RACF reports that come with the software serves its purpose for individual queries. However, if it would be used for audits, it would be very difficult. For one, the auditor has to know the command format to produce the reports. The auditor also needs basic knowledge on mainframe software like TSO/ISPF.
If an auditor is able to produce the reports, the reports would be bulky. A comprehensive report on the RACF users, groups, accesses, etc will probably be printed in two boxes of paper. This is definietly a waste of resources, not to mention the environment. Being bulky, it would be extremely difficult for the auditor (in most cases auditors) to sort through the report.
My experience with this is that a comprehensive RACF audit takes four auditors about two months to go through the reports and write their findings. The auditors also need to know what fields they have to look for in the report. They also need to create tons of working papers to support their findings. After at least eight person-months of audit, the report will probably not be relevant as the errors would probably have been detected and corrected or worse, a security exposure might have occurred already.
That is why there is a need to use tools to simplify RACF audits. One of the tools that we used to do this is Microsoft Access. The reason for using Microsoft Access is that it has a user interface that makes it easy to create queries and more importantly, reports.
The concept of this tool is to extract ALL RACF records from the RACF database and load the relevant records onto a relational database You can then create queries to display only discrepancies and generate reports for these discrepancies.
Although the concept is simple, the process of doing this is a little tricky. To be able to do this, you need to have an idea of how the RACF records look like, what is relevant, what to extract and finally, how to load the records onto the database

RACF Record Structure

All access information is stored in the RACF database. The procedure to extract all records from the RACF database is detailed in the RACF System Programmers' Reference. What you want is to copy all the RACF database records to a flat file. When the RACF records are copied to a flat file, it will be as a variable blocked file. In MVS, a variable blocked file has four overhead bytes at the start of each record. The first two bytes indicate the length of the record. The second two bytes are used internally by MVS. The fifth byte indicates the type the record. Examples of a record type could be user record, group record, dataset record, resource record, etc.
You will have to determine first which of these records are relevant to your audit and which fields within the records are relevant. Once you have determined this, you can now determine which records are to be removed from the extracted RACF file. You can then sort the extracted RACF file and filter out unwanted records. This step is optional but remember that you will eventually download this file to your PC. So the bigger your file, the more space it will use on your hard drive and the longer it will take to download the file.

Designing Microsoft Access Table

Once you have determined which records and fields are relevant, you have to design your Microsoft Access database. The fields in your tables will depend on the fields and records you want to extract. My experience is that you will have to define indeces for your tables as you might want to load data from two record types onto one table. This is why we selected Microsoft Access for this system. Microsoft Access comes with an easy to use GUI for defining tables. This eliminates the need to learn SQL. When designing your tables, remember to define the relationships. You can never over-stress the importance of creating indeces for your tables. When we began this, we had no knowledge of Microsoft Access. We therefore did not create indices to our tables. The load process was still running after 15 hours. When we added an index to a table, the oad process was done in less than 3 hours.

Loading the Tables

Once you have designed your tables, you will have to decide on how to load the data. We used Visual Basic to do this and it worked fine. One of the advantages of using Visual Basic is that there probably is someone in your organization that knows it. Another thing about Visual Basic is that it has functions that make loading the database easier. When using Visual Basic, remember to use the Seek function rather than FindFirst or FindNext when searching for a record in a table. This will definitely shorten your load time.
With the gaining popularity of scripting languages, you might also want to consider using Perl. Perl is a language that was designed to make filtering records and formatting records easier. It also has an interface with Microsoft Access BUT you will have to know some SQL to make use of it. Another option is to use Perl and generate comma-separated value files that you load onto your database later. Whatever option you take, it is your decision.

Creating Queries

When creating queries, you have to remember that these queries will be used to generate the reports later on. You may also have to create Functions (Modules) to fine tune your queries. Microsoft Access allows you to create queries graphically without knowledge of SQL. You can create a set of queries that filter out the rows in a table and use this query to check other combinations. The design of the queries depends on what you want to audit and what your organization security policy is.
Remember to put some description in your query. You may want to use a naming standard for your queries. Filter queries may start with the letter f. report queries can start with r and so on. You would appreciate what you did a few weeks after you have used the system. Believe me, you will forget what a query does and the naming standards and comments will help refresh your memory.

Creating Reports

The input for your reports will mostly come from your Queries. Microsoft Access has a handy report design feature that allows you to create reports graphically. Make sure that the reports are easy to read as these will be your report to management. You can also create sub-reports that can be displayed with the reports. This is one of the most used features of Microsoft Access in this system. Check if your query is correct  before you submit your report.

Creating Macros

Once you have determined the reports that you want to produce, you can create a macro to produce those reports. This helps to simplify your audit procedure as a macro can be programmed to open all the needed reports.

What about Working Papers

Traditional audits require working papers. But as you can see, there are no working papers as the summary and temporary reports are all done within the database. Your working paper therefore is the entire database. As such, you have to treat your database as you would your working paper. Back it up onto your corporate server to ensure that it is secure.

What Next

With your system in place, you can use it repeatedly in the next audit. You can do your audits more frequently and with more accuracy. When done manually, the entire process took four auditors two months to finish. With the system, two auditors did the audit in two days. This savings in effort provided the auditors more time for analysis and also ensured the completeness and accuracy of the audit.

No comments:

Post a Comment

Total Pageviews