Which users are authorized to these files and tables?

This post started as one idea, and the more I played it morphed into something else.

The starting point was the SYSTABAUTH view. This view displays the authority of all DDS PF and LF files, SQL DDL tables, views and indexes, in all libraries, except QTEMP. It contains the same information that is shown when I use the Display Object Authority command, DSPOBJAUT. As it is a view I can retrieve the results from SQL statements and manipulate it any way I desire.

In these examples I will be using a few of the view's columns:

1. GRANTEE:  the user profile that has been authorized to the object
2. SYSTEM_TABLE_SCHEMA:  the system name of the library that contains the object
3. SYSTEM_TABLE_NAME:  the system name of the object
4. PRIVILEGE_TYPE:  the various authorities granted to the user profile to this object

There are seven different types of privilege that can be given to an object:

Privilege type	Description
ALTER	Authority to alter the table. Does not mean the data within it, means the structure of the table/file/etc.
DELETE	Authority to delete rows/records
INDEX	Authority to create an index or LF on the table/file
INSERT	Authority to insert rows/records
REFERENCES	Authority to reference this table/file in a referential constraint
SELECT	Authority to select rows/read records
UPDATE	Authority to update rows/records

The first part of my play was to create two SQL DDL tables and a view.

01 CREATE OR REPLACE TABLE MYLIB.TABLE1 (COL1 CHAR(1)) ; 02 CREATE OR REPLACE TABLE MYLIB.TABLE2 (COL2 CHAR(1)) ; 03 CREATE OR REPLACE VIEW MYLIB.VIEW1 AS SELECT * FROM MYLIB.TABLE1 ;

Lines 1 and 2: These are the SQL DDL statements to create the tables. In this scenario it does not matter that they contain one column.

Line 3: Here I am creating a view based on TABLE1.

Just to prove that I can get the same information about a DDS physical file I created one, the DDS source is irrelevant for these experiments.

01 CRTPF FILE(MYLIB/TESTFILE) SRCFILE(MYLIB/DEVSRC)

I then used the Grant Object Authority command, GRTOBJAUT, to change the tables' and the file's public authority.

01 GRTOBJAUT OBJ(MYLIB/TABLE1) OBJTYPE(*FILE) USER(*PUBLIC) + AUT(*ALL) 02 GRTOBJAUT OBJ(MYLIB/TABLE2) OBJTYPE(*FILE) USER(*PUBLIC) + AUT(*EXCLUDE) 03 GRTOBJAUT OBJ(MYLIB/TESTFILE) OBJTYPE(*FILE) USER(*PUBLIC) + AUT(*USE)

Line 1: Public will have all authority to TABLE1.

Line 2: Public is excluded from TABLE2.

Line 3: Public will have use authority to TESTFILE.

Now I have my tables and file created I can run my SQL statement:

01 SELECT SYSTEM_TABLE_SCHEMA,SYSTEM_TABLE_NAME,GRANTEE, 02 PRIVILEGE_TYPE 03 FROM QSYS2.SYSTABAUTH 04 WHERE SYSTEM_TABLE_SCHEMA = 'MYLIB' 05 ORDER BY SYSTEM_TABLE_SCHEMA,SYSTEM_TABLE_NAME,GRANTEE

Lines 1 and 2: The following columns will be returned: library, object, the profile that has had the privilege given, user profile, and privilege granted.

Line 3: The SYSTABAUTH view is in the library QSYS2.

Line 4: I only want the results for the files, tables, views, etc in my personal library.

Line 5: The sort order the results will be returned in.

I granted public all privileges to TABLE1, and as I created the table my profile has all the privileges too:

SYSTEM_TABLE_SCHEMA SYSTEM_TABLE_NAME GRANTEE PRIVILEGE_TYPE MYLIB TABLE1 PUBLIC SELECT MYLIB TABLE1 PUBLIC INSERT MYLIB TABLE1 PUBLIC UPDATE MYLIB TABLE1 PUBLIC DELETE MYLIB TABLE1 PUBLIC REFERENCES MYLIB TABLE1 PUBLIC ALTER MYLIB TABLE1 PUBLIC INDEX

Note:  I have removed the results for my profile as they will be the same in all of the examples.

I excluded public from TABLE2, therefore no results are returned for public for TABLE2.

SYSTEM_TABLE_SCHEMA SYSTEM_TABLE_NAME GRANTEE PRIVILEGE_TYPE

I gave public TESTFILE use authority.

SYSTEM_TABLE_SCHEMA SYSTEM_TABLE_NAME GRANTEE PRIVILEGE_TYPE MYLIB TESTFILE PUBLIC SELECT MYLIB TESTFILE PUBLIC INSERT MYLIB TESTFILE PUBLIC UPDATE MYLIB TESTFILE PUBLIC DELETE

