Security information from a SQL View

Introduced as part of the latest Technology Refreshes for IBM i 7.4 and 7.3 is a SQL View which lists all of the security information for the partition I am using.

The View SECURITY_INFO only has one row, and shows the same information as the Display Security Attributes, DSPSECA, and Display Security Auditing commands, DSPSECAUD. Many of the columns in this View are taken from the security system values.

To be able to see the contents of the audit receiver columns I must have Object operation authority, *OBJAOPR, to the Audit journal, QSYS/QAUDJRN, or to the attached journal receiver.

In its simplest form to see all of the security I can just use the following statement:

SELECT * FROM QSYS2.SECURITY_INFO ;

The results could be different in your partition when compared to the one I am using, therefore, I am not going to show you all of my results. I will refer you to the link to IBM's documentation at the bottom of this post for an explanation of what all the columns are.

I did find the first four columns interesting as a check to make sure no-one has changed things that would be applied at the next IPL.

SELECT SECURITY_LEVEL,PENDING_SECURITY_LEVEL, PASSWORD_LEVEL,PENDING_PASSWORD_LEVEL FROM QSYS2.SECURITY_INFO ;

My results are:

PENDING_ PENDING_ SECURITY_LEVEL SECURITY_LEVEL PASSWORD_LEVEL PASSWORD_LEVEL -------------- -------------- -------------- -------------- 50 <NULL> 3 <NULL>

The two pending columns are null, as this partition does not have any pending changes to the security and password levels. If I was to find values in the pending columns that I was not aware of, I would not IPL the partition until I found the person who changed them and agreed with his/her reasons for the change.

I am not authorized to the audit journal on the partition I am using for writing this posts. As I explained above, when I am not authorized to the audit journal nor its receiver I cannot see its information, null is returned in those columns:

SELECT AUDIT_JOURNAL_EXISTS AS "Exist", AUDIT_JOURNAL_RECEIVER_LIBRARY AS "Library", AUDIT_JOURNAL_RECEIVER as "Receiver" FROM QSYS2.SECURITY_INFO ; Exists Library Receiver ------ ------- -------- YES <NULL> <NULL>

I can quickly get to some of the information auditors always ask me for:

• Password change interval
• Minimum password length
• Password rules used

I can retrieve this information with the following statement:

SELECT PASSWORD_EXPIRATION_INTERVAL AS "Interval", PASSWORD_MINIMUM_LENGTH AS "Min length", PASSWORD_RULES AS "Rules" FROM QSYS2.SECURITY_INFO ;

The results are:

Interval Min length Rules -------- ---------- ---------- *NOMAX 6 *PWDSYSVAL

As the partition I am using is not one I have to audit I am not concerned that passwords never expire.

I would have my production passwords set to a minimum length of 8.

*PWDSYSVAL refers back to the allowed values in the QPWDRULES system value. You can see the explanation of the values used in that system value in IBM's documentation here.

Check the results from the SECURITY_INFO View for your partitions, and make everything is as it should be.

 

You can learn more about the SECURITY_INFO SQL View from the IBM website here.

 

This article was written for IBM i 7.4 TR4, and will work for 7.3 TR10 too.