Now lets explore more on User profile security and audit

There are many common scenario's that needs to be taken care of while auditing and ensuring security of the system. Now, lets explore some of the basic one's.

1- Finding user profile's with default password :- While auditing IBM i, the common thing that the auditor would like to check is the list of user profile's that have default password. 

What is default password? When the user profile name and password are same, it is called default password. As you already know, the system defaults to *USRPRF while creating a new user profile and if unless changed would generate the user profile with same password as the user id, it is called default password.

As a responsible administrator, it is required to check periodically if any user profile has default password and the same report needs to be provided while auditing. So, how to generate a report with user profile's that have default password .

AS400 command ANZDFTPWD would do that.

Command is

ANZDFTPWD ACTION(*NONE)

This would generate a spool file QPSECPWD for the user. You can use iSeries navigator to download this report. 

If you want to disable the user profiles with default password , use the following

ANZDFTPWD ACTION(*DISABLE)

You can also expire the password of all the user profiles having default password, 

ANZDFTPWD ACTION(*PWDEXP)

ANZDFTPWD is that it produces a physical file containing all the information from its QPSECPWD listing. Whenever you run ANZDFTPWD, a complete list of all user profiles with default passwords is copied in the QASECPWD file in the QUSRSYS library. This file can be read in other programs or in an SQL query to gather more information.

2-  Password Configuration details :- Sometimes it would be required to check and provide the password configuration parameters for auditing. You can easily achieve this by iSeries Navigator.

Expand the system name under Management Central. Expand the security tab. Click on Password policy.

You can copy these details in General tab, validation tab and Expiration tab and provide it to auditors.

3- Querying User Profile to extract specific details :-  As a system administrator, it would be required to periodically check for users who no longer use the system for more than 6 months. Sometimes, to provide a list of users having a specific special authority. 

In order to do this, first create a physical file of the user profile in your system.  This can be done by

DSPUSRPRF USRPRF(*ALL) TYPE(*BASIC) OUTPUT(*OUTFILE) 

OUTFILE(LIBRARY NAME/FILE NAME) 

Now, this file is the physical file upon which you can query according to the requirement and provide the desired output. 

For generating report of all admin user profile,

SELECT  UPUPRF,  UPUSCL,  UPSPAU,  UPGRPF,  UPTEXT,  UPSTAT,  UPPSOD  FROM LIBNAME.FILENAME DSPUSRPRF  WHERE UPUSCL = '*SECADM' OR UPUSCL = '*SECOFR' OR UPUSCL = '*SYSOPR'   ORDER BY  2 

For user profile that have not used for last 6 months,

SELECT  UPUPRF,  UPGRPF,  UPTEXT,  UPSTAT,  UPPSOD,  UPPWCD,  UPPWEX,  UPLTCP,  UPSPAU,  UPOTQU  FROM LIBNAME.FILENAME SOXUSRPRF  WHERE UPPSOD < 'MMDDYYYY' AND UPSTAT <> '*ENABLED'   ORDER BY  2 

Run this statement to get all user profiles with a user class equal to *SECOFR or *PGMR.

SELECT * FROM LIBNAME.FILENAME WHERE UPUSCL = '*SECOFR' OR UPUSCL = '*PGMR'

To retrieve all user profile records that contain *ALLOBJ or *SECADM authority 
in their profiles, run this SQL statement.
SELECT * FROM LIB/FILENAME WHERE              
UPSPAU LIKE '%ALLOBJ%' OR UPSPAU LIKE '%SECADM%'





To capture all the user records that have command line authority on the system, 
run this SQL statement.
SELECT * FROM LIB/FILENAME WHERE UPLTCP = '*NO'



The hardest part is to determine the field name in this spool file. For that you can use


DSPFFD FILE(LIB/FILENAME) OUTPUT(*PRINT)



Since, these files could be way too big, it is better to use Help systems's  Showcase query as this enables the admin to generate the query output in excel sheet.  Showcase uses normal SQL to query the files.


3- Authority for running commands :-  The Auditors would also like to see the authority to run specific commands, for example SBMJOB, CHGJOB and ADDJOBSCDE command allows the user to make changes to jobs and change the job scheduler. To retrieve the authority information to run these commands, use the following.


DSPOBJAUTH OBJ(lib name/obj name) Objtype(*CMD)


I will discuss the rest security and auditing related parameters in my next post.


Thank you.