Checking authority to a file using SQL

It was another one of those examples of a find when I was looking for something else. This is a SQL scalar function, SQL_CHECK_AUTHORITY, that returns a single value to inform me if I am authorized to use a file or not.

What is a scalar function? A scalar function is passed one or more parameters and returns a single value.

The syntax is very simple:

QSYS2.SQL_CHECK_AUTHORITY(library,file)

It returns one of the following values:

• 0 = Not authorized to file
• 1 = Authorized to file

What kinds of files can this check? There are many types of files, the ones that came to my mind to try were:

1. Physical files
2. Logical files
3. Display files
4. Printer files
5. DDM files
6. DDL (SQL) tables
7. DDL (SQL) indexes
8. DDL (SQL) views

I created one of each of these files in my library, MYLIB, and changed the authority that only I was authorized to them and everyone else was excluded. I can check the objects authority using the OBJECT_PRIVILEGES SQL View.

SELECT OBJECT_NAME AS "File", SQL_OBJECT_TYPE AS "SQL type", AUTHORIZATION_NAME AS "User", OBJECT_AUTHORITY AS "Authority" FROM QSYS2.OBJECT_PRIVILEGES WHERE OBJECT_SCHEMA = 'MYLIB' AND SYSTEM_OBJECT_NAME LIKE 'TEST%' AND OBJECT_TYPE = '*FILE'

The results show two lines for each object, one for each user profile that is authorized to the object.

File SQL type User Authority --------- -------- ------- --------- TESTDDMF - *PUBLIC *EXCLUDE TESTDDMF - SIMON *ALL TESTDSPF - *PUBLIC *EXCLUDE TESTDSPF - SIMON *ALL TESTINDEX INDEX *PUBLIC *EXCLUDE TESTINDEX INDEX SIMON *ALL TESTLF - *PUBLIC *EXCLUDE TESTLF - SIMON *ALL TESTPF - *PUBLIC *EXCLUDE TESTPF - SIMON *ALL TESTPRTF - *PUBLIC *EXCLUDE TESTPRTF - SIMON *ALL TESTTABLE TABLE *PUBLIC *EXCLUDE TESTTABLE TABLE SIMON *ALL TESTVIEW VIEW *PUBLIC *EXCLUDE TESTVIEW VIEW SIMON *ALL

If I use my favorite SQL client I can check whether my user profile, SIMON, is authorized to one of these files:

VALUES QSYS2.SQL_CHECK_AUTHORITY('MYLIB','TESTPF')

The returned result says I am:

0001 ---- 1

When I signon with another user profile, NOTSIMON, and execute the same statement as before a result of 0 (zero) is returned which indicates that I am not authorized to the file.

If I wanted to test for all of the file types I mentioned above I could do it in one statement:

SELECT QSYS2.SQL_CHECK_AUTHORITY('MYLIB','TESTPF') AS "PF", QSYS2.SQL_CHECK_AUTHORITY('MYLIB','TESTLF') AS "LF", QSYS2.SQL_CHECK_AUTHORITY('MYLIB','TESTTABLE') AS "TABLE", QSYS2.SQL_CHECK_AUTHORITY('MYLIB','TESTVIEW') AS "VIEW", QSYS2.SQL_CHECK_AUTHORITY('MYLIB','TESTINDEX') AS "INDEX", QSYS2.SQL_CHECK_AUTHORITY('MYLIB','TESTDDMF') AS "DDMF", QSYS2.SQL_CHECK_AUTHORITY('MYLIB','TESTDSPF') AS "DSPF", QSYS2.SQL_CHECK_AUTHORITY('MYLIB','TESTPRTF') AS "PRTF" FROM SYSIBM.SYSDUMMY1

If I am signed on as SIMON my results look like:

PF LF TABLE VIEW INDEX DDMF DSPF PRTF -- -- ----- ---- ----- ---- ---- ---- 1 1 1 1 1 1 1 1

If any other profile is used the results are all zeroes.

When I use this Scalar Function I do have to give the library name, not '*LIBL', blank, or null, if I use those I am returned a zero.

This is all fine and dandy but how about using it in a more real world, useful scenario. In the following example I have a procedure, in a module, which I can bind to a program to check if I am authorized to a file.

The is the source code for my procedure:

