Hide Oracle Objects when Connecting via ODBC from ACCESS

[cdutta]

We have users connecting From ACCESS to Oracle 9iR2 via ODBC. The problem is that the ODBC lists all the objects in ORACLE ( sys, system + all other users). How can I limit the users access to view only objects that they have privileges to access. Did anyone encountered this problem.

Any help would be greatly appreciated.

Thanks
CD

[Sureshy]

Create a user account with privileges to only see those objects you want them to see.

Connect to Oracle via ODBC with this users credentials.

[cdutta]

It does not matter if I create another user. The new user will still see everything even though it only has create session privilege. From Access it will still see the entire list of objects. I am using ODBC from 9i R2.

[Sureshy]

That's strange,

Can this same user see all of these objects when connected through sql*plus.

[cdutta]

Hi Suresh,

All the Other objects are not visible to this user
when connecting via SQLPLUS. The Oracle security is not
seen/enforced by the ODBC. I found a way ( Metalink has a document) where I create views in the users schema which limits the list of the objects listed in the ODBC drop down list box to the following views.

CREATE OR REPLACE VIEW ALL_OBJECTS AS
SELECT * FROM SYS.ALL_OBJECTS
WHERE OWNER IN ('JOEN');


CREATE OR REPLACE VIEW ALL_SYNONYMS AS
SELECT * FROM SYS.ALL_SYNONYMS
WHERE OWNER IN ('JOEN');

However I cannot see other objects to which user has been
given privileges. I would have to create views inside users schema which point to the objects which the user need to query or use.

Did anyone encountered this problem??? Is there a cleaner solution?


Thanks,
CD