Pages

Tuesday, July 27, 2021

Security information from a SQL View

sql view to show all security information for partition

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.

No comments:

Post a Comment

To prevent "comment spam" all comments are moderated.
Learn about this website's comments policy here.

Some people have reported that they cannot post a comment using certain computers and browsers. If this is you feel free to use the Contact Form to send me the comment and I will post it for you, please include the title of the post so I know which one to post the comment to.