SPECIAL_AUTHORITY_DATA_MART table

The SPECIAL_AUTHORITY_DATA_MART table is a data mart that contains information about special authorities for users. These authorities can come directly from a user profile or can be obtained through membership in a group profile.

This data mart is implemented as a materialized query table (MQT). The data in this table is only populated and refreshed when requested by a user. This is done by using the REFRESH TABLE SQL statement.

Gathering the special authority information can be a long running task. Consider running the refresh in a batch job.

Authorization: The table is shipped with public authority of *EXCLUDE.

To refresh the table, the caller must have:

*EXECUTE authority to the SYSTOOLS library, and
*OBJMGT, *OBJOPR, *ADD, and *DELETE authority to the SYSTOOLS/SPEC_AUTH file.

To query the table, the caller must have:

*EXECUTE authority to the SYSTOOLS library, and
*OBJOPR and *READ authority to the SYSTOOLS/SPEC_AUTH file.

The following table describes the columns in the table. The system name is SPEC_AUTH. The schema is SYSTOOLS.

Table 1. SPECIAL_AUTHORITY_DATA_MART table
Column Name	System Column Name	Data Type	Description
AUTHORIZATION_NAME	USER_NAME	VARCHAR(10)	User profile name.
SPECIAL_AUTHORITY	SPCAUT	VARCHAR(9)	Special authority value.
AUTHORITY_SOURCE	AUTHSRC	VARCHAR(13)	Source of the special authority for this row. GROUP PROFILE The authority was obtained through the group profile in the GROUP_PROFILE_NAME column. USER PROFILE The authority is part of the user profile definition.
GROUP_PROFILE_NAME	GROUP_NAME	VARCHAR(10) Nullable	The group profile that provided the special authority for this row. Contains the null value if the special authority was obtained from the user profile.
STATUS	STATUS	VARCHAR(10)	The status of the user profile. 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.
TEXT_DESCRIPTION	TEXT	VARCHAR(50) Nullable	The descriptive text for the user profile. Contains the null value if the user profile has no text description.
LAST_USED_DATE	LAST_USED	DATE Nullable	The date the user profile was used last. Contains the null value if the user profile has not been used.

Examples

Update the table to contain the current special authority values for all user profiles on the system.
```
REFRESH TABLE SYSTOOLS.SPECIAL_AUTHORITY_DATA_MART;
```

Determine when the table was last refreshed.

SELECT REFRESH_TIME FROM QSYS2.SYSTABLES
  WHERE TABLE_SCHEMA = 'SYSTOOLS' AND
        TABLE_NAME = 'SPECIAL_AUTHORITY_DATA_MART';

List all the users who have *ALLOBJ special authority, either directly from their user profile or as part of a group profile.
```
SELECT DISTINCT USER_NAME FROM SYSTOOLS.SPECIAL_AUTHORITY_DATA_MART
  WHERE SPECIAL_AUTHORITY = '*ALLOBJ';
```