Manage MySQL Database Password Within R


RMySQL package allows us to talk with MySQL database in R, with the following code to build the connection:

conn <- dbConnect(MySQL(),
    user = as.character(UID),
    password = as.character(PWD),
    host = as.character(HOST),
    dbname = as.character(DBNAME))
dbDisconnect(conn)

The above code requires username passeword hostname etc. it turns to be really unsafe to leave these information in R code. I have this experience for several time, when I share my Rmarkdown slides to others, without deleting the R code.

So I had some time this week, so I tried to write a R function to manage the database passwords. I knew the unix-like system store some password in the ~/.netrc, which is very convinent. Similar to this, I managed the database passwords in ~/.dbrc.

The code shows below:

Main function

require(RMySQL)
## Loading required package: RMySQL
## Loading required package: DBI
require(tidyverse)
## Loading required package: tidyverse
## ── Attaching packages ─────────────────────────────────── tidyverse 1.3.0 ──
## ✔ ggplot2 3.3.2     ✔ purrr   0.3.4
## ✔ tibble  3.0.4     ✔ dplyr   1.0.2
## ✔ tidyr   1.1.2     ✔ stringr 1.4.0
## ✔ readr   1.3.1     ✔ forcats 0.5.0
## ── Conflicts ────────────────────────────────────── tidyverse_conflicts() ──
## ✖ dplyr::filter() masks stats::filter()
## ✖ dplyr::lag()    masks stats::lag()
#' create and read databse access file, so that the passwords are invisible in R
#' code
#'
#' create and read databse access file, so that the passwords are invisible in R
#' code; if no dbrc file exists in "$HOME/.dbrc", it will create one with user
#' input; if dbrc file exists, it will read the dbrc file and check if the
#' DBNAME's information stored or not, if YES, it will be loaded, otherwise, it
#' will ask user to create the DBNAME information
#'
#' @param dbname database name, this is the keyword for databases, should be
#' unique
#'
#' @return return the dbinfo as a dataframe, with DBNAME, HOST, UID, PWD
#'
#' @examples
#' getdbaccess(dbname = "weather")
#' getdbaccess("radiation")
#' getdbaccess("emtuser")

getdbaccess <- function (dbname) {
    dbrcpath <- file.path(Sys.getenv("HOME"), ".dbrc")
    if (!file.exists(dbrcpath)) {
        dbinfo <- adddbinfo(dbname)
        dbrc <- dbinfo
    } else {
        dbrc <- read.csv(dbrcpath)
        dbinfo <- dbrc %>% filter(DBNAME == dbname)
        if (nrow(dbinfo) == 0) {
            dbinfo <- adddbinfo(dbname)
            dbrc <- rbind(dbrc, dbinfo)
        } else if(nrow(dbinfo) > 1) {
            dbinfo <- dbrc %>% filter(DBNAME == dbname) %>% slice(1)
            dbrc <- dbrc %>% filter(DBNAME != dbname)
            write.csv(dbrc, file = dbrcpath, row.names = F, quote = F)
            testdbconn(dbinfo)
            dbrc <- rbind(dbrc, dbinfo)
        }
    }
    write.csv(dbrc, file = dbrcpath, row.names = F, quote = F)
    return(dbinfo)
}

Function to test the connection

#' test the database connection
#'
#' 1 paragraph detailed description of the function
#'
#' @param dbinfo a dataframe with DBNAME, HOST, UID, PWD
#'
#' @return success or error
#'
#' @examples
#' dbinfo <- data.frame(DBNAME = "test",
#'                      HOST = "159.226.240.69",
#'                      UID = "testuser",
#'                      PWD = "testpwd")
#' testdbconn(dbinfo)
testdbconn <- function (dbinfo) {
    conn <- dbConnect(MySQL(),
                      user = as.character(dbinfo$UID),
                      password = as.character(dbinfo$PWD),
                      host = as.character(dbinfo$HOST),
                      dbname = as.character(dbinfo$DBNAME))
    dbDisconnect(conn)
    return(TRUE)
}

Function to add the database information by reading user input

#' Add database information to $HOME/.dbrc
#'
#' Add database information to $HOME/.dbrc, by read the user input
#'
#' @param dbname the database name to be added
#'
#' @param dbinfo a dataframe with DBNAME, HOST, UID, PWD for the added dbname.
#'
#' @examples
#' adddbinfo(dbname = "test")

adddbinfo <- function (dbname) {
        host <- readline(prompt="Enter DATABASE HOST, e.g.:\nip: 159.226.240.69;\ndomin: dbhost.com;\n")
        message(paste("HOST:", host))
        uid <- readline(prompt="Enter UID, e.g.:\nreadweatdata;\n")
        message(paste("UID:", uid))
        pwd <- readline(prompt="Enter PASSWORD:\n")
        message(paste("pwd:", pwd))
        # test connection
        dbinfo <- data.frame(DBNAME = dbname,
                           HOST = host,
                           UID = uid,
                           PWD = pwd)
        testdbconn(dbinfo)
        print("Success")
        return(dbinfo)
}

This R source file can be found in my website

苏命
苏命
副研究员

我的研究方向:水源地水质安全保障,主要针对水源地由于藻类爆发引起的水体嗅味问题开展研究。