Solved

Encrypt a column in a oracle table

Posted on 2002-06-19
6
9,310 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
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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 47

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

How to Create User-Defined Aggregates in Oracle Before we begin creating these things, what are user-defined aggregates?  They are a feature introduced in Oracle 9i that allows a developer to create his or her own functions like "SUM", "AVG", and…
Cursors in Oracle: A cursor is used to process individual rows returned by database system for a query. In oracle every SQL statement executed by the oracle server has a private area. This area contains information about the SQL statement and the…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

705 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

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now