Safely use passwords in R files - prevent them to be stored as plain text

user_5795592 0
Viewed 671

I am wondering if there is a package or a solution to, in my opinion, a very common issue. In most cases, when using R to access databases one has to provide the ODBC driver with the combination of user and password. For example a pretty common R script in this case will look like this:

 library(DBI)
 rodbc <- DBI::dbConnect(RODBCDBI::ODBC()
                   , dsn = "DSN0123"
                   , user = "user"
                   , password = "pass" )

I would like to know if there's an automatic way around the fact that the user/password combination resides on the filesystem in plain text. Of course I could remove the combination manually, but this is pretty tedious. Perhaps there's also a package which allows me to get prompted for the password, whenever I first access the database.

rodbc r security
1 Answers

One solution is to use the keyringr package and use it as following. I adapted the Howto on the CRAN page. This is the solution for a Windows machine. First of all one has to create a small powershell script PasswordEncryption.ps1: 

# Create directory user profile if it doesn't already exist.
$passwordDir = "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")

After the execution of this script and the entering of the label and the password one now can use the encrypted password in R.

library(keyringr)
credential_label <- "MYDB_MYUSER"
credential_path <- paste( getwd(),'\\DPAPI\\passwords\\', Sys.info()["nodename"], '\\', credential_label, '.txt', sep="")
my_pwd <- decrypt_dpapi_pw(credential_path)
print(my_pwd)

Or better just add the call to decrypt the password directly to the ODBC command and don't store it in the R environment.

library(DBI)
rodbc <- DBI::dbConnect(RODBCDBI::ODBC()
                   , dsn = "DSN0123"
                   , user = "user"
                   , password = decrypt_dpapi_pw(credential_path))

Edit: An alternative, when using rstudio is to use the rstudioapi, like this:

rodbc <- DBI::dbConnect(RODBCDBI::ODBC()
                   , dsn = "DSN0123"
                   , user = rstudioapi::askForPassword("Database user")
                   , password = rstudioapi::askForPassword("Database password")
user_57955920
Related