Linked Tables and Server Passwords
Posted on 2009-12-16
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).
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.