Linked Tables and Server Passwords

Posted on 2009-12-16
Medium Priority
Last Modified: 2013-11-29

I'm doing some "brainstorming" about linked tables and passwords. The context: I'm porting an Access database to Oracle, keeping Access as the font-end for the time being. Write access to Oracle tables it granted by user name, read access is allowed using a common "read-only" user name.

I need to connect to the Oracle database through code. I have created a set of modules that will request the user name and password the first time the connection is used. That is the easy part.

I would also like to use bound forms. This requires linked tables, which will not contain any password, or perhaps the "read-only" user name and password. That way, all forms would be available in read-only mode directly. When linked tables are stored without user name and password, Access will ask for that information, but only once. I found no way to verify the "connect state" of tables, or of the database engine. Apparently, the connection remains open during the entire session, and the password is never asked again.

This makes it difficult to use linked tables.

Some of the ideas I was playing with:

* Always create the record source of forms from code. (Problem: you can't use link fields or other automated linking tools).

* Create the linked tables after asking for the user name and password. (Problem: if the application doesn't end normally, the tables with the embedded passwords remain in the front-end, a potential risk.)

* Use "anonymous" links and open one table through code, triggering the "user name and password" prompt once. (Problem: my own code will have to do the same, so the user would have to enter the information twice, with the potential risk or having desynchronized users between tables and code).

My question:

How do you manage passwords and linked tables? The problem isn't specific to Oracle, and I'm certain many developers have asked themselves these questions before. What are the standard solutions?

Note: this is NOT a high-security environment. We only want to maintain an audit trail of modifications, so the user identification is mandatory. We do not anticipate hacking or identity theft. The goal is to make is simple to connect and start working. However, no password should be stored anywhere outside of Oracle.

Question by:harfang
  • 6
  • 3
LVL 28

Accepted Solution

omgang earned 1332 total points
ID: 26065316
I'm in the middle of a project where I've developed an Access front end connecting to MS SQL db's.  I have a number of processes that switch which db is currently linked and other processes that establish temporary table links for the purposes of updating data between the SQL db's.  I'm using integrated Windows authentication so I don't have the issue with password storage.

A couple of low-tech (inelegant?) thoughts for you though:
create DSNs for each user on their machine (same DSN name) and create the links using the DSN
execute a procedure at both app quit and app launch to set all linked table connection strings to use the common ReadOnly username (I know this still allows for the possibility of a user password to be present if the app exits non-gracefully and the app is launched in a manner to bypass the startup process to change existing links).

Just throwing some ideas out there.
OM Gang

Assisted Solution

by:Rog D
Rog D earned 668 total points
ID: 26066792
I would  look into DNSLESS connections for your linked tables...  Below you will find some code examples of how to do this...
The code in the example was with SQL Server, but you should be able to do something similar to Oracle.

Here is a link to a specific article on Oracle...
Public Const strConnDev As String = "Your Specific COnnection String Here."

Function AttachDSNLessTable(stLocalTableName As String, stRemoteTableName As String, stConnect As String)
    On Error GoTo AttachDSNLessTable_Err
    Dim td As TableDef
    For Each td In CurrentDb.TableDefs
        If td.Name = stLocalTableName Then
            CurrentDb.TableDefs.Delete stLocalTableName
        End If
'    If Len(stUsername) = 0 Then
'        '//Use trusted authentication if stUsername is not supplied.
'        stConnect = "ODBC;DRIVER=SQL Server;SERVER=" & stServer & ";DATABASE=" & stDatabase & ";Trusted_Connection=Yes"
'    Else
'        '//WARNING: This will save the username and the password with the linked table information.
'        stConnect = "ODBC;DRIVER=SQL Server;SERVER=" & stServer & ";DATABASE=" & stDatabase & ";UID=" & stUsername & ";PWD=" & stPassword
'    End If
    Set td = CurrentDb.CreateTableDef(stLocalTableName, dbAttachSavePWD, stRemoteTableName, stConnect)
    CurrentDb.TableDefs.Append td
    AttachDSNLessTable = True
    Exit Function

    AttachDSNLessTable = False
    MsgBox "AttachDSNLessTable encountered an unexpected error: " & Err.Description

End Function

'Here is how to call it
'AttachDSNLessTable ("authors", "authors", "(local)", "pubs", "", "")

'get list of tables
Public Sub getAllTables()
   Dim db As Database, tbl As TableDef, fld As Field
   Set db = CurrentDb
   For Each tbl In db.TableDefs
     If Left$(tbl.Name, 4) <> "MSys" Then
       Debug.Print tbl.Name & "      " & tbl.DateCreated & "      " & _
        tbl.LastUpdated & "     " & tbl.RecordCount
        'db.Execute "Insert into tblList (TableName) values('" & tbl.Name & "')"
       ' optional code to print all the fields
         'For Each fld In tbl.Fields
         '   Debug.Print fld.Name
         'Next fld
     End If
   Next tbl
End Sub

Public Sub Test1()
   ConnectOneTable "IssueAttachments", "IssueAttachments"
End Sub

Public Sub ConnectOneTable(sLocaltableName As String, sRemoteTable As String)
Dim bResult As Boolean
  bResult = AttachDSNLessTable(sLocaltableName, sRemoteTable, strConnDev)
  If bResult Then
    MsgBox "Linked " & sLocaltableName & " from " & sRemoteTable
    MsgBox "Error Linking Table: " & sRemoteTable
  End If
End Sub

Public Sub ConnectAlltables()

On Error GoTo Error_Handler
Dim db As Database
Dim rs As Recordset
Dim rs2 As Recordset
Dim sTableName As String
Dim scolumn As String
Dim b As Boolean

Set db = CurrentDb
Set rs = db.OpenRecordset("Select * from qryTableList")

If Not (rs.EOF And rs.BOF) Then
  Do While Not rs.EOF
    If InStr(1, rs("Name"), "Old") < 1 Then
       sTableName = Replace(rs("Name"), "dbo_", "")
          b = AttachDSNLessTable(sTableName, sTableName, strConnDev)
          sSql = "Select * from " & sTableName
          Set rs2 = db.OpenRecordset(sSql, dbOpenForwardOnly)
          scolumn = rs2(0).Name
          CreateIndex sTableName, scolumn
      End If
     Debug.Print sTableName
End If
Exit Sub

  MsgBox (Err.Number & " " & Err.Description)
End Sub

Public Sub CreateIndex(sTableName As String, sField As String)

On Error GoTo ErrorHandler

Dim idx As String
Dim sSql As String

idx = sTableName & "idx"

sSql = "Create Unique Index " & idx & " on " & sTableName & "(" & sField & ");"
'Call CurrentDb.Execute("CREATE UNIQUE INDEX NameIDX ON myTable (field1, field2);")
CurrentDb.Execute (sSql)
  Exit Sub
  MsgBox Err.Number & " " & Err.Description
End Sub

Open in new window

LVL 58

Author Comment

ID: 26067730
This all seems to point to recreating the table links at startup, or rather replacing the "read-only" versions with "authenticated" versions. I can live with that.

I did notice that Jet remembers the password of a connection for the duration of the session. I guess that "closing the session" will equate "closing the front-end". I don't believe there is a way to reset the internal connection objects (those used for linked tables).

One question to Rog: why do you call this "DNS-less"? I might have everything mixed up, but when your connection string says "SERVER=ABC", doesn't that imply some translation of "ABC", hence some DNS involvement?

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

LVL 28

Expert Comment

ID: 26067750
I believe Rog meant to say DSNless, i.e. not utilizing a DSN for the connection info.

OM Gang
LVL 58

Author Comment

ID: 26067753
You caught me! I wanted to write DSN (and not DNS) both times. Ah, dyslexia...

Since I'm at it, all machines in the service have the correct DSN already created (I'm adding new tables to an existing system, which works fine already, but which isn't using Access as front-end).

LVL 58

Author Comment

ID: 26067786
Re-reading. What is the "DSN execute a procedure"? Perhaps some method I missed. I know about creating linked tables providing a connection string (and creating queries with a connection string for that matter). Is this something different?

LVL 58

Author Comment

ID: 26067949
Sorry, disregard the last comment. I must be getting tired. -- (°v°)
LVL 28

Assisted Solution

omgang earned 1332 total points
ID: 26067954
Sorry, those two lines should be seperated.
My first thought is you could use DSN's on each user computer.  The connection info. for the Oracle db and the user credentials will be stored in the DSN and not in the Access front end.

My other thought is to write a procedure to set the connection string for all linked tables using the standard ReadOnly user account.  If you call this procedure at app launch and at app quit then the linked tables will not be saved with the user specific credentials.  You'd use a different procedure (or the same one with input variables) to change the linked table connection strings to use the user specific credentials as needed at run-time.  This does not prevent the possibility of the linked Oracle tables retaining the priveledged connection credentials in the event of the Access app exiting ungracefully but you mentioned you're not in a high-security environment.  Running the procedure at app launch (to relink the tables with the ReadOnly credentials) will clear out the priveleged credentials if the app was closed ungracefully previously.  Of course, there are simple methods to open the Access app without launching startup macros or processes so this method is not fullproof.

Another thought is to capture the users credentials into a public variable.  When necessary to change the link for a table to read/write you could then run the refresh link procedure using the value in the public variable.  You'd want to promptly swith the linked table connection string back to the ReadOnly credentials as soon the read/write link was no longer necessary.  Still, if the app exited ungracefully the linked table(s) would retain their last connection stiring settings.  Also a run-time error will destroy the public variable so you'd need to make sure the error handling was robust (or make sure you had a procedure to reprompt the user for their credentials if the value of the public variable returned "").

These are just some thoughts I've had for your Q.  In the app I'm working on now I have a public function to change linked table connection string for all tables or to link a table from a specific db (based upon input values by the calling procedure).  Since I am using integrated authentication I can store the connection strings in a lookup table without user credentials.  Wish I could be more help.

OM Gang
LVL 58

Author Comment

ID: 26068098
> Wish I could be more help.

Don't worry, this is helping a lot. I knew I wasn't the only one trying to find elegant solutions to this problem, and I was mainly checking to see if I had overlooked something obvious, and using this thread for "brainstorming".

You gave me an idea which I will try first thing tomorrow (well, after the stuff in my other question, http:/Q_24983664.html, which is driving me nuts!).

If I create a new linked table with user name and password, then open it trough code to read one record, Jet will store that connection. I should then be able to create new table links *without* the password. When used during the session, the open connection is used (not asking for the password again), but if the application is closed, the user would be prompted for it...

I could have mentioned the full background: the database is searchable at http://www.ville-ge.ch/musinfo/bd/cjb/africa/ and the source data is being ported from Access to the in-house botanical database on Oracle, hosting all other projects of the Botanical Garden.

It's all public data, entered by scientists, we only need traceability of edits and validations...

LVL 58

Author Closing Comment

ID: 32837914
Sorry, I went off-line for a couple of months. Thanks for the answers; I will tackle this problem again, and perhaps open a new question then.

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
Sometimes MS breaks things just for fun... In Access 2003, only the maximum allowable SQL string length could cause problems as you built a recordset. Now, when using string data in a WHERE clause, the 'identifier' maximum is 128 characters. So, …
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …
Suggested Courses

850 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question