Database security issue.

First of all crate this pakage in sys 

CREATE OR REPLACE PACKAGE SYS.enc_dec

AS

   FUNCTION encrypt (p_plainText VARCHAR2) RETURN RAW DETERMINISTIC;

   FUNCTION decrypt (p_encryptedText RAW) RETURN VARCHAR2 DETERMINISTIC;

END;

CREATE OR REPLACE PACKAGE BODY SYS.enc_dec

AS

     encryption_type    PLS_INTEGER := DBMS_CRYPTO.ENCRYPT_DES

                                     + DBMS_CRYPTO.CHAIN_CBC

                                     + DBMS_CRYPTO.PAD_PKCS5;

     /*

       ENCRYPT_DES is the encryption algorithem. Data Encryption Standard. Block cipher.

       Uses key length of 56 bits.

       CHAIN_CBC Cipher Block Chaining. Plaintext is XORed with the previous ciphertext

       block before it is encrypted.

       PAD_PKCS5 Provides padding which complies with the PKCS #5: Password-Based

       Cryptography Standard

     */

     encryption_key     RAW (32) := UTL_RAW.cast_to_raw('MyEncryptionKey');

     -- The encryption key for DES algorithem, should be 8 bytes or more.

     FUNCTION encrypt (p_plainText VARCHAR2) RETURN RAW DETERMINISTIC

     IS

        encrypted_raw      RAW (2000);

     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_encryptedText RAW) RETURN VARCHAR2 DETERMINISTIC

     IS

        decrypted_raw      RAW (2000);

     BEGIN

        decrypted_raw := DBMS_CRYPTO.DECRYPT

        (

            src => p_encryptedText,

            typ => encryption_type,

            key => encryption_key

        );

        RETURN (UTL_RAW.CAST_TO_VARCHAR2 (decrypted_raw));

     END decrypt;

END;

/

After creating pakage execute this query in sys:

grant execute on enc_dec to hr;

create public synonym enc_dec for sys.enc_dec;

 exit;
connect hr schema
and test this 
select enc_dec.encrypt('Hello World') encrypted
from dual;

ENCRYPTED
----------------------------------
89738046FA0CFDD2581198FBF98DE2C5

/* A simple value encrypted using the package we just created. */
select enc_dec.decrypt('89738046FA0CFDD2581198FBF98DE2C5') decrypted
from dual;

DECRYPTED
------------------
Hello World

Note : 

These encrypted values can only be seen by the users who have EXECUTE access to the ENC_DEC package. The data can only be decrypted using the same key and algorithem it was encrypted with. So all these password values can only be decrypted using the KEY and ALGORITHEM specified in the package ENC_DEC.
I have granted SELECT on table "users" to HR. Lets see what he sees when he querys data from users tables.

$ sqlplus / as sysdba

Connected to:

Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production

With the Partitioning, OLAP and Data Mining options

SQL> @create_enc_dec_package.wrp

Package created.

Package body created.

SQL> column text format a70

SQL> select text from dba_source where name = 'ENC_DEC';

TEXT

----------------------------------------------------------------------

PACKAGE enc_dec wrapped

a000000

1

abcd

abcd

abcd

abcd

abcd

abcd

abcd

abcd

abcd

abcd

abcd

abcd

abcd

abcd

abcd

9

a6 b2

ceYtyd1wwstfJ/3xbNlo4sobVxYwg0xHAMusaS/pOPYrUgzeeSYbTuJ789ScOKWw4LWYL1

91

ERLxTlyzbW7nRf8Cg4W0plfc4t7qD8d69uAPwYNtQpv3U6F9kwZQZnVeV+a5FlnUcEgL7J

8k

hQZIhcYLQoTZ/irf0ixRnEj+4VqG1c4=

PACKAGE BODY enc_dec wrapped

a000000

1

abcd

abcd

abcd

abcd

abcd

abcd

abcd

abcd

abcd

abcd

abcd

abcd

abcd

abcd

abcd

b

40a 1de

e5uq2Fhk2UgpK5ktxFEVOrE3zyowg5DxLdxqfC9AWE6OGeznw1zpVqLpwIrvVN522Z83WR

wv

HQw142Mg0KQxSHaso6WOT7ud5P5VvVmrcR3le4Pvj9tpyogriMDGGQGWIR5T3g4s5tMka+

Qj

TA4FsoMpOy3+bK/y/VW+u8+zHHC1m0LOziMSmnhkB+nM+U1jEvvRFGGXfOJrOSmXs+VcyV

r8

pyIFRQgr3JDZotwcfIZAw10k4Dcm87LMeBk6c0q2wdqgqcA422/awXKrAODetRti870jST

pn

46w5MWX/ickZHdrfBh6mMttQ8x4jDaNEcZR3X7VRdReUt05S6/LToL4T/VwlYFIqbzH7rb

OR

kaEYBQchlWDg5n3hRBahHVLvEeOuoQVsdBqMwA55PfP1yqqsYWSBW4Mm4OYFJP/ry1NJYb

