Oracle PL/SQL Tutorial/System Packages/dbms crypto

dbms_crypto.ENCRYPT_AES128

SQL> declare
  2     l_key     varchar2(2000) := "1234567890123456";
  3     l_in_val  varchar2(2000) := "ConfidentialData";
  4     l_mod     number := dbms_crypto.ENCRYPT_AES128
  5                         + dbms_crypto.CHAIN_CBC
  6                         + dbms_crypto.PAD_PKCS5;
  7     l_enc     raw (2000);
  8  begin
  9     l_enc := dbms_crypto.encrypt
 10     (
 11         UTL_I18N.STRING_TO_RAW (l_in_val, "AL32UTF8"),
 12         l_mod,
 13         UTL_I18N.STRING_TO_RAW (l_key, "AL32UTF8")
 14    );
 15    dbms_output.put_line ("Encrypted="||l_enc);
 16  end;
 17  /

SQL>
SQL>

DBMS_CRYPTO.encrypt_aes128 + DBMS_CRYPTO.chain_cbc + DBMS_CRYPTO.pad_pkcs5

SQL>
SQL> DECLARE
  2     l_enc_val   BLOB;
  3     l_in_val    CLOB;
  4     l_key       VARCHAR2 (16) := "1234567890123456";
  5  BEGIN
  6     DBMS_CRYPTO.encrypt (dst      => l_enc_val,
  7                          src      => l_in_val,
  8                          KEY      => utl_i18n.string_to_raw (l_key, "AL32UTF8"),
  9                          typ      =>   DBMS_CRYPTO.encrypt_aes128
 10                                      + DBMS_CRYPTO.chain_cbc
 11                                      + DBMS_CRYPTO.pad_pkcs5
 12                         );
 13  END;
 14  /

SQL>
SQL>

dbms_crypto.hash

SQL>
SQL> declare
  2    l_in_val varchar2(2000) := "CriticalData";
  3    l_hash   raw(2000);
  4  begin
  5    l_hash := dbms_crypto.hash (
  6       src => UTL_I18N.STRING_TO_RAW (l_in_val, "AL32UTF8"),
  7       typ => dbms_crypto.hash_sh1
  8    );
  9    dbms_output.put_line("Hash="||l_hash);
 10  end;
 11  /

SQL>
SQL>

dbms_crypto.hash_sh1

SQL>
SQL>
SQL> CREATE TABLE EMP(
  2      EMPNO NUMBER(4) NOT NULL,
  3      ENAME VARCHAR2(10),
  4      JOB VARCHAR2(9),
  5      MGR NUMBER(4),
  6      HIREDATE DATE,
  7      SAL NUMBER(7, 2),
  8      COMM NUMBER(7, 2),
  9      DEPTNO NUMBER(2)
 10  );
Table created.
SQL>
SQL> INSERT INTO EMP VALUES(7369, "SMITH", "CLERK", 7902,TO_DATE("17-DEC-1980", "DD-MON-YYYY"), 800, NULL, 20);
1 row created.
SQL> INSERT INTO EMP VALUES(7499, "ALLEN", "SALESMAN", 7698,TO_DATE("20-FEB-1981", "DD-MON-YYYY"), 1600, 300, 30);
1 row created.
SQL> INSERT INTO EMP VALUES(7521, "WARD", "SALESMAN", 7698,TO_DATE("22-FEB-1981", "DD-MON-YYYY"), 1250, 500, 30);
1 row created.
SQL> INSERT INTO EMP VALUES(7566, "JONES", "MANAGER", 7839,TO_DATE("2-APR-1981", "DD-MON-YYYY"), 2975, NULL, 20);
1 row created.
SQL> INSERT INTO EMP VALUES(7654, "MARTIN", "SALESMAN", 7698,TO_DATE("28-SEP-1981", "DD-MON-YYYY"), 1250, 1400, 30);
1 row created.
SQL> INSERT INTO EMP VALUES(7698, "BLAKE", "MANAGER", 7839,TO_DATE("1-MAY-1981", "DD-MON-YYYY"), 2850, NULL, 30);
1 row created.
SQL> INSERT INTO EMP VALUES(7782, "CLARK", "MANAGER", 7839,TO_DATE("9-JUN-1981", "DD-MON-YYYY"), 2450, NULL, 10);
1 row created.
SQL> INSERT INTO EMP VALUES(7788, "SCOTT", "ANALYST", 7566,TO_DATE("09-DEC-1982", "DD-MON-YYYY"), 3000, NULL, 20);
1 row created.
SQL> INSERT INTO EMP VALUES(7839, "KING", "PRESIDENT", NULL,TO_DATE("17-NOV-1981", "DD-MON-YYYY"), 5000, NULL, 10);
1 row created.
SQL> INSERT INTO EMP VALUES(7844, "TURNER", "SALESMAN", 7698,TO_DATE("8-SEP-1981", "DD-MON-YYYY"), 1500, 0, 30);
1 row created.
SQL> INSERT INTO EMP VALUES(7876, "ADAMS", "CLERK", 7788,TO_DATE("12-JAN-1983", "DD-MON-YYYY"), 1100, NULL, 20);
1 row created.
SQL> INSERT INTO EMP VALUES(7900, "E", "CLERK", 7698,TO_DATE("3-DEC-1981", "DD-MON-YYYY"), 950, NULL, 30);
1 row created.
SQL> INSERT INTO EMP VALUES(7902, "FORD", "ANALYST", 7566,TO_DATE("3-DEC-1981", "DD-MON-YYYY"), 3000, NULL, 20);
1 row created.
SQL> INSERT INTO EMP VALUES(7934, "MILLER", "CLERK", 7782,TO_DATE("23-JAN-1982", "DD-MON-YYYY"), 1300, NULL, 10);
1 row created.
SQL>
SQL> CREATE TABLE DEPT(
  2      DEPTNO NUMBER(2),
  3      DNAME VARCHAR2(14),
  4      LOC VARCHAR2(13)
  5  );
