473,243 Members | 1,850 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,243 software developers and data experts.

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

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.
Jun 27 '08 #1
7 4620
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
Jun 27 '08 #2
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 --
Jun 27 '08 #3
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'.
Jun 27 '08 #4
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
Jun 27 '08 #5
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
Jun 27 '08 #6
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
Jun 27 '08 #7
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
Jun 27 '08 #8

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

1
by: Jason Gleason | last post by:
I am using the following method in a web service that utilizes the system.directoryservices namespace: public ArrayList GetAllAppPools(){ System.DirectoryServices.DirectoryEntry apppools = new...
5
by: Nathan Sokalski | last post by:
I am trying to learn how to use the System.Timers.Timer control to perform an action every certain amount of time. However, the Elapsed event doesn't want to fire, but I can't figure out why. I...
3
by: Rudy | last post by:
I am writing a program in VB.NET and as I was debugging a problem I noticed my For loop doesn't want to loop! I originally had a upper bound which was an expression and it wasn't working. WHen I...
89
by: Homer J Simpson | last post by:
I am coming to the conclusion that Microsoft doesn't want you to use VB ..Net, based on my experiences. I've downloaded the Express version and signed up for various support options etc. At every...
4
by: Eric Lilja | last post by:
Is this an invalid program? Doesn't compile on my system: #include <cstdio> class Why { enum TArch {LITTLE_ENDIAN, BIG_ENDIAN, NON_IEEE}; TArch Architecture; }; int
1
by: awesomewebsitesforyou | last post by:
Learn DB2 system catalog tables http://db2examples.googlepages.com/systemcatalogtables
4
by: xperre van wilrijk | last post by:
Hi, I have inherited an access userinterface that links to sql server tables through ODBC. The SQL server database contains data related to villages in my country and is populated by my...
0
by: fareedcanada | last post by:
Hello I am trying to split number on their count. suppose i have 121314151617 (12cnt) then number should be split like 12,13,14,15,16,17 and if 11314151617 (11cnt) then should be split like...
0
by: stefan129 | last post by:
Hey forum members, I'm exploring options for SSL certificates for multiple domains. Has anyone had experience with multi-domain SSL certificates? Any recommendations on reliable providers or specific...
1
by: davi5007 | last post by:
Hi, Basically, I am trying to automate a field named TraceabilityNo into a web page from an access form. I've got the serial held in the variable strSearchString. How can I get this into the...
0
by: MeoLessi9 | last post by:
I have VirtualBox installed on Windows 11 and now I would like to install Kali on a virtual machine. However, on the official website, I see two options: "Installer images" and "Virtual machines"....
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: Aftab Ahmad | last post by:
Hello Experts! I have written a code in MS Access for a cmd called "WhatsApp Message" to open WhatsApp using that very code but the problem is that it gives a popup message everytime I clicked on...
0
by: Aftab Ahmad | last post by:
So, I have written a code for a cmd called "Send WhatsApp Message" to open and send WhatsApp messaage. The code is given below. Dim IE As Object Set IE =...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.