Re: MS Access, Oracle 9i, security, and pass-thru update queries

Tim Marshall wrote:
> DFS wrote:
>
>> Since the pass-thru queries need Admin priveleges, I could/would
>> like to store the Admin password with the query. But that's
>> Security Breach #1, since you can import the query into another .mdb
>> (even from the .mde), and see the Admin password.
>
> Then use a temporary query (see air code example, below).

I have done those temp pass-thru queries, many times in the past. But it's just so kludgey I was wondering if anybody has smoother solutions to storing and executing pass-thrus, but still protecting the queries. I guess the temp queries is the way to go.

> You can
> save the admin password in VBA code as part of the ODBC connect
> string - I would save it as a constant in a standard module. Get the
> connect
> string expression from the connect property of any saved PTQ you
> currently have. For example, in a module called Mod_Constants (watch
> for wrap):
>
> Option Compare Database
> Option Explicit
>
> '*******Connect String*************
>
> Public Const cUserOracleConnect =
>

"ODBC;DSN=XXX;UID=USER_NAME;PWD=<WhateverYourPasswordIs>;DBQ=tma;DBA=W;APA=T ;PFC=1;TLO=0;DATABASE="
>
> If this app is distributed as an mde, you wouldn't need to worry about
> this getting out.

True, but if you open an .mdb file you can import queries from .mde files (tables too). Depending on security settings in place.

> You're scuppered if it's to be issued as an mdb, though.

> If the tables to which updates are being performed by non-admin people
> are limited, ie, not all the tables in the database, it might also be
> advisable to consider a new user with grant update on TABLE_NAME to
> UserName and use THAT in the constant above. That way, damage is
> limited if, for whataever reason, the user password gets out and you
> don't need to worry about the Admins.

Not a bad idea.

> Dealing with your actual question,
>
> Here's an example of a temporary query using DAO methods (air code):

<snip example>

Thanks Tim Received on Thu Oct 13 2005 - 16:19:39 CDT