Hide Oracle Objects when Connecting via ODBC from ACCESS
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 5 of 5

Thread: Hide Oracle Objects when Connecting via ODBC from ACCESS

  1. #1
    Join Date
    Jul 2000
    Posts
    41

    Question


    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

  2. #2
    Join Date
    Apr 2001
    Location
    London
    Posts
    725
    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.
    Once you have eliminated all of the impossible,
    whatever remains however improbable,
    must be true.

  3. #3
    Join Date
    Jul 2000
    Posts
    41
    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.

  4. #4
    Join Date
    Apr 2001
    Location
    London
    Posts
    725
    That's strange,

    Can this same user see all of these objects when connected through sql*plus.
    Once you have eliminated all of the impossible,
    whatever remains however improbable,
    must be true.

  5. #5
    Join Date
    Jul 2000
    Posts
    41
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width