Solved

Encrypt a column in a oracle table

Posted on 2002-06-19
6
9,435 Views
Last Modified: 2012-08-13
How do I encrypt a field in an oracle table and allow it to be display by a specific oracle user and encrypt for all others users who would browse the table. However with the specific users browse or access the table the field is decrypted for the specific user.
0
Comment
Question by:MrCrawfish
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
6 Comments
 
LVL 3

Expert Comment

by:UsamaMunir
ID: 7093740
you can use DBMS_OBSIFUCATION_TOOLKIT package to encrypt table data, however it will be encrypted for all users then. you will have to do a workarround. create procedures to decrypt the information and provide the users(who are supposed to see the data) with those procedures or functions. for ex u can provide users with a decrypt function and let them write some thing like

select decrypt(column) from encrypted_table. or something like that
0
 
LVL 1

Expert Comment

by:Bashar
ID: 7093796
Hi,
Here is an idea that might me of interest.
You can use the DBMS_OBSIFUCATION_TOOLKIT package as stated by Usama. But, the idea is to use the invokers user name as the key in which you do the encryption/decryption.
You encrypt the data using his username (or part of it) and then he reads the data by calling a function that accepts the current username as a parameter. This would be a good way to encrypt the data for each user, since the "user" built-in function returns the current username only. You can't trick it.

Example: To store the data use:
My_Package.encrypt('TARGET_USER');
Where "TARGET_USER" is the user who will be allowed to view the data.
Later, he could decrypt and view the data by invoking:
My_Package.decrypt(user);
this will return the correct data only if he is logged on as "TARGET_USER".

Hope this helps.

Regards,
Bashar.
0
 
LVL 1

Expert Comment

by:Bashar
ID: 7093808
Sorry for the typo. I did a copy/paste of the package name.
Its name is: DBMS_OBFUSCATION_TOOLKIT.

Regards,
Bashar.
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 48

Accepted Solution

by:
schwertner earned 50 total points
ID: 7094903
ENCRYPTION:
Here is a procedure, that can be used to encrypt and decrypt the column values. The same procedure can

be used for parameter strings.

/* Procedure to encrypt and decrypt the column values
input colum value, and the action(encrypt or decrypt)
*/

CREATE OR REPLACE PROCEDURE EnDe ( p_in_value IN VARCHAR2,
p_action IN VARCHAR2,
p_out_value OUT VARCHAR2 ) IS

v_text_char VARCHAR2(1) := NULL;
v_full_string VARCHAR2(20) := NULL;
v_string_length number :=0;

BEGIN
v_string_length :=LENGTH(p_in_value);
IF p_action = 'ENCRYPT' then
FOR v_loop_count IN 1..v_string_length LOOP
v_text_char := SUBSTR ( p_in_value, v_loop_count, 1 );
v_text_char := CHR (v_string_length + ASCII ( v_text_char )+
v_loop_count );
v_full_string := v_full_string||v_text_char;
END LOOP;
ELSIF p_action = 'DECRYPT' then
FOR v_loop_count in 1..v_string_length LOOP
v_text_char := SUBSTR ( p_in_value, v_loop_count, 1 );
v_text_char := CHR ( ASCII ( v_text_char ) - v_string_length -
v_loop_count );
v_full_string := v_full_string||v_text_char;
END LOOP;
END IF;
p_out_value := v_full_string;
END EnDe;

/*PL/SQL block for testing....*/

DECLARE
PASSWD VARCHAR2(20):='&PASSWORD';
OUTPASSWD VARCHAR2(20):=NULL;
BEGIN
ENDE(PASSWD,'ENCRYPT',OUTPASSWD);
DBMS_OUTPUT.PUT_LINE('ENCRYPTED PASSWORD : '||OUTPASSWD);
ENDE(OUTPASSWD,'DECRYPT',OUTPASSWD);
DBMS_OUTPUT.PUT_LINE('DECRYPTED(GIVEN) PASSWORD : '||OUTPASSWD);
END;
/

