-- category:Robert Berendt
select *
FROM TABLE (QSYS2.HISTORY_LOG_INFO(START_TIME => CURRENT DATE - 2 days
)) AS X
Where message_id='CPI3E34'
and from_job_name like 'QRWT%'
ORDER BY ORDINAL_POSITION desc;
-- category: bryandietz
-- find DRDA and ODBC like connections
-- description: history log-find user from QZDASOINIT-QRWTSRVR
SELECT Message_Timestamp
,From_User
,From_Job
,Message_Id
,MESSAGE_TEXT
FROM TABLE(Qsys2.History_Log_Info(
Start_Time => current_timestamp - 1 day, -- pick your time frame
End_Time => current_timestamp
)) i
WHERE Message_Id in ('CPIAD09','CPI3E34')
-- AND MESSAGE_TEXT LIKE '%YOUR_USER%' -- if needing to "audit" for a single user
;
-- find ip from message_tokens
-- category: Robert Berendt
select trim(substring(message_tokens, 75, 15)) as IP_address, x.*
FROM TABLE (QSYS2.HISTORY_LOG_INFO(START_TIME => CURRENT DATE - 2 days
)) AS X
Where message_id='CPI3E34'
and from_job_name like 'QRWT%'
ORDER BY ORDINAL_POSITION desc;
-- find IP
-- category: bryandietz
-- find DRDA and ODBC like connections
-- description: history log-find user from QZDASOINIT-QRWTSRVR
SELECT Message_Timestamp
,From_User
,From_Job
,Message_Id
,MESSAGE_TEXT
,TRIM(SUBSTR(Message_Text,(LOCATE_IN_STRING(Message_Text, 'client', 1)+7), -- start of IP
(LOCATE_IN_STRING(Message_Text, ' connected', 1) -
(LOCATE_IN_STRING(Message_Text, 'client ', 1)+7) -- end of IP address
))) AS IP_addr
FROM TABLE(Qsys2.History_Log_Info(
Start_Time => current_timestamp - 1 day, -- pick your time frame
End_Time => current_timestamp
)) i
WHERE Message_Id in ('CPIAD09','CPI3E34')
-- AND MESSAGE_TEXT LIKE '%YOUR_USER%' -- if needing to "audit" for a single user
;
The IBM i best practices automate the recommended system configuration for securing your IBM i system.
Table 1 describes the best practices for securing an IBM i system.
Table 1. Settings related to the IBM i Best Practices
Group
Description
Location of the script that modifies the setting
System-wide access control
Sets the default public authority used when objects are created into a library. When the *LIBCRTAUT value of the AUT keyword of a create object command is used to set public authority for an object, the CRTAUT value of the library where the object is being created determines what public authority will be used for the object. If the CRTAUT value of the library is set to *SYSVAL, the value specified in the QCRTAUT system value is used to set the public authority for the object being created.
/etc/security/pscxpert/bin/worksystemvalue
Arguments:QCRTAUT *USE
Password policies
Sets the password expiration warning. It controls the number of days prior to a password expiring to begin displaying password expiration warning messages on the sign-on information display.
/etc/security/pscxpert/bin/worksystemvalue
Arguments:QPWDEXPWRN 7
Password policies
Block password change. Specifies the time period in hours during which a password is blocked from being changed following the prior successful password change operation. This system value does not restrict password changes made by the Change User Profile (CHGUSRPRF) command.
The default value is *NONE.
/etc/security/pscxpert/bin/worksystemvalue
Arguments:QPWDCHGBLK 24
Password policies
Password rules. Specifies the password composition rules used to check whether a password is formed correctly. See the IBM i help text of system value QPWDRULES for all possible values. Note that you get the full mixed case support and special character support with QPWDLVL 2 or 3.
Password expiration interval. Specifies the number of days for which passwords are valid. This provides password security by requiring users to change their passwords after a specified number of days. If the password is not changed within the specified number of days, the user cannot sign on until the password is changed.
/etc/security/pscxpert/bin/worksystemvalue
Arguments:QPWDEXPITV 90
Password policies
Duplicate password control (password history). Controls when a previously used password can be used again. The setting specifies how many times a password must be different than the previously used passwords. Valid values are as follows:
0 - A password can be the same as one previously used.
1 - A password must be different than the previous 32 passwords.
2 - A password must be different than the previous 24 passwords.
3 - A password must be different than the previous 18 passwords.
4 - A password must be different than the previous 12 passwords.
5 - A password must be different than the previous 10 passwords.
6 - A password must be different than the previous 8 passwords.
7 - A password must be different than the previous 6 passwords.
8 - A password must be different than the previous 4 passwords.
The recommendation is that a password cannot be reused for 2 years. The chosen value is calculated by the password expiration and the duplicate password control.
/etc/security/pscxpert/bin/worksystemvalue
Arguments:QPWDRQDDIF 6
System security
Sets the system-wide security level. The default value is 40.
/etc/security/pscxpert/bin/worksystemvalue
Arguments:QSECURITY 40
System security
Retain server security data. Determines whether the security data needed by a server to authenticate a user on a target system through client-server interfaces can be retained on the host system. Because many network services require the storage of security data, it is recommended to set the value to 1.
/etc/security/pscxpert/bin/worksystemvalue
Arguments:QRETSVRSEC 1
System security
Verify object on restore. This system value specifies the policy to be used for object signature verification during a restore operation. This value applies to objects of the following types:
*CMD
*PGM
*SRVPGM
*SQLPKG
*MODULE
*STMF objects that contain Java™ programs
The recommended value is 3.
/etc/security/pscxpert/bin/worksystemvalue
Arguments:QVFYOBJRST 3
Login controls
Limits security officer device access. This system value controls whether users with *ALLOBJ or *SERVICE special authorities need explicit authority to specific work stations.
/etc/security/pscxpert/bin/worksystemvalue
Arguments:QLMTSECOFR 1
Login controls
Maximum sign-on attempts action. Specifies how the system reacts when the maximum number of consecutive, incorrect, sign-on attempts (the system value QMAXSIGN) is reached. Valid values are as follows:
1 - Vary off device if limit is reached.
2 - Disable user profile if limit is reached.
3 - Vary off device and disable user profile if limit is reached.
The default value is 3.
/etc/security/pscxpert/bin/worksystemvalue
Arguments:QMAXSGNACN 3
Login controls
Maximum number of invalid sign-on attempts action. If the number of invalid sign-on attempts is reached, the action as specified in the QMAXSGNACN system value is performed.
/etc/security/pscxpert/bin/worksystemvalue
Arguments:QMAXSIGN 3
Login controls
Sets the password expiration warning. It controls the number of days prior to a password expiring to begin displaying password expiration warning messages on the sign-on information display.
/etc/security/pscxpert/bin/worksystemvalue
Arguments:QDSPSGNINF 1
System auditing
Audit control. This system value contains the on and off switches for object and user action auditing. This system value activates auditing on the system that is selected by the Change Object Auditing (CHGOBJAUD) and Change User Auditing (CHGUSRAUD) commands and the QAUDLVL and QAUDLVL2 system values.
/etc/security/pscxpert/bin/worksystemvalue
Arguments:QAUDCTL "*AUDLVL *NOQTEMP *OBJAUD"
System auditing
Security auditing level. Controls the level of action auditing on the system. If the QAUDLVL system value contains the value *AUDLVL2, then the values in the QAUDLVL2 system value will also be used.
/etc/security/pscxpert/bin/worksystemvalue
Arguments:QAUDLVL *AUDLVL2
System auditing
Security auditing level extension specifying the audit event types to be logged. It is a best practice to add all system audit events to the QAUDLVL2 system value.
Secure Sockets Layer (SSL) cipher control. Specifies whether or not the QSSLCSL (SSL cipher specification list) system value is controlled by the system or by the user.
/etc/security/pscxpert/bin/worksystemvalue
Arguments:QSSLCSLCTL *USRDFN
Secure connections
Secure Sockets Layer (SSL) cipher specification list. Specifies the list of cipher suites that are supported by System SSL. The values are read-only unless the QSSLCSLCTL (SSL cipher control) system value is set to *USRDFN. The rule disallows the use of the cipher suites with SHA-1 or MD5 message authentication algorithms.
Secure Sockets Layer (SSL) cipher specification list. Specifies the list of cipher suites that are supported by System SSL. The values are read-only unless the QSSLCSLCTL (SSL cipher control) system value is set to *USRDFN. The rule disallows the use of the cipher suites with SHA-1 or MD5 message authentication algorithms.
The Transport Layer Security protocols (QSSLPCL) system value specifies the Transport Layer Security (TLS) protocols supported by the System TLS.
This rule applies to IBM i V7R4 or later systems.
/etc/security/pscxpert/bin/worksystemvalue
Arguments:QSSLPCL "*TLSV1.3 *TLSV1.2"
Secure connections
The Transport Layer Security protocols (QSSLPCL) system value specifies the Transport Layer Security (TLS) protocols supported by the System TLS.
/etc/security/pscxpert/bin/worksystemvalue
Arguments:QSSLPCL *TLSV1.2
User profile security
Sets the public authorities for user profile objects. The default authority is *EXCLUDE. When checking all user profiles (parameter 1=*ALL), the apply will set the public authority for all user profiles to *EXCLUDE with the exception of the default IBM i profiles QDBSHR, QDBSHRDO, and QTMPLPD. You can also set and check the public authority for single profiles by specifying an individual user profile in parameter 1 and the desired public authority in parameter 2. Parameter 3 specifies one or more user profiles names to be exempt from the check.
/etc/security/pscxpert/bin/userpublicpermissions
Arguments:*ALL *EXCLUDE ""
Secure connections
Check if port 992 (secure Telnet) is in listen state. If not, report it as a violation and it is recommended to turn on TLS encryption for TELNET.
/etc/security/pscxpert/bin/checksecureport
Arguments:992 Telnet
Secure connections
Check if port 990 (secure FTP) is in listen state. If not, report it as a violation and it is recommended to turn on TLS encryption for FTP.
/etc/security/pscxpert/bin/checksecureport
Arguments:990 FTP
Secure connections
Check if port 9470 (Secure central server) is in listen state. If not, report it as a violation and it is recommended to turn on the secure central server.
/etc/security/pscxpert/bin/checksecureport
Arguments:9470 "Secure central server"
Secure connections
Check if port 9471 (Secure database server) is in listen state. If not, report it as a violation and it is recommended to turn on the secure database server.
/etc/security/pscxpert/bin/checksecureport
Arguments:9471 "Secure database server"
Secure connections
Check if port 9472 (Secure data queue server) is in listen state. If not, report it as a violation and it is recommended to turn on the secure data queue server.
/etc/security/pscxpert/bin/checksecureport
Arguments:9472 "Secure data queue server"
Secure connections
Check if port 9473 (Secure file server) is in listen state. If not, report it as a violation and it is recommended to turn on the secure file server.
/etc/security/pscxpert/bin/checksecureport
Arguments:9473 "Secure file server"
Secure connections
Check if port 9474 (Secure network print server) is in listen state. If not, report it as a violation and it is recommended to turn on the secure network print server.
/etc/security/pscxpert/bin/checksecureport
Arguments:9474 "Secure network print server"
Secure connections
Check if port 9475 (Secure remote command/Program call server) is in listen state. If not, report it as a violation and it is recommended to turn on the secure remote command/Program call server.
Check if port 9476 (Secure signon server) is in listen state. If not, report it as a violation and it is recommended to turn on the secure signon server.
/etc/security/pscxpert/bin/checksecureport
Arguments:9476 "Secure signon server"
Network services
Check if REXEC (port 512) is up and running. If yes, it is recommended to disable it.
/etc/security/pscxpert/bin/checkNetworkService
Network services
Check if LPD (port 515) is up and running. If yes, it is recommended to disable it.
/etc/security/pscxpert/bin/checkNetworkService
Group PTF currency status
Checks whether the installed group PTF levels are current or if a newer level exists.
/etc/security/pscxpert/bin/checkPTFgroupsstatus
Network services
Check if the DDM network server attributes (lowest authentication method) do not contain the following values: *NO, *VLDONLY, *USRID, *USRIDPWD.
/etc/security/pscxpert/bin/checkDDMAuthentication
Arguments:*USRENCPWD
System auditing
Command auditing for privileged users. It is assumed that all users that posses one or more special authorities are treated as a privileged user. Performs the following actions:
Checks (using the USER_INFO table function) whether a user has directly assigned special authorities. Retrieves potential primary and supplemental group profile names. Checks whether the assigned groups provide a special authority.
Checks if the users who have a direct (or indirect via group membership) assigned special authority have the *CMD event turned on for their user profile audit level parameter.
Reports every privileged user who does not have the *CMD auditing value turned as a violation.
Parm 1: Individual user profile name or *ALL to check all users.
Parm 2: The audit event to be checked. The default is *CMD. You can copy and customize the rule to run against another audit event, such as *CREATE or *DELETE, and so forth
Parm 3: One or more user profile names to be exempt from the check.
Applying the rule performs the check and, if not set, sets the required audit event.
/etc/security/pscxpert/bin/checkAuditing
Arguments:*ALL *CMD ""
Patch status individual PTFs
Patch status individual PTFs. If you need a specific patch (PTF) installed on a system, you can use this check to see if a specific patch has been applied on the system. The first argument is the PTF's name. The second argument is the expected status. Set the third argument to y to download, load, and apply the PTF.
/etc/security/pscxpert/bin/checkPTFStatus
Arguments:MF57964 SUPERCEDED n
Default passwords
Analyze default passwords and report those with a default password as a violation. Passwords must be changed by the administrator. The first argument is the action to take against the identified users with default passwords:
*NONE - No action is taken against profiles with a default password.
*DISABLE - The user profile STATUS field is set to *DISABLED.
*PWDEXP - The user profile PWDEXP field is set to *YES.
