Encrypt Password in R - to Connect to an Oracle Db Using Rodbc

Encrypt password in R - to connect to an Oracle DB using RODBC

EDIT: The below functionality is now available in my R package keyringr. The keyringr package also has similar functions to access the Gnome Keyring and macOS Keychain.

---

If you are using Windows you can use PowerShell to do this. See my blog post below.

http://www.gilfillan.space/2016/04/21/Using-PowerShell-and-DPAPI-to-securely-mask-passwords-in-R-scripts/

Essentially...

1. Ensure you have enabled PowerShell execution.

2. Save the following text into a file called EncryptPassword.ps1:

   # Create directory user profile if it doesn't already exist.
   $passwordDir = "$($env:USERPROFILE)\DPAPI\passwords\$($env:computername)"
   New-Item -ItemType Directory -Force -Path $passwordDir
   
   # Prompt for password to encrypt
   $account = Read-Host "Please enter a label for the text to encrypt.  This will be how you refer to the password in R.  eg. MYDB_MYUSER
   $SecurePassword = Read-Host -AsSecureString  "Enter password" | convertfrom-securestring | out-file "$($passwordDir)\$($account).txt"
   
   # Check output and press any key to exit
   Write-Host "Press any key to continue..."
   $x = $host.UI.RawUI.ReadKey("NoEcho,IncludeKeyDown")
   

3. Execute the script above (right click > Run with PowerShell), provide a meaningful name for the password, and type in the password. You can now verify that the password has been encrypted by checking the file in %USERPROFILE%/DPAPI/passwords/[PC NAME]/[PASSWORD IDENTIFIER.txt]

4. Now run the following code from within R (I have this function saved in an R script that I source at the start of each script.

   getEncryptedPassword <- function(credential_label, credential_path) {
     # if path not supplied, use %USER_PROFILE%\DPAPI\passwords\computername\credential_label.txt as default
     if (missing(credential_path)) {
       credential_path <- paste(Sys.getenv("USERPROFILE"), '\\DPAPI\\passwords\\', Sys.info()["nodename"], '\\', credential_label, '.txt', sep="")
     }
     # construct command
     command <- paste('powershell -command "$PlainPassword = Get-Content ', credential_path, '; $SecurePassword = ConvertTo-SecureString $PlainPassword; $BSTR = [System.Runtime.InteropServices.Marshal]::SecureStringToBSTR($SecurePassword); $UnsecurePassword = [System.Runtime.InteropServices.Marshal]::PtrToStringAuto($BSTR); echo $UnsecurePassword"', sep='')
     # execute powershell and return command
     return(system(command, intern=TRUE))
   }
   

5. Now when you need to supply a password in R, you can run the following command instead of hardcoding / prompting for the password:

   getEncryptedPassword("[PASSWORD IDENTIFIER]")
   

   For example, instead of running the ROracle command:

   dbConnect(driver, "MYUSER", "MY PASSWORD", dbname="MYDB")
   

   You can run this instead (the identifier I supplied in Step 3 is "MYUSER_MYDB":

   dbConnect(driver, "MYUSER", getEncryptedPassword("MYUSER_MYDB"), dbname="MYDB")
   

6. You can repeat Step 3 for as many passwords as are required, and simply call them with the correct identifier in Step 5.

How do I input username and password in R securely without details showing up in console?

Have you tried creating a secure folder and putting a text file in it with your logon credentials?
You could just call that and reference the inputted variables in your connection string.

Just not sure if it will come up in the console when you connect or not..

Enter passwords interactively in R or R Studio (Server)?

Actually R Studio (Server) provides a nice solution. You can access it by using .rs functions. They provide an undocumented password function which is really nice though there's no guaranteed support forever and a day:

.rs.askForPassword("foo")

You can find the the original hint from RStudio's Josh here:
http://support.rstudio.org/help/discussions/questions/1448-password-interaction-with-dbs-on-rstudio-server

EDIT:
As of 2015 there is another nice solution to enter password interactively. You could use shiny to have a little web based window with a password form. This discussion I had with @hadley shows an example snippet: https://github.com/rstats-db/RPostgres/issues/26

EDIT:
As of 2017 there is another update to this. @m-dz pointed us to this:
rstudioapi::askForPassword("Enter your pw") as well as getPass::getPass() from the R Studio Support webpage. Plus, I also realized that there is .rs.api.askForPassword() which is equivalent to the initially suggested call - at least as far as I've seen.

How do I prevent exposure of my password when using RGoogleDocs?

My approach is to set the login-name & password in the R options list
within the R startup file .Rprofile. Then my code gets the value
with getOption() and then the value is never visible or stored
in a top-level variable in globalenv(). (It could be save if
one does post-mortem debugging via dump.frames).

It is vital that the .Rprofile cannot be read by anybody other than you.

So

options(GoogleDocsPassword = c(login = 'password'))

in the .Rprofile and then

auth = getGoogleAuth()

just works as the default value for the first parameter is to look for the GoogleDocsPassword option.

D.

shinyapps.io does not work when my shiny use RODBC to link a SQL database

The app works on your computer because the Data Source Name (DSN) has been configured there. It is not configured on shinyapps.io. According to this help article you can use for example

odbcDriverConnect('Driver=FreeTDS;TDS_Version=7.0;Server=<server>;Port=<port>;Database=<db>;Uid=<uid>;Pwd=<pw>;Encrypt=yes;TrustServerCertificate=no;Connection Timeout=30;')

A more complete treatment can be found in the documentation.

---

Related Topics