01 **free 02 ctl-opt nomain option(*srcstmt) ; 03 /copy devsrc,copybook 04 dcl-proc CheckFileAuthority export ; 05 dcl-pi *n packed(3) ; 06 File char(10) const ; 07 Lib char(10) const ; 08 end-pi ; 09 dcl-s wkStatus packed(3) ; 10 exec sql SET :wkStatus = QSYS2.SQL_CHECK_AUTHORITY(:Lib,:File) ; 11 if (wkStatus = 1) ; //Authorized to file 12 return 0 ; 13 else ; 14 return -1 ; 15 endif ; 16 end-proc ;

Line 1: Totally free RPG is now four years old.

Line 2: NOMAIN is used in the control options to indicate that there is no main procedure that can be called from a command line. I always use the OPTION(*SRCSTMT) so that the line numbers of the source are used as the objects line numbers when this module is compiled.

Line 3: I have placed the procedure prototype definition in an external source member, COPYBOOK, that will be copied/included into this source member when this module is compiled. I will show the contents of this source member below.

Line 4: This is the start of the procedure. As this procedure is called from a program outside of this module I must use the EXPORT keyword.

Lines 5 – 8: This is the procedure interface, which defines what is passed to the procedure and what is returned from it. Using the name in the name of the procedure in the procedure is optional, therefore, I always use *N rather than the procedures name. On the same line of code I have defined PACKED(3) which means that a 3 long packed value is returned from this procedure. The two variables passed to this procedure is File, line 6, and Lib, line 7. Both are defined with the CONST keyword that means that the value in these variables is constant, which allows me to pass strings to the procedure, rather than having to move those strings to variables which is used in the call to the procedure.

Line 9: Here I am defining the variable that will contain the result from the SQL statement.

Line 10: I am using the SQL Set to return the value from the SQL statement into the wkStatus variable.

Line 11 – 15: If something in a procedure fails or is unsuccessful I always like to return the value of -1 to denote an "error", or if everything was successful then I return a zero.

Now to the contents of the copybook:

01 /if defined(CheckFileAuthority) 02 dcl-pr CheckFileAuthority packed(3) ; 03 *n char(10) const ; //File name 04 *n char(10) const ; //Library 05 end-pr ; 06 /endif

Line 1: The /IF DEFINED allows me to only copy part of the contents of the source from this member into other source members.

Lines 2 – 5: This is the prototype definition for my procedure.

Line 6: If I have a /IF I also need a /ENDIF to indicate where the end of section of code that is defined ends.

To compile a SQLRPGLE source member I use the same command I would for creating a program, CRTSQLRPGI, but I have to change the Object Type parameter to: OBJTYPE(*MODULE).

When the module has been created I add it to the binding directory TEST.

And now to the program that calls the procedure to determine if I am authorized to a file.

01 **free 02 ctl-opt option(*srcstmt) dftactgrp(*no) bnddir('TEST') ; 03 /define CheckFileAuthority 04 /include devsrc,copybook 05 dcl-s wkStatus packed(3) ; 06 wkStatus = CheckFileAuthority('TESTPF':'MYLIB') ; 07 dsply ('MYLIB/TESTPF = ' + %char(wkStatus)) ; 08 wkStatus = CheckFileAuthority('NOT_EXIST':'NOT_LIB') ; 09 dsply ('NOT_LIB/NOT_EXIST = ' + %char(wkStatus)) ;

Line 2: My control options now include DFTACTGRP(*NO) which I need as this program calls a procedure. And I have added the option to define the binding directory that will be used when I create this program, which includes the module I created above.

Line 3: I need the /DEFINE so that only the parts of the copybook source member I want copied are included in this source member.

Line 4: Include/copy the relevant code from the copybook source member here. This is the procedure prototype definition.

Line 5: In this program wkStatus contains the value returned from the procedure.

Line 6: I am calling the procedure with the name of a file I authorized to, and the library it is in. The returned value from the procedure is placed in wkStatus.

Line 7: I am using the DSPLY operation code to display what is returned.

Line 8: This time the procedure is called with a file, and library, that does not exist.

line 9: The value returned from the procedure is displayed.

When I run this program I see the following:

DSPLY MYLIB/TESTPF = 0 DSPLY NOT_LIB/NOT_EXIST = -1

As I would expect the first time the procedure is called zero is returned as I am authorized to TESTPF in MYLIB. As the second call passes the name of a file and library that does not exist I get -1 returned.

 

You can learn more about the SQL_CHECK_AUTHORITY scalar function from the IBM website here.

 

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