ODBC behavior with pre-bound packages

John,

The behavior you describe here certainly sounds unexpected. By default, the job you ran should have created packages in the NULLID schema and then granted permissions on them to PUBLIC. Are all your users in the PUBLIC schema?

Upon connecting to the server, the driver issues a query against the SYSIBM.SYSPACKAGE table to check for the existence of the packages and if they're not found, it will try to create them. Do your users have SELECT privileges on this table? If the query against the SYSPACKAGE table fails, then the driver will assume the packages do not exist and try to create them. This might explain why you are seeing the driver try to create the required packages for every user that connects.

It is not expected for you to grant BINDADD and CREATE IN to your individual users. Package creation is something of an expensive process, so the expectation is that it is done once and then the packages are simply available for the drivers to utilize.

Please check on the items listed above and let me know what you find.

Hi,
Thanks for the information. I was not previously setting the users up with select access against sysibm.syspackage. I was not aware it was a requirement.

Your question: "Are all your users in the PUBLIC schema?" - I'm not sure what you are trying to distinguish. (That I know of) If a privilege doesn't exist for a specific user, then that user would pick up any privileges granted to public.

However, I cleaned up, bind the packages with the job, granted execute against the packages and select against sysibm.syspackage. I still get the bind auth error

I collected trace, here is the file
(See attached file: odbctrace01.out)

Here are the details of what I did:

To start clean:

• I removed all of the privileges I had previously granted for the users, dropped the packages.

• Then, I re-ran the BIND job, binding the packages and granting execute to public. (results below)
                        G                    Package                      H B E C
  Sel Grantor  Grantee  T Collection         Name     Grant Timestamp     G D X O
      JGOOD*   *        * *                  *        *                   * * * *
  --- -------- -------- - ------------------ -------- ------------------- - - - -
      JGOOD    PUBLIC     NULLID             DDOC510A 2018-07-13-20.32.48     Y  
      JGOOD    PUBLIC     NULLID             DDOC510B 2018-07-13-20.32.48     Y  
      JGOOD    PUBLIC     NULLID             DDOC510C 2018-07-13-20.32.48     Y  
      JGOOD    PUBLIC     NULLID             DDOU510A 2018-07-13-20.32.48     Y  
      JGOOD    PUBLIC     NULLID             DDOU510B 2018-07-13-20.32.48     Y  
      JGOOD    PUBLIC     NULLID             DDOU510C 2018-07-13-20.32.48     Y  
      JGOOD    PUBLIC     NULLID             DDOR510A 2018-07-13-20.32.48     Y  
      JGOOD    PUBLIC     NULLID             DDOR510B 2018-07-13-20.32.48     Y  
      JGOOD    PUBLIC     NULLID             DDOR510C 2018-07-13-20.32.48     Y  
      JGOOD    PUBLIC     NULLID             DDOS510A 2018-07-13-20.32.48     Y  
      JGOOD    PUBLIC     NULLID             DDOS510B 2018-07-13-20.32.48     Y  
      JGOOD    PUBLIC     NULLID             DDOS510C 2018-07-13-20.32.48     Y  
      JGOOD    PUBLIC     NULLID             DDON510A 2018-07-13-20.32.48     Y  
      JGOOD    PUBLIC     NULLID             DDON510B 2018-07-13-20.32.48     Y  
      JGOOD    PUBLIC     NULLID             DDON510C 2018-07-13-20.32.48     Y  
  
  
• Then, I granted select against syspackage to public
  odb2 "grant select on SYSIBM.SYSPACKAGE to public"
• I used a jupyter notebook to verify that the same user that uses the odbc access can select * from sysibm.syspackage
• Then from a linux system, I setup ODBC trace:
  [jgood@localhost(192.168.164.223) ~/src/example]$ grep -i trace /opt/SDAP712/odbc.ini
  Trace=1
  TraceFile=/home/jgood/src/example/odbctrace.out
  TraceDll=/opt/SDAP712/lib/XEtrc27.so
  TraceOptions=1
•  - and ran an modified version of example.c that just runs one SQL request

./simple 'DB2 Wire Protocol' zmml01  db2au01 'select count(*) from mlz.churn_cust_sum'

./simple DataDirect Technologies, Inc. ODBC Example Application.
connecting to datasource: 'DB2 Wire Protocol', uid: 'zmml01', password: 'db2au01', opt1: '', opt2: ''
SQLSTATE = S1000
NATIVE ERROR = -567
MSG = [IBM SPSS][ODBC DB2 Wire Protocol driver][DB2]AUTHORIZATION ERROR BIND ZMML01.  DSNXBAC.NULLID.DDOS510A.(01) BINDADD

