Oracle 函数中的加密/解密密码
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/12058356/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Encrypt/Decrypt Password in Oracle Function
提问by Victor
Due to previously poorly designed structure, the current database that I have to work with stores users' password as text.
由于以前设计的结构很差，我必须使用的当前数据库将用户密码存储为文本。
Now, I am building a front end part that has to use those passwords and I certainly don't want to be sending passwords unencrypted.
现在，我正在构建一个必须使用这些密码的前端部分，我当然不想发送未加密的密码。
My idea is to write an Oracle function to encrypt and decrypt text password and use those functions in the stored procedures that will return encrypted data.
我的想法是编写一个 Oracle 函数来加密和解密文本密码，并在将返回加密数据的存储过程中使用这些函数。
What would be the best approach in Oracle to do so?
Oracle 这样做的最佳方法是什么？
回答by Justin Cave
If you want to write your own functions to encrypt and decrypt data, you would simply want to call the DBMS_CRYPTOencryptand decryptmethods with appropriate parameters (i.e. pick your encryption algorithm, your key, etc.).
如果您想编写自己的函数来加密和解密数据，您只需使用适当的参数调用DBMS_CRYPTOencrypt和decrypt方法（即选择您的加密算法、您的密钥等）。
Of course, if you write your own routines, assuming that you store the key in the database or somewhere the database has access to, you're not doing much for security. It's bad to send passwords unencrypted over the network but it is generally much worse to store unencrypted passwords in the database (or encrypted passwords if there is a decryptmethod in the database that has access to the key to decrypt the data). It's generally a lot easier to steal data from a database than it is to sniff data getting sent over the network in order to find a password.
当然，如果您编写自己的例程，假设您将密钥存储在数据库中或数据库可以访问的某个地方，那么您就没有做太多的安全工作。通过网络发送未加密的密码是不好的，但将未加密的密码存储在数据库中通常会更糟糕（如果decrypt数据库中有可以访问密钥来解密数据的方法，则存储加密的密码）。从数据库中窃取数据通常比嗅探通过网络发送的数据以找到密码要容易得多。
The right answer, of course, would be to rearchitect the system so that you don't store the passwords at all. You should be storing password hashes (which you can also generate using the DBMS_CRYPTOpackage) which are non-reversible.
当然，正确的答案是重新构建系统，以便您根本不存储密码。您应该存储DBMS_CRYPTO不可逆的密码哈希（您也可以使用包生成）。
回答by cagcowboy
Take a look at DBMS_CRYPTO
看看 DBMS_CRYPTO
It has methods to encrypt and decrypt data built in. Better than writing your own.
它内置了加密和解密数据的方法。比自己编写更好。
http://docs.oracle.com/cd/B19306_01/appdev.102/b14258/d_crypto.htm
http://docs.oracle.com/cd/B19306_01/appdev.102/b14258/d_crypto.htm
回答by AnBisw
Here's a packaged function (successful implementation) for encrypting passwords using (DBMS_CRYPTO)-
这是使用 ( DBMS_CRYPTO)-加密密码的打包函数（成功实现）-
CREATE OR REPLACE
PACKAGE BODY encrypt_paswd
AS
G_CHARACTER_SET VARCHAR2(10) := 'AL32UTF8';
G_STRING VARCHAR2(32) := '12345678901234567890123456789012';
G_KEY RAW(250) := utl_i18n.string_to_raw
( data => G_STRING,
dst_charset => G_CHARACTER_SET );
G_ENCRYPTION_TYPE PLS_INTEGER := dbms_crypto.encrypt_aes256
+ dbms_crypto.chain_cbc
+ dbms_crypto.pad_pkcs5;
------------------------------------------------------------------------
--Encrypt a password
--Salt the password
------------------------------------------------------------------------
FUNCTION encrypt_val( p_val IN VARCHAR2 ) RETURN RAW
IS
l_val RAW(32) := UTL_I18N.STRING_TO_RAW( p_val, G_CHARACTER_SET );
l_encrypted RAW(32);
BEGIN
l_val := utl_i18n.string_to_raw
( data => p_val,
dst_charset => G_CHARACTER_SET );
l_encrypted := dbms_crypto.encrypt
( src => l_val,
typ => G_ENCRYPTION_TYPE,
key => G_KEY );
RETURN l_encrypted;
END encrypt_val;
END encrypt_paswd;
This uses encrypt_aes256-"Advanced Encryption Standard. Block cipher. Uses 256-bit key size." , chain_cbc- "Cipher Block Chaining. Plaintext is XORed with the previous ciphertext block before it is encrypted." and pad_pkcs5- "Provides padding which complies with the PKCS #5: Password-Based Cryptography Standard".
这使用encrypt_aes256-“高级加密标准。分组密码。使用 256 位密钥大小。” , chain_cbc- “密码块链接。明文在加密之前与前一个密文块进行异或。” 和pad_pkcs5- “提供符合 PKCS #5：基于密码的加密标准的填充”。
In addition to this You can create a similar function to decrypt. like -
除此之外，您还可以创建一个类似的函数来解密。喜欢 -
FUNCTION decrypt_val( p_val IN RAW ) RETURN VARCHAR2
IS
l_decrypted RAW(32);
l_decrypted_string VARCHAR2(32);
l_user VARCHAR2(32);
BEGIN
SELECT user
INTO l_user
FROM dual;
if l_user = 'ADMIN' -- you can restrict usage of decrypt to certain db users only.
then
l_decrypted := dbms_crypto.decrypt
( src => p_val,
typ => G_ENCRYPTION_TYPE,
key => G_KEY );
l_decrypted_string := utl_i18n.raw_to_char
( data => l_decrypted,
src_charset => G_CHARACTER_SET );
RETURN l_decrypted_string;
else
RAISE_APPLICATION_ERROR(-20101, 'You are not authorized to use this function - decrypt_val()');
end if;
RETURN 'Unknown';
END decrypt_val;
You may also consider wrapping the package before compiling it in the database using wrap iname=package_name.pkband then compiling the resulting plb.
您还可以考虑在将包编译到数据库中之前对其进行包装wrap iname=package_name.pkb，然后使用plb.
回答by Joram
Or you could use the password hashing algorithm pbkdf2 like this http://mikepargeter.wordpress.com/2012/11/26/pbkdf2-in-oracle/I verified the outcome with the rfc https://www.ietf.org/rfc/rfc6070.txtand it works fine.
或者你可以像这样使用密码哈希算法 pbkdf2 http://mikepargeter.wordpress.com/2012/11/26/pbkdf2-in-oracle/我用 rfc https://www.ietf.org/验证了结果rfc/rfc6070.txt并且它工作正常。
Check this link for minimal iterations and key_size: PBKDF2 recommended key size?Note that the result is double the length, because it is hex-encoded.
检查此链接以获取最小迭代和 key_size：PBKDF2 推荐的密钥大小？请注意，结果是长度的两倍，因为它是十六进制编码的。
we store these columns in the db
我们将这些列存储在数据库中
PASSWORD_HASH VARCHAR2(512 BYTE),
PASSWORD_SALT VARCHAR2(256 BYTE),
PASSWORD_ITERATIONS NUMBER(10),
PASSWORD_HASH_METHOD VARCHAR2(30 BYTE),
PASSWORD_CHANGED_DT DATE
the hash, salt and iterations are to feed the pbkdf2 algorithm the hash_method is for migration purposes and the changed_dt is to expire passwords
hash、salt 和迭代用于提供 pbkdf2 算法 hash_method 用于迁移目的而 changed_dt 用于使密码过期
回答by Sakib Mulla
user this code definitely work
用户此代码肯定有效
create or replace PACKAGE "PKG_LOGI_PWD_REG"
AS
function ENCRYPT_VAL( P_VAL in varchar2 ) return varchar2;
function DECRYPT_VAL( P_VAL in raw ) return varchar2;
end;
/
create or replace PACKAGE BODY "PKG_LOGI_PWD_REG"
as
FUNCTION decrypt_val( p_val IN RAW ) RETURN VARCHAR2
IS
l_decrypted RAW(32);
l_decrypted_string VARCHAR2(32);
L_USER varchar2(32);
L_CHARACTER_SET varchar2(10);
L_STRING varchar2(32);
L_KEY raw(250);
L_ENCRYPTION_TYPE PLS_INTEGER;
BEGIN
L_KEY := UTL_I18N.STRING_TO_RAW
( data => '98345678901234567890123456789012',
DST_CHARSET => 'AL32UTF8' );
L_ENCRYPTION_TYPE := dbms_crypto.encrypt_aes256
+ DBMS_CRYPTO.CHAIN_CBC
+ DBMS_CRYPTO.PAD_PKCS5;
l_decrypted := dbms_crypto.decrypt
( SRC => P_VAL,
TYP => L_ENCRYPTION_TYPE,
key => L_KEY );
l_decrypted_string := utl_i18n.raw_to_char
( data => l_decrypted ,
src_charset => 'AL32UTF8' );
RETURN l_decrypted_string;
end DECRYPT_VAL;
FUNCTION encrypt_val( p_val IN VARCHAR2 ) RETURN VARCHAR2
is
L_VAL RAW(32);
L_ENCRYPTED raw(32);
L_CHARACTER_SET varchar2(10);
L_STRING varchar2(32);
L_KEY RAW(250);
L_ENCRYPTION_TYPE PLS_INTEGER;
begin
L_KEY := UTL_I18N.STRING_TO_RAW
( data => '98345678901234567890123456789012',
DST_CHARSET => 'AL32UTF8' );
L_ENCRYPTION_TYPE := dbms_crypto.encrypt_aes256
+ DBMS_CRYPTO.CHAIN_CBC
+ DBMS_CRYPTO.PAD_PKCS5;
L_VAL := utl_i18n.string_to_raw
( data => p_val,
dst_charset => 'AL32UTF8' );
L_ENCRYPTED := dbms_crypto.encrypt
( SRC => L_VAL,
TYP => L_ENCRYPTION_TYPE,
key => L_KEY );
return L_ENCRYPTED;
EXCEPTION when OTHERS then
RETURN SQLCODE||'-'||SQLERRM;
end ENCRYPT_VAL;
end PKG_LOGI_PWD_REG;
/
