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

vslabs_at_onwe.co.za (Billy Verreynne) wrote in message news:<1a75df45.0309120318.5e742d45_at_posting.google.com>...
> 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, thank you very much for this.

I totally agree with you and I have done some quick and dirty stuff on OEM to find out the sqls fired up by the ODBC connection used by certain Access session, but apparently I failed in collecting the whole information which that connection needs to build meta info for Access in its session (Looks like it is more than ALL_TABLES or ALL_OBJECTS views/synonyms). I will try as you suggested.

Facing some harsh INDIVIDUAL like this, I am trying to think in positive way. At least I can understand Oracle more by doing this.

BTW, when I create LOCAL view, you mean I need to create them under schema of every single user? If I have 50 users I have to create 50 schemas only for this? It is expensive, is it? I will try anyway.

Thank you again.

Gary Received on Mon Sep 15 2003 - 04:05:45 CEST