Use these external SQL routines as an alternative to the somewhat restrictive SET SESSION AUTHORIZATION statement.

In "Impersonate Your Neighbor Using DB2 for i," I outlined how to use the SET SESSION AUTHORIZATION SQL statement to override the user profile of the current thread or, in many cases, the current job. The benefits of doing this include providing temporary authorization to resources such as IFS files and impersonating another user so that user-dependent code will be tricked into thinking that a different user is running it. However, there are a few restrictions on this statement that can limit its usefulness. In this tip, I'll introduce some external SQL routines (written in RPG) that allow an application to override the user profile without some of the restrictions of the built-in DB2 functionality.

In particular, a few potential restrictions I found using SET SESSION AUTHORIZATION are these:

• This statement cannot be executed within an SQL function or procedure (although it can be embedded in a High-Level Language (HLL) program provided that the program is invoked with an IBM i OS call).
• The user profile handle cannot be changed without DB2 closing all open resources (such as LOB locators, open transactions, open cursors, etc.).
• *ALLOBJ authority is required to use SET SESSION AUTHORIZATION. Again, the one exception I found to this is a HLL program that uses adopted authority and is invoked by an IBM i OS CALL or SBMJOB command. If you're in a client/server environment, then as of IBM i 7.2, I have found no way of escaping this requirement.

The RPG service program USRPRFR can be downloaded here. The instructions for how to create the service program and create the external routine definitions (CREATE PROCEDURE, CREATE FUNCTION, etc.) are in the source's header comments.

The RPG code contains four external routines (one scalar function and three procedures):

• Function GetProfileHandle—Query the current user profile handle of the job. This scalar function returns a BINARY(12) value. The value returned from this function is meant to be used in conjunction with the RestoreProfileHandle procedure.
• Procedure ChangeCurrentUser—Change the current user profile of the job (similar to SET SESSION AUTHORIZATION). This procedure accepts two parameters: user name and password. An error will be thrown if the requested user or password is inaccessible or invalid.
• Procedure RestoreProfileHandle—After ChangeCurrentUser is called, this procedure can revert the current user profile of the job back to the original user without specifying a password. This procedure accepts a BINARY(12) user profile handle (retrieved from the GetProfileHandle scalar function) that was saved in a local or global variable. An error is thrown if the original user profile handle can't be restored.
• Procedure WriteIFSTest—This dummy procedure is used to create IFS file /tmp/test.txt. It can be used to test whether or not the overridden profile is the owner of the newly created IFS file (see example below)..

The RPG code was developed on IBM i 7.1, but it should be able to run on IBM i V5R4 or higher. The RPG program makes use of special security APIs called Get Profile Handle (QSYGETPH) and Set Profile Handle (QWTSETP). See the reference at the end of this tip for more information on these APIs and how to use them in an RPG program.

Sample Usage

The following dynamic compound statement (requires IBM i 7.1Group PTF Level 26) illustrates how the aforementioned user profile routines work together:

-- Start the dynamic compound statement using

-- user profile NOBLE

