DBMS_CRYPTO ve LOB dataları Nasıl sifreleriz?

Aşağıdaki örnekte BLOB datasını nasıl encrypt ve decrypt yapıldıgını gosteriyorum.

* İlk önce yeni bir tablo create edelim(içinde BLOB kolon olan)
* RAW deger insert edelim
* Raw datayı Encrypt edelim.
* Encrypt ettigimiz datayı Decrypt edelim.


create table table_lob (id number, loc blob);

-- 3 tane bos lobs for src/enc/dec insert edelim

insert into table_lob values (1, EMPTY_BLOB());
insert into table_lob values (2, EMPTY_BLOB());
insert into table_lob values (3, EMPTY_BLOB());


declare
srcdata RAW(1000);
srcblob BLOB;
encrypblob BLOB;
encrypraw RAW(1000);
encrawlen BINARY_INTEGER;
decrypblob BLOB;
decrypraw RAW(1000);
decrawlen BINARY_INTEGER;
leng INTEGER;

begin

-- 16 bytes raw datayı girelim
srcdata := hextoraw('6D6D6D6D6D6D6D6D6D6D6D6D6D6D6D6D');

dbms_output.put_line('---');
dbms_output.put_line('girilen data ' || srcdata);
dbms_output.put_line('---');

-- Bos lob locator for src/enc/dec select edelim

select loc into srcblob from table_lob where id = 1;
select loc into encrypblob from table_lob where id = 2;
select loc into decrypblob from table_lob where id = 3;

dbms_output.put_line('Bos create ettik LOBS');
dbms_output.put_line('---');

leng := DBMS_LOB.GETLENGTH(srcblob);
IF leng IS NULL THEN
dbms_output.put_line('Kaynak BLOB uzunlugu NULL ');
ELSE
dbms_output.put_line('Kaynak BLOB Uzunlugu ' || leng);
END IF;

leng := DBMS_LOB.GETLENGTH(encrypblob);
IF leng IS NULL THEN
dbms_output.put_line('Encrypt BLOB uzunlugu NULL ');
ELSE
dbms_output.put_line('Encrypt BLOB uzunlugu ' || leng);
END IF;

leng := DBMS_LOB.GETLENGTH(decrypblob);
IF leng IS NULL THEN
dbms_output.put_line('Decrypt BLOB uzunlugu NULL ');
ELSE
dbms_output.put_line('Decrypt BLOB uzunlugu ' || leng);
END IF;

-- source raw datasını blob yazalım

DBMS_LOB.OPEN (srcblob, DBMS_LOB.lob_readwrite);
DBMS_LOB.WRITEAPPEND (srcblob, 16, srcdata);
DBMS_LOB.CLOSE (srcblob);

dbms_output.put_line('Kaynak raw data yazıldı kaynak blob');
dbms_output.put_line('---');

leng := DBMS_LOB.GETLENGTH(srcblob);
IF leng IS NULL THEN
dbms_output.put_line('kaynak BLOB uzunlugu NULL ');
ELSE
dbms_output.put_line('kaynak BLOB uzunlugu ' || leng);
END IF;

DBMS_CRYPTO.Encrypt(encrypblob,
srcblob,
DBMS_CRYPTO.AES_CBC_PKCS5,
hextoraw ('000102030405060708090A0B0C0D0E0F101112131415161718191A1B1C1D1E1F'),
hextoraw('00000000000000000000000000000000'));


dbms_output.put_line('Encryption yapıldı');
dbms_output.put_line('---');

leng := DBMS_LOB.GETLENGTH(encrypblob);
IF leng IS NULL THEN
dbms_output.put_line('Encrypt BLOB uzunlugu NULL');
ELSE
dbms_output.put_line('Encrypt BLOB uzunlugu ' || leng);
END IF;

-- encrypblob to a raw okuyalım
encrawlen := 999;

DBMS_LOB.OPEN (encrypblob, DBMS_LOB.lob_readwrite);
DBMS_LOB.READ (encrypblob, encrawlen, 1, encrypraw);
DBMS_LOB.CLOSE (encrypblob);

dbms_output.put_line('encrypt blob raw okundu');
dbms_output.put_line('---');

dbms_output.put_line('Encrypted data (256 bit key) ' || encrypraw);
dbms_output.put_line('---');


DBMS_CRYPTO.Decrypt(decrypblob,
encrypblob,
DBMS_CRYPTO.AES_CBC_PKCS5,
hextoraw
('000102030405060708090A0B0C0D0E0F101112131415161718191A1B1C1D1E1F'),
hextoraw('00000000000000000000000000000000'));

leng := DBMS_LOB.GETLENGTH(decrypblob);
IF leng IS NULL THEN
dbms_output.put_line('Decrypt BLOB Uzunlugu NULL');
ELSE
dbms_output.put_line('Decrypt BLOB Uzunlugu ' || leng);
END IF;

-- decrypblob raw okuyalım
decrawlen := 999;

DBMS_LOB.OPEN (decrypblob, DBMS_LOB.lob_readwrite);
DBMS_LOB.READ (decrypblob, decrawlen, 1, decrypraw);
DBMS_LOB.CLOSE (decrypblob);

dbms_output.put_line('Decrypted data (256 bit key) ' || decrypraw);
dbms_output.put_line('---');

DBMS_LOB.OPEN (srcblob, DBMS_LOB.lob_readwrite);
DBMS_LOB.TRIM (srcblob, 0);
DBMS_LOB.CLOSE (srcblob);

DBMS_LOB.OPEN (encrypblob, DBMS_LOB.lob_readwrite);
DBMS_LOB.TRIM (encrypblob, 0);
DBMS_LOB.CLOSE (encrypblob);

DBMS_LOB.OPEN (decrypblob, DBMS_LOB.lob_readwrite);
DBMS_LOB.TRIM (decrypblob, 0);
DBMS_LOB.CLOSE (decrypblob);

end;
/

Advertisements

About oracledocuments

Zekeriya Beşiroğlu, It joined the Bilginc IT Academy in July 2000. In the meantime, the Oracle Education, Oracle Data Base Management System and Oracle Internet Technologies, Oracle development technologies such as the training of Oracle products is responsible for training as consultants. During this task, since 1 April 2008 with 293 Oracle training in total, in 2150 Oracle customers successfully submitted. Oracle 10g New York in November 2002 have received training and education in Turkey was the first time the consultants. 20 April 2008 on education in Turkey 11g is the first who was a consultant. Oracle Real Application Cluster Expert advisor is certified is the first . Http://zekeriyabesiroglu.blogspot.com and Http://www.oracleforum.info owner and manager of the site. Its own has more than one hundred articles. In addition, creation and improvement of training materials are also related to work. Oracle products are used, database management and reporting Academy eruditely It also is working on. From the date of 1 January 2009 will continue to work as the Technical Director
This entry was posted in Uncategorized and tagged , . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s