USER_INFO view

The USER_INFO view contains information about user profiles.

Start of changeThe USER_INFO_BASIC view returns a subset of this information and is faster.End of change

The values returned for the columns in the view are closely related to the values returned by Retrieve User Information (QSYRUSRI) API. Refer to the API for more detailed information.

Authorization: Only *USRPRF objects that the caller has *OBJOPR and *READ authority to will be returned. Start of changeTo see a non-null value for the USER_DEFAULT_PASSWORD column, the caller must have *ALLOBJ and *SECADM authority.End of change

The following table describes the columns in the view. The schema is QSYS2.

Table 1. USER_INFO view
Column Name System Column Name Data Type Description
AUTHORIZATION_NAME USER_NAME VARCHAR(10)
Nullable
User profile name.
PREVIOUS_SIGNON PRVSIGNON TIMESTAMP
Nullable
The date and time the user last signed on.

Contains null if the profile has never been used to sign on.

SIGN_ON_ATTEMPTS_NOT_VALID SIGNONINV INTEGER
Nullable
The number of sign-on attempts that were not valid since the last successful sign-on.
STATUS STATUS VARCHAR(10)
Nullable
The status of the user profile. Contains one of the following values:
*DISABLED
The user profile is disabled; therefore, the user cannot sign on.
*ENABLED
The user profile is enabled; therefore, the user is able to sign on.
Start of changeNETSERVER_DISABLEDEnd of change Start of changeNETSERVEREnd of change Start of changeVARCHAR(3)End of change Start of changeWhether this user profile is disabled for IBM i NetServer use.
NO
The user profile is not disabled for IBM i NetServer use.
YES
The user profile is disabled for IBM i NetServer use.
End of change
PASSWORD_CHANGE_DATE PWDCHGDAT TIMESTAMP
Nullable
The date the user's password was last changed.
NO_PASSWORD_INDICATOR NOPWD VARCHAR(3)
Nullable
Indicates whether *NONE is specified for the password in the user profile.
NO
The password in the user profile is not *NONE.
YES
The password in the user profile is *NONE.
Start of changePASSWORD_LEVEL_0_1End of change Start of changePWD_0_1End of change Start of changeVARCHAR(3)
Nullable
End of change
Start of changeIndicates whether the user profile has a password that can be used for a system at QPWDLVL 0 or 1.
NO
The password cannot be used.
YES
The password can be used.

Contains the null value if NO_PASSWORD_INDICATOR has a value of YES.

End of change
Start of changePASSWORD_LEVEL_2_3End of change Start of changePWD_2_3End of change Start of changeVARCHAR(3)
Nullable
End of change
Start of changeIndicates whether the user profile has a password that can be used for a system at QPWDLVL 2 or 3.
NO
The password cannot be used.
YES
The password can be used.

Contains the null value if NO_PASSWORD_INDICATOR has a value of YES.

End of change
PASSWORD_EXPIRATION_INTERVAL PWDEXPITV SMALLINT
Nullable
The number of days (from 1 through 366) the user's password can remain active before it must be changed. Can also be one of the following values:
0
The system value QPWDEXPITV is used to determine the user's password expiration interval.
-1
The user's password does not expire (*NOMAX).
DATE_PASSWORD_EXPIRES PWDEXPDAT TIMESTAMP
Nullable
The date the user's password expires.

Contains null if the password will not expire.

DAYS_UNTIL_PASSWORD_EXPIRES PWDDAYSEXP INTEGER
Nullable
The number of days until the password will expire. A value of 0 indicates the password is expired.

Contains null if the password will not expire within the number of days specified by the password expiration warning (QPWDEXPWRN) system value.

SET_PASSWORD_TO_EXPIRE PWDEXP VARCHAR(3)
Nullable
Indicates whether the user's password is set to expire, requiring the user to change the password when signing on. Contains one of the following values:
NO
The user's password is not set to expire.
YES
The user's password is set to expire.
USER_CLASS_NAME USRCLS VARCHAR(10)
Nullable
The user's class name. Contains one of the following values:
*PGMR
The user has a class of programmer.
*SECADM
The user has a class of security administrator.
*SECOFR
The user has a class of security officer.
*SYSOPR
The user has a class of system operator.
*USER
The user has a class of end user.
SPECIAL_AUTHORITIES SPCAUT VARCHAR(88)
Nullable
A list of the special authorities the user has. Up to 8 authorities are returned. Each entry is padded with blanks to fill 11 characters.