bA

wVAA/SBw965bdu5doXjpf6y7D5dHh5dtIOL9uUA=

SQL>

----FROM ATI

CREATE OR REPLACE PACKAGE ARCHITECTURE_CHANGE.enc_dec

AS

   FUNCTION encrypt (p_plainText VARCHAR2) RETURN RAW DETERMINISTIC;

   FUNCTION decrypt (p_encryptedText RAW) RETURN VARCHAR2 DETERMINISTIC;

END;

/

CREATE OR REPLACE PACKAGE BODY ARCHITECTURE_CHANGE.enc_dec

AS

     encryption_type    PLS_INTEGER := DBMS_CRYPTO.ENCRYPT_DES

                                     + DBMS_CRYPTO.CHAIN_CBC

                                     + DBMS_CRYPTO.PAD_PKCS5;

     /*

       ENCRYPT_DES is the encryption algorithem. Data Encryption Standard. Block cipher.

       Uses key length of 56 bits.

       CHAIN_CBC Cipher Block Chaining. Plaintext is XORed with the previous ciphertext

       block before it is encrypted.

       PAD_PKCS5 Provides padding which complies with the PKCS #5: Password-Based

       Cryptography Standard

     */

     encryption_key     RAW (32) := UTL_RAW.cast_to_raw('MyEncryptionKey');

     -- The encryption key for DES algorithem, should be 8 bytes or more.

     FUNCTION encrypt (p_plainText VARCHAR2) RETURN RAW DETERMINISTIC

     IS

        encrypted_raw      RAW (2000);

     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_encryptedText RAW) RETURN VARCHAR2 DETERMINISTIC

     IS

        decrypted_raw      RAW (2000);

     BEGIN

        decrypted_raw := DBMS_CRYPTO.DECRYPT

        (

            src => p_encryptedText,

            typ => encryption_type,

            key => encryption_key

        );

        RETURN (UTL_RAW.CAST_TO_VARCHAR2 (decrypted_raw));

     END decrypt;

END;

/

----FROM ALL PURPOSE:

AT FIRST GRANT TO H R FORM SYS   :
GRANT execute ON dbms_crypto TO HR;

THEN:

CREATE OR REPLACE PACKAGE HR.PKG_PASSWORD
AS
FUNCTION Encrypt_Pass  (pPass IN Varchar2,
                        pKey  IN Varchar2
                        )
RETURN RAW;

FUNCTION Decrypt_Pass  (pPass IN Raw,
                        pKey IN Varchar2
                        )
RETURN VARCHAR2;

FUNCTION Encrypt_Passmd5(pPass IN VARCHAR2
                        )
RETURN RAW;
END Pkg_Password;
/

---

CREATE OR REPLACE PACKAGE BODY HR.PKG_PASSWORD
AS 
    --DO NOT FORGET TO WRAP THIS BEFORE LOADING INTO DATABASE 
    --IF IT IS NOT WRAPPED, THE KEY WILL BE EXPOSED

FUNCTION Encrypt_Pass  (pPass IN Varchar2,
                        pKey  IN Varchar2
                        )
RETURN RAW 
IS   
    G_Key       RAW(32)     := UTL_I18N.STRING_TO_RAW( 'JU'||pKey||'JUL', 'AL32UTF8' );
    V_Pass      RAW(3000)   := UTL_I18N.STRING_TO_RAW( pPass, 'AL32UTF8' );   
    V_encrypted RAW(32); 
BEGIN   
    V_encrypted := dbms_crypto.encrypt ( src => V_Pass,typ => DBMS_CRYPTO.DES_CBC_PKCS5,key => G_Key );
    RETURN (V_encrypted); 
END Encrypt_Pass;
 

FUNCTION Decrypt_Pass  (pPass IN Raw,
                        pKey IN Varchar2
                        )
RETURN VARCHAR2 
IS   
    V_Decrypted     RAW(3000); 
    G_Key           RAW(32);
BEGIN   
    G_Key       := UTL_I18N.STRING_TO_RAW( 'JU'||pKey||'JUL', 'AL32UTF8' );
    V_Decrypted := dbms_crypto.decrypt ( src => pPass,typ => DBMS_CRYPTO.DES_CBC_PKCS5,key => G_Key );
    RETURN (UTL_I18N.RAW_TO_CHAR( V_Decrypted, 'AL32UTF8')); 
END Decrypt_Pass; 


FUNCTION Encrypt_Passmd5( pPass IN VARCHAR2 )
RETURN RAW 
IS   
    V_Pass RAW(32000) := UTL_I18N.STRING_TO_RAW( pPass, 'AL32UTF8' );   
    V_Encrypted RAW(40); 
BEGIN   
    V_Encrypted := dbms_crypto.hash (V_Pass,3 );
RETURN (V_Encrypted); 
END Encrypt_Passmd5; 
END Pkg_Password;
/
---

EXAMPLE:

select PKG_PASSWORD.Decrypt_Pass('4694C2D7663DB357','ATI') decrypted
from dual;