/* OUTPUT */

Enter value for password: HOWISIT
old 2: PASSWD VARCHAR2(20):='&PASSWORD';
new 2: PASSWD VARCHAR2(20):='HOWISIT';

ENCRYPTED PASSWORD : PXaT_Vb

DECRYPTED(GIVEN) PASSWORD : HOWISIT

You can use your own logic too.

Another example:

In 7.x , PL/SQL can do this through the use of DBMS_CRYPTO_TOOLKIT. However, this package is only functional

in Trusted Oracle or the Oracle Security Server.

You can write your own functions, for example,

- - - - - - - - - - - - - - - - Code begins here - - - - - - - - -

CREATE OR REPLACE FUNCTION encrypt( convstr IN VARCHAR2 ) RETURN VARCHAR2 IS
retstr VARCHAR2( 32000 ):= NULL;
tempno NUMBER;
BEGIN
FOR i IN 1 .. length( convstr ) LOOP
tempno:= ascii( substr( convstr, i, 1 ));
retstr:= retstr || ltrim( to_char( tempno + 60, '000' ));
END LOOP;

RETURN retstr;
END;
/

CREATE OR REPLACE FUNCTION decrypt( convstr IN VARCHAR2 ) RETURN VARCHAR2 IS
retstr VARCHAR2( 32000 ):= NULL;
tempch VARCHAR2( 2 );
loopno NUMBER;
stepno NUMBER;
BEGIN
loopno:= length( convstr ) / 3;
FOR i IN 1 .. loopno LOOP
stepno:= i * 3 - 2;
tempch:= chr( to_number( substr( convstr, stepno, 3 )) - 60 );
retstr:= retstr || tempch;
END LOOP;

RETURN retstr;
END;
/

- - - - - - - - - - - - - - - - Code ends here - - - - - - - - - -

Sample Output
-------------

SQL> SELECT encrypt('mystr') FROM dual;

ENCRYPT('MYSTR')
---------------------------------------
169181175176174

SQL> SELECT decrypt('169181175176174') FROM dual;

DECRYPT('169181175176174')
-------------------------------------------------
mystr




Also on 'Ask Tom': http://asktom.oracle.com/pls/ask/f?p=4950:8:::::F4950_P8_DISPLAYID:95412348059

you can read the following:

I'll get to some possible solutions for 8.1.5 and before in a moment but in
Oracle8i, release 8.1.6 there is:

<quote>
The DBMS_OBFUSCATION_TOOLKIT Package

For applications dealing with this highly sensitive data, Oracle provides the
DBMS_OBFUSCATION_TOOLKIT PL/SQL package to encrypt and decrypt data, including
string inputs and raw inputs. The function is limited to selected algorithms,
such as the Data Encryption Standard (DES). Developers may not plug in their own
encryption algorithms, and the key length is also fixed. The function prohibits
making multiple passes of encryption; that is, you cannot nest encryption calls,
thereby encrypting an encrypted value. These restrictions are required by U.S.
laws governing the export of cryptographic products.
</quote>


You can read more about it at


http://technet.oracle.com/doc/oracle8i_816/server.816/a76936/dbms_obf.htm#6518


Now, in 8.1.5 and before -- what could you do?  Well, most passwords are not
stored encrypted, but rather they are stord HASHED or DIGESTED.  For example, in
Oracle, the password is not encrypted (that would imply there is a decrypt but
there is not).  Instead, to validate a username/password we take them, plus some
magic "salt" and hash it.  This results in a fixed length string of some bytes
of data.  We compare that to the stored hash and if they match -- you are in.  
If not -- you are not.

So, if I was to write my own password check function, I would simply glue the
USERNAME together with the supplied PASSWORD.  I would call
dbms_utility.get_hash_value to generate some hashes.  See

http://technet.oracle.com/doc/oracle8i_816/server.816/a76936/dbms_ut2.htm#1002139
for info in the hashing package.  For example:

