How does SQLRPGLE USRPRF(OWNER) work with RPGLE Programs?
If you compile IBM i programs using USRPRF(*OWNER) then they will adopt the security setting for the program owner at runtime. #sometimes
If you are calling an IBM i SQL program, that has been compiled with USRPRF(*OWNER) you may find that it does NOT adopt the security setting. This is because SQL is a special beast that has it’s own compile time settings that override this authority elevation.
Luckily it’s easy to fix — the SQL setting can (a) be set by changing a parameter at Compile time or (b) be set in the code itself by defining how you want this SQL program to be created.
“User profile (USRPRF) – Specifies whether the authority checking done while this program is running should include only the user who is running the program (*USER) or both the user who is running the program and the program owner (*OWNER). The profiles of the program user or both the program user and the program owner are used to control which objects can be used by the program, including the authority the program has for each object. To change the user profile attribute, you or one of your group profiles must either be the owner of the program or have all object (*ALLOBJ) and security administrator (*SECADM) special authorities. The program must be re-created to change the user profile. To be eligible for re-creation, OPM programs must have all observability and ILE programs must have all creation data, and the creation data must be observable. Use the Display Program (DSPPGM) command to determine whether a program is observable or has all creation data.”somebloke
I tend to add the SQL settings to my SQLRPGLE code as standard:
EXEC SQL Set Option Naming = *Sys, Commit = *None, UsrPrf = *Owner, DynUsrPrf = *Owner, CloSqlCsr = *EndMod;
This will allow any SQL program to adopt *OWNER rights if the program is changed to USRPRF(*OWNER) for any reason.
If you add this code to your program I tend to stick it in the *INZSR routine but it doesn’t need executing so it can be tucked away somewhere like a dummy subroutine if that floats your boat.
PRTSQLINF – Useful way to find SQL program settings
SO, I ran this over an SQLRPGLE program in lib(projex4i):
PRTSQLINF OBJ(PROJEX4I/@SQLIT) OBJTYPE(*PGM)
PRTSQLINF Spool file shows the value for the SQL SET
5770SS1 V7R1M0 100423 Print SQL information Program *LIBL/@SQLIT Object name...............*LIBL/@SQLIT Object type...............*PGM CRTSQLRPGI OBJ(QTEMP/@SQLIT) SRCFILE(PROJEXSRC/@COREAPI) SRCMBR(@SQLIT) COMMIT(*NONE) OPTION(*SYS *NOEXTIND *PERIOD) TGTRLS(V7R1M0) ALWCPYDTA(*OPTIMIZE) CLOSQLCSR(*ENDACTGRP) RDB(*LOCAL) DATFMT(*YMD) DATSEP('/') TIMFMT(*HMS) TIMSEP(':') DFTRDBCOL(*NONE) DYNDFTCOL(*NO) SQLPKG(PROJEX4I/@SQLIT) MONITOR(*USER) SQLCURRULE(*DB2) ALWBLK(*ALLREAD) DLYPRP(*NO) DYNUSRPRF(*USER) USRPRF(*NAMING) SRTSEQ(*HEX) LANGID(ENU) RDBCNNMTH(*DUW) TEXT('*API: Run SQL Command - using PARM |165') SQLPATH(*LIBL) DECRESULT(31 31 0) DECFLTRND(*HALFEVEN) CONACC(*DFT) STATEMENT TEXT CCSID(37) EXECUTE IMMEDIATE : H
But remember compiling a program as USRPRF(*OWNER) is not the same as the USRPRF value in PRTSQLINF.
The User Profile (USRPRF) is a Program (PGM) attribute rather than of the SQL; the Print SQL Information (PRTSQLINF) will not even show the USRPRF attribute, showing only the Dynamic User Profile (DYNUSRPRF) attribute, such that the Display Program (DSPPGM) must be used instead. Use the Change Object Owner (CHGOBJOWN) and\or the Change Program (CHGPGM) to effect any desired modifications to the USRPRF option.
The SQL OPTION for USRPRF and DYNUSR can only be set by recompilation. AFAIK anwyay.
REF: SQL Adopted Authority – http://www.ibmsystemsmag.com/ibmi/administrator/security/Fortifying-Security-With-SQL/?page=3