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: DBIrequire(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
