在 R 中加密密码 - 使用 RODBC 连接到 Oracle DB-数据库问题

Encrypt password in R - to connect to an Oracle DB using RODBC(在 R 中加密密码 - 使用 RODBC 连接到 Oracle DB)

本文介绍了在 R 中加密密码 - 使用 RODBC 连接到 Oracle DB的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！

问题描述

我使用以下代码连接Oracle数据库:

I use the following code to connect Oracle database:

> library(RODBC)
> channel <- odbcConnect("R", uid="xxx", pwd="catch@123") 
> sqlSave(channel ,resultsclassifiedfinal_MC_TC_P1, tablename="table1", rownames=FALSE, append=TRUE, fast = FALSE, nastring = NULL)
> odbcClose(channel)

但是，我不能在公共场合使用明文密码.我想加密密码.

However, I cannot use a cleartext password in public. I want to encrypt the password.

我看到一个 pki 和 digest 库要使用，这是一种方法吗?

I see a pki and digest library to use, is this a way to do it?

更新
我试过下面的代码:

Update
I tried below code :

> require(PKI)
> key <- PKI.genRSAkey(2048)
> x <- charToRaw("catch@123")
> e <- PKI.encrypt(x, key)
> y <- PKI.decrypt(e, key)
> stopifnot(identical(x, y))
> print(rawToChar(y))
[1] "catch@123"
> e
  [1] 85 8e 6b 38 da 69 8a 4c 20 ea 24 4e 6d cb 47 3b e6 d5 48 b4 57 93 31 d9 0c 20 70 89
 [29] fa 3c 94 bf b6 09 82 29 6f 15 c5 ab 75 e6 e7 3a 4f 9a ec cb 37 a0 0d 19 58 db a3 1f
 [57] 65 ef f2 bd a1 c8 7e 2a f0 f2 a9 bc 19 59 4e 36 64 19 3f 00 a5 bb dc d1 1b d7 bf c5
 [85] cf 60 83 88 17 fe cc e1 b6 ee 5b dc 11 cf b1 8f 8f e0 07 99 8e 2c 1f 4f 46 7e 1d 73
[113] 69 12 44 b6 0a 4c 41 2a 62 df bf 48 e3 11 15 ed fb c4 06 85 c9 fc c3 7d 1b a8 93 7d
[141] 58 72 71 8b 0b bb fc 3d 1c fe 88 28 4c 43 ef 95 c1 8f 95 cd 59 66 81 c5 c9 6f 46 81
[169] 8b 53 8e cb 3e 45 2b c6 ea 86 47 97 a3 09 60 73 36 d4 76 76 a0 84 7b 42 07 f8 32 c2
[197] 19 55 93 39 9c a4 fe 3b a9 1a 26 fa c6 bd 77 50 ac 41 92 a2 b5 c4 1d a6 0e 30 00 d8
[225] ab 1e 79 13 23 be a7 89 fe d1 3c d2 ea b0 35 f0 69 7d 06 77 d1 03 a7 55 f2 d3 ca 1d
[253] 66 fb c7 26
> y
[1] 63 61 74 63 68 40 31 32 33

加密是这样完成的吗?我应该使用这样的东西吗:

Is this how the encryption is done? Should I use something like this:

channel <- odbcConnect("R", uid="xxx", pwd=rawToChar(y))

推荐答案

以下功能现在可以在我的 R 包中使用 keyringr.keyringr 包也有类似的功能来访问 Gnome Keyring 和 macOS Keychain.

The below functionality is now available in my R package keyringr. The keyringr package also has similar functions to access the Gnome Keyring and macOS Keychain.

---

如果您使用的是 Windows，则可以使用 PowerShell 来执行此操作.请参阅下面的博客文章.

If you are using Windows you can use PowerShell to do this. See my blog post below.

http://www.gilfillan.space/2016/04/21/Using-PowerShell-and-DPAPI-to-securely-mask-passwords-in-R-scripts/

基本上...

确保您已启用 PowerShell 执行.

将以下文本保存到名为 EncryptPassword.ps1 的文件中:

Save the following text into a file called EncryptPassword.ps1:

# Create directory user profile if it doesn't already exist.
$passwordDir = "$($env:USERPROFILE)DPAPIpasswords$($env:computername)"
New-Item -ItemType Directory -Force -Path $passwordDir

# Prompt for password to encrypt
$account = Read-Host "Please enter a label for the text to encrypt.  This will be how you refer to the password in R.  eg. MYDB_MYUSER
$SecurePassword = Read-Host -AsSecureString  "Enter password" | convertfrom-securestring | out-file "$($passwordDir)$($account).txt"

# Check output and press any key to exit
Write-Host "Press any key to continue..."
$x = $host.UI.RawUI.ReadKey("NoEcho,IncludeKeyDown")

执行上面的脚本(右键单击 > 使用 PowerShell 运行)，为密码提供一个有意义的名称，然后输入密码.您现在可以通过检查 %USERPROFILE%/DPAPI/passwords/[PC NAME]/[PASSWORD IDENTIFIER.txt] 中的文件来验证密码是否已加密

Execute the script above (right click > Run with PowerShell), provide a meaningful name for the password, and type in the password. You can now verify that the password has been encrypted by checking the file in %USERPROFILE%/DPAPI/passwords/[PC NAME]/[PASSWORD IDENTIFIER.txt]

现在在 R 中运行以下代码(我将此函数保存在一个 R 脚本中，我 source 在每个脚本的开头.

Now run the following code from within R (I have this function saved in an R script that I source at the start of each script.

getEncryptedPassword <- function(credential_label, credential_path) {
  # if path not supplied, use %USER_PROFILE%DPAPIpasswordscomputernamecredential_label.txt as default
  if (missing(credential_path)) {
    credential_path <- paste(Sys.getenv("USERPROFILE"), '\DPAPI\passwords\', Sys.info()["nodename"], '\', credential_label, '.txt', sep="")
  }
  # construct command
  command <- paste('powershell -command "$PlainPassword = Get-Content ', credential_path, '; $SecurePassword = ConvertTo-SecureString $PlainPassword; $BSTR = [System.Runtime.InteropServices.Marshal]::SecureStringToBSTR($SecurePassword); $UnsecurePassword = [System.Runtime.InteropServices.Marshal]::PtrToStringAuto($BSTR); echo $UnsecurePassword"', sep='')
  # execute powershell and return command
  return(system(command, intern=TRUE))
}

现在，当您需要在 R 中提供密码时，您可以运行以下命令，而不是硬编码/提示输入密码:

Now when you need to supply a password in R, you can run the following command instead of hardcoding / prompting for the password:

getEncryptedPassword("[PASSWORD IDENTIFIER]")

例如，而不是运行 ROracle 命令:

For example, instead of running the ROracle command:

dbConnect(driver, "MYUSER", "MY PASSWORD", dbname="MYDB")

您可以改为运行它(我在第 3 步中提供的标识符是MYUSER_MYDB":

You can run this instead (the identifier I supplied in Step 3 is "MYUSER_MYDB":

dbConnect(driver, "MYUSER", getEncryptedPassword("MYUSER_MYDB"), dbname="MYDB")

您可以根据需要重复第 3 步的密码，并在第 5 步中使用正确的标识符调用它们.

这篇关于在 R 中加密密码 - 使用 RODBC 连接到 Oracle DB的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持编程学习网！