WinSQL Lite: Unable to load tables in WinSQL Lite

Question

Question

Steve DiQuattro · Jun 9, 2021

I have created a custom role in IRIS for users to provide limited view-only access for querying tables in the HSANALYTICS namespace. WinSQL has been installed on a Windows server (WinSQL Lite version 14.0.244.784) and an ODBC connection entry has been created in WinSQL for the users to log in and run SQL queries. The custom role provides the roles/resources included in the attached file. The WinSQL System DSN entry defined is:

Health Insight UAT HSANALYTICS 64-bit InterSystems ODBC35

The ODBC35 driver is defined as:

InterSystems ODBC35 2018.01.00.184 CACHEODBC3564.DLL 9/19/2018

When a user with the custom role connects to the database with WinSQL the catalog does not load/display the available database tables. Performing a refresh does not remedy the problem. But the user can successfully run SQL queries if he/she knows the name of the table(s). However, a user that is assigned the %All role has no such issues...tables will load and display. It seems that it must be a permissions issue but I am not sure what other roles/resources are required for the customer role to allow the tables to be loaded and viewable in WinSQL. I was wondering if anyone else has seen this problem and might have any suggestions.

Thank you,

Steve Diquattro

Product version: IRIS 2019.1

$ZV: IRIS for UNIX (Red Hat Enterprise Linux for x86-64) 2019.1.1 (Build 612_0_20518U) Fri Dec 18 2020 10:20:04 EST [HealthConnect:1.1.2]

Restricted Role Definition

Discussion (7)2

Log in or sign up to continue

Peter Steiwer Jun 11, 2021 to Steve DiQuattro

What database is in /hs/nehi/ ? These protect errors suggest the user may not have permissions on this database

0 0

Steve DiQuattro Jun 14, 2021 to Peter Steiwer

Hello Peter,

Thank you for the suggestion. I ultimately opened a WRC with InterSystems Support and after some digging around it was found that there was another database file required to be added to the custom role because of a dependency. Once that was added to the custom role tables started displaying.

Thanks to all who responded to my inquiry, much appreciated.

Steve

0 0

score 0 · Answer 1 · 2021-06-09T16:31:01-04:00

Hello Steve,

I don't have experience with this but here are some past posts that point to the INFORMATION_SCHEMA permissions. Perhaps that helps?

https://community.intersystems.com/post/odbc-user-cannot-see-catalog

https://community.intersystems.com/post/odbc-viewing-table-information

score 0 · Answer 2 · 2021-06-09T16:58:37-04:00

Thank you Vic, I will explore those. I wasn't very successful in my earlier searches in the community here or on Google on finding more specific suggestions.

score 0 · Answer 3 · 2021-06-09T16:44:00-04:00

OK: %ALL means really all.- no surprise
in your list, I miss role %SQL which gives access to all INFORMATION_SCHEMA.*
as @Vic Sun just pointed out

score 0 · Answer 4 · 2021-06-09T16:59:58-04:00

Thank you Robert, I may not have noticed that role (don't know how though). I will do some more testing using your suggestion as well as Vic's...much appreciated.

score 0 · Answer 5 · 2021-06-11T11:29:10-04:00

Hello,

I have done some more testing including adding %SQL to the custom role. I am still unsuccessful in having tables load in WinSQL. I have contacted Support at Synametrics (maker of WinSQL) and provided them with output from an error log file. The following was captured:

After making a connection in WinSQL with the user account:

6/10/2021 11:23:57 AM [ERROR] {MainThread} - Unable to run SQLTable.

[Cache ODBC][State : HYC00][Native Code 469]

[C:\Program Files (x86)\Synametrics Technologies\WinSQL64\WinSQL.exe]

ERROR #388: Unknown error, code 469

6/10/2021 11:23:57 AM [ERROR] {MainThread} - Unable to run SQLTable.

[Cache ODBC][State : HY000][Native Code 400]

[C:\Program Files (x86)\Synametrics Technologies\WinSQL64\WinSQL.exe]

[SQLCODE: <-400>:<Fatal error occurred>]

[Cache Error: <<PROTECT>AnySchemaTablePriv+4^%SYS.SQLSEC ^rINDEXSQL("TABLE","NEHI"),/hs/nehi/hin

6/10/2021 11:23:57 AM [ERROR] {MainThread} - Unable to fetch catalog [RT]. Error: Object reference not set to an instance of an object.

After clicking Refresh Catalog:

6/10/2021 11:24:36 AM [ERROR] {MainThread} - Unable to run SQLTable.

[Cache ODBC][State : HY000][Native Code 400]

[C:\Program Files (x86)\Synametrics Technologies\WinSQL64\WinSQL.exe]

[SQLCODE: <-400>:<Fatal error occurred>]

[Cache Error: <<PROTECT>ztaExecute+44^%Library.ODBCCatalog.1 ^rINDEXSQL("TABLE","NEHI"),/hs/nehi

6/10/2021 11:24:37 AM [ERROR] {MainThread} - Unable to run SQLTable.

[Cache ODBC][State : HY000][Native Code 400]

[C:\Program Files (x86)\Synametrics Technologies\WinSQL64\WinSQL.exe]

[SQLCODE: <-400>:<Fatal error occurred>]

[Cache Error: <<PROTECT>ztaExecute+44^%Library.ODBCCatalog.1 ^rINDEXSQL("TABLE","NEHI"),/hs/nehi

6/10/2021 11:24:37 AM [ERROR] {MainThread} - Unable to run SQLTable.

[Cache ODBC][State : HY000][Native Code 400]

[C:\Program Files (x86)\Synametrics Technologies\WinSQL64\WinSQL.exe]

[SQLCODE: <-400>:<Fatal error occurred>]

[Cache Error: <<PROTECT>ztaExecute+44^%Library.ODBCCatalog.1 ^rINDEXSQL("VIEW","HSAA"),/hs/nehi/

The messages in blue are coming from the ODBC driver.