How can I access an Oracle database via ODBC from R without making the password public?

Question

This question is based on my attempt to create an application in R that can pull data in from a remote Oracle database via ODBC, but I doubt the answers will be R specific.

I am trying to create an application that several users will use (that I'll call Reporter). The application will pull data from a remote Oracle database that is used by a Corporate application (that I'll call CorpApp) via an ODBC link. The Reporter app will then process the data and produce reports automatically.

The CorpApp has it's own, built in user authentication, and it doesn't use Oracle user accounts to control access (I assume the CorpApp install includes one set of Oracle User credentials in some secret, binary location that it uses).

As R is a scripted language, at the moment the user credentials I use to authenticate to Oracle are either available in the code, or in a data file that R can understand. Currently I am planning that this app will be an R package, probably as an extension to RCommander.

Are there any good ways to ensure that;

1. Multiple users can use this application simply ?
2. People who aren't authentic users can't access the underlying data ?
3. Account details are sent in a secure fashion ?
4. The data in the database is secure ?

Answer 1

Another possibility is to use an environment variable which your R scripts read via Sys.getenv() and then insert into the connection string.

Answer 2

Assuming this is from Windows, you can set up the DSN in the Data Sources to store the password information. Also you can allow the user to provide the details at runtime - if they are required by odbcConnect the authentication dialog from the Data Sources set up will come up.

Those options aren't very advanced and they require transferring your questions about security to the operating system, but might be helpful if you've not considered them already.