Hi,I'm trying to use your ODBC drivers against z/OS DB2 v10 ( and V12 in the future).Having some dated experience with z/OS DB2 customers, the DB admins tend not to give applications bind access. The DB admins want to manually bind the packages and then just give the application execute permission on the packages. So I uploaded the dbrms and jcl that you supplied and ran it, creating the packages with the different sorts of Isolation levels. However, when I ran the application that's using your drivers, it got bind errrors.Setting plan A aside, I granted my users "create in" and bindadd authority. The first user to use the drivers worked OK, but then the 2nd user generated an exception trying to free the packageI ran across this article in your knowledgebase from a couple yearsback on this issue:knowledgebase.progress.com/.../3017This seems like a bug. At the least, your drivers should support access from multiple users.And getting back to my initial assertion, you drivers shouldn't need to bind their self to the database if they are are already bound. Questions:1) Is there some sort of configuration process I'm missing that would allow the drivers not to attempt to bind theirself. 2) If #1 isn't currently possible, is there some sort of configuration action available that will stop the drivers from trying to free packages? Concurrent access by multiple users is a must!
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.DocumentationOn 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.SecurityThinking 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 Goodyearz Systems Analytics zChampionWSC z Systems Applied TechnologiesHerndon, VA"Brian Derwart" ---07/16/2018 02:40:30 PM---Update from Progress Community [INVALID URI REMOVED
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 errorI collected trace, here is the file(See attached file: odbctrace01.out)Here are the details of what I did:To start clean:
./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 = S1000NATIVE ERROR = -567MSG = [IBM SPSS][ODBC DB2 Wire Protocol driver][DB2]AUTHORIZATION ERROR BIND ZMML01. DSNXBAC.NULLID.DDOS510A.(01) BINDADDSQLConnect: Retrying Connect.SQLSTATE = S1000NATIVE ERROR = -567MSG = [IBM SPSS][ODBC DB2 Wire Protocol driver][DB2]AUTHORIZATION ERROR BIND ZMML01. DSNXBAC.NULLID.DDOS510A.(01) BINDADDSQLConnect: Retrying Connect.SQLSTATE = S1000NATIVE ERROR = -567MSG = [IBM SPSS][ODBC DB2 Wire Protocol driver][DB2]AUTHORIZATION ERROR BIND ZMML01. DSNXBAC.NULLID.DDOS510A.(01) BINDADDSQLConnect: Retrying Connect.Regards,John Goodyearz Systems Analytics zChampionWSC z Systems Applied TechnologiesHerndon, VA"Brian Derwart" ---07/13/2018 09:54:44 AM---Update from Progress Community [INVALID URI REMOVED
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.
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 Goodyearz Systems Analytics zChampionWSC z Systems Applied TechnologiesHerndon, VA"Brian Derwart" ---07/15/2018 01:21:03 PM---Update from Progress Community [INVALID URI REMOVED
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.
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.