Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Protect user credentials when connecting R with databases using JDBC/ODBC drivers

Usually I connect to a database with R using JDBC/ODBC driver. A typical code would look like

library(RJDBC)
vDriver = JDBC(driverClass="com.vertica.jdbc.Driver", classPath="/home/Drivers/vertica-jdbc-7.0.1-0.jar")
vertica = dbConnect(vDriver, "jdbc:vertica://servername:5433/db", "username", "password")

I would like others to access the db using my credentials but I want to protect my username and password. So I plan save the above script as a "Connections.r" file and ask users to source this file.

source("/opt/mount1/Connections.r")

If I give execute only permission to Connections.r others cannot source the file

chmod 710 Connections.r

Only if I give read and execute permission R lets users to source it. If I give the read permission my credentials will be exposed. Is there anyways we could solve this by protecting user credentials?

like image 861
Jana Avatar asked Oct 08 '14 21:10

Jana


4 Answers

Unless you were to deeply obfuscate your credentials by making an Rcpp function or package that does the initial JDBC connection (which won't be trivial) one of your only lighter obfuscation mechanisms is to store your credentials in a file and have your sourced R script read them from the file, use them in the call and them rm them from the environment right after that call. That will still expose them, but not directly.

One other way, since the users have their own logins to RStudio Server, is to use Hadley's new secure package (a few of us sec folks are running it through it's paces), add the user keys and have your credentials stored encrypted but have your sourced R script auto-decrypt them. You'll still need to do the rm of any variables you use since they'll be part of environment if you don't.

A final way, since you're giving them access to the data anyway, is to use a separate set of credentials (the way you phrased the question it seems you're using your credentials for this) that only work in read-only mode to the databases & tables required for these analyses. That way, it doesn't matter if the creds leak since there's nothing "bad" that can be done with them.

Ultimately, I'm as confused as to why you can't just setup the users with read only permissions on the database side? That's what role-based access controls are for. It's administrative work, but it's absolutely the right thing to do.

like image 79
hrbrmstr Avatar answered Sep 22 '22 02:09

hrbrmstr


Do you want to give someone access, but not have them be able to see your credentials? That's not possible in this case. If my code can read a file, I can see everything in the file.

Make more accounts on the SQL server. Or make one guest account. But you're trying to solve the problem that account management solves.

like image 32
Andy V Avatar answered Sep 21 '22 02:09

Andy V


Have the credentials sent as command arguments? Here's an example of how one would do that:

suppressPackageStartupMessages(library("argparse"))
# create parser object
parser <- ArgumentParser()
# specify our desired options
# by default ArgumentParser will add an help option
parser$add_argument("-v", "--verbose", action="store_true", default=TRUE,
 help="Print extra output [default]")
parser$add_argument("-q", "--quietly", action="store_false",
 dest="verbose", help="Print little output")
parser$add_argument("-c", "--count", type="integer", default=5,
 help="Number of random normals to generate [default %(default)s]",
 metavar="number")
parser$add_argument("--generator", default="rnorm",
 help = "Function to generate random deviates [default \"%(default)s\"]")
parser$add_argument("--mean", default=0, type="double", help="Mean if generator == \"rnorm\" [default %(default)s]")
parser$add_argument("--sd", default=1, type="double",
 metavar="standard deviation",
 help="Standard deviation if generator == \"rnorm\" [default %(default)s]")
# get command line options, if help option encountered print help and exit,
# otherwise if options not found on command line then set defaults,
args <- parser$parse_args()
# print some progress messages to stderr if "quietly" wasn't requested
if ( args$verbose ) {
 write("writing some verbose output to standard error...\n", stderr())
}
# do some operations based on user input
if( args$generator == "rnorm") {
 cat(paste(rnorm(args$count, mean=args$mean, sd=args$sd), collapse="\n"))
} else {
 cat(paste(do.call(args$generator, list(args$count)), collapse="\n"))
}
cat("\n")

Sample run (no parameters):

usage: example.R [-h] [-v] [-q] [-c number] [--generator GENERATOR] [--mean MEAN] [--sd standard deviation]
optional arguments:
 -h, --help show this help message and exit
 -v, --verbose Print extra output [default]
 -q, --quietly Print little output
 -c number, --count number
 Number of random normals to generate [default 5]
 --generator GENERATOR
 Function to generate random deviates [default "rnorm"]
 --mean MEAN Mean if generator == "rnorm" [default 0]
 --sd standard deviation
 Standard deviation if generator == "rnorm" [default 1]

The package was apparently inspired by the python package of the same name, so looking there may also be useful.

Looking at your code, I'd probably rewrite it as follows:

library(RJDBC)
library(argparse)
args <- ArgumentParser()
args$add_argument('--driver', dest='driver', default="com.vertica.jdbc.Driver")
args$add_argument('--classPath', dest='classPath', default="/home/Drivers/vertica-jdbc-7.0.1-0.jar")
args$add_argument('--url', dest='url', default="jdbc:vertica://servername:5433/db")
args$add_argument('--user', dest='user', default='username')
args$add_argument('--password', dest='password', default='password')
parser <- args$parse_args
vDriver <- JDBC(driverClass=parser$driver, parser$classPath)
vertica <- dbConnect(vDriver, parser$url, parser$user , parser$password)
# continue here
like image 26
hd1 Avatar answered Sep 20 '22 02:09

hd1


Jana, it seems odd that you are willing to let the users connect via R but not in any other way. How is that obscuring anything from them?

I don't understand why you would not be satisfied with a guest account that has specific SELECT-only access to certain tables (or even views)?

like image 43
MonetsChemist Avatar answered Sep 19 '22 02:09

MonetsChemist