Adopting Authority – Problems with SQL RPG Programs

RPG

Dec 14

RPG PROGRAM USER(*OWNER) – Adopts the object owner level of Authority

Most IBM i Programmers are aware of the USER(*OWNER) compile setting — anyone calling this program will adopt the authority level of the program. So if a program is compiled by user QSECOFR and USER(*OWNER) then anyone calling that program will automatically be elevated to QSECOFR level rights for the duration of that program execution.

This is a simple trick that’s sometimes used to create utilities that need specific authority settings.

It’s also used to allow users to adopt an authority setting for access to a specific application (without needing to grant authority levels to all the users in question).

Adopting USER(*OWNER) rights lets a user access a database that he/she might usually not be authorised to…. but only when calling that program. You may be able to see that if a user initial program is *OWNER, this can be used to automatically grants specific users rights to an application and its database when they wouldnt normally have that access just from a command line.

So PGMA (compiled at QSECOFR *OWNER) calls PGMb which calls PGMc which calls PGMd - and the security officer authority elevation set at the initial program is carried all the way through that stack. So each program is running with elevated rights.

This method works for older RPG program, modern RPGLE, C, CL and all other native IBM i languages. I’m going to refer to this as native-io access. This means that programs that are using IBM i native file access (read, chain, setll, write, update, etc) will be allowed to access a database that they are authorised because of the *OWNER elevation.

SQLRPG – SQL is a special beast

SQL Programs access the database using SQL methodology rather than Native IO. That sounds fairly obvious.

But SQL Programs ignore any existing authority elevation using *OWNER – now that doesnt sound very obvious. Lets look at the same example:

So PGMA (compiled at QSECOFR *OWNER) calls PGMb which calls PGMc which calls SQLPGMd - and the security officer authority elevation set at the initial program is carried all the way through that stack. So each program should be running with elevated rights... should be... but when the SQLPGMd runs it finds itself demoted to its standard level and only authorized to process files using SQL at its standard authority level

This is because SQL RPG Programs (type SQLRPGLE) have their own User Authority rules and this is set at compile time. When you compile an SQLRPGLE program you can specific compile parameter UsrPrf which can be *NAMING, *USER or *OWNER:

User profile (USRPRF) – Specifies the user profile that is used when the compiled program object and SQL package object is run, including the authority that the program object or SQL package has for each object in static SQL statements. The profile of either the owner or the user is used to control access to objects.

*NAMING – The user profile is determined by the naming convention. If the naming convention is *SQL, USRPRF(*OWNER) is used. If the naming convention is *SYS, USRPRF(*USER) is used.

*USER – The profile of the user running the program or SQL package is used.

*OWNER – The user profiles of both the owner and the user are used when the program or SQL package is run.

Set USER Defaults at Compile Time

? CRTSQLRPGI ??OBJ(library/sqlprogram) 
 ?*SRCFILE(library/QRPGLESRC) 
 ?*SRCMBR(sqlprogram) 
 ?*OBJTYPE(*PGM) 
 ??REPLACE(*NO) 
 USRPRF(*OWNER) 
 
compiler command for sql rpg program

Define in program code

This is my preferred solution.

Add this to your program code to define the settings internally and not worry about changing it at compile time:

EXEC SQL
 Set Option
 Naming = *Sys,
 Commit = *None,
 UsrPrf = *Owner,
 DynUsrPrf = *Owner,
 CloSqlCsr = *EndMod;

Add this to a common /COPYBOOK and simply do an /INCLUDE in each of your SQLRPGLE programs.

Follow

About the Author

IBM i Software Developer, Digital Dad, AS400 Anarchist, RPG Modernizer, Alpha Nerd and Passionate Eater of Cheese and Biscuits. Nick Litten Dot Com is a mixture of blog posts that can be sometimes serious, frequently playful and probably down-right pointless all in the space of a day. Enjoy your stay, feel free to comment and in the words of the most interesting man in the world: Stay thirsty my friend.