OBJECT_PRIVILEGES view

The OBJECT_PRIVILEGES view returns a row for every user authorized to an object, along with their associated object and data authorities.

The information returned is similar to the information available through the Display Object Authority (DSPOBJAUT) CL command.

Authorization: All authorized users are returned for an object when at least one of the following is true:

The caller has *OBJMGT authority.
The caller is the owner of the object.
The object is an authorization list.
The caller is authorized to the QIBM_DB_SECADM function usage identifier.

Otherwise, only authorizations for the caller are returned.

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

Table 1. OBJECT_PRIVILEGES view
Column name	System column name	Data type	Description
OBJECT_SCHEMA	OSCHEMA	VARCHAR(128)	The SQL schema name for this object.
OBJECT_NAME	NAME	VARCHAR(128) Nullable	The SQL name of the object. For an external procedure or an external function, the name will be returned when a single procedure or function exists for that PGM or SRVPGM object. Contains the null value if an SQL name could not be returned.
SYSTEM_OBJECT_SCHEMA	SYS_DNAME	VARCHAR(10)	The library that contains the object.
SYSTEM_OBJECT_NAME	SYS_ONAME	VARCHAR(10)	The system object name.
OBJECT_TYPE	OBJTYPE	VARCHAR(8)	The system object type.
SQL_OBJECT_TYPE	SQLTYPE	VARCHAR(9) Nullable	The SQL object type. The following values can be returned. ALIAS The object is an SQL alias. FUNCTION The object is an SQL function. INDEX The object is an SQL index. PACKAGE The object is an SQL package. PROCEDURE The object is an SQL procedure. ROUTINE The object is used in SQL by one or more external functions and/or external procedures. SEQUENCE The object is an SQL sequence. TABLE The object is an SQL table. TRIGGER The object is an SQL trigger. TYPE The object is an SQL type. VARIABLE The object is an SQL global variable. VIEW The object is an SQL view. XSR The object is an XML schema repository object. Contains the null value if the object is not an SQL object or if the user does not have sufficient authority to the object.
AUTHORIZATION_NAME	USER_NAME	VARCHAR(10)	User profile name. Can contain the following special value. *PUBLIC This row contains the public authority for the object.
OBJECT_AUTHORITY	OBJ_AUTH	VARCHAR(12)	The authority that the user has to the object. Contains one of the following special values: ALL Allows all operations on the object except those that are limited to the owner or controlled by authorization list management authority. AUTL The public authority specified in the authorization list used by this object is used. CHANGE Allows all operations on the object except those that are limited to the owner or controlled by object existence authority, object alter authority, object reference authority, and object management authority. EXCLUDE All operations on the object are prohibited. *USE Allows access to the object attributes and use of the object. The user cannot change the object. USER DEFINED The specific object authorities and data authorities do not match any of the predefined object authority levels.
OWNER	OWNER	VARCHAR(10)	The user profile that owns the object.
AUTHORIZATION_LIST	AUTL	VARCHAR(10) Nullable	The name of the authorization list if the object is secured by an authorization list. Contains null if the object is not secured by an authorization list.
PRIMARY_GROUP	GROUP	VARCHAR(10) Nullable	The user who is the primary group for the object. Contains the null value if there is no primary group for the object.
AUTHORIZATION_LIST_MANAGEMENT	AUTL_MGMT	VARCHAR(3)	The authorization list management authority for AUTHORIZATION_NAME. NO The user does not have this authority. YES The user has this authority.
OBJECT_OWNER	OBJ_OWNER	VARCHAR(3)	Indicates whether the AUTHORIZATION_NAME for this row is also the OWNER of the object. NO AUTHORIZATION_NAME is not the owner of the object. YES AUTHORIZATION_NAME is the owner of the object.
OBJECT_OPERATIONAL	OBJOPER	VARCHAR(3)	Indicates the object operational authority for AUTHORIZATION_NAME. NO The user does not have this authority. YES The user has this authority.
OBJECT_MANAGEMENT	OBJMGT	VARCHAR(3)	The object management authority for AUTHORIZATION_NAME. NO The user does not have this authority. YES The user has this authority.
OBJECT_EXISTENCE	OBJEXIST	VARCHAR(3)	The object existence authority for AUTHORIZATION_NAME. NO The user does not have this authority. YES The user has this authority.
OBJECT_ALTER	OBJALTER	VARCHAR(3)	The object alter authority for AUTHORIZATION_NAME. NO The user does not have this authority. YES The user has this authority.
OBJECT_REFERENCE	OBJREF	VARCHAR(3)	The object reference authority for AUTHORIZATION_NAME. NO The user does not have this authority. YES The user has this authority.
DATA_READ	DATA_READ	VARCHAR(3)	The data read authority for AUTHORIZATION_NAME. NO The user does not have this authority. YES The user has this authority.
DATA_ADD	DATA_ADD	VARCHAR(3)	The data add authority for AUTHORIZATION_NAME. NO The user does not have this authority. YES The user has this authority.
DATA_UPDATE	DATA_UPD	VARCHAR(3)	The data update authority for AUTHORIZATION_NAME. NO The user does not have this authority. YES The user has this authority.
DATA_DELETE	DATA_DEL	VARCHAR(3)	The data delete authority for AUTHORIZATION_NAME. NO The user does not have this authority. YES The user has this authority.
DATA_EXECUTE	DATA_EXEC	VARCHAR(3)	The data execute authority for AUTHORIZATION_NAME. NO The user does not have this authority. YES The user has this authority.
TEXT_DESCRIPTION	TEXT	VARCHAR(50) Nullable	The descriptive text for this object. Contains the null value if the object has no text description or if the user does not have sufficient authority to the object.

Example

Find user profiles that are publicly accessible.

SELECT *
   FROM QSYS2.OBJECT_PRIVILEGES
   WHERE SYSTEM_OBJECT_SCHEMA = 'QSYS' AND
         OBJECT_TYPE = '*USRPRF' AND
         AUTHORIZATION_NAME = '*PUBLIC' AND
         OBJECT_AUTHORITY <> '*EXCLUDE';