ops$tkyte@8i> declare
2      function digest( p_username in varchar2, p_password in varchar2 )
return varchar2
3      is
4      begin
5          return ltrim( to_char( dbms_utility.get_hash_value(
upper(p_username)||'/'||upper(p_password),
6                                                       1000000000,
power(2,30) ),
7                          rpad( 'X',29,'X')||'X' ) );
8      end digest;
9  begin
10      for x in ( select username from all_users where rownum < 20 )
11      loop
12          dbms_output.put_line( 'User: ' || rpad( x.username , 30 ) ||
13                                ' digest: ' || digest( x.username, 'TIGER' )
);
14      end loop;
15  end;
16  /
User: SYS                            digest: 6869FA1A
User: SYSTEM                         digest: 79F08AFC
User: OUTLN                          digest: 5ABFB255
User: DBSNMP                         digest: 43415F6B
User: TRACESVR                       digest: 49CF26F6
User: CTXSYS                         digest: 4910C297
User: OEM                            digest: 69463BC2
User: ORDSYS                         digest: 6F048B2B
User: ORDPLUGINS                     digest: 6547459C
User: MDSYS                          digest: 43C0B367
User: AURORA$ORB$UNAUTHENTICATED     digest: 5073BBFC
User: WEB$CDEJESUS                   digest: 6FB5CDB6
User: SCOTT                          digest: 4307767C
User: WEB$SMAYFIEL                   digest: 71ED5065
User: UTILS                          digest: 5B7912B7
User: OAS_PUBLIC                     digest: 502BAE3A
User: WEBDB                          digest: 5A7AC149
User: WEB$RDRISCOL                   digest: 3E72D3F6
User: WEB$KWARREN                    digest: 7123F5A1

PL/SQL procedure successfully completed.


So, I have a function digest that takes a username and password, hashes it into
1 of 1073741824 different numeric values, adds 1000000000 to it (to make it big)
and turns it into HEX.  This is what I would store in the database -- not the
password (which I really don't ever need to know).  

Now when the user presents me a username/password, I digest it and compare -- if
they match, you get in.  If not you do not.

Note that before 8.1.5, the to_char( .., 'XXXXXX' ) format does not work (not
implemented).  See
http://osi.oracle.com/~tkyte/hexdec/index.html
for a solution in 8.0 and 7.x  (I have a to_hex routine
you can use).
0
 
LVL 3

Expert Comment

by:rkogelhe
ID: 7095512
The only comment I would have to add to this is that once you have a function such as ENCR() and DECR(), you can create the table MYTABLE such that an on-insert trigger uses ENCR() on the encrypted column. Then create a special view

Create or replace view schowner.MYTABLE_DECR_VW As
  Select ID, ATTR1, ATTR2, DECR(ATTR3) ATTR3
  From MYTABLE;

Then grant this view only to the privileged user, and give the user a private synonym:

create synonym privuser.MYTABLE for schowner.MYTABLE_DECR_VW;

Oh, also be careful using DBMS_OBFUSCATION_TOOLKIT when doing exports. When you re-import the data, you can lose the coordination between the encryption and decryption. Test it :)

Ryan
0
 
LVL 4

Expert Comment

by:asimkovsky
ID: 7252820
Make sure you wrap your PL/SQL code with the PL/SQL wrapper. If you don't, your encryption key will be visible in DBA_SOURCE.



Andrew
0

Featured Post

Instantly Create Instructional Tutorials

Contextual Guidance at the moment of need helps your employees adopt to new software or processes instantly. Boost knowledge retention and employee engagement step-by-step with one easy solution.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Background In several of the companies I have worked for, I noticed that corporate reporting is off loaded from the production database and done mainly on a clone database which needs to be kept up to date daily by various means, be it a logical…
How to Unravel a Tricky Query Introduction If you browse through the Oracle zones or any of the other database-related zones you'll come across some complicated solutions and sometimes you'll just have to wonder how anyone came up with them.  â€¦
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.
Suggested Courses

623 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question