How to Prevent Exposure of My Password When Using Rgoogledocs

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.

RGoogleDocs authentication failure

RGoogleDocs is a poorly-supported hack (and you can quote me on that), but it's the only tool for the job, sadly. I suspect you're using version 0.4-0, the latest version on the OmegaHat web page, right? Well, for access to the content of Google Spreadsheets, you actually need 0.4-1, which is available at this URL: http://www.omegahat.org/RGoogleDocs/RGoogleDocs_0.4-1.tar.gz.

That may work. There are some issues, though. I ran across one where a feature in the Google Docs API, to increase the number of rows in a spreadsheet, is not exposed via this package. So you can't add content beyond the number of existing rows, except by manually logging in and clicking the "add rows" button.

If you have the time and resources to fork the RGoogleDocs package and update it to use the current version of the API, including all features, I will seriously buy you some damn good beer if you're ever in NYC!

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.

RGoogleDocs Token Invalid Error

As a solution to my own problem in case anyone else needs a workaround as well, I ended up using googlecl (https://code.google.com/p/googlecl/) and just executed a system call in R to download the spreadsheet I needed. Finally I read it into R using the XLConnect library.

---

Related Topics