Chuck points out something here I didn't know ... JDBC selections are based on privileges in the system catalog and system privileges assigned to IBMi objects are somewhat unrelated.
You might try using the GRANT and REVOKE sql statements and see if that will render your list of libraries as you desire. I don't know what the syntax might be for revoking the privilege to "see" a schema name in a list of schemas. Chuck gives you an example of how to exclude a table.
-----Original Message-----
From: java400-l-bounces@xxxxxxxxxxxx [mailto:java400-l-bounces@xxxxxxxxxxxx] On Behalf Of CRPence
Sent: Sunday, February 10, 2013 12:24 PM
To: java400-l@xxxxxxxxxxxx
Subject: Re: JDBC question
On 08 Feb 2013 12:06, BButterworth@xxxxxxxxxxxxxx wrote:
We're setting up a profile on our IBM i web server to use for querying
data in a web application. For security purposes I've excluded the
group said profile is in from a number of libraries on the system,
granting it *USE authority to the library containing the data for the
web application. The question I have is that when I use this profile
in an SQL client (like DbVisualizer) with the JTOpen driver I can see
every library and file in the system, including those that are *PUBLIC
*EXCLUDE. I can't actually see or modify any data, but is there a way
(system value, etc.) to prevent a user profile from even seeing
restricted libraries and their contents as I wouldn't want a potential
hacker to even know the existence of restricted libraries and their
contents? I've done a little research online, but haven't come across
anything.
The same information is available to a local connection by a query of SQL Catalog VIEWs. See the following documentation which I believe explains the situation. From that information I infer the catalog requests would need to be redirected to QSYS2 [and optionally revoke authority to the other VIEWs]. In the distant past for the few customers that had an issue with the amount of data provided by the SQL Catalog VIEWs, I suggested modifying the SQL Catalog VIEW definitions to add selection via a predicate using a UDF in the WHERE clause. Seems perhaps that was since done by the system, according to these docs.
http://publib.boulder.ibm.com/infocenter/iseries/v7r1m0/topic/db2/rbafzcatalog.htm
_i DB2 for i catalog views i_
"The views contained in a DB2R for i catalog are described in this section.
The database manager maintains a set of tables containing information about the data in each relational database. These tables are collectively known as the catalog. The catalog tables contain information about tables, user-defined functions, distinct types, parameters, procedures, packages, views, indexes, aliases, sequences, variables, constraints, triggers, XSR objects, and languages supported by DB2 for i. The catalog also contains information about all relational databases that are accessible from this system.
There are three classes of catalog views:
_i * IBMR i catalog tables and views i_
<<SNIP>>
...
_i * ODBC and JDBC catalog views i_
The ODBC and JDBC catalog views are designed to satisfy ODBC and JDBC metadata API requests. For example, SQLCOLUMNS. These views are compatible with views on DB2 LUW Version 8. These views will be modified as ODBC or JDBC enhances or modifies their metadata APIs.
These views exist in schema SYSIBM.
_i * ANS and ISO catalog views i_
The ANS and ISO catalog views are designed to comply with the ANS and ISO SQL standard (the Information Schema catalog views). These views will be modified as the ANS and ISO standard is enhanced or modified.
There are several columns in these views that are reserved for future standard enhancements.
There are two versions of these views:
* The first version of these views exist in schema INFORMATION_SCHEMA ≥1≤. Only rows associated with objects to which the user has some privilege are included in the views. This version is compatible with the ANS and ISO SQL standard.
If you use of this set of catalog views to prevent users from seeing any information about objects to which they have no privilege, you should revoke privileges to the other catalog views from users and PUBLIC.
* The second version of these views exist in schema SYSIBM. All rows are included in these views whether or not the user has some privilege to the objects associated with rows in the views. These views are compatible with views on DB2 LUW Version 8 and will generally perform better than the ANS and ISO views in QSYS2.
For example, assume that a user has the SELECT privilege to the QSYS2.TABLES and SYSIBM.TABLES catalog views but does not have any privilege to a table called WORK.EMPLOYEE. The following SQL statement will not return a result row:
SELECT *
FROM QSYS2.TABLES
WHERE TABLE_SCHEMA = 'WORK' AND TABLE_NAME = 'EMPLOYEE'
However, the following SQL statement will return a result row:
SELECT *
FROM SYSIBM.TABLES
WHERE TABLE_SCHEMA = 'WORK' AND TABLE_NAME = 'EMPLOYEE'
...
http://publib.boulder.ibm.com/infocenter/iseries/v7r1m0/topic/db2/rbafzcatalogodbc.htm
_i ODBC and JDBC catalog views i_
The catalog includes the views and tables in the SYSIBM library displayed in this section.
...
≤1≥ INFORMATION_SCHEMA is the ANS and ISO SQL standard schema name that contains catalog views. It is a synonym for QSYS2."
--
Regards, Chuck
--
This is the Java Programming on and around the IBM i (JAVA400-L) mailing list To post a message email: JAVA400-L@xxxxxxxxxxxx To subscribe, unsubscribe, or change list options,
visit:
http://lists.midrange.com/mailman/listinfo/java400-l
or email: JAVA400-L-request@xxxxxxxxxxxx Before posting, please take a moment to review the archives at
http://archive.midrange.com/java400-l.
midrange.com
