asked on

Oralce Function

Is there any function in Oracle to encrypt the data so that when people select from SQL Plus and can not see the real data? (Like one in SQL Server)
I use this to encrypt the password of users in my multi-user system.

ASKER CERTIFIED SOLUTION

alexgg

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

NBee

ASKER

I create a table in Oracle with the columns: UserName, Password.
When I add new user with his password, I want the password field will be encrypted by Oracle function so that no one can get it.
Is there any such a function in Oracle (encrypt and decrypt it)???

bkowalski

Check out the DBMS_OBFUSCATION_TOOLKIT package. The script to install the package is in %ORACLE_HOME%\rdbms\admin. I forget what it's called, and I don't have Oracle installed on my machine right now, so just do a search for files containing the word encrypt.

Documentation on the package can be found here:

http://technet.oracle.com/docs/products/oracle8i/doc_library/817_doc/appdev.817/a76936/dbms_obf.htm#6518

mschlafer

NBee,

alexqq provided an answer that "encrypts" PL/SQL source code of stored procedures, functions, and packages. The source code is referred to as "wrapped" in this state. It resembles machine code in appearance, but this doesn't apply to data in tables.

As for 2-way encryption of your data, you will need to find a piece of code or build one that accomplishes the task.

One way to obfuscate, but not necessarily secure the password would be to shift each character in the string. For example (assuming a one-byte character), x <-- chr( mod( ascii( c ) + 13, 256 ) ).

Best of luck,

Martin Schlafer
Answerthink

maceyah

See this simple implementation from http://www.oracle.com/oramag/code/cod11226.html

create or replace FUNCTION Xorbin(c1 CHAR,c2 CHAR) RETURN CHAR IS
loop1 NUMBER;
loop11 NUMBER;
r1 VARCHAR2(8);
r2 VARCHAR2(8);
r3 NUMBER;
result VARCHAR2(40);
divis NUMBER;
BEGIN
result := '';
FOR loop1 IN 1..LENGTH(c1) LOOP
r1 := Convbin(SUBSTR(c1,loop1,1));
r2 := Convbin(SUBSTR(c2,loop1,1));
divis := 128;
r3 := 0;
FOR loop11 IN 1..8 LOOP
IF TO_NUMBER(SUBSTR(r1,loop11,1))+TO_NUMBER(SUBSTR(r2,loop11,1))=1 THEN
r3 := r3 + divis;
END IF;
divis := divis / 2;
END LOOP;
result := result || CHR(r3);
END LOOP;
RETURN(result);
END;
/

create or replace FUNCTION Convbin(c1 CHAR) RETURN CHAR IS
loop1 NUMBER;
value NUMBER;
divis NUMBER;
r1 VARCHAR2(30);
-- encryption encrypt crypt unencrypt in Oracle

BEGIN
r1 := '';
value := ASCII(c1);
divis := 128;
FOR loop1 IN 0..7 LOOP
IF TRUNC(value/divis) = 1 THEN
r1 := r1 || '1';
ELSE
r1 := r1 || '0';
END IF;
value := value MOD divis;
divis := divis / 2;
END LOOP;
RETURN r1;
END;
/

SET serveroutput ON
DECLARE
result VARCHAR2(30);
mask VARCHAR2(30);
BEGIN
mask := 'abc'; /* Must be greater or equal to the value that
will be "Crypted" */

result := XORBIN('ABD',mask); /* "Crypt" */
dbms_output.put_line(result);

result := XORBIN(result,mask); /* "Unencrypt" */
dbms_output.put_line(result);

END;
/

dbalaski

Hmmm, 14 questions asked -- 1 graded

most locked for well over 30 days ----

you should accept/reject the answers instead of leading people on,......

Netminder

Netminder

Proposed answer rejected.

maceyah,

You should enter your remarks as an Answer only when you are certain that yours are the absolutely 100 per cent certain solution to the problem, AND when they do not duplicate or paraphrase someone else's suggestion.

Experts Exchange is a collaborative site. When you "Answer", as opposed to "Comment", you lock the question, which makes it difficult for other Experts to contribute towards a solution.

Further, you can see that a rejected Answer is shown in bright red, and I don't know about you, but to me, I feel like hiding when that happens.

Netminder
Community Support Moderator
Experts Exchange

Netminder

Admin notified of user neglect. Force/accepted by

Netminder
Community Support Moderator
Experts Exchange