Encrypt a column in a oracle table

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.
MrCrawfishAsked:
Who is Participating?
 
schwertnerConnect With a Mentor Commented:
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
 
UsamaMunirCommented:
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
 
BasharCommented:
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
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
BasharCommented:
Sorry for the typo. I did a copy/paste of the package name.
Its name is: DBMS_OBFUSCATION_TOOLKIT.

Regards,
Bashar.
0
 
rkogelheCommented:
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
 
asimkovskyCommented:
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
All Courses

From novice to tech pro — start learning today.