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

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). 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. 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.

Dealing with your actual question,

Here's an example of a temporary query using DAO methods (air code):

Function fUpdateWhatever(strS as string) as Boolean

'This function creates a temporary PTQ that is executed
'And then discarded. It will never be saved.
'
'Called by something like:
' If fUpdateWhatever(Oracle Update SQL) = True then
'if False is returned, the calling procedure will
'know the update did not occur.
'
'strS is the Oracle SQL update or other action statement
'Develop strS in the calling sub/function and pass here
'I include a database variable here, though I always prefer
'to use the David Fenton dbLocal database variable function
'to avoid clean-up

   dim dbs as DAO.Database
   dim qdf as DAO.QueryDef

   On Error GoTo Err_Proc

   'Set value of this function to true. Any irrecoverable    'errors will st value to false.

   fUpdateWhatever = True

   set dbs = access.CurrentDb

   set qdf = dbs.CreateQueryDef ("")

   'The empty string => life of the query is for the duration of this
   'procedure only - it won't be saved as a query on the database
   'window, ie, it cannot be exported.

   with qdf

     'Connect string constant defined above

     .connect = cUserOracleConnect

     'Give the temp Query the Oracle SQL

     .SQL = strS

     'The following is necessary or Access will
     'throw an error/advisory

     .ReturnsRecords = False

     'Execute the SQL - I don't include error
     'Handling, though you can trap Oracle errors
     'and not just Access errors

     .Execute, dbFailOnError

     .Close

   end with

Exit_Proc:

   Set qdf = Nothing

   dbs.close

   Set Dbs = nothing

   Exit Function

Err_Proc:

   'Error handling - set fUpdateWhatever = False if    'error cannot be recovered

   ....

End Function

-- 
Tim    http://www.ucs.mun.ca/~tmarshal/
^o<
/#) "Burp-beep, burp-beep, burp-beep?" - Quaker Jake
/^^ "Whatcha doin?" - Ditto  "TIM-MAY!!" - Me