This is where I started to go off on a tangent. If a group user profile was authorized to an object, which user profiles belong to that group?

Fortunately there is a SQL view that has the information I want: GROUP_PROFILE_ENTIRES. This view contains one row for each user profile that is part of a group. If I wanted to see which groups my profile is a member of:

01 SELECT * 02 FROM QSYS2.GROUP_PROFILE_ENTRIES 03 WHERE USER_PROFILE_NAME = 'SIMON'

Which returns the following result:

GROUP_PROFILE_NAME USER_PROFILE_NAME USER_TEXT GROUP1 SIMON Simon Hutchinson

But I only want to know which active (enabled) user profiles belong to the groups. The GROUP_PROFILE_ENTRIES view does not contain that information. I need to link to the USER_INFO view, and retrieve the STATUS column.

01 SELECT A.*,B.STATUS 02 FROM QSYS2.GROUP_PROFILE_ENTRIES A 03 LEFT OUTER JOIN QSYS2.USER_INFO B 04 ON A.USER_PROFILE_NAME = B.USER_NAME 05 WHERE A.USER_PROFILE_NAME = 'SIMON'

I already know my profile is enabled.

GROUP_PROFILE_NAME USER_PROFILE_NAME USER_TEXT STATUS QPGMR SIMON Simon Hutchinson *ENABLED

From this I can make a statement to return a count of active user profiles belonging to each group profile:

01 SELECT A.GROUP_PROFILE_NAME,COUNT(A.USER_PROFILE_NAME) AS "COUNT" 02 FROM QSYS2.GROUP_PROFILE_ENTRIES A 03 LEFT OUTER JOIN QSYS2.USER_INFO B 04 ON A.USER_PROFILE_NAME = B.USER_NAME 05 WHERE B.STATUS = '*ENABLED' 06 GROUP BY A.GROUP_PROFILE_NAME

Line 1: I am selecting the group profile and the count of the rows, I have to give a column name within the count otherwise the statement will not execute.

Line 2 - 4: I am joining the group profile view with the user profile view using the user profile as the join column.

Line 5: I only want to count the rows where the profile is enabled.

Line 6: By using the GROUP BY the count will be returned for each group profile.

The results would look something like this:

GROUP_PROFILE_NAME COUNT GROUP1 53 GROUP2 1 GROUP3 4

if I wanted to see which active profiles were members of GROUP3 I would need to use the following statement:

01 SELECT A.GROUP_PROFILE_NAME,A.USER_PROFILE_NAME 02 FROM QSYS2.GROUP_PROFILE_ENTRIES A 03 LEFT OUTER JOIN QSYS2.USER_INFO B 04 ON A.USER_PROFILE_NAME = B.USER_NAME 05 WHERE B.STATUS = '*ENABLED' 06 AND A.GROUP_PROFILE_NAME = 'GROUP3'

The results would look something like this:

GROUP_PROFILE_NAME USER_PROFILE_NAME GROUP3 S********* GROUP3 L********* GROUP3 P********* GROUP3 B*********

I am going to make this last SQL statement a lot more complicated. I want to know which user profiles have what privileges to TESTFILE. I want to know what privileges PUBLIC has, which group(s) and user(s) belonging to those groups have what privileges to the file, and what users who are not a member of the group.

My SQL statement looks very big, but it contains many of the things I have mentioned above.

