USER_INFO_BASIC view

The USER_INFO_BASIC view contains information about user profiles.

This view returns a subset of columns that are returned by the USER_INFO view. It does not return: USER_OWNER , USER_CREATOR, SIZE, CREATION_TIMESTAMP, LAST_USED_TIMESTAMP, DAYS_USED_COUNT, LAST_RESET_TIMESTAMP, or USER_DEFAULT_PASSWORD. USER_INFO_BASIC typically performs much better than USER_INFO.

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: To see a user profile, the caller must have *OBJOPR and *READ authorities to the *USRPRF.

Start of change To see a non-null value for the NETSERVER_DISABLED column, the caller must have either *IOSYSCFG special authority or be authorized to the QIBM_IOSYSCFG_VIEW function usage identifier. End of change

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

Table 1. USER_INFO_BASIC view
Column Name	System Column Name	Data Type	Description
AUTHORIZATION_NAME	USER_NAME	VARCHAR(10)	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	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)	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. Contains the null value if not authorized to view this information.
PASSWORD_CHANGE_DATE	PWDCHGDAT	TIMESTAMP	The date the user's password was last changed.
NO_PASSWORD_INDICATOR	NOPWD	VARCHAR(3)	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	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)	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.
USE_EXIT_PROGRAM_AUTHENTICATION	EXITPGMAUT	VARCHAR(3)	Whether the exit program registered under exit point QIBM_QSY_AUTH is called during user ID and password authentication. NO The exit program authentication method will not be used. YES The exit program authentication method will be used.
TOTP_KEY_EXISTS	TOTPKEY	VARCHAR(3)	Whether the user has a time-based one-time password (TOTP) key. NO The user does not have a TOTP key. YES The user has a TOTP key.
TOTP_KEY_LAST_CHANGED	TOTPKEYCHG	TIMESTAMP(0) Nullable	The timestamp when the TOTP key was changed using the Change TOTP Key (CHGTOTPKEY) CL command or the QSYS2.CHANGE_TOTP_KEY table function. Contains the null value if the user has never run the CHGTOTPKEY CL command or invoked the QSYS2.CHANGE_TOTP_KEY table function.
USE_TOTP_AUTHENTICATION	TOTPAUT	VARCHAR(3)	Whether the TOTP authentication method is used in addition to user ID and password authentication. NO The TOTP authentication method will not be used. YES The TOTP authentication method will be used. This authentication method is only enforced when the additional sign-on factor security attribute is enabled.
TOTP_INTERVAL	TOTP_INTV	INTEGER	The TOTP optional interval, in minutes, used during authentication. This is the amount of time after a successful authentication using a TOTP value that subsequent authentications will be allowed without specifying a TOTP value. A value of 0 means there is no optional interval, and all authentications require a valid TOTP value to be specified.
TOTP_REMAINING_MINUTES	TOTP_REM	INTEGER Nullable	The number of minutes remaining in the TOTP optional interval during which an authentication without specifying a TOTP value is allowed. Contains the null value if TOTP_INTERVAL is 0.
USER_CLASS_NAME	USRCLS	VARCHAR(10)	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)	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)	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)	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)	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)	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)	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)	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)	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)	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)	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)	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	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)	The highest scheduling priority the user is allowed to have for each job submitted to the system.
JOB_DESCRIPTION_NAME	JOBD	VARCHAR(10)	The name of the job description used for jobs that start through subsystem work station entries.
JOB_DESCRIPTION_LIBRARY_NAME	JOBDLIB	VARCHAR(10)	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)	The name of the message queue that is used by this user.
MESSAGE_QUEUE_LIBRARY_NAME	MSGQLIB	VARCHAR(10)	The name of the library the message queue is in. Can contain the special value *LIBL.
MESSAGE_QUEUE_DELIVERY_METHOD	DLVRY	VARCHAR(10)	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	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)	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)	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)	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)	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)	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)	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)	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)	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)	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(374) Nullable	The action audit values for this user. Up to 34 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)	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	The user ID number for the user profile.
GROUP_ID_NUMBER	GID	BIGINT	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)	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)	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)	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)	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)	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)	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)	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	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)	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	The home directory for this user profile.
LOCALE_PATH_NAME	LOCALE	VARGRAPHIC(1024) CCSID 1200	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.
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 have *ALLOBJ special authority.

SELECT * FROM QSYS2.USER_INFO_BASIC
  WHERE SPECIAL_AUTHORITIES LIKE '%*ALLOBJ%';