[gdal-dev] OGR ODBC Driver and Default Enumeration

[Frank Warmerdam]

Simon Greener wrote:
> Frank,
> 
> What sort of SQL are you using when you query Oracle and SQL Server?

Simon,

The OGR ODBC driver does not directly use SQL to fetch back the
table list.  It issues an ODBC SQLTables command:

     SQLTables( m_hStmt,
                (SQLCHAR *) pszCatalog, SQL_NTS,
                (SQLCHAR *) pszSchema, SQL_NTS,
                (SQLCHAR *) NULL, SQL_NTS,
                (SQLCHAR *) "'TABLE','VIEW'", SQL_NTS );

Basically this asks the ODBC driver to return a list of all tables
and views.  Currently we are always passing the catalog and schema
setting as NULL resulting in a fetch of entries from all tables and
schemas.

> What role does the INFORMATION_SCHEMA play in you changed ODBC driver? I 
> noticed that, with the current version if I created an 
> INFORMATION_SCHEMA and the appropriate views, ogrinfo started to work.

I really don't know if or how INFORMATION_SCHEMA is used, though I noticed
that it is returned for SQL Server.

> With Oracle, if you use the DBA_* views (eg DBA_OBJECTS) you will see 
> tables in schemas that you may not actually have permission to read/update.
> ALL_* (eg ALL_OBJECTS) will show you those the connecting user can see 
> (eg SELECT) but may not  be able to update.

 From my reading of the ODBC documentation, I'd have to do a bunch of
additional permissions queries if I want to restrict the list to
tables with at least read access. I'm not keen on populating the
ODBC driver with any database specific criteria if I can help it.

> My point is this: you should really only show the tables that the 
> connecting user can actually read/update. No point in being able to see 
> all sorts of other table/columns if you can't actually use them.

I can see your point, though I'm not clear on how much it will help
to restrict things to tables we have at least read access to or
how expensive/incompatible the extra queries for read permissions
will be.

Best regards,
-- 
---------------------------------------+--------------------------------------
I set the clouds in motion - turn up   | Frank Warmerdam, warmerdam at pobox.com
light and sound - activate the windows | http://pobox.com/~warmerdam
and watch the world go round - Rush    | Geospatial Programmer for Rent