Cobol connect to oracle failed when password field is defined longer than real password value

Question

My COBOL program cannot connect to oracle when the password field is defined longer than actual password length for a user. i.e, if the password value is 'mypasswd', the host variable to keep password must be defined with "PIC X(8)", otherwise, connection will fail; for example:

 1         IDENTIFICATION DIVISION.
 2         PROGRAM-ID. SAMPLE.
 3         ENVIRONMENT DIVISION.
 4         DATA DIVISION.
 5         WORKING-STORAGE SECTION.
 6             EXEC SQL BEGIN DECLARE SECTION END-EXEC.
 7         01 USERNAME      PIC         X(010).
 8         01 PASSWD        PIC         X(010).
 9         01 DBSTRING      PIC         X(020).
10             EXEC SQL END DECLARE SECTION END-EXEC.
11             EXEC SQL INCLUDE SQLCA END-EXEC.
12
13         PROCEDURE DIVISION.
14         BEGIN-PGM.
15             EXEC SQL WHENEVER SQLERROR
16              DO PERFORM SQL-ERROR
17             END-EXEC.
18         LOGON.
19             MOVE "myuser" TO USERNAME.
20             MOVE "mypasswd" TO PASSWD.
21             MOVE "mydb" TO DBSTRING.
22             EXEC SQL
23              CONNECT :USERNAME IDENTIFIED BY :PASSWD USING :DBSTRING
24             END-EXEC.
25         LOGOUT.
26             DISPLAY "HAVE A GOOD DAY.".
27             EXEC SQL COMMIT WORK RELEASE END-EXEC.
28             STOP RUN.
29         SQL-ERROR.
30             EXEC SQL WHENEVER SQLERROR CONTINUE END-EXEC.
31             DISPLAY "ORACLE ERROR DETECTED:".
32             DISPLAY SQLERRMC.
33             EXEC SQL ROLLBACK WORK RELEASE END-EXEC.
34             STOP RUN.

I must get a connect failure: ORACLE ERROR DETECTED: ORA-01017: invalid username/password; logon denied

But when I change the password field definition to: 8 01 PASSWD PIC X(008). i.e. length is the same length of real password value (length("mypasswd")=8), the program can connect to Oracle successfully.

My situation is that we need users to be able to provide their own username and password, so we must firstly define a username and password fields long enough to keep the maximum length we allow. However, as stated above, all connection requests will be failed if a user chooses a shorter password than the maximum.

The program is migrated from an old version of Oracle 11.2.0.1.0, where we don't have this issue, the program was working fine, the connect operation was successful. But the problem occurred after we migrate to Oracle 12.1.0.1.0.

Answer 1

If you are using Pro*COBOL, then this link is for you: http://docs.oracle.com/cd/A57673_01/DOC/api/doc/PCO18/ch1.htm#toc024

It shows how to define your username and password fields as VARYING.

 WORKING STORAGE SECTION. 

     ... 

     EXEC SQL BEGIN DECLARE SECTION END-EXEC. 

         01  USERNAME  PIC X(10) VARYING. 

         01  PASSWD    PIC X(10) VARYING. 

         ... 

     EXEC SQL END DECLARE SECTION END-EXEC. 

     ... 

 PROCEDURE DIVISION. 

 LOGON. 

     MOVE "SCOTT" TO USERNAME-ARR. 

     MOVE 5 TO USERNAME-LEN. 

     MOVE "TIGER" TO PASSWD-ARR. 

     MOVE 5 TO PASSWD-LEN. 

     EXEC SQL WHENEVER SQLERROR GOTO LOGON-ERROR END-EXEC. 

     EXEC SQL 

         CONNECT :USERNAME IDENTIFIED BY :PASSWD 

     END-EXEC. 

As it turns out, the example quoted is not directly useful to you (from Comments) because your passwords may not be five in length.

This really is no problem. You can calculate the length of the password for a given user, and then, instead of using the literal 5, use the value that you have calculated.

@NealB has shown in his answer a simple way to do this (if you can have no leading or embedded blanks in a password).

INSPECT PASSWD TALLYING PSSWDLEN FOR ALL SPACE
COMPUTE PSSWDLEN = LENGTH OF PASSWD - PSSWDLEN 

If you are unable to use that method, a simple loop-construct of your choice starting from the last byte of the password field and continuing whilst a space is encountered. Watch out for the entirely-space possibility.

You may want to use the same technique for username anyway, as it would be more transportable amongst different flavours of Oracle/OS (depending on what it is that is allowing it to work for you). I'd do that, unless it it absolutely impossible that it is ever required.

You do mention a move to a new Oracle version. This behaviour should be documented in the Summary of Changes, or similar, section of the documentation. If you cannot find reference to it, contact Oracle and find out what is going on.

If you are not using Pro*COBOL, you may be able to emulate the effect of VARYING.

     EXEC SQL BEGIN DECLARE SECTION END-EXEC. 

         01  USERNAME.
             05  USERNAME-LEN BINARY PIC 9(4).
             05  USERNAME-VALUE      PIC X(10). 

         01  PASSWD.
             05  PASSWD-LEN BINARY PIC 9(4).
             05  PASSWD-VALUE      PIC X(10). 

     END-EXEC. 

Then:

 LOGON. 

     MOVE "SCOTT" TO USERNAME-VALUE. 

     MOVE 5 TO USERNAME-LEN. 

     MOVE "TIGER" TO PASSWD-VALUE. 

     MOVE 5 TO PASSWD-LEN. 

     EXEC SQL WHENEVER SQLERROR GOTO LOGON-ERROR END-EXEC. 

     EXEC SQL 

         CONNECT :USERNAME IDENTIFIED BY :PASSWD 

     END-EXEC. 

You may have to try:

         01  USERNAME.
             05  USERNAME-LEN BINARY PIC 9(4).
             05  USERNAME-VALUE.
                 10  FILLER OCCURS 1 TO 10 TIMES
                     DEPENDING ON USERNAME-LEN.
                     15  FILLER      PIC X. 

         01  PASSWD.
             05  PASSWD-LEN BINARY PIC 9(4).
             05  PASSWD-VALUE.
                 10  FILLER OCCURS 1 TO 10 TIMES
                     DEPENDING ON PASSWD-LEN.
                     15  FILLER      PIC X. 

     END-EXEC. 

If getting nowhere with suggestion, you need to supply more information, like OS, version of COBOL, version of Oracle, and what you have tried and what results you got with those attempts.

Answer 2

Have you tried using reference modification to adjust the length of the username/password on the connect request?

I am not an Oracle kind-of-guy, but something like this might work:

22             EXEC SQL
23              CONNECT :USERNAME(1:UNAMELEN) IDENTIFIED BY :PASSWD(1:PSSWDLEN) USING :DBSTRING
24             END-EXEC.

where the UNAMELEN and PSSWDLEN are numeric variables (e.g. PIC S9(4) BINARY) containing the actual lengths of the user name and password.

Determining the values for UNAMELEN and PSSWDLEN can be done using the INSPECT verb something like this:

INSPECT PASSWD TALLYING PSSWDLEN FOR ALL SPACE
COMPUTE PSSWDLEN = LENGTH OF PASSWD - PSSWDLEN 

This will work provided that passwords and user names do not contain internal blank spaces. If they do you will have to compute the actual lengths differently.