Usually I connect to a database with R using JDBC/ODBC driver. A typical code would look like
通常我使用JDBC / ODBC驱动程序使用R连接到数据库。典型的代码看起来像
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.
我希望其他人使用我的凭据访问数据库,但我想保护我的用户名和密码。因此,我计划将上述脚本保存为“Connections.r”文件,并要求用户提供此文件。
source("/opt/mount1/Connections.r")
If I give execute only permission to Connections.r others cannot source the file
如果我只赋予Connections执行权限,则其他人无法获取该文件
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?
只有当我提供读取和执行权限时,R才允许用户使用它。如果我给予读取权限,我的凭证将被公开。无论如何我们可以通过保护用户凭据来解决这个问题吗?
4 个解决方案
#1
3
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.
除非你通过制作一个初始JDBC连接的Rcpp函数或包来深深地模糊你的凭证(这不会是微不足道的),你唯一一个更轻松的混淆机制就是将你的凭证存储在一个文件中并拥有你的源R脚本从文件中读取它们,在呼叫中使用它们,然后在呼叫之后立即从环境中将它们发送出去。这仍然会暴露他们,但不是直接暴露他们。
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.
另一种方式,因为用户有他们自己的登录到RStudio服务器,是使用Hadley的新安全包(我们中的一些人通过它的步伐运行它),添加用户密钥并将您的凭据加密存储但是有你的源代码R脚本自动解密它们。您仍然需要使用您使用的任何变量的rm,因为如果不这样,它们将成为环境的一部分。
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.
最后,我很困惑为什么你不能只在数据库端设置具有只读权限的用户?这就是基于角色的访问控制的用途。这是行政工作,但绝对是正确的事情。
#2
2
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.
在SQL服务器上创建更多帐户。或者制作一个来宾帐户。但是你正试图解决账户管理解决的问题。
#3
1
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.
该软件包显然受到同名python包的启发,所以看起来也可能有用。
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
#4
1
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?
Jana,你似乎很愿意让用户通过R连接而不是以任何其他方式连接。这怎么会掩盖他们的一切?
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)?
我不明白为什么你不满意具有特定SELECT访问某些表(甚至视图)的访客帐户?
#1
3
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.
除非你通过制作一个初始JDBC连接的Rcpp函数或包来深深地模糊你的凭证(这不会是微不足道的),你唯一一个更轻松的混淆机制就是将你的凭证存储在一个文件中并拥有你的源R脚本从文件中读取它们,在呼叫中使用它们,然后在呼叫之后立即从环境中将它们发送出去。这仍然会暴露他们,但不是直接暴露他们。
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.
另一种方式,因为用户有他们自己的登录到RStudio服务器,是使用Hadley的新安全包(我们中的一些人通过它的步伐运行它),添加用户密钥并将您的凭据加密存储但是有你的源代码R脚本自动解密它们。您仍然需要使用您使用的任何变量的rm,因为如果不这样,它们将成为环境的一部分。
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.
最后,我很困惑为什么你不能只在数据库端设置具有只读权限的用户?这就是基于角色的访问控制的用途。这是行政工作,但绝对是正确的事情。
#2
2
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.
在SQL服务器上创建更多帐户。或者制作一个来宾帐户。但是你正试图解决账户管理解决的问题。
#3
1
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.
该软件包显然受到同名python包的启发,所以看起来也可能有用。
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
#4
1
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?
Jana,你似乎很愿意让用户通过R连接而不是以任何其他方式连接。这怎么会掩盖他们的一切?
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)?
我不明白为什么你不满意具有特定SELECT访问某些表(甚至视图)的访客帐户?