Wednesday, July 10, 2019

Which users are authorized to these files and tables?

systabaut, system table authority, sql view

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:

 

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

No comments:

Post a Comment

To prevent "comment spam" all comments are moderated.
Learn about this website's comments policy here.

Some people have reported that they cannot post a comment using certain computers and browsers. If this is you feel free to use the Contact Form to send me the comment and I will post it for you, please include the title of the post so I know which one to post the comment to.