Finally, dynamic SQL is used quite frequently today, and it’s often the case that the dynamic SQL statement within the program should adopt authority just as the program it’s in adopts authority. The problem is that that doesn’t happen by default. Even when the program’s User profile attribute is set to *OWNER, the dynamic SQL doesn’t adopt. To cause it to adopt, you must set the Dynamic user profile attribute to *OWNER when the program is compiled. To discover a program’s Dynamic user profile attribute, run this for OPM programs:
And use this for modules bound into ILE and service programs:
The Dynamic user profile attribute defaults to *USER, meaning that the Dynamic SQL statement doesn’t adopt. If you want the statement to adopt, it used to be that your only opportunity to change it from *USER to *OWNER was when you compiled the program. If you didn’t set it on the compile, you were forced to recompile the program. For many reasons, organizations may not want or be able to recompile. Thanks to the team at IBM Rochester, you now have another option. The QSYS2.SWAP_DYNUSRPRF procedure takes the current Dynamic user profile setting of the program that you pass in and flips it to be the other setting.
For example, if you discovered that DYNSQL200 in PROD_LIB has the Dynamic user profile set to *USER and you need it to be *OWNER, you could run the following and the result would be that the Dynamic user profile attribute would be set to *OWNER.
More information about this procedure can be found here:
https://www.ibm.com/docs/en/i/7.4?topic=services-swap-dynusrprf-procedure
SQL Naming Conventions and Adopted Authority
A nuisance that you may not be aware of (I wasn’t until writing this book!) is the fact that the naming convention used in your static SQL (as opposed to Dynamic SQL) determines whether the program or procedure containing the static SQL adopts. This is true whether it’s created using SQL or a CL command such as Create SQL RPG Program (CRTSQLRPG).
As shown in Figure 8.1, the User profile parameter defaults to *NAMING. A little-known fact is that if the static SQL uses SQL naming (objects are specified using the library.object naming convention), the User profile attribute of the program or procedure will be set to *OWNER, meaning that the program or procedure will adopt! If the static SQL uses the System (*SYS) naming convention, the User profile attribute will be set to *USER, meaning that the program or procedure will not adopt. When running the CRTSQLxxx commands, you can override the User profile parameter, but most people don’t.
Figure 8.1: Whether the program adopts authority depends on the naming convention used by the static SQL contained in the program when using the default of *NAMING for User profile.
I hope this raises the awareness of the importance of having objects owned by the proper profiles. It should also underscore my recommendation that application-owning profiles and developers should not have *ALLOBJ special authority! I also hope that this drives home the importance of monitoring for new programs that adopt authority and that appropriate code-promotion procedures be in place and followed.