如何处理R连接字符串中的DB密码?

Though I don't know what the SO quorum would be, the question itself is simple: How do y'all handle passwords in db connection string when you connect to a database from R?

虽然我不知道SO仲裁是什么，但问题本身很简单:当您从R连接数据库时，您如何处理db连接字符串中的密码?

Tutorials often show an example like this.

教程经常展示这样的示例。

con <- dbConnect(MySQL(), user="root", password="test", 
             dbname="research_db", host="localhost",
             client.flag=CLIENT_MULTI_STATEMENTS)

If the database is indeed your experimental localhost, this might be somewhat realistic. However if you use it with multiple users on a server you might not want to expose the db credentials like this. Particularly when combining RStudio Server with a SQL database you might want to do something encrypted. What is your experience?

如果数据库确实是实验性的本地主机，这可能有点现实。但是，如果您对服务器上的多个用户使用它，您可能不希望像这样公开db凭据。特别是在将RStudio服务器与SQL数据库结合时，您可能想要做一些加密的事情。你的经验是什么?

3 个解决方案

#1

Here is a piece of example code that uses the tcltk package to prompt for a password while hiding the actual value:

下面是一个示例代码，它使用tcltk包提示输入密码，同时隐藏实际的值:

library(tcltk)
tt <- tktoplevel()
pass <- tclVar()
tkpack(tklabel(tt,text='Password:'))
tkpack(tkentry(tt,textvariable=pass,show='*'))
tkpack(tkbutton(tt,text="Done",command=function()tkdestroy(tt)))
tkwait.window(tt)
tclvalue(pass)

In this case it just prints out the unhidden password at the end, but you could wrap this in a function to return that value, then use that as the value for the password argument. Or you could put this and the connect call (with the tclvalue line as the password) inside a call to local so that the variable containing the password disappears as soon as it is used.

在本例中，它只是在最后打印出未隐藏的密码，但是您可以将其包装在函数中以返回该值，然后将其作为密码参数的值。或者，您可以将这个和connect调用(以tclvalue行作为密码)放在对local的调用中，以便包含密码的变量在使用后立即消失。

Edit

编辑

For RStudio and RStudio server there is a function .rs.askForPassword. Use it like:

对于RStudio和RStudio服务器，有一个函数。使用它:

psswd <- .rs.askForPassword("Database Password:")
con <- dbConnect(MySQL(), user="root", password=psswd, 
             dbname="research_db", host="localhost",
             client.flag=CLIENT_MULTI_STATEMENTS)

#2

I have a different solution for the same problem, which doesn't require the user to type in their password every time they are connecting. I'm using the .my.cnf file functionality. Basically every user has a .my.cnf file in the root of their RStudio Server home directory which contains their password(s) to all MySQL databases, so in the R script I just refer to the database through the 'group' functionality.

对于相同的问题，我有不同的解决方案，不需要用户每次连接时都输入密码。我正在使用.my.cnf文件功能。基本上每个用户在RStudio服务器主目录的根目录中都有一个.my.cnf文件，它包含所有MySQL数据库的密码，所以在R脚本中，我只是通过“group”功能引用数据库。

R scripts:

R脚本:

library("RMySQL")
m <- dbDriver("MySQL")
# connect using .my.cnf
con <- dbConnect(m, group = "theDatabase")

.my.cnf file:

.my.cnf文件:

[client]
user = userName
host = mysql.server.com
password = MyPassword
[theDatabase]
database = hr
[theDatabase2]
user = opto
database = opto
password = pure-light
host = merced

#3

So I like the solution of using the config file - that is a great answer. There are also some good comments on the password prompting answer that led me to this solution:

所以我喜欢使用配置文件的解决方案——这是一个很好的答案。还有一些关于密码提示答案的很好的评论，让我想到了这个解决方案:

conn <- dbConnect(drv, "jdbc:sqlserver://host:port", 'username', password=.rs.askForPassword("Enter password:"))

#1