Special Authority Data Mart

A second data mart was introduced as part of the last round of Technology Refreshes that allows me to capture and store information about the special authorities given to user profiles in my partitions.

The Special Authority Data Mart is held in the table SPECIAL_AUTHORITY_DATA_MART, which resides in the SYSTOOLS. The table is a Materialize Query Table, MQT. This means that the contents of the table are not dynamic, changed when changes are made to the user profiles, it is refreshed using the REFRESH TABLE SQL statement. If you don't know what that is I will show how to use it in my examples below.

The most basic SQL statement to retrieve the data from the Table is:

01 SELECT * FROM SYSTOOLS.SPECIAL_AUTHORITY_DATA_MART

This will display all the rows and all the columns of data. You can then decide which are important to you.

If the Table is empty we need to populate it, and as I mentioned I do that using the SQL Refresh Table statement. On October 25 I ran the follow Refresh Table statement:

REFRESH TABLE SYSTOOLS.SPECIAL_AUTHORITY_DATA_MART

You might not be authorized to run the Refresh Table, if you are not then ask your System Administrator to run the Refresh Table statement.

As I said this populates the Table with the Special Authority data. Now I can use the previous Select statement to show what data I have. I am not going to do that here as there are too many columns and rows for me to show it in any meaningful way.

What I did was to create the following Select statement that shows only the columns I want for my own user profile, SIMON.

01 SELECT SPECIAL_AUTHORITY, 02 AUTHORITY_SOURCE, 03 GROUP_PROFILE_NAME, 04 LAST_USED_DATE 05 FROM SYSTOOLS.SPECIAL_AUTHORITY_DATA_MART 06 WHERE AUTHORIZATION_NAME = 'SIMON' 07 ORDER BY 1

Line 1: This is the column that contains the Special Authority value.

Line 2: Where I get the Special Authority from. 'USER PROFILE' means that I get it from my user profile. 'GROUP PROFILE' the authority comes from my group authority, which is displayed in…

Line 3: The group profile that provides the Special Authority.

Line 4: The date the user profile was last used. Null denotes that the profile has never been used.

Line 6: I am only interested in the results for my user profile, SIMON.

Line 7: The results will be sorted by the first column, SPECIAL_AUTHORITY.

The results look like:

GROUP_ SPECIAL_ AUTHORITY PROFILE LAST_ AUTHORITY _SOURCE _NAME USED_DATE --------- ------------- --------- ---------- *ALLOBJ USER PROFILE <NULL> 2024-10-25 *ALLOBJ GROUP PROFILE GRPADMIN 2024-10-25 *AUDIT GROUP PROFILE GRPADMIN 2024-10-25 *IOSYSCFG GROUP PROFILE GRPADMIN 2024-10-25 *JOBCTL GROUP PROFILE GRPADMIN 2024-10-25 *SAVSYS GROUP PROFILE GRPADMIN 2024-10-25 *SECADM USER PROFILE <NULL> 2024-10-25 *SECADM GROUP PROFILE GRPADMIN 2024-10-25 *SERVICE GROUP PROFILE GRPADMIN 2024-10-25 *SPLCTL GROUP PROFILE GRPADMIN 2024-10-25

I managed to be patient and wait until November 3, when I executed the Refresh Table statement again to update the contents of SPECIAL_AUTHORITY_DATA_MART.

I ran the previous Select statement which gave me:

GROUP_ SPECIAL_ AUTHORITY PROFILE LAST_ AUTHORITY _SOURCE _NAME USED_DATE --------- ------------- --------- ---------- *ALLOBJ USER PROFILE <NULL> 2024-11-03 *ALLOBJ GROUP PROFILE GRPADMIN 2024-11-03 *AUDIT GROUP PROFILE GRPADMIN 2024-11-03 *IOSYSCFG GROUP PROFILE GRPADMIN 2024-11-03 *JOBCTL GROUP PROFILE GRPADMIN 2024-11-03 *SAVSYS GROUP PROFILE GRPADMIN 2024-11-03 *SECADM USER PROFILE <NULL> 2024-11-03 *SECADM GROUP PROFILE GRPADMIN 2024-11-03 *SERVICE GROUP PROFILE GRPADMIN 2024-11-03 *SPLCTL GROUP PROFILE GRPADMIN 2024-11-03

