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

[Gary]

Hello guys!

Bear with me, I am a newbie.

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?",
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!

Anyone got this kind of experience? I am pretty sure it is how M$
Access works when it builds the table list. I am trying to do some
investigation for M$ Access to see whether I can use some .ini file to
block those system views in the table list in Access.

Any input is appreciated.

[Billy Verreynne]

ro************@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>

<sighAs 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

[Mark D Powell]

ro************@yahoo.com.au (Gary) wrote in message news:<17*************************@posting.google.c om>...

Hello guys!

Bear with me, I am a newbie.

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?",
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!

Anyone got this kind of experience? I am pretty sure it is how M$
Access works when it builds the table list. I am trying to do some
investigation for M$ Access to see whether I can use some .ini file to
block those system views in the table list in Access.

Any input is appreciated.

Gary, I do not know if you can remove the Oracle dictionary views from
ODBC users because I have traced ODBC connections in the past and the
connections issue queries against the dictionary views to build SQL
statements with. That is the actual SQL in the application just
queries tables but behind the scenes it appeared the ODBC queried
Oracle to find out column information like datatype etc....

HTH -- Mark D Powell --

[Ryan Gaffuri]

ro************@yahoo.com.au (Gary) wrote in message news:<17*************************@posting.google.c om>...

Hello guys!

Bear with me, I am a newbie.

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?",
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!

Anyone got this kind of experience? I am pretty sure it is how M$
Access works when it builds the table list. I am trying to do some
investigation for M$ Access to see whether I can use some .ini file to
block those system views in the table list in Access.

Any input is appreciated.

Your datawarehouse manager is an idiot. Its not your fault your new at
this. Oracle != SQL Server. Different architecture. Get her fired.

OK if you cant do that, you need to rethink your design. Access NEEDS
the system views to build lists of the tables you need right? You dont
want the specific users themselves to have access to those views. Now
is this an oracle question or an Access question(I dont know Access).
Oracle is providing the Access account select priviledges on the
views. Access needs these priviledges to do what it needs to do.
Therefore Access must block select priviledges on these views. All you
can do with Oracle is go, yes you can see them or no you cannot. If
yes, then its up to the client.

If Access cant block it, then your stuck.

Your DW warehouse manager will want to go back to SQL Server because
its 'superior'.

[Gary]

rg******@cox.net (Ryan Gaffuri) wrote in message news:<1e**************************@posting.google. com>...

ro************@yahoo.com.au (Gary) wrote in message news:<17*************************@posting.google.c om>...

Hello guys!

Bear with me, I am a newbie.

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?",
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!

Anyone got this kind of experience? I am pretty sure it is how M$
Access works when it builds the table list. I am trying to do some
investigation for M$ Access to see whether I can use some .ini file to
block those system views in the table list in Access.

Any input is appreciated.

Your datawarehouse manager is an idiot. Its not your fault your new at
this. Oracle != SQL Server. Different architecture. Get her fired.

OK if you cant do that, you need to rethink your design. Access NEEDS
the system views to build lists of the tables you need right? You dont
want the specific users themselves to have access to those views. Now
is this an oracle question or an Access question(I dont know Access).
Oracle is providing the Access account select priviledges on the
views. Access needs these priviledges to do what it needs to do.
Therefore Access must block select priviledges on these views. All you
can do with Oracle is go, yes you can see them or no you cannot. If
yes, then its up to the client.

If Access cant block it, then your stuck.

Your DW warehouse manager will want to go back to SQL Server because
its 'superior'.

Ryan, thanks for this.

I can't agree with you more. Yes I am stuck but I am trying to fake it
by using some Local views (as Billy suggested) for the users 'cause I
don't really want to waste time arguing with her. I was her M$SQL DBA
and she doesn't know much about M$SQL anyway.

Cheers,

Gary

[Gary]

Ma*********@eds.com (Mark D Powell) wrote in message news:<26**************************@posting.google. com>...

ro************@yahoo.com.au (Gary) wrote in message news:<17*************************@posting.google.c om>...

Hello guys!

Bear with me, I am a newbie.

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?",
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!

