Chuck and Dan,
I have been unable to respond to your messages since I have been out of
the office lately due to the birth of a child. But thanks for your
assistance as it is very helpful.
With regard to DB2 for IBM i, I didn't think about the angle of the system
catalog, but am familiar with the concept from working some with MySQL and
DB2 LUW. This would explain why I can see the library and file existence
and structure without accessing data. When I am back in the office I will
review the IBM documentation and see what I can do to restrict access to
the catalog from the user profile in question.
Thanks,
Blake
-----java400-l-bounces@xxxxxxxxxxxx wrote: -----
message: 1
date: Sun, 10 Feb 2013 10:24:16 -0800
from: CRPence
subject: Re: JDBC question
On 08 Feb 2013 12:06, BButterworth 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.
[1]
http://publib.boulder.ibm.com/infocenter/iseries/v7r1m0/topic/db2/rbafzcatalog.htm
_i DB2 for i catalog views i_
"The views contained in a DB2? 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 * IBM? 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'
...
[2]
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
------------------------------
message: 2
date: Mon, 11 Feb 2013 09:42:15 -0600
from: Dan Kimmel
subject: RE: JDBC question
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.
References
Visible links
1.
http://publib.boulder.ibm.com/infocenter/iseries/v7r1m0/topic/db2/rbafzcatalog.htm
2.
http://publib.boulder.ibm.com/infocenter/iseries/v7r1m0/topic/db2/rbafzcatalogodbc.htm
midrange.com
