Hi,
We have recently converted our 11g DB character set to UTF8, since then we are not able to view data in the data grid for some of the columns that has encrypted data.
How can this be resolved? We do not have any issues when we use SQLDeveloper to view data.
Thanks,
Narmada
If it’s Oracle’s Transparent Data Encryption, there shouldn’t be anything that you have to do in Toad to see the data. If it’s some other kind of encryption, I guess I’ll need more information.
We are using DBMS_OBFUSCATION_TOOLKIT.desencrypt to encrypt and store the data into varchar2 column of a table.
OK. Toad doesn’t have any built-in mechanism to automatically decrypt columns that are encrypted that way. You’d have to include the decrypt calls in your SQL statement. I don’t know what SQLDeveloper is doing, but if you feel it’s a bug in Toad, I’ll need some steps to reproduce the problem.
I used that toolkit some time back to create a Web based password vault under 9i. If I remember correctly, I had to use RAW or BLOB data types to hold the encrypted data. Not sure if the toolkit is improved or different/better these days as I haven't needed to use it lately.
HTH.
Cheers,
Norm [ TeamT ]
Sent from my Android device with K-9 Mail. Please excuse my brevity.
John,
I am trying to display encrypted data… it works fine for non utf8 DB.
Does the encryption result in null (0) character as part of it? Select from that column so that you see the data in hex (the Oracle function to do so slips my
mind) and look for null character. If you see this then Toad will only display the data up to the null as null character terminates strings in the language Toad is written in, Delphi. Do not select from the column and then use Toad's built-in hex viewer as
this will not work.
Hi Narmada,
The best way we can help you is if you can provide us specific information on what you’re doing, what you’re attempting to do, what your operating environment is like, and what is being output.
So far, all we know is:
You’re presumably using Toad
You have Oracle 11g
You’re using DBMS_OBFUSCATION_TOOLKIT
Something works in SQL*Dev and not in Toad.
I hope you can see that from our point of view, there’s not really a lot to go on here. The details are important, so some information that would be a good start for troubleshooting might be:
Version of Toad, including 32/64-bit
Version of Windows
Specific Oracle server version/platform
Specific Oracle client installation and version
Example SQL statement and the means to recreate it (e.g. table layout).
It might also help knowing if your SQL*Dev and Toad are both using the same Oracle Client.
You’re getting into the fun world of encryption and (potentially) character set translations in Oracle, and it can be a bit of a challenge. Let us know more details about your setup and hopefully we can help you troubleshoot!
Rich
Select dump (column, 16) from ... where ...;
Cheers,
Norm [ TeamT ]
Sent from my Android device with K-9 Mail. Please excuse my brevity.
I know this thread is very old, but a similar case just came through support, so I guess people are still having this problem.
I just created a small test and was not able to reproduce any problem. Just posting my test here incase it can help somebody.
The script that I used:
CREATE OR REPLACE PACKAGE toolkit AS
FUNCTION encrypt (p_text IN VARCHAR2) RETURN RAW;
FUNCTION decrypt (p_raw IN RAW) RETURN VARCHAR2;
END toolkit;
/
CREATE OR REPLACE PACKAGE BODY toolkit AS
g_key RAW(32767) := UTL_RAW.cast_to_raw('12345678');
g_pad_chr VARCHAR2(1) := '~';
PROCEDURE padstring (p_text IN OUT VARCHAR2);
-- --------------------------------------------------
FUNCTION encrypt (p_text IN VARCHAR2) RETURN RAW IS
-- --------------------------------------------------
l_text VARCHAR2(32767) := p_text;
l_encrypted RAW(32767);
BEGIN
padstring(l_text);
DBMS_OBFUSCATION_TOOLKIT.desencrypt(input => UTL_RAW.cast_to_raw(l_text),
key => g_key,
encrypted_data => l_encrypted);
RETURN l_encrypted;
END;
-- --------------------------------------------------
-- --------------------------------------------------
FUNCTION decrypt (p_raw IN RAW) RETURN VARCHAR2 IS
-- --------------------------------------------------
l_decrypted VARCHAR2(32767);
BEGIN
DBMS_OBFUSCATION_TOOLKIT.desdecrypt(input => p_raw,
key => g_key,
decrypted_data => l_decrypted);
RETURN RTrim(UTL_RAW.cast_to_varchar2(l_decrypted), g_pad_chr);
END;
-- --------------------------------------------------
-- --------------------------------------------------
PROCEDURE padstring (p_text IN OUT VARCHAR2) IS
-- --------------------------------------------------
l_units NUMBER;
BEGIN
IF LENGTH(p_text) MOD 8 > 0 THEN
l_units := TRUNC(LENGTH(p_text)/8) + 1;
p_text := RPAD(p_text, l_units * 8, g_pad_chr);
END IF;
END;
-- --------------------------------------------------
END toolkit;
/
create table toolkit_test
(new_data varchar2(1000),
encrypted_data raw(2000),
decrypted_data varchar2(1000));
insert into toolkit_test
(new_data) values ('hello');
insert into toolkit_test
(new_data) values ('encrypt this');
insert into toolkit_test
(new_data) values ('test');
commit;
update toolkit_test
set encrypted_data = toolkit.encrypt(new_data);
commit;
update toolkit_test
set decrypted_data = toolkit.decrypt(encrypted_data);
commit;
select * from toolkit_test;
and the proof that it's working in Toad...