Anyone got this kind of experience? I am pretty sure it is how M$
Access works when it builds the table list. I am trying to do some
investigation for M$ Access to see whether I can use some .ini file to
block those system views in the table list in Access.

Any input is appreciated.

Gary, I do not know if you can remove the Oracle dictionary views from
ODBC users because I have traced ODBC connections in the past and the
connections issue queries against the dictionary views to build SQL
statements with. That is the actual SQL in the application just
queries tables but behind the scenes it appeared the ODBC queried
Oracle to find out column information like datatype etc....

HTH -- Mark D Powell --

Mark, Thank you for you input.

Sure Access uses some of Oracle system views (I am trying to find them
ALL!) to build its own meta info for the existing session. In old
version of Access you can modify "AttachableObjects" method to bring
up some views the users CAN't see, but I still don't know how to make
those views invisible for the users via Access as a ODBC connection.
So I need to do something on server side to fake it.

As I said, it is not a real technical problem but rather a fussy
requirement from a layman. I am taking this as a task to help myself
to understand Oracle more.

Cheers,

Gary

[Gary]

vs****@onwe.co.za (Billy Verreynne) wrote in message news:<1a**************************@posting.google. com>...

ro************@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>

<sighAs 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

[Gary]

rg******@cox.net (Ryan Gaffuri) wrote in message news:<1e**************************@posting.google. com>...

ro************@yahoo.com.au (Gary) wrote in message news:<17**************************@posting.google. com>...

rg******@cox.net (Ryan Gaffuri) wrote in message news:<1e**************************@posting.google. com>...

ro************@yahoo.com.au (Gary) wrote in message news:<17*************************@posting.google.c om>...
Hello guys!

Bear with me, I am a newbie.

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?",
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!

Anyone got this kind of experience? I am pretty sure it is how M$
Access works when it builds the table list. I am trying to do some
investigation for M$ Access to see whether I can use some .ini file to
block those system views in the table list in Access.

Any input is appreciated.
>
>
Your datawarehouse manager is an idiot. Its not your fault your new at
this. Oracle != SQL Server. Different architecture. Get her fired.
>
OK if you cant do that, you need to rethink your design. Access NEEDS
the system views to build lists of the tables you need right? You dont
want the specific users themselves to have access to those views. Now
is this an oracle question or an Access question(I dont know Access).
Oracle is providing the Access account select priviledges on the
views. Access needs these priviledges to do what it needs to do.
Therefore Access must block select priviledges on these views. All you
can do with Oracle is go, yes you can see them or no you cannot. If
yes, then its up to the client.
>
If Access cant block it, then your stuck.
>
Your DW warehouse manager will want to go back to SQL Server because
its 'superior'.

Ryan, thanks for this.

I can't agree with you more. Yes I am stuck but I am trying to fake it
by using some Local views (as Billy suggested) for the users 'cause I
don't really want to waste time arguing with her. I was her M$SQL DBA
and she doesn't know much about M$SQL anyway.

Cheers,

Gary

Im not attacking SQL Server. It may well be a viable platform what you
want to do. Its my opinion that a 'manager' who is getting payed
should be held to a higher standard. She is acting like a functional
user with no technical understanding whatsoever. Her response is what
you expect from your users. Which is acceptable from them since they
just want it to work.

Doesnt it suck when you have to train your boss? Been there.

Ryan,

what she wants is exactly to "make it work" as a average user wants. I
don't think she even gives a sh** about the technical stuff. And it
does suck when you are in a position of either educating THEM or
faking it. Well, what can I do! You might disagree, but I do believe
the more technical you are the less possibility you have got to be a
manager, BECAUSE you care and you have to
as you don't have scapegoat anywhere whatsoever.

I don't think you were attacking SQL server. I have been M$SQL DBA for
7 years while I have been also coding for the front end. To be honest,
I like it and I am still maintaining our SQL servers. Besides all
those well-known disadvantages of M$SQL compared with Oracle, the
major difference to me is Oracle is more manageable. I have been a
lazy DBA for these years ( You are not as busy as a Oracle DBA when
you use M$SQL) and I am trying to pick up a bit daily.

I appreciate your input. It is really nice to have some experienced
and nice guys here to help you.

Cheers,

Gary