|
Category :
Database
Resources -> SQL and PLSQL
DB Version
:
Oracle 8i / 9i
OS Details :
Sun Solaris9
One
of the newer features added since oracle8i is data encryption. Oracle
has supplied a new package called dbms_obfuscation_toolkit which allows
you to encrypt and decrypt strings. This functionality will be
particularly useful for data encryption when sensitive data like
passwords and social security numbers are stored within the database.
This article gives an example on encryption and decryption using the
dbms_obfuscation_toolkit package.
This example explains the usage
of
dbms_obfuscation package for encrypting user passwords before storing
then within the database tables:
CREATE OR
REPLACE PACKAGE USER_MANAGE
as
procedure create_user(user_name in varchar2,user_password in
varchar2);
function password_encrypt(user_name in varchar2,user_password in
varchar2) return varchar2;
function password_decrypt(var_user_name in varchar2) return varchar2;
end web_user_manage;
/
CREATE OR REPLACE PACKAGE BODY
USER_MANAGE
as
-- This procedure is for
storing the actual username and the encrypted passwords within the
database
procedure create_user(user_name in varchar2,user_password in varchar2)
is
encrypt_password varchar2(30);
begin
-- Call to the encvryption
routine
encrypt_password := user_manage.password_encrypt(null,user_password);
if (user_name is not null and encrypt_password is not null)
then
-- Insert encrypted values in
to the table
insert into user_mgmt_table (user_id,user_name,user_password,user_status,user_createdate,
) values (user_seq.nextval,upper(user_name), encrypt_password,'OPEN',
sysdate);
commit;
else
null;
end if;
end create_user;
-- Function to encrypt the
passed in string. Returns the encrypted value
function password_encrypt(user_name in varchar2,user_password in
varchar2)
return varchar2
is
-- All encryptions and
decryptions are based on this key value
password_key varchar2(32) := 'passwdkey';
password_encrypt varchar2(32);
begin
if (user_password is not null)
then
-- Call to the
dbms_obfuscation_toolkit package to do the encryption
dbms_obfuscation_toolkit.DESENCRYPT(input_string=>user_password,key_string=>
password_key,encrypted_string=>password_encrypt);
return password_encrypt;
else
return null;
end if;
end password_encrypt;
-- Function to decrypt the
passed in string. Returns the decrypted value
function password_decrypt(var_user_name in varchar2)
return varchar2
is
-- The same key value that was
used for encrypting
password_key varchar2(32) := 'passwdkey';
password_decrypt varchar2(32);
password_encrypt varchar2(32);
begin
if (var_user_name is not null)
then
begin
-- Fetches the encrypted
password from the database
select user_password into password_encrypt from
user_mgmt_table where user_name = upper(var_user_name);
-- Call to the
dbms_obfuscation_toolkit package to do the decryption
dbms_obfuscation_toolkit.DESDECRYPT(input_string=>password_encrypt,key_string=>
password_key,decrypted_string=>password_decrypt);
return
password_decrypt;
exception
when no_data_found then return null;
end;
else
return null;
end if;
end password_decrypt;
end user_manage;
/
|
