Learn how to override the current user profile handle with a different user profile in DB2.

For most database-related applications, using adopted authority is the preferred way to temporarily grant users controlled access to sensitive data. But one limitation of the adopted authority model is that it doesn't override IFS permissions. A second limitation is that the adopted authority model doesn't allow an application to "impersonate" another user. To help with these issues, the DB2 for i statement SET SESSION AUTHORIZATION provides a way to overcome both of these limitations.

What Does SET SESSION AUTHORIZATION Do?

This SQL statement is used to change the user profile of the current thread (or job) to a specified user. For instance, if you sign on to the green-screen using user profile UserA, running this statement in STRSQL will change the job to run under the authority of UserB:

SET SESSION AUTHORIZATION = UserB;

-- Code here is now running under UserB

VALUES(SESSION_USER,USER,CURRENT_USER,SYSTEM_USER);

-- All values will be UserB except for

-- SYSTEM_USER which will remain UserA

The SESSION_USER, USER, and CURRENT_USER special registers are overridden to hold the value of the specified user UserB. In essence, the job is now running as though UserB logged in. This is similar in concept to the Linux/UNIX su (super user/substitute user) command that allows impersonation.

Reverting to the Original Profile

To give control of the job back to UserA (the original profile), issue the following statement:

SET SESSION AUTHORIZATION SYSTEM_USER;

While SET SESSION AUTHORIZATION overrides the values of the USER and SESSION_USER special registers, the SYSTEM_USER special register always maintains the value of the original user (in this case, UserA).

What Is Adopted Authority?

Recall that adopted authority is used by setting the USRPRF or DYNUSRPRF setting of a High-Level Language (HLL) program or a DB2 routine to *OWNER. When a user runs a program created with *OWNER, it runs with the authority of the profile that owns the program object instead of the current user's authority.

This is useful because users can be authorized to access data via application code without being authorized to directly access the underlying database tables and views. But as already mentioned, adopted authority has two limitations that SET SESSION AUTHORIZATION can solve.

Adopted Authority Limitation #1: Accessing IFS Files

Adopted authority overrides the current user's authorization to /QSYS.LIB objects by using the authority of the program owner. Object authorization to other IFS areas is not overridden by adopted authority.

To illustrate, say application profile NOBLE has sole authority to IFS folder MyApp and its files. RPG program MYAPPIFS (written as an external stored procedure) has been created to maintain the content of these IFS files. The RPG program was created with USRPRF=*OWNER. Application user SERF runs the program and finds that he can't access the IFS files even though program MYAPPIFS is compiled with the USRPRF *OWNER option. Now what? Does an admin need to explicitly authorize user SERF (or a group profile) to these IFS objects? Not necessarily.

One way to avoid managing individual authorizations is to change the job's user profile to "impersonate" another user. The following two statements will do this:

-- Script starts running as user SERF

SET SESSION AUTHORIZATION = NOBLE; -- Change user to NOBLE

CALL MYAPPIFS (parameters);

Now, because DB2 has changed the user profile associated with the current job from SERF to NOBLE, procedure MYAPPIFS runs as user NOBLE and can access the IFS files.

Adopted Authority Limitation #2: Run Application Code When Impersonating Another User

Say you're trying to integrate a manufacturing application and a workflow application on the IBM i. Before a manufacturing order can be released to production, the workflow application's job is to route some tasks to purchasing users for review and approval. Moreover, both apps have their own sets of user profiles.

To complete the integration, the manufacturing application has to write some data to the workflow app's tables and then call a workflow program to process the request. Workflow applications are dependent on business rules assigned to specific user or group profiles, so running workflow code under an unrecognized profile will fail. While using adopted authority can grant the manufacturing user access to the workflow data, in this case it's not enough. The job's user profile needs to change because the workflow program code relies on the current user (obtained by RPG program status data structure or DB2 special registers) to determine logic flow:

-- Script starts running as user MFGSYSTEM

… DO STUFF HERE…

-- Override

SET SESSION AUTHORIZATION = PURCHASING;

INSERT INTO WORKFLOW.REQUESTS

VALUES(1,100,'Mfg Order waiting for purchasing approval','N',

      USER,CURRENT_TIMESTAMP);

CALL WORKFLOW.ENGINE (parameters);

-- Revert user profile to original profile MFGSYSTEM

SET SESSION AUTHORIZATION SYSTEM_USER;

In this case, when the WORKFLOW.ENGINE program is called, it will run under the context of user PURCHASING, a user profile recognized by the workflow application.

Notes on SET SESSION AUTHORIZATION

• When this statement is executed, DB2 will release any resources it has (such as open cursors, LOB locators, procedure results, open files, open transactions, etc.).
  • On the IBM i, only users with *ALLOBJ authority can execute SET SESSION AUTHORIZATION. That certainly limits who can use it. And since this statement cannot be embedded in a procedure, adopted authority can't help here either—double bummer! (See below for one exception to this rule.)In other versions of DB2, there is a SETSESSIONUSER privilege that can be granted to users to allow them to run this statement. I'd prefer it if IBM would allow a similar kind of privilege in DB2 for i.
• This statement's use is fairly restricted. It cannot be placed in a stored procedure, function, or trigger. It will not run with the IBM i OS Run SQL Statements (RUNSQLSTM) or Run SQL (RUNSQL) commands. In other words, it has to be issued at the beginning of a unit of work in whatever SQL environment you're using. This restriction makes it difficult to use in the typical green-screen environment of SQL and HLL code.
  • It can be placed in a HLL program and run only if the program is invoked from the IBM i OS CALL or SBMJOB commands. A SET SESSION AUTHORIZATION statement in a HLL program will fail if the program is invoked with an SQL CALL statement. In this case, error SQL0428 "SQL statement cannot be run" will be issued. HLL adopted authority can be used in this specific instance to allow users to issue a SET SESSION AUTHORIZATION without having *ALLOBJ special authority. If your impersonation code can be crammed into a single HLL program that isn't invoked with an SQL CALL, then you've avoided the primary restriction.
  • The above SQL samples can be run in dynamic SQL, a client/server environment or can be embedded in a HLL program provided that the program is called from IBM i OS.
  • This statement can be issued from a client/server environment (JDBC,ODBC,.NET provider, or OLE DB). But if you need to revert back to the original user, the only way I found to reliably get back to the original user is to close the connection and reconnect with the original connection string. This is because, for client/server database connections, the SYSTEM_USER register holds the value of QUSER (the default user profile for database host server jobs) instead of the user that connected to the database server job. If needed, the current connection can be reverted to the original user by calling SET SESSION AUTHORIZATION statement again. However, the current user needs *ALLOBJ authority and the application has to store the original user name
• SET SESSION_USER is a syntactic equivalent alternative to SET SESSION AUTHORIZATION.

When all is said and done, SET SESSION AUTHORIZATION provides an easy way to impersonate another user profile either temporarily or for the duration of the session. Once the profile override is in effect, access to IFS files and DB2 user special registers (except SYSTEM_USER) are all set to the requested profile. In a future tip, I'll demonstrate another impersonation technique that doesn't have as many restrictions as SET SESSION AUTHORIZATION.

Also Read

TechTip: Fine-Tuning Db2 Symmetric Multiprocessing (SMP) to Improve Performance for Long-Running Queries

Celebrating 40 Years of Db2: Running the World’s Mission Critical Workloads

TechTip: Data Source Base Project, Part 3 - SSIS Code Structure