Resolving The Problem

Some applications are coded so that they run as a user profile that has no password. By designing the software this way, the software does not have the exposure of a common user profile and password that every user of that software would have access to. Even if someone knows that you are using software package ABC and that this software will always make connections and run as user XYZ, I cannot exploit that because XYZ has no password. I cannot sign on as that user or connect to the system as that user using any common interface.

Changes were made to the operating system starting with PTFs in V5R3 and extending through V6R1 which will cause programs like the one described above to fail if they tried to make an SQL connection in a CLI program using a user ID that has a password of *NONE. These PTFs and the data area that can be used to extend the old behavior through V6R1 are documented in Rochester Support Center knowledgebase document New, CPF9898 Authorization Error with Native JDBC or CLI: .

Because the old behavior will only be supported through V6R1 and, even then, only if the data area is created to allow the old behavior, a new way to get the same results is needed. Programmers need a way to be able to make the SQLConnect function work with a specific user that does not have a password (referred to in the remainder of this document as the default user). One way to do this is to make a fairly simple modification to existing SQL CLI programs so that the job is swapped from the current user (the user that signed on to the system) to the default user. The default user can then make the SQL connection, and then the job is swapped back immediately back to the current user. The rationale for immediately swapping the current user of the job back to the current user is to prevent the user from getting back to a command line with the authority of the default user. If the SQL CLI program were to return or abnormally end while swapped to the default user, the interactive session that the program was called from will now be running as the default user. This is something that we do not want to have happen. The result would likely be that the user will have more authority to some data than they would normally have. Note that if there are any spooled files created in the job, they will be owned by whichever profile the job is running under when the file is opened.

The following APIs are used to retrieve, set, and release the user profile handles so that the job can be switched from the current user, to default user, and back: QSYGETPH (get profile handle), QWTSETP or QsySetToProfileHandle (to set the job to use a profile handle), and QSYRLSPH (to release the profile handle).

The following C example demonstrates the use of these APIs in an SQL CLI program:

/***************************************************************************/
/* CLICONNECT 2008/12/30 */
/* */
/* Test the SQLConnect using *NONE for a password. This program uses */
/* a profile that was created with *NONE for a password. */
/* */
/***************************************************************************/

#include <stdio.h>
#include <string.h>
#include "sqlcli.h"
#include <stdlib.h>
#include <qsyphandle.h>
#include <qsygetph.h>

#define MAXSTATEMENTLEN 32000

int print_err (SQLHDBC hdbc,
SQLHSTMT hstmt);

void strset (char *str,
char c,
int size);

main ()
{
SQLHENV henv;
SQLHDBC hdbc;
SQLHSTMT hstmt;
SQLCHAR server[] = "*LOCAL";
SQLINTEGER id;
SQLINTEGER namelen;
long attr;
SQLRETURN rc;
SQLCHAR callStmtStr[MAXSTATEMENTLEN];
char uid[10] = "JDBCTEST";
char pwd[10] = "*NOPWD";
char hProfile[12];
char hCurrentProfile[12];
char errorCode[1024];
FILE *prtfile1;
FILE *prtfile2;
FILE *prtfile3;

attr = SQL_TRUE;

// if you open the print file before the swap the profile, it will
// be owned by the current user
prtfile1 = fopen("*LIBL/QPRINT", "wb");
fprintf(prtfile1, "This is prtfile1\n\nStarting program\n");

// first create a profile handle for JDBCTEST and the current user
// because we have to switch back to it or we will be JDBCTEST when
// the job ends.
QSYGETPH(uid, pwd, hProfile);
QSYGETPH("*CURRENT", "*NOPWD", hCurrentProfile);
// switch the job to run under that profile
QsySetToProfileHandle(hProfile, errorCode);

rc = SQLAllocEnv (&henv);
rc = SQLSetEnvAttr(henv, SQL_ATTR_SERVER_MODE, &attr, 0);
rc = SQLAllocConnect (henv, &hdbc);

// connect as the current user, which is now JDBCTest
if (SQLConnect (hdbc, server, SQL_NTS, NULL, SQL_NTS, NULL, SQL_NTS)
!= SQL_SUCCESS)
return (print_err (hdbc, SQL_NULL_HSTMT));

// Open a print file now and it will be owned by JDBCTEST
prtfile2 = fopen("*LIBL/QPRINT", "wb");
fprintf(prtfile1, "Just connected, job running as JDBCTEST.\n");
fprintf(prtfile2, "This is prtfile2\n\n");
fprintf(prtfile2, "Just connected, job running as JDBCTEST.\n");

// We can swap back to the original user right after the connection so
// regardless of any failure condition after the connection, when the cli
// part of the job is done, you will be "yourself" again.
QsySetToProfileHandle(hCurrentProfile, errorCode);

// Release the profile handle for JDBCTEST and the current user
QSYRLSPH(hProfile);
QSYRLSPH(hCurrentProfile);

rc = SQLAllocStmt (hdbc, &hstmt);
sprintf(callStmtStr, "SELECT DISTINCT USER FROM QIWS.QCUSTCDT");
rc = SQLExecDirect (hstmt, callStmtStr, SQL_NTS);
if (rc != 0)
print_err(hdbc, hstmt);
else
{
SQLBindCol(hstmt, 1, SQL_CHAR, (SQLPOINTER) &uid[0], 11, &namelen);
SQLFetch(hstmt);
printf("The user id is: %s\n", uid);
prtfile3 = fopen("*LIBL/QPRINT", "wb");
fprintf(prtfile1, "The user id is: %s\n", uid);
fprintf(prtfile2, "The user id is: %s\n", uid);
fprintf(prtfile3, "This is prtfile3\n\n");
fprintf(prtfile3, "The user id is: %s\n", uid);
fclose(prtfile3);
}
SQLFreeStmt (hstmt, SQL_DROP); /* free the statement handle */
SQLDisconnect (hdbc); /* disconnect from the database */
SQLFreeConnect (hdbc); /* free the connection handle */
SQLFreeEnv (henv); /* free the environment handle */
fclose(prtfile1);
fclose(prtfile2);
return (0);
}

/*--------------------------------------------------*/
/* print_err Routine */
/*--------------------------------------------------*/
int print_err (SQLHDBC hdbc,
SQLHSTMT hstmt)
{
SQLCHAR buffer[SQL_MAX_MESSAGE_LENGTH + 1];
SQLCHAR sqlstate[SQL_SQLSTATE_SIZE + 1];
SQLINTEGER sqlcode;
SQLSMALLINT length;

while ( SQLError(SQL_NULL_HENV, hdbc, hstmt, sqlstate, &sqlcode,
buffer, SQL_MAX_MESSAGE_LENGTH + 1, &length) == SQL_SUCCESS )
{
printf("SQLSTATE: %s Native Error Code: %ld\n", sqlstate, sqlcode);
printf("%s \n", buffer);
printf("----------------------------- \n");
};

return(SQL_ERROR);
}

void strset(char* str, char c, int size)
{
int i;

for(i=0; i<size; i++)
str[i]=c;
str[i-1]=(char) 0;
}