Contains null if the user has no special authorities.

GROUP_PROFILE_NAME GRPPRF VARCHAR(10)
Nullable
The name of the group profile. Contains the value *NONE if the user does not have a group profile.
Start of changeSUPPLEMENTAL_GROUP_COUNTEnd of change Start of changeSUPGRPCNTEnd of change Start of changeSMALLINTEnd of change Start of changeThe number of supplemental groups in the SUPPLEMENTAL_GROUP_LIST column.End of change
Start of changeSUPPLEMENTAL_GROUP_LISTEnd of change Start of changeSUPGRPLISTEnd of change Start of changeVARCHAR(150)
Nullable
End of change
Start of changeA list of supplemental groups for the user profile. Up to 15 supplemental groups are returned. Each entry except for the last one is padded with blanks to fill 10 characters.

Contains null if the user has no supplemental groups.

End of change
OWNER OWNER VARCHAR(10)
Nullable
This field indicates who is to own objects created by this user. Contains one of the following values:
*GRPPRF
The user's group profile owns any objects the user creates.
*USRPRF
The user owns any objects the user creates.
GROUP_AUTHORITY GRPAUT VARCHAR(10)
Nullable
The authority the user's group profile has to objects the user creates. Contains one of the following values:
*ALL
The group profile has all authority to the objects the user creates.
*CHANGE
The group profile has change authority to the objects the user creates.
*EXCLUDE
The group profile has exclude authority to the objects the user creates.
*NONE
The group profile has no authority to the objects the user creates. If the user does not have a group profile, this value is returned.
*USE
The group profile has use authority to the objects the user creates.
ASSISTANCE_LEVEL ASTLVL VARCHAR(10)
Nullable
The user interface that the user will use. Contains one of the following values:
*ADVANCED
The expert system user interface.
*BASIC
The Operational Assistant user interface.
*INTERMED
The system user interface.
*SYSVAL
The system value QASTLVL determines which user interface the user is using.
CURRENT_LIBRARY_NAME CURLIB VARCHAR(10)
Nullable
The name of the user's current library. Contains *CRTDFT if the user does not have a current library.
INITIAL_MENU_NAME INLMNU VARCHAR(10)
Nullable
The initial menu for the user. Can contain the special value *SIGNOFF.
INITIAL_MENU_LIBRARY_NAME INLMNULIB VARCHAR(10)
Nullable
The name of the library that the initial menu is in. Can contain the special value *LIBL.

Contains null if the menu name is *SIGNOFF.

INITIAL_PROGRAM_NAME INITPGM VARCHAR(10)
Nullable
The initial program for the user. Contains *NONE is there is no initial program.
INITIAL_PROGRAM_LIBRARY_NAME INITPGMLIB VARCHAR(10)
Nullable
The name of the library that the initial program is in. Can contain the special value *LIBL.

Contains null if the initial program name is *NONE.

LIMIT_CAPABILITIES LMTCPB VARCHAR(10)
Nullable
Indicates whether the user has limited capabilities. Contains one of the following values:
*NO
The user is not limited.
*PARTIAL
The user cannot change his initial program or current library.
*YES
The user cannot change his initial menu, initial program, or current library. The user cannot run commands from the command line.
TEXT_DESCRIPTION TEXT VARCHAR(50)
Nullable
The descriptive text for the user profile.

Contains null if the user profile has no text description.

