DBMS_CRYPTO

Encryptage  d une  colonne   avec le  package DBMS_CRYPTO

1-  creer  la  table   avec  les  enregistrements

 CREATE TABLE users (

   userid       NUMBER,

   username     VARCHAR2(30),

   userlocation VARCHAR2(30),

   password     VARCHAR2(200),

   CONSTRAINT users_pk PRIMARY KEY (userid)

);

 

insert into users

values (1,'JAMES','TEXAS','james123');

insert into users

values (2,'JONES','TEXAS','jones001');

insert into users

values (3,'ALLEN','TEXAS','allen789');

commit;

En    sysdba   creer  le  package de  cryptage

CREATE OR REPLACE PACKAGE 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 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;

/

Droit  d executer  le  package :

grant execute on enc_dec to MG;

create public synonym enc_dec for sys.enc_dec;

On   encrypte  hello world

select enc_dec.encrypt('Hello World') encrypted

from dual;

ENCRYPTED

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

89738046FA0CFDD2581198FBF98DE2C5

On decrypte

select enc_dec.decrypt('89738046FA0CFDD2581198FBF98DE2C5') decrypted

from dual;

DECRYPTED

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

Hello World

Avant   cryptage

column username format a10

column userlocation format a10

column password format a10

select * from users;

    USERID USERNAME   USERLOCATI PASSWORD

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

         1 JAMES      TEXAS      james123

         2 JONES      TEXAS      jones001

         3 ALLEN      TEXAS      allen789

On  encrypte  la colonne des  passwords

update users

  set password = enc_dec.encrypt (password);

    USERID USERNAME   USERLOCATI PASSWORD

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

         1 JAMES      TEXAS      D705C2186A64B1A6FF3B6E6220746731

         2 JONES      TEXAS      98DDCC4DAB5F13140C8D657D381E05FC

         3 ALLEN      TEXAS      D9A656AD83B7ADC7443D6BECD173715E

SQL> commit;

On  cree  un user  a qui   on donne   le  droit de lire la table

grant  create  session  to   DP  identified  by DP;

grant select  on  MG.users  to DP;

Il se  connecte  lie  la  table  et  tente de  decrypter

conn  dp/DP

select *  from  MG.users;

    USERID USERNAME   USERLOCATI PASSWORD

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

         1 JAMES      TEXAS      D705C2186A64B1A6FF3B6E6220746731

         2 JONES      TEXAS      98DDCC4DAB5F13140C8D657D381E05FC

         3 ALLEN      TEXAS      D9A656AD83B7ADC7443D6BECD173715E

         4 SCOTT      TEXAS      41D69256E23E7A3D2AFEFF2E5C082FFD

select   enc_dec.decrypt('41D69256E23E7A3D2AFEFF2E5C082FFD')   from   dual

         *

ERROR at line 1:

ORA-00904: : invalid identifier

Si  on  donne  le droit d exécuter  alors   il  peut  décrypter  les  valeurs

grant   execute  on  ENC_DEC  to DP;

conn  DP/DP

select   sys.enc_dec.decrypt('41D69256E23E7A3D2AFEFF2E5C082FFD')   from   dual;

SYS.ENC_DEC.DECRYPT('41D69256E23E7A3D2AFEFF2E5C082FFD')

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

scott456

Idéalement  il faudratit   pas donner  le  grant  execute  a tous le  monde et   wrapper  le package  et conserver  le  code source  du  pacakage  pour decrypter  ou  le  modifier.

wrap iname=create_enc_dec_package.sql oname=create_enc_dec_package.wrp

PL/SQL Wrapper: Release 10.2.0.1.0- Production on Sun Aug 15 06:44:32 2010

Copyright (c) 1993, 2004, Oracle.  All rights reserved.

Processing create_enc_dec_package.sql to create_enc_dec_package.wrp

SQL> @create_enc_dec_package.wrp

Package created.

Package body created.

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

TEXT

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

abcd

abcd

abcd

abcd

abcd

abcd

abcd

9

a6 b6

g7Sz0DOZDQaKKCsvm5in+lHVuSEwg0xH2ssVZ3REZ/xuBo1QJejCNChLpgPAdiJcGMin+JzK

NvePpoJc9Za64CNfahCr/SmrphMZKs1dDvZoJ+CTByMvTl0qUqdQkNsjAnAz7ue+DnyIgMce

TEXT

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

JaBzoZ7BXU4tNIycE9AgkiyzDJgw4e3vVlM=

PACKAGE BODY enc_dec wrapped

a000000

1

abcd

abcd

abcd

abcd

abcd

abcd

TEXT

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

abcd

abcd

abcd

abcd

abcd

abcd

abcd

abcd

abcd

b

41e 1ea

TEXT

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

qn+8IJDIwwvpH/T58q56mAja+bwwg5DxNa5qyo7GAP5euLr91yVHMC1YvQLBFFUW++gz2zgz

jwGrhgGah03dYAMBXtFi0P6q0LW7qHzepeFpVSXLgQXwQh54iCuIwJlX95BQ2ENgv+FxWlaN

OHFq4RRFT++jZImI8YMmct+ONORoRdxdXb2CSFou+e9vdauzLzLqc+WCndTfq31AyNuWFp2Q

lP9qo5bwVa69K4N9PHPBtz3LYRNkVcH++MZhKb+3Zm8cItD23HGvaf1DFeNsn5GHKiPUJUX3

YTQ27afPEYA11nF//4nJGe8F1psyqUUzEarGk5Ona7+ymdSuFkpyZhvBB2mNFBH5uEe/4IxT

rY4xW4+ijEn3GldT3hjw20aP4UQW9DfFXxhAMCpmLqjxSpc3YVyAUDfkiIO8PCwRsBjzeaow

bp/dAEWf5rnTRR+GslabCKnvbnpSXo5bpXOhjTEuMoEdx+yl/A==

SQL> SQL>