With DBMS_CRYPTO, a user with select access will see the encrypted values. The security is gained by not allowing the user access to execute the procs that encrypt or decrypt the data. You simply allow the application layer access to execute these procs and then the application can use the unencrypted value. Note that you should be "wrapping” the proc so the user community cannot see the key used in the DBA_SOURCE views to encrypt or decrypt the data.
/* Start a sqlplus session as sysdba and create this package */
CREATE OR REPLACE PACKAGE pkg_encrypt_decrypt AS
FUNCTION encrypt (p_PlainText VARCHAR2) RETURN RAW DETERMINISTIC;
FUNCTION decrypt (p_Encrypted RAW) RETURN VARCHAR2 DETERMINISTIC;
END pkg_encrypt_decrypt;
/
CREATE OR REPLACE PACKAGE BODY pkg_encrypt_decrypt
AS
EncryptionType PLS_INTEGER := DBMS_CRYPTO.ENCRYPT_AES256
+ DBMS_CRYPTO.CHAIN_CBC
+ DBMS_CRYPTO.PAD_PKCS5;
/* ENCRYPT_AES256 is the encryption algorithem AES 256-bit algorithm.
CHAIN_CBC for Cipher Block Chaining, a process that hides plain text patterns
by XORing blocks with the previous ciphertext block, and then encrypted.
PAD_PKCS5 Provides padding which complies with the
PKCS #5 Password-Based Cryptography Standard. */
EncryptionKey RAW (32) := UTL_RAW.cast_to_raw('ThisTextIsMyEncryptionKeyForThisPackage');
-- The encryption key for AES256 algorithem, should be 32 bytes or more.
FUNCTION encrypt (p_PlainText VARCHAR2) RETURN RAW DETERMINISTIC
IS
encrypted_raw RAW (2048);
BEGIN
encrypted_raw := DBMS_CRYPTO.ENCRYPT
( src => UTL_RAW.CAST_TO_RAW (p_PlainText),
typ => encryption_type,
key => encryption_key
);
RETURN encrypted_raw;
END encrypt;
FUNCTION decrypt (p_Encrypted RAW) RETURN VARCHAR2 DETERMINISTIC
IS
decrypt_raw RAW (2048);
BEGIN
decrypt_raw := DBMS_CRYPTO.DECRYPT
( src => p_Encrypted,
typ => encryption_type,
key => encryption_key
);
RETURN (UTL_RAW.CAST_TO_VARCHAR2 (decrypt_raw));
END decrypt;
END pkg_encrypt_decrypt;
/
grant execute on gen_encr to scott;
create public synonym gen_encr for sys.gen_encr;
exit;
select pkg_encrypt_decrypt.encrypt('Hello World') encrypted from dual;
ENCRYPTED
----------------------------------
89738046FA0CFDD2581198FBF98DE2C5
/* Now let's try to decrypt that value using the package we created. */
select pkg_encrypt_decrypt.decrypt('89738046FA0CFDD2581198FBF98DE2C5') decrypted from dual;
DECRYPTED
------------------
Hello World
/* The passwords are in plain text above, let's encrypt them in the table. */
update users set password = pkg_encrypt_decrypt.encrypt(password);
commit;
Its all about keeping your encryption algorithm and key hidden. If they are exposed, anyone can decrypt your encrypted data and see it all. In our case the key and the algorithm is stored in the pkg_encrypt_decrypt package itself.
I put CREATE PACKAGE statements in a file named pkg_encrypt_decrypt.sql and then run the wrap utility to wrap the code into a new file named pkg_encrypt_decrypt.wrp.
The wrap utility actually has encrypted the PL/SQL code in the .sql file, and made it unreadable for anyone. Now use pkg_encrypt_decrypt.wrp file to create the package. Keep your .sql source file safe with you as if you want to make changes to the package later on, you will need it i.e. make changes to the .sql file, wrap it again and recreate the package.
As can be seen that after creating the package from wrapped sql script the package source code is unreadable to anyone, even the owner of the package. This way we can hide the encryption logic completely from every one.
Comments
Post a Comment