DISPLAY_SIGNON_INFORMATION DSPSGNINF VARCHAR(10)
Nullable
Indicates whether the sign-on information display is shown when the user signs on. Contains one of the following special values:
*NO
The sign-on information display is not shown when the user signs on.
*SYSVAL
The system value QDSPSGNINF determines if the sign-on information display is shown when the user signs on.
*YES
The sign-on information display is shown when the user signs on.
LIMIT_DEVICE_SESSIONS LMTDEVSSN VARCHAR(10)
Nullable
Specifies if the number of device sessions allowed for a user is limited. Can contain one of the following special values:
*NO
The user is not limited to a specific number of device sessions.
*SYSVAL
The system value QLMTDEVSSN determines if the user is limited to a specific number of device sessions.
*YES
The user is limited to one device session.
KEYBOARD_BUFFERING KBDBUF VARCHAR(10)
Nullable
The keyboard buffering value that is used when a job is initialized for this user. Contains one of the following special values:
*NO
The type-ahead and attention-key buffering options are not on.
*SYSVAL
The system value QKBDBUF determines the keyboard buffering value for this user.
*TYPEAHEAD
The type-ahead option is on, but the attention-key buffering option is not.
*YES
The type-ahead and attention-key buffering options are both on.
MAXIMUM_ALLOWED_STORAGE MAXSTGLRG BIGINT
Nullable
The maximum amount of auxiliary storage (in kilobytes) that can be assigned to store permanent objects owned by the user. Contains null if the user has no maximum storage.
STORAGE_USED STGUSED BIGINT
Nullable
The amount of auxiliary storage (in kilobytes) occupied by this user's owned objects on *SYSBAS.  The QSYS2.USER_STORAGE catalog should be used to determine the storage consumed on all ASPs.
HIGHEST_SCHEDULING_PRIORITY PTYLMT CHAR(1)
Nullable
The highest scheduling priority the user is allowed to have for each job submitted to the system.
JOB_DESCRIPTION_NAME JOBD VARCHAR(10)
Nullable
The name of the job description used for jobs that start through subsystem work station entries.
JOB_DESCRIPTION_LIBRARY_NAME JOBDLIB VARCHAR(10)
Nullable
Job description library name. Can contain the special value *LIBL.
ACCOUNTING_CODE ACGCDE VARCHAR(15)
Nullable
The accounting code that is associated with this user.

Contains null if there is no accounting code.

MESSAGE_QUEUE_NAME MSGQ VARCHAR(10)
Nullable
The name of the message queue that is used by this user.
MESSAGE_QUEUE_LIBRARY_NAME MSGQLIB VARCHAR(10)
Nullable
The name of the library the message queue is in. Can contain the special value *LIBL.
MESSAGE_QUEUE_DELIVERY_METHOD DLVRY VARCHAR(10)
Nullable
How the messages are delivered to the message queue used by the user. Contains one of the following values:
*BREAK
The job to which the message queue is assigned is interrupted when a message arrives on the message queue.
*DFT
Messages requiring replies are answered with their default reply.
*HOLD
The messages are held in the message queue until they are requested by the user or program.
*NOTIFY
The job to which the message queue is assigned is notified when a message arrives on the message queue.
MESSAGE_QUEUE_SEVERITY SEV SMALLINT
Nullable
The lowest severity that a message can have and still be delivered to a user in break or notify mode
OUTPUT_QUEUE_NAME OUTQ VARCHAR(10)
Nullable
The output queue used by this user. Can contain one of the following special values:
*DEV
An output queue with the same name as the device specified in the printer device parameter is used by the user.
*WRKSTN
The output queue assigned to the user's work station is used.
OUTPUT_QUEUE_LIBRARY_NAME OUTQLIB VARCHAR(10)
Nullable
The name of the library where the output queue is located.

Contains null if the output queue name is *DEV or *WRKSTN.

PRINT_DEVICE PRTDEV VARCHAR(10)
Nullable
The printer used to print for this user. Can contain one of the following special values:
*SYSVAL
The default system printer specified in the system value QPRTDEV is used.
*WRKSTN
The printer assigned to the user's work station is used.
SPECIAL_ENVIRONMENT SPCENV VARCHAR(10)
Nullable
The special environment the user operates in after signing on. Contains one of the following special values:
*NONE
The user operates in the IBM i environment.
*SYSVAL
The system value QSPCENV is used to determine the user's special environment.
*S36
The user operates in the System/36 environment.
ATTENTION_KEY_HANDLING_
  PROGRAM_NAME
ATNPGM VARCHAR(10)
Nullable
The attention key handling program for this user. Can contain one of the following special values:
*NONE
No Attention-key-handling program is used.
*SYSVAL
The system value QATNPGM determines the user's Attention-key-handling program.
ATTENTION_KEY_HANDLING_
  PROGRAM_LIBRARY_NAME
ATNPGMLIB VARCHAR(10)
Nullable
The name of the library where the program is located. Can contain the special value *LIBL.

Contains null if the attention key handling program is *NONE or *SYSVAL.

