Limiting table list returned when connecting to Oracle Database using ODBC

This is not a spatial article but an article on how to limit the list of objects returned to an ODBC client (eg Access) on initial connection.

One of the problems users face when connecting ODBC clients to an Oracle database is that the list of objects (tables, views etc) returned includes all the schemas in the database even if they are Oracle’s own (eg MDSYS, CTXSYS, DBSNMP, DMSYS, EXFSYS, OLAPSYS, SYSMAN, XDB etc etc). Here is an example where I have used the Microsoft ODBC driver for Oracle to make a database connection using Open Office Base:

|._ !http://www.spatialdbadvisor.com/images/27.png (Standard ODBC Administrator page showing the connections used in testing.)!|._ !http://www.spatialdbadvisor.com/images/25.png (Open Office Base showing unrestricted object list before creation of views)!|

Note that objects have been extracted from a large range of schemas (I have collapsed the schema folders holding the tables to make it easier to display in an image).

Plainly the display of objects from Oracle owned schemas or schemas for which the connecting user has not been granted permissions (via GRANTs) to any of its objects is confusing.

But there is a way around this.

Oracle’s Metalink describes the issue in article 124117.1. In this article it is suggested that schema level views be created for ALL_OBJECTS and ALL_SYNONYMS to override the views with the same name owned by SYS as these are the two views that are queried by ODBC drivers once a connection is established.

The suggested views are very simplistic so I modified these for my own use and publish them here.

 CREATE OR REPLACE VIEW ALL_OBJECTS AS
 SELECT *
   FROM SYS.ALL_OBJECTS ao
  WHERE ao.OWNER = sys_context('userenv','session_user') /* Obviously, we want the connecting users objects */
    AND ao.object_name not in ('ALL_OBJECTS','ALL_SYNONYMS')
    AND ao.secondary = 'N' /* Remove secondary objects because Oracle Spatial implements its Quad/RTree indexes using seconday tables named MDQT% or MDRT% */
     OR EXISTS (SELECT 1
                  FROM USER_TAB_PRIVS_RECD utpr
                 WHERE utpr.owner = ao.owner
                   AND utpr.table_name = ao.object_name) /* Is there anything in SYS.ALL_OBJECTS that I have been granted permission to use? */;

 CREATE OR REPLACE VIEW ALL_SYNONYMS AS
 SELECT *
   FROM SYS.ALL_SYNONYMS asyn
  WHERE asyn.OWNER = sys_context('userenv','session_user') /* Obviously, we want the connecting users synonyms
     OR EXISTS (SELECT 1
                  FROM USER_TAB_PRIVS_RECD utpr
                 WHERE utpr.owner = asyn.owner
                   AND utpr.table_name = asyn.SYNONYM_NAME) /* Is there anything in ALL_SYNONYMS that I have been granted permission to use? */;

When I now make a connection to Oracle using either the Microsoft of Oracle ODBC drivers I now get the desired reduced list as can be seen in the image below.

!http://www.spatialdbadvisor.com/images/26.png (Open Office Base showing restricted object list after creation of views)!

Ahh, that’s much nicer.

Now I am not suggesting that my views are right for anyone, all I am doing is pointing a way forward for those people who have experienced this problem and would like to be able to correct it. The approach Oracle suggests is powerful and flexible: you are free to modify or “tune” the view however you wish.

I hope this is of help to someone.