The number of rows did not change as my Special Authorities had not changed since October 25. The Last Used Date column did change as I last signed onto this partition November 3, which was the current day I captured this information.

I can use the data in the Special Authority Data Mart for more than just my information. How about I want to know who has All Object authority? (*ALLOBJ)

01 SELECT DISTINCT USER_NAME 02 FROM SYSTOOLS.SPECIAL_AUTHORITY_DATA_MART 03 WHERE SPECIAL_AUTHORITY = '*ALLOBJ' 04 ORDER BY 1 05 LIMIT 5

Line 1: I am using Select Distinct as I only want one result per user profile. I have used the short column name USER_NAME rather that the long name AUTHORIZATION_NAME. IMHO USER_NAME is a better name as it explains more accurately the contents of the column.

Line 3: I am only select the tows that have *ALLOBJ authority.

Line 4: Order by the first, and only, column.

Line 5: As this is an example I only want five results. If this was in a real-world scenario I would want all of the results.

The results are:

USER_NAME ---------- B********* D********* E********* G********* QLPAUTO

I can see a need for me to save this data, to be able to compare the Special Authorities at different dates to ensure user profiles had had Special Authority removed, and to discover if someone has had Special Authorities added that I was unaware of.

Historical information cannot be stored in the SPECIAL_AUTHORITY_DATA_MART Table as it is a MQT, and when the Table is refreshed the old data is lost. What I need to do is to make a "History Table" to contain this information. I can use the following two SQL statements to do that:

01 DROP TABLE IF EXISTS MYLIB.SPCAUTHHST ; 02 CREATE TABLE MYLIB.SPECIAL_AUTHORITY_DATA_MART_HISTORY 03 FOR SYSTEM NAME "SPCAUTHHST" 04 (USER_NAME,STATUS,SPECIAL_AUTHORITY, 05 GROUP_PROFILE,LAST_USED_DATE,RUN_TIMESTAMP) 06 AS 07 (SELECT USER_NAME,STATUS,SPECIAL_AUTHORITY, 08 GROUP_PROFILE_NAME,LAST_USED_DATE, 09 CURRENT_TIMESTAMP 10 FROM SYSTOOLS.SPECIAL_AUTHORITY_DATA_MART) 11 DEFINITION ONLY ;

Line 1: In this statement if the object already exists it is dropped (deleted). The IF EXISTS stops the statement from erroring if the Table does not exist.

Lines 2 – 11: I am creating this Table "on the fly" as it is based on another Table, the Special Authority Data Mart Table.

Lines 2 and 3: I am giving the Table both long and short names.

Lines 4 – 5: List of the columns for the Table. The only one different from previous statements in the last column, RUN_TIMESTAMP.

Line 9: The new column is populated with the current timestamp. This gives me a column where I can group results from particular dates together.

Line 11: This states that the statement is just used for definition, the creation of the file only.

I wrote a program to refresh the MQT and copy the data from the SPECIAL_AUTHORITY_DATA_MART Table to the new SPECIAL_AUTHORITY_DATA_MART_HISTORY Table. The program looks like:

01 **free // Refresh MQT 02 exec sql REFRESH TABLE SYSTOOLS.SPECIAL_AUTHORITY_DATA_MART ; // Insert data into History file 03 exec sql INSERT INTO MYLIB.SPECIAL_AUTHORITY_DATA_MART_HISTORY 04 SELECT USER_NAME,STATUS,SPECIAL_AUTHORITY, 05 GROUP_PROFILE_NAME,LAST_USED_DATE, 06 CURRENT_TIMESTAMP 07 FROM SYSTOOLS.SPECIAL_AUTHORITY_DATA_MART ; 08 *inlr = *on ;

Line 2: The Refresh table is run first to refresh the MQT.

Line 3 – 7: This statement inserts the data from SPECIAL_AUTHORITY_DATA_MART Table into the SPECIAL_AUTHORITY_DATA_MART_HISTORY Table.

I can add this program to the job scheduler and run it periodically.

 

• SPECIAL_AUTHORITY_DATA_MART Table
• Refresh Table SQL statement

 

This article was written for IBM i 7.5 TR4 and 7.4 TR10.