LANGUAGE_ID LANGID VARCHAR(10)
Nullable
The language ID used by the system for this user. Can contain the following special value:
*SYSVAL
The system value QLANGID is used to determine the user's language ID.
COUNTRY_OR_REGION_ID CNTRYID VARCHAR(10)
Nullable
Country or region ID. Can contain the following special value:
*SYSVAL
The system value QCNTRYID is used to determine the user's country or region ID.
CHARACTER_CODE_SET_ID CCSID VARCHAR(6)
Nullable
The CCSID for the user. Can contain the following special value:
QCCSID
The system value QCCSID is used to determine the user's character code set ID.
USER_OPTIONS USROPT VARCHAR(77)
Nullable
A list of the options for users to customize their environment. Up to 7 options are returned. Each entry is padded with blanks to fill 11 characters.

Contains null if there are no user options.

SORT_SEQUENCE_TABLE_NAME SRTSEQ VARCHAR(10)
Nullable
The name of the sort sequence table used for string comparisons. Can contain one of the following special values:
*HEX
The hexadecimal values of the characters are used to determine the sort sequence.
*LANGIDSHR
A shared-weight sort table associated with the language specified.
*LANGIDUNQ
A unique-weight sort table associated with the language specified.
*SYSVAL
The system value QSRTSEQ.
SORT_SEQUENCE_TABLE_LIBRARY_NAME SRTSEQLIB VARCHAR(10)
Nullable
The name of the library that is used to locate the sort sequence table.

Contains null if the sort sequence table is a special value.

OBJECT_AUDITING_VALUE OBJAUD VARCHAR(10)
Nullable
The object auditing value for this user. Contains one of the following values:
*ALL
Object read and change operations are audited for the current user if the object's auditing value is *USRPRF.
*CHANGE
Object changes are audited for the current user if the object's auditing value is *USRPRF.
*NONE
No additional object auditing is done for the current user.
*NOTAVL
The user is not allowed to retrieve the object auditing value.
USER_ACTION_AUDIT_LEVEL AUDLVL VARCHAR(363)
Nullable
The action audit values for this user. Up to 31 options are returned. Each entry is padded with blanks to fill 11 characters.

Contains null if there are no action values or if the caller is not authorized to retrieve the action audit level.

GROUP_AUTHORITY_TYPE GRPAUTTYP VARCHAR(10)
Nullable
The type of authority the user's group profile has to objects the user creates. Contains one of the following special values:
*PGP
The group profile will be the primary group for objects the user creates.
*PRIVATE
The group profile has a private authority to the objects the user creates. If the user does not have a group profile, this value is returned.
USER_ID_NUMBER UID BIGINT
Nullable
The user ID number for the user profile.
GROUP_ID_NUMBER GID BIGINT
Nullable
The group ID number for the user profile. The value 0 is returned if the user has no group ID number.
LOCALE_JOB_ATTRIBUTES SETJOBATR VARCHAR(88)
Nullable
A list of the job attributes that are taken from the user's locale path. This column contains a list of up to 8 items. Each entry is padded with blanks to fill 11 characters.
GROUP_MEMBER_INDICATOR GRPMBR VARCHAR(3)
Nullable
Whether this user is a group that has members. Contains one of the following values:
NO
The user is not a group, or is a group but does not have any members.
YES
The user is a group that has members.
DIGITAL_CERTIFICATE_INDICATOR DCIND VARCHAR(3)
Nullable
Whether there are digital certificates associated with this user. Contains one of the following values:
NO
There are no digital certificates associated with this user.
YES
There is at least one digital certificate associated with this user.
CHARACTER_IDENTIFIER_CONTROL CHRIDCTL VARCHAR(10)
Nullable
The character identifier control for the user. Can contain the following special values:
*DEVD
The *DEVD special value performs the same function as on the CHRID command parameter for display files, printer files, and panel groups.
*JOBCCSID
The *JOBCCSID special value performs the same function as on the CHRID command parameter for display files, printer files, and panel groups.
*SYSVAL
The value QCHRIDCTL system value will be used to determine the CHRID control for this user.
LOCAL_PASSWORD_MANAGEMENT LCLPWDMGT VARCHAR(3)
Nullable
Indicates if password is managed locally. Contains one of the following values:
NO
The password is not managed locally.
YES
The password is managed locally.
BLOCK_PASSWORD_CHANGE PWDCHGBLK VARCHAR(10)
Nullable
Specifies the time period, in hours, during which a password is blocked from being changed following the prior successful password change operation. Can contain one of the following special values:
*NONE
The password can be changed at any time.
*SYSVAL
The system value QPWDCHGBLK is used to determine the password change limit.
USER_ENTITLEMENT_REQUIRED ENTITLERQD VARCHAR(3)
Nullable
Whether a user entitlement is required for this user profile. Contains one of the following values:
NO
A user entitlement is not required for this user profile.
YES
A user entitlement is required for this user profile.
USER_EXPIRATION_INTERVAL USREXPITV SMALLINT
Nullable
The number of days (from 1 through 366) before the user profile is automatically disabled. The value 0 is returned if no expiration interval is defined.
USER_EXPIRATION_DATE ESREXPDATE TIMESTAMP
Nullable
The date when the user profile expires and is automatically disabled or deleted.

