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:

Standard ODBC Administrator page showing the connections used in testing.

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.

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.

<<Filtering Rings (Oracle Spatial) >>Implementing a Rotate/ST_Rotate function for Oracle Spatial

Name		Remember
E-mail
http://
Message
	Textile Help