Table created.
SQL>
SQL> INSERT INTO DEPT VALUES (10, "ACCOUNTING", "NEW YORK");
1 row created.
SQL> INSERT INTO DEPT VALUES (20, "RESEARCH", "DALLAS");
1 row created.
SQL> INSERT INTO DEPT VALUES (30, "SALES", "CHICAGO");
1 row created.
SQL> INSERT INTO DEPT VALUES (40, "OPERATIONS", "BOSTON");
1 row created.
SQL>
SQL>
SQL> begin
  2      for x in ( select deptno, dname, loc from dept where deptno = 10 )
  3      loop
  4          dbms_output.put_line( "Dname:  " || x.dname );
  5          dbms_output.put_line( "Loc:    " || x.loc );
  6          dbms_output.put_line( "Hash:   " ||dbms_crypto.hash( utl_raw.cast_to_raw(x.deptno||"/"||x.dname||"/"||x.loc),dbms_crypto.hash_sh1 ) );
  7      end loop;
  8  end;
  9  /

SQL>
SQL>
SQL>
SQL> drop table dept;
Table dropped.
SQL> drop table emp;
Table dropped.

dbms_crypto.mac

SQL> declare
  2     l_in_val varchar2(2000) := "CriticalData";
  3     l_key    varchar2(2000) := "1234567890123456";
  4     l_mac   raw(2000);
  5  begin
  6     l_mac := dbms_crypto.mac (
  7        src => UTL_I18N.STRING_TO_RAW (l_in_val, "AL32UTF8"),
  8        typ => dbms_crypto.hmac_sh1,
  9        key => UTL_I18N.STRING_TO_RAW (l_key, "AL32UTF8")
 10     );
 11     dbms_output.put_line("MAC="||l_mac);
 12  end;
 13  /

SQL>

dbms_crypto.randombytes

SQL>
SQL> declare
  2     l_key    raw(16);
  3  begin
  4     l_key := dbms_crypto.randombytes(16);
  5  end;
  6
  7  /

SQL>
SQL>

utl_i18n.string_to_raw and dbms_crypto.encrypt

SQL> create or replace function get_enc_val
  2  (
  3     p_in_val    in varchar2,
  4     p_key       in varchar2
  5  )
  6  return varchar2
  7  is
  8     l_enc_val raw(4000);
  9  begin
 10     l_enc_val := dbms_crypto.encrypt
 11        (
 12           src => utl_i18n.string_to_raw (p_in_val, "AL32UTF8"),
 13           key => utl_i18n.string_to_raw (p_key, "AL32UTF8"),
 14           typ => dbms_crypto.encrypt_aes128 +
 15                  dbms_crypto.chain_cbc +
 16                  dbms_crypto.pad_pkcs5
 17        );
 18     return l_enc_val;
 19  end;
 20  /

SQL>
SQL>