Thread: Filtering DatabaseMetaData to show only the items for which the current user has access

Langley, Scott E wrote:
> Hello PostgreSQL JDBC Developers,
>
> We have a desire to hide unneeded database objects from our database-challenged
> users - as they might see in a simple database viewer application - by removing
> their privileges on such objects.
>
> It appears that many database viewing applications, e.g., DbVisualizer, rely on
> what is returned by the JDBC driver’s DatabaseMetaData methods to determine
> which schemas and tables to display to the user:
>
> ~
 > ~
 > ~
>
> If you would accept a patch along these lines, which approach would be preferable?
>
> Thanks for your consideration.
> Scott Langley

Seems what you are asking the JDBC driver to do is the work of what
should be done by a client application. Yes MySQL does offer this
more advanced control of users.

Most of this can be accomplished as indicated, filtering through the
DatabaseMetaData.getTables(catalog, schemaPattern, tableNamePattern,
tableTypes)

MyJSQLView does exactly this and some basic filtering can be accomplished
via a configuration file, myjsqlview.conf. If not edit the DatabaseProperties
Java code to only allow, the particular aspects desired. This might be much
easier then editing the JDBC code to add this feature.

Overall seems the additional control should be requested via the server
features.

danap
http://myjsqlview.com

Langley, Scott E wrote:
> Hello PostgreSQL JDBC Developers,
>
> We have a desire to hide unneeded database objects from our database-challenged
> users - as they might see in a simple database viewer application - by removing
> their privileges on such objects.
>
> It appears that many database viewing applications, e.g., DbVisualizer, rely on
> what is returned by the JDBC driver’s DatabaseMetaData methods to determine
> which schemas and tables to display to the user:
>
> ~
 > ~
 > ~
>
> If you would accept a patch along these lines, which approach would be preferable?
>
> Thanks for your consideration.
> Scott Langley

Seems what you are asking the JDBC driver to do is the work of what
should be done by a client application. Yes MySQL does offer this
more advanced control of users.

Most of this can be accomplished as indicated, filtering through the
DatabaseMetaData.getTables(catalog, schemaPattern, tableNamePattern,
tableTypes)

MyJSQLView does exactly this and some basic filtering can be accomplished
via a configuration file, myjsqlview.conf. If not edit the DatabaseProperties
Java code to only allow, the particular aspects desired. This might be much
easier then editing the JDBC code to add this feature.

Overall seems the additional control should be requested via the server
features.

danap
http://myjsqlview.com

Langley, Scott E schrieb am 13.09.2016 um 01:54:
> We have a desire to hide unneeded database objects from our
> database-challenged users - as they might see in a simple database
> viewer application - by removing their privileges on such objects.
>
> It appears that many database viewing applications, e.g.,
> DbVisualizer, rely on what is returned by the JDBC driver’s
> DatabaseMetaData methods to determine which schemas and tables to
> display to the user
>
> Another way would be to:
>
> 1. Modify the information_schema views for a particular database to
> only show the current user the objects for which they have
> privileges.
>
> 2. Then, have the PostgreSQL JDBC driver populate its
> DatabaseMetatData information only using the contents of the
> information_schema and not query pg_catalog, pg_class, and the other
> Postgres-specific system information tables.

If your aim is users of SQL clients, then what's the use of that?

As you can run any SQL statement inside those applications, the users
can still run the query directly against pg_class and the other system tables

Or they just use psql or any other tools that is not based on the JDBC driver
to get that information. You can never get security right on the client side.

Plus: with the driver being OpenSource, anyone can build their own version
with those checks disabled, or simply use an older version of the driver.

Thomas

Langley, Scott E schrieb am 13.09.2016 um 01:54:
> We have a desire to hide unneeded database objects from our
> database-challenged users - as they might see in a simple database
> viewer application - by removing their privileges on such objects.
>
> It appears that many database viewing applications, e.g.,
> DbVisualizer, rely on what is returned by the JDBC driver’s
> DatabaseMetaData methods to determine which schemas and tables to
> display to the user
>
> Another way would be to:
>
> 1. Modify the information_schema views for a particular database to
> only show the current user the objects for which they have
> privileges.
>
> 2. Then, have the PostgreSQL JDBC driver populate its
> DatabaseMetatData information only using the contents of the
> information_schema and not query pg_catalog, pg_class, and the other
> Postgres-specific system information tables.

If your aim is users of SQL clients, then what's the use of that?

As you can run any SQL statement inside those applications, the users
can still run the query directly against pg_class and the other system tables

Or they just use psql or any other tools that is not based on the JDBC driver
to get that information. You can never get security right on the client side.

Plus: with the driver being OpenSource, anyone can build their own version
with those checks disabled, or simply use an older version of the driver.

Thomas

Vladimir Sitnikov wrote:
> Dave>I think a connection parameter patch might be acceptable.
>
> I think the main question is which filters should be enabled by default.
> For instance: should it list a table that is read-only (i.e. no write grants)?

My application requires access to possible read-only tables to gain
functionality, like for instance creating schema for the database.
I would have to check.

Is it really the functionlity of the JDBC to limit access to the
database? Which is already provided through the DatabaseMetaData.

Filter by default on tables that are not readable or writable is
one thing, but limiting by default read-only tables is going to
possibly limit functionality of clients which depend on these
tables for information.

danap.

>
> However, I think it makes sense to hide those tables by default that are
> completely inaccessible (neither readable, nor writable).
>
> Vladimir

Vladimir Sitnikov wrote:
> Dave>I think a connection parameter patch might be acceptable.
>
> I think the main question is which filters should be enabled by default.
> For instance: should it list a table that is read-only (i.e. no write grants)?

My application requires access to possible read-only tables to gain
functionality, like for instance creating schema for the database.
I would have to check.

Is it really the functionlity of the JDBC to limit access to the
database? Which is already provided through the DatabaseMetaData.

Filter by default on tables that are not readable or writable is
one thing, but limiting by default read-only tables is going to
possibly limit functionality of clients which depend on these
tables for information.

danap.

>
> However, I think it makes sense to hide those tables by default that are
> completely inaccessible (neither readable, nor writable).
>
> Vladimir