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

Question

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.

hannes101 · Accepted Answer · 2023-02-23T07:12:34.567

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 username")
                   , password = rstudioapi::askForPassword("Database password")

Thank you! It's a really clean solution, and an improvement on ```Sys.getenv()```. The encryption layer is really neat. — James, Apr 01 '22 at 08:32
This really is fantastic. There's so many packages out there, it's hard to keep track of all the capabilities. I'd give you more than one upvote if I could! — icj, Feb 22 '23 at 17:40
Thank you, just looking at the question again I came across the following https://stackoverflow.com/a/67480410/5795592 this is interesting, if you are using username in the input text, the input is unmasked. — hannes101, Feb 23 '23 at 07:13

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

1 Answers1