USER_INFO view

The USER_INFO view contains information about user profiles.

The USER_INFO_BASIC view returns a subset of this information and is faster.

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: The caller must have *OBJOPR and *READ authorities to the *USRPRF. To see a non-null value for the USER_DEFAULT_PASSWORD column, the caller must have *ALLOBJ and *SECADM authorities.

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.
MAXIMUM_SIGN_ON_ATTEMPTS	MAXSIGN	VARCHAR(7)	The maximum number of sign-on attempts allowed. If the maximum sign-on attempts value is reached, the action specified by the QMAXSGNACN system value is performed. 1-25 The number of sign-on attempts allowed. *SYSVAL The system value QMAXSIGN determines the maximum sign-on attempts.
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.
NETSERVER_DISABLED	NETSERVER	VARCHAR(3)	Whether 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.
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.
PASSWORD_LEVEL_0_1	PWD_0_1	VARCHAR(3) Nullable	Indicates 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.
PASSWORD_LEVEL_2_3	PWD_2_3	VARCHAR(3) Nullable	Indicates 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.
PASSWORD_LEVEL_4	PWD_4	VARCHAR(3) Nullable	Indicates whether the user profile has a password that can be used for a system at QPWDLVL 4. 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.
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.
SUPPLEMENTAL_GROUP_COUNT	SUPGRPCNT	SMALLINT	The number of supplemental groups in the SUPPLEMENTAL_GROUP_LIST column.
SUPPLEMENTAL_GROUP_LIST	SUPGRPLIST	VARCHAR(150) Nullable	A 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.
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 if 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	USREXPDATE	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. Contains null if not authorized to view this information.
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.
AUTHORITY_COLLECTION_ACTIVE	AUTCOLACT	VARCHAR(3)	Whether authority collection is active for this user. NO Authority collection is not active for this user. YES Authority collection is active for this user.
AUTHORITY_COLLECTION_REPOSITORY_ EXISTS	AUTCOLREP	VARCHAR(3)	Whether 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.
PASE_SHELL_PATH	SHELL_PATH	VARCHAR(1024) CCSID 1208 Nullable	Path 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.

Example

Determine which users are having trouble signing on.

SELECT * FROM QSYS2.USER_INFO
  WHERE SIGN_ON_ATTEMPTS_NOT_VALID > 0