December 14

1 comments

Adopting Authority – Problems with SQL RPG Programs

By NickLitten

December 14, 2016

SQLRPGLE

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.

  • {"email":"Email address invalid","url":"Website address invalid","required":"Required field missing"}

    Join the IBM i Community for FREE Presentations, Lessons, Hints and Tips

    >