01 SELECT A.SYSTEM_TABLE_SCHEMA AS "Library", 02 A.SYSTEM_TABLE_NAME AS "File", 03 CAST(A.GRANTEE AS CHAR(10)) AS "User profile", 04 A.PRIVILEGE_TYPE AS "Privilege type", 05 CAST(B.USER_PROFILE_NAME AS CHAR(10)) AS "Group user", 06 C.TEXT as "Profile text", 07 C.STATUS as "Profile status" 08 FROM QSYS2.SYSTABAUTH A 09 LEFT OUTER JOIN QSYS2.GROUP_PROFILE_ENTRIES B 10 ON A.GRANTEE = B.GROUP_PROFILE_NAME 11 LEFT OUTER JOIN QSYS2.USER_INFO C 12 ON A.GRANTEE = C.USER_NAME 13 WHERE A.SYSTEM_TABLE_SCHEMA = 'MYLIB' 14 AND A.SYSTEM_TABLE_NAME = 'TESTFILE' 15 AND B.GROUP_PROFILE_NAME IS NULL 16 AND C.STATUS = '*ENABLED' 17 UNION 18 SELECT A.SYSTEM_TABLE_SCHEMA AS "Library", 19 A.SYSTEM_TABLE_NAME AS "File", 20 CAST(A.GRANTEE AS CHAR(10)) AS "User profile", 21 A.PRIVILEGE_TYPE AS "Privilege type", 22 CAST(B.USER_PROFILE_NAME AS CHAR(10)) AS "Group user", 23 C.TEXT as "Profile text", 24 C.STATUS as "Profile status" 25 FROM QSYS2.SYSTABAUTH A 26 LEFT OUTER JOIN QSYS2.GROUP_PROFILE_ENTRIES B 27 ON A.GRANTEE = B.GROUP_PROFILE_NAME 28 LEFT OUTER JOIN QSYS2.USER_INFO C 29 ON B.USER_PROFILE_NAME = C.USER_NAME 30 WHERE A.SYSTEM_TABLE_SCHEMA = 'MYLIB' 31 AND A.SYSTEM_TABLE_NAME = 'TESTFILE' 32 AND C.STATUS = '*ENABLED' 33 AND B.GROUP_PROFILE_NAME IS NOT NULL 34 ORDER BY "User profile"

The above statement is really two smaller statements brought together by a UNION clause. The top part returns the information about all profiles that are not members of groups, this includes public. The bottom part returns the information for the group profile authorizations.

Lines 1 – 7: These are the columns I want returned in my results.

Lines 8 – 12: These are the SQL views that I am joining together: SYSTABAUTH, GROUP_PROFILE_ENTRIES, and USER_INFO. And using the user profile columns to perform the join: GRANTEE, GROUP_PROFILE_NAME, and USER_NAME. Notice that on the join to USER_INFO I have used the GRANTEE column from SYSTABAUTH.

Lines 13 – 16: I am only going to display the results for the one file, TESTFILE in MYLIB. As this first part is for those profiles that are not groups and public the group profile column, GROUP_PROFILE_NAME, must be null. If it is not then this would be a group profile. And I am only concerned with active user profiles, STATUS = enabled.

Line 17: The union clause brings the two SQL smaller statements together.

Lines 18 – 33: This is pretty much the same as the previous section except for:

Line 29: The join uses the user profile from GROUP_PROFILE_ENTRIES as I want the information for the members of that group.

Line 33: The group profile cannot be null, if it is then this is not a group profile and would be included in the results from the previous part of the statement.

Line 34: I want my result sorted by the returned user profile. As this statement contains a union clause I must use the name I gave the column I want to sort by.

The results look like:

Library File User profile Privilege type Group user Profile text Profile status MYLIB TESTFILE GROUP3 SELECT B********* Bianca Hernandez *ENABLED MYLIB TESTFILE GROUP3 INSERT B********* Bianca Hernandez *ENABLED MYLIB TESTFILE GROUP3 UPDATE B********* Bianca Hernandez *ENABLED MYLIB TESTFILE GROUP3 DELETE B********* Bianca Hernandez *ENABLED MYLIB TESTFILE GROUP3 REFERENCES B********* Bianca Hernandez *ENABLED MYLIB TESTFILE GROUP3 ALTER B********* Bianca Hernandez *ENABLED MYLIB TESTFILE GROUP3 INDEX B********* Bianca Hernandez *ENABLED MYLIB TESTFILE PUBLIC SELECT - - - MYLIB TESTFILE PUBLIC INSERT - - - MYLIB TESTFILE PUBLIC UPDATE - - - MYLIB TESTFILE PUBLIC DELETE - - - MYLIB TESTFILE SIMON SELECT - Simon Hutchinson *ENABLED MYLIB TESTFILE SIMON INSERT - Simon Hutchinson *ENABLED MYLIB TESTFILE SIMON UPDATE - Simon Hutchinson *ENABLED MYLIB TESTFILE SIMON DELETE - Simon Hutchinson *ENABLED MYLIB TESTFILE SIMON REFERENCES - Simon Hutchinson *ENABLED MYLIB TESTFILE SIMON ALTER - Simon Hutchinson *ENABLED MYLIB TESTFILE SIMON INDEX - Simon Hutchinson *ENABLED

Note:  I have only included the results from one of the members of GROUP3 as they will all have the same.

 

This is a good example of why play is good. I went from just looking at which profiles are authorized to certain objects, and finished knowing which user profiles are members of the group users that are authorized to tables/files/etc.

You can learn more about this from the IBM website:

• SYSTABAUTH view
• GROUP_PROFILES_ENTRIES view
• USER_INFO view

 

This article was written for IBM i 7.3, and should work for some earlier releases too.