в 13:45, 26 мая 2010

2010-05-26T13:45:46Z

Admin: 1 версия

2010-05-26T10:05:56Z

1 версия

Новая страница

== dbms_obfuscation_toolkit.DES3Decrypt==



<source lang="sql">
SQL> create table emp(
2 emp_id integer primary key
3 ,lastname varchar2(20) not null
4 ,firstname varchar2(15) not null
5 ,midinit varchar2(1)
6 ,street varchar2(30)
7 ,city varchar2(20)
8 ,state varchar2(2)
9 ,zip varchar2(5)
10 ,shortZipCode varchar2(4)
11 ,area_code varchar2(3)
12 ,phone varchar2(8)
13 ,company_name varchar2(50));
Table created.
SQL>
SQL>
SQL> insert into emp(emp_id,lastname,firstname,midinit,street,city,state,zip,shortZipCode,area_code,phone,company_name)values
2 (1,"Jones","Joe","J","1 Ave","New York","NY","11202","1111","212", "221-4333","Big Company");
1 row created.
SQL> insert into emp(emp_id,lastname,firstname,midinit,street,city,state,zip,shortZipCode,area_code,phone,company_name)values
2 (2,"Smith","Sue","J","1 Street","New York","NY","11444","1111","212", "436-6773","Little Company");
1 row created.
SQL> insert into emp(emp_id,lastname,firstname,midinit,street,city,state,zip,shortZipCode,area_code,phone,company_name)values
2 (3,"X","Peggy","J","1 Drive","New York","NY","45502","2222","212", "234-4444","Medium Company");
1 row created.
SQL> insert into emp(emp_id,lastname,firstname,midinit,street,city,state,zip,shortZipCode,area_code,phone,company_name)values
2 (4,"Murdy","Jill", null,"930 Eady St","New York","NY","45452","6458","212", "634-7733","Wilton Company");
1 row created.
SQL>
SQL> ALTER TABLE emp
2 ADD ccn VARCHAR2(2048);
Table altered.
SQL>
SQL> CREATE TABLE emp_id_key_table(emp_id NUMBER PRIMARY KEY,key_value VARCHAR2(80));
Table created.
SQL>
SQL>
SQL> CREATE or REPLACE FUNCTION ccn_decrypt(v_emp_id IN NUMBER, v_ccn IN VARCHAR2)
2 RETURN VARCHAR2
3 IS
4 v_key VARCHAR2(80);
5 v_decrypted_string VARCHAR2(2048);
6 BEGIN
7 SELECT key_value INTO v_key FROM emp_id_key_table WHERE emp_id = v_emp_id;
8
9 dbms_obfuscation_toolkit.DES3Decrypt(input_string => v_ccn, key_string => v_key, decrypted_string => v_decrypted_string);
10 RETURN (v_decrypted_string);
11 END;
12 /
Function created.
SQL>
SQL> select emp_id, ccn_decrypt(emp_id, ccn) from emp where ccn is not null;
no rows selected
SQL>
SQL> drop table emp;
Table dropped.
SQL> drop table emp_id_key_table;
Table dropped.</source>

== dbms_obfuscation_toolkit.DES3GETKEY and dbms_obfuscation_toolkit.DES3ENCRYPT==



<source lang="sql">
SQL> -- create demo table
SQL> create table emp(
2 ID VARCHAR2(4 BYTE) NOT NULL,
3 fname VARCHAR2(10 BYTE),
4 lname VARCHAR2(10 BYTE),
5 Start_Date DATE,
6 End_Date DATE,
7 Salary Number(8,2),
8 City VARCHAR2(10 BYTE),
9 Description VARCHAR2(15 BYTE)
10 )
11 /
Table created.
SQL>
SQL>
SQL>
SQL> CREATE or REPLACE TRIGGER ccn_encrypt_trig
2 BEFORE INSERT or UPDATE of fname on emp
3 FOR EACH ROW
4 DECLARE
5 v_key VARCHAR2(80);
6 v_seed VARCHAR2(80) := "0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ";
7 v_encrypted_string VARCHAR2(2048);
8 BEGIN
9 dbms_obfuscation_toolkit.DES3GETKEY(which=>1, seed_string=>v_seed,key=>v_key);
10 dbms_obfuscation_toolkit.DES3ENCRYPT(input_string => RPAD(:NEW.fname,16," "), key_string => v_key, encrypted_string => v_encrypted_string );
11 END;
12 /
Trigger created.
SQL>
SQL> -- prepare data
SQL> insert into emp(ID, fname, lname, Start_Date, End_Date, Salary, City, Description)
2 values ("01","Jason", "Martin", to_date("19960725","YYYYMMDD"), to_date("20060725","YYYYMMDD"), 1234.56, "Toronto", "Programmer")
3 /
insert into emp(ID, fname, lname, Start_Date, End_Date,
Salary, City, Description)
*
ERROR at line 1:
ORA-28237: seed length too short
ORA-06512: at "SYS.DBMS_OBFUSCATION_TOOLKIT_FFI",
line 3
ORA-06512: at "SYS.DBMS_OBFUSCATION_TOOLKIT", line 76
ORA-06512: at "sqle.CCN_ENCRYPT_TRIG", line 6
ORA-04088: error during execution of trigger
"sqle.CCN_ENCRYPT_TRIG"

SQL>
SQL> drop table emp;
Table dropped.
SQL></source>

== Demonstrate DES3 encryption==



<source lang="sql">
SQL>
SQL> DECLARE
2 input_string VARCHAR2(160) := "password01";
3 key_string VARCHAR2(24);
4
5 encrypted_string VARCHAR2(2048);
6 decrypted_string VARCHAR2(2048);
7 error_in_input_buffer_length EXCEPTION;
8 PRAGMA EXCEPTION_INIT(error_in_input_buffer_length, -28232);
9 INPUT_BUFFER_LENGTH_ERR_MSG VARCHAR2(100) := " ";
10
11 BEGIN
12 input_string := input_string||input_string||input_string||input_string||input_string||input_string||input_string||input_string;
13 dbms_output.put_line("input string : " || input_string);
14 dbms_obfuscation_toolkit.DES3GETKEY(which=>1, seed_string=>input_string,key=>key_string);
15 dbms_obfuscation_toolkit.DESENCRYPT(input_string => input_string,key_string => key_string, encrypted_string => encrypted_string );
16 dbms_output.put_line("encrypted string : " ||encrypted_string);
17 dbms_obfuscation_toolkit.DESDecrypt(input_string => encrypted_string,key_string => key_string, decrypted_string => decrypted_string);
18 dbms_output.put_line("Decrypted output : " ||decrypted_string);
19 if input_string = decrypted_string THEN
20 dbms_output.put_line("> DES Encryption and Decryption successful");
21 END if;
22 EXCEPTION
23 WHEN error_in_input_buffer_length THEN dbms_output.put_line("> " || INPUT_BUFFER_LENGTH_ERR_MSG);
24 END;
25
26 /

PL/SQL procedure successfully completed.
SQL></source>

← Предыдущая	Версия 13:45, 26 мая 2010
(нет различий)

Oracle PL/SQL Tutorial/System Packages/dbms obfuscation toolkit - История изменений

в 13:45, 26 мая 2010

Admin: 1 версия