Contains null if the user profile will not expire.

USER_EXPIRATION_ACTION ACTION VARCHAR(8)
Nullable
The action that will occur when the user profile has expired. Contains one of the following values:
*DELETE
The user profile will be deleted. If the user profile cannot be deleted, it will be disabled.
*DISABLE
The user profile will be disabled.
*NONE
The user profile will not expire.
HOME_DIRECTORY HOMEDIR
VARGRAPHIC(1024)
CCSID 1200

Nullable
The home directory for this user profile.
LOCALE_PATH_NAME LOCALE
VARGRAPHIC(1024)
CCSID 1200

Nullable
The locale path name that is assigned to the user profile when a job is started. Can contain one of the following special values:
*C
The C locale path name is assigned.
*NONE
No locale path name is assigned.
*POSIX
The POSIX locale path name is assigned.
*SYSVAL
The QLOCALE system value is used to determine the locale path name.
USER_DEFAULT_PASSWORD DFTPWD VARCHAR(3)
Nullable
The password is the default password.
NO
The password is not the default password.
YES
The password appears to be the default password since it matches the user profile name.

Start of changeContains null if not authorized to view this information.End of change

USER_OWNER USER_OWNER VARCHAR(10)
Nullable
The user profile that owns this user profile.
USER_CREATOR CREATOR VARCHAR(10)
Nullable
The user profile that created this user profile.
SIZE SIZE DECIMAL(15,0)
Nullable
Size of the user profile, in bytes.
CREATION_TIMESTAMP TIMESTAMP TIMESTAMP
Nullable
Timestamp of when the user profile was created.
LAST_USED_TIMESTAMP LASTUSED TIMESTAMP
Nullable
The date the user profile was used last. The time portion of the timestamp will always be 0.
DAYS_USED_COUNT DAYSUSED INTEGER
Nullable
The number of days the user profile has been used on the system.
LAST_RESET_TIMESTAMP LASTRESET TIMESTAMP
Nullable
The date when the days used count was last reset to zero. The time portion of the timestamp will always be 0.
Start of changeAUTHORITY_COLLECTION_ACTIVEEnd of change Start of changeAUTCOLACTEnd of change Start of changeVARCHAR(3)End of change Start of changeWhether authority collection is active for this user.
NO
Authority collection is not active for this user.
YES
Authority collection is active for this user.
End of change
Start of change
AUTHORITY_COLLECTION_REPOSITORY_
  EXISTS
End of change
Start of changeAUTCOLREPEnd of change Start of changeVARCHAR(3)End of change Start of changeWhether an authority collection repository exists for this user.
NO
An authority collection repository does not exist for this user.
YES
An authority collection repository exists for this user.
End of change
Start of changePASE_SHELL_PATHEnd of change Start of changeSHELL_PATHEnd of change Start of change
VARCHAR(1024)
CCSID 1208
Nullable
End of change
Start of changePath to the user's PASE shell. If AUTHORIZATION_NAME is QSYS, this column contains the default shell path used for all user profiles that have not had a value explicitly set.

Returns the null value if a value has not been set using the QSYS2.SET_PASE_SHELL_INFO procedure.

End of change

Example

Determine which users are having trouble signing on.

SELECT * FROM QSYS2.USER_INFO
  WHERE SIGN_ON_ATTEMPTS_NOT_VALID > 0