Re: Help with limiting libraries that show in ODBC connection using OpenLink and JTOPEN.

On 31-Aug-2015 05:54 -0600, rob wrote:

I, personally, would not attempt to put my own RCAC on any of those
tables and views in the system catalog. That might cause a real issue
on a RCLSTG or any number of other operations.

CRTLIB LIB(ROBXLIB)

strsql

CREATE TABLE ROBXLIB.MYTABLE (MYCOL CHAR (5 ) NOT NULL WITH DEFAULT)
Table MYTABLE in ROBXLIB created but was not journaled.
SELECT SYSTEM_TABLE_NAME, SYSTEM_TABLE_SCHEMA FROM qsys2.systables
WHERE SYSTEM_TABLE_SCHEMA = 'ROBXLIB'
Finds it

GRTOBJAUT OBJ(ROBXLIB) OBJTYPE(*LIB) USER(*PUBLIC) AUT(*EXCLUDE)

crtusrprf dummy

Sign on as DUMMY

dsplib robxlib
Not authorized to library ROBXLIB.

SELECT * FROM qsys2.sysschemas WHERE SYSTEM_SCHEMA_NAME = 'ROBXLIB'
-- finds it.

SELECT SYSTEM_TABLE_NAME, SYSTEM_TABLE_SCHEMA FROM qsys2.systables
WHERE SYSTEM_TABLE_SCHEMA = 'ROBXLIB'
-- finds it.

SELECT SYSTEM_COLUMN_NAME ,SYSTEM_TABLE_NAME, SYSTEM_TABLE_SCHEMA
FROM qsys2.syscolumns WHERE SYSTEM_TABLE_SCHEMA = 'ROBXLIB'

....+....1....+....2....+....3....+....4....+....5....+...
SYSTEM_COLUMN_NAME SYSTEM_TABLE_NAME SYSTEM_TABLE_SCHEMA
MYCOL MYTABLE ROBXLIB

So, yes, they could search all libraries for a column called
PAYRATE.
<<SNIP>>

What if the equivalent SELECT statements were performed with that user profile, but querying each analogous "ODBC and JDBC catalog" VIEW from library SYSIBM; i.e. those named mimicking the respective ODBC API, for which the following doc link [in at least one older release] used to state additionally that "These views will be modified as ODBC or JDBC enhances or modifies their metadata APIs." [http://www.ibm.com/support/knowledgecenter/api/content/nl/en-us/ssw_ibm_i_72/db2/rbafzcatalogodbc.htm]. Oddly it was that set of those "ANS and ISO catalog views" [http://www.ibm.com/support/knowledgecenter/api/content/nl/en-us/ssw_ibm_i_72/db2/rbafzcatalogans.htm] which the old documentation suggested the rows seen in the tests should have been excluded [though when using TABLES instead of SYSTABLES, so possibly similarly using COLUMNS instead of SYSCOLUMNS].?

See several lines since\preceding the first appearance of the non-delimited token "INFORMATION_SCHEMA" through the end of my reply [http://archive.midrange.com/java400-l/201302/msg00010.html] suggesting some particular set of VIEWs do not show entries\objects for libraries to which the user has no authority; possibly extending to the object-level. Of course, although the "tables and views in the catalogs are shipped with the SELECT privilege to PUBLIC", that "privilege may be revoked and the SELECT privilege granted to individual users" as noted in [http://www.ibm.com/support/knowledgecenter/ssw_ibm_i_72/db2/rbafzcatalog.htm]