BEGIN

   DECLARE @SQL VARCHAR(1024)

       DEFAULT 'CREATE VARIABLE DATA.PROFILE_HANDLE BINARY(12)';

   -- Drop global variable if it exists

   IF EXISTS (

       SELECT *

         FROM QSYS2.SYSVARIABLES

        WHERE VARIABLE_SCHEMA='DATA'

          AND VARIABLE_NAME='PROFILE_HANDLE') THEN

       DROP VARIABLE DATA.PROFILE_HANDLE;

   END IF;

   -- Create global variable to hold profile handle

   -- Is this a DB2 bug with dynamic compound statements?

   -- Use dynamic SQL instead of a static statement  :(

   EXECUTE IMMEDIATE @SQL;

   -- Make sure that the variable can be read by others

   GRANT READ ON VARIABLE DATA.PROFILE_HANDLE TO PUBLIC;

   INSERT INTO QTEMP.RESULTS VALUES('Original Profile',SYSTEM_USER,USER);

   -- Save the handle of the existing profile in a global variable

   SET DATA.PROFILE_HANDLE=DATA.GetProfileHandle();

   -- Change the job's current user to SERF

   CALL DATA.ChangeCurrentUser('SERF','serf');

   INSERT INTO QTEMP.RESULTS VALUES('New Profile',SYSTEM_USER,USER);

   -- Create a test file on the IFS (should be owned by SERF)

   CALL DATA.WriteIFSTest;

   CREATE TABLE DATA.TEST1 (C1 CHAR(1));  -- Owned by SERF

   -- Restore the user profile NOBLE by retrieving the saved handle

   CALL DATA.RestoreProfileHandle(DATA.PROFILE_HANDLE);

   INSERT INTO QTEMP.RESULTS VALUES('Back to Original',SYSTEM_USER,USER);

   CREATE TABLE DATA.TEST2 (C1 CHAR(1));  -- Owned by NOBLE

END;

The steps in the process are fairly easy to discern:

1. The current user profile handle for user NOBLE is fetched with function GetProfileHandle()and saved into a global variable.
2. The job impersonates user profile "SERF" via the ChangeCurrentUser procedure. Unfortunately, the profile's password is required. Though not shown here, it's always a good idea to encrypt a password using one of the DB2 encryption functions before storing it in a table, data area, or global variable.
3. Two operations are done (create IFS file and CREATE TABLE) to demonstrate that SERF is considered the current user and owns the new objects. To verify, review the object owner after the code finishes.
4. Procedure RestoreProfileHandle is used to put the user profile back to the original profile without using a password. Don't forget; the original user's handle was retrieved with GetProfileHandle().
5. Finally, another table TEST2 is created to demonstrate that the original user (NOBLE) owns the newly created table instead of the impersonated user SERF.
6. Throughout the procedure, a dummy table with three columns called QTEMP.RESULTS is populated to show what the USER and SYSTEM_USER special registers contain before the profile handle is changed, after the profile handle is changed, and after the original profile handle has been restored.

One other thing is that DB2 will now allow a transaction to run with the user impersonation. In contrast, when using SET SESSION AUTHORIZATION, all open transactions are discarded.

-- Start the dynamic compound statement using

-- user profile NOBLE  (assumes PROFILE_HANDLE global variable exists

-- and that tables MYTABLE and YOURTABLE are journaled)

SET TRANSACTION ISOLATION LEVEL READ COMMITTED;

INSERT INTO MYDATA.MYTABLE VALUES(1,'Original User',USER);

-- Save the handle of the existing profile in a global variable

SET DATA.PROFILE_HANDLE=DATA.GetProfileHandle();

-- Change the job's current user to SERF

CALL DATA.ChangeCurrentUser('SERF','serf');

INSERT INTO MYDATA.YOURTABLE VALUES(1,'Override User',USER);

-- Restore the user profile NOBLE by retrieving the saved handle

CALL DATA.RestoreProfileHandle(DATA.PROFILE_HANDLE);

COMMIT;

-- Verify data committed successfully and that the

-- user profile register was saved as expected

SELECT * FROM MYDATA.MYTABLE

UNION ALL

SELECT * FROM MYDATA.YOURTABLE;

Restrictions and "Funny Behavior"

• In a series of nested procedure or function calls, DB2 may not recognize the profile change.
• The SYSTEM_USER special register does not change after calling the ChangeCurrentUser function, but the USER, SESSION_USER,and CURRENT_USER special registers do. By comparing these registers, it's possible to see the "overridden" user profile and the original connected user profile simultaneously. This behavior is consistent with SET SESSION AUTHORIZATION. However, if your code relies on SYSTEM_USER instead of USER, then the impersonation may not affect the code execution as desired. Note: If you want your application code to avoid the effects of impersonation, use SYSTEM_USER because it will not change (although in a client/server environment, SYSTEM_USER will most likely contain QUSER).
• While testing a user profile change in a dynamic compound statement, I found when an error occurs, DB2 will return a strange error message and then quit. For example, when trying to create a table that already exists, you'd expect error SQL0601 (*FILE already exists). However, once the profile has been overridden, DB2 for i returns an SQL0952 error (SQLSTATE 57014, "Processing of the SQL statement ended.") At this point, you have to go back and look for the true error. Also, unless error handling is implemented appropriately, your current SQL session will still be under the control of the overridden profile instead of the original.
• I imagine changing the profile on the fly has the potential to screw up DB2 royally. There is probably a good reason why IBM releases all resources before allowing a profile switch. So make sure to test this code thoroughly (including potential for trapping various errors and continued but unwanted authorization to various resources) before using it in a production environment.

• When it comes to security checking by DB2, I found that when changing the user profile midstream in code and then calling a procedure, DB2 didn't correctly check the permission on the procedure. In my testing, I started a dynamic compound statement (DCS) with a profile with *ALLOBJ special authority, within the DCS switched to a user with no special authority, and then called a procedure. I was still able to run the procedure even though the current user technically wasn't authorized.  So it appears as though DB2 ignores or is oblivious to the user switch when checking some authorizations on objects.

These DB2 for i external routines used to override the user profile can allow your code to impersonate another user. This is useful for applications such as workflows that follow rules based on user profiles. The routines also provide an alternative to the DB2 SET SESSION AUTHORIZATION but with the following benefits:

• A user isn't required to have *ALLOBJ special authority.
• The profile change request can be placed within a dynamic compound statement, SQL or HLL-embedded SQL code, and can also be used in a client/server environment.
• A transaction can remain open across a profile context change.

The one drawback compared to SET SESSION AUTHORIZAITON is that the application has to supply the password to impersonate the desired user profile.

References

TechTip: Change the User Profile for the Currently Running Job