Re: She doesn't want the ODBC users to see system views/tables via M$ Access.

rooty_hill2002_at_yahoo.com.au (Gary) wrote

> She is the Data Warehouse manager. She has about 50 users to use the
> Oracle database from M$ Access via ODBC connection. All those users
> have only SELECT privileges on certain tables. I built all the roles
> and users for them and they work fine.
>
> Then she asked "Why do YOU let them see all those system tables?",
<snipped>

<sigh> As if MS Access is the type of front-end one can use for a Data Warehouse.

I wanted to say "some years ago" and then realised it is actually "many years ago". Am getting old. <double sigh>

Anyway, many years ago I had also had an MS Access user on a warehouse. She insisted on working "directly" with the data. And was very upset when I implemented a limit on the number of bytes that users could pull down in a session (the query tools were all configured for one-query-one-session which is a good thing). The data was pretty sensitive too.

She was trying to pull down the 170+ million fact table (considered VLT back then) into MS Access when the bytes-to-client limit kicked in.

When queried, she responsed with "I have over 20MB of free space for a Access MDB on my notebook. So what is your problem!".

> I replied:
> 1. First of all they are not tables, most of them are views;
> 2. Your users only have select privileges on them; (I have done my
> best to limit the privileges user PUBLIC has. I only give it select on
> ALL_ views. Maybe I can do more about it, I just don't know how.)
> 3. M$ Access needs these views to build its own environment;
>
> She went "Users didn't see them when we used Sql server, why should
> them see now?"
>
> Bloody hell!

Yeah.. I suggest that you fake it. Determine what SQLs MS Access runs when connecting. The easiest would be to create a db session trigger and use DBMS_SYSTEM.SET_SQL_TRACE_IN_SESSION(sid, serial#, TRUE ) to see what MS Access is throwing server side for dictionary access.

Likely, it is hitting the ALL_TABLES or ALL_OBJECTS views/synonyms of the Oracle data dictionary. (the clever ODBC driver will do that without the ODBC client needing to know the actual dictionary table names).

Now for the dirty hack. Create a LOCAL view in said Oracle Schemas used for logging on via MS Access and filter the stuff out that Ms. Warehouse do not want her users to see.

The way I understand Oracle's scope declaration, the ODBC client will query that local view instead of the public view/synonym.

--
Billy