SQLConnect: Retrying Connect.
SQLSTATE = S1000
NATIVE ERROR = -567
MSG = [IBM SPSS][ODBC DB2 Wire Protocol driver][DB2]AUTHORIZATION ERROR BIND ZMML01.  DSNXBAC.NULLID.DDOS510A.(01) BINDADD

SQLConnect: Retrying Connect.
SQLSTATE = S1000
NATIVE ERROR = -567
MSG = [IBM SPSS][ODBC DB2 Wire Protocol driver][DB2]AUTHORIZATION ERROR BIND ZMML01.  DSNXBAC.NULLID.DDOS510A.(01) BINDADD

SQLConnect: Retrying Connect.

Regards,

John Goodyear
z Systems Analytics zChampion
WSC z Systems Applied Technologies
Herndon, VA


"Brian Derwart" ---07/13/2018 09:54:44 AM---Update from Progress Community [INVALID URI REMOVED

John,

Thanks for the details of your troubleshooting efforts. You can disregard my question about the PUBLIC schema as I was just trying to make sure you hadn't done anything out of the ordinary like revoke user privileges to PUBLIC.

Since the driver's verification of the existence of packages is an internal process, could you please create a network packet trace for me to examine? To enable the network packet logging, set EnablePacketLogging=1 in the odbc.ini file within the data source definition that you've configured for the Progress DB2 driver. Please also send the data source definition from that file to me so I can check all the other settings you're using and determine the cause of this issue.

The network packet log will show me the response from the DB2 server during the driver's check for the packages which should point us to the exact reason for this failure.

Regards,

Brian

Hi,
I enabled packet logging for the datasource definition (DB2 Wire Protocol) in the odbc.ini file and re-ran the sample application.

Included in the zip file is the odbc.ini file, the trace output, and the packet trace.


(See attached file: odbc-trace2.zip)


Regards,

John Goodyear
z Systems Analytics zChampion
WSC z Systems Applied Technologies
Herndon, VA


"Brian Derwart" ---07/15/2018 01:21:03 PM---Update from Progress Community [INVALID URI REMOVED

John,

I apologized, but I led you slightly astray in a previous comment I made. If you look in the packet logs you provided, you can see that the driver attempts to select from SYSIBM.SYSPACKSTMT in order to validate the existence of the packages rather than the SYSIBM.SYSPACKAGE table. The reply from the server indicates a permissions error on this system table.

You can see in the reply starting on line 93 of the file pkt20180715230101951_01.out that the SQL Code returned is -551 (Hex: FF FF FD D9) with SQLState 42501.

Please grant SELECT permissions to your user on the SYSIBM.SYSPACKSTMT table and let me know if it resolves this issue.

Thanks,

Brian

Hi.
I revoked access to SYSPACKAGE and granted select for SYSPACKSTMT.

Running the test program, I'm able to run the query without any errors, and it runs quickly with no delays as before when the user was granted bind-add/create in and the packages were getting rebound on every call. I check of the timestamp on the packages confirms that they were not rebound.

I then dropped the packages, re-ran the test app which failed due to bind permission (expected).

Then I ran /opt/SDAP712/bin/bind27 'DB2 Wire Protocol' which successfully rebound the packages. After that the test app worked.

So in addition to confirming the bind JCL and the bind27 utility bind the packages in a manner suitable for running the test application, (seems) to confirm that select access to SYSPACKSTMT for the connecting user is sufficient . I'm going to have to run this on a different system to make sure I didn't miss any permissions.

Documentation
On the topic of documentation, I curious where the SYSPACKSTMT access requirement is document, or should be documented. I couldn't find it in your doc.


Security
Thinking in terms of security, I expect that some DBADMs would not want to grant users access to SYSPACKSTMT. It could be considered a security vulnerability as it gives the users access to all of the statements in the packages bound in the database. Do you know if this has ever been a concern?

Has development considered just trying open the package, and if it fails, confirm via the error code that the package doesn't exist? Having said that, I'm not sure what the IBM CLI packages did...


Regards,

John Goodyear
z Systems Analytics zChampion
WSC z Systems Applied Technologies
Herndon, VA


"Brian Derwart" ---07/16/2018 02:40:30 PM---Update from Progress Community [INVALID URI REMOVED

John,

Glad to hear you got this all working as expected. I'll make a note to add something to the KB about the requirement that users have SELECT access to the SYSPACKSTMT table.

I've not heard any other customers raise security concerns for the driver selecting from the SYSPACKSTMT table. The reason for this particular package validation methodology is so that the driver can check for the existence of all the packages required to support the various ODBC isolation levels. If the driver finds that any of the packages are missing, then it goes through the process of re-creating them so that it can support any isolation level set by the ODBC application at run time.

I'm also not sure what the IBM CLI driver does to check for the packages and have only executed the bind using the Admin Assistant configuration tool.

Regards,

Brian