Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Encrypting and Decrypting the table

Posted on 2002-04-11
6
Medium Priority
?
1,167 Views
Last Modified: 2007-12-19
hai,

i want a step by step procedure for decrypting and encrypting the table content. i am storing the username
and password of one of the application in a table using the frontend tool (VB or Delphi or D2k)

i tried out using "Translate" query but not satisfied.

so plz guide me how to go abt by..

warm wishes

Shyam
0
Comment
Question by:shyampaliyath
[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 5

Expert Comment

by:sora
ID: 6936010
use DBMS_OBFUSCATION_TOOLKIT Oracle supplied package:

See this:


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


sora
0
 
LVL 48

Accepted Solution

by:
schwertner earned 360 total points
ID: 6936115
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 5

Expert Comment

by:sora
ID: 6936243
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 1

Author Comment

by:shyampaliyath
ID: 6936357
thanks for the quick response.

if u don't think otherwise....

plz one more question how can i lock any particular table??

in the sense by giving password to the particular table in

a user.

Warm wishes

Shyam


0
 
LVL 5

Expert Comment

by:sora
ID: 6936366
You can GRANT the table to the particular user.

GRANT SELECT, INSERT,ETC ON TABLE_NAME TO USER_NAME;


or

create a role

SQL> CREATE ROLE XYZ IDENTIFIED BY ABC;

Role created.

SQL> GRANT SELECT,INSERT,DELETE,UPDATE ON TABLE_NAME TO XYZ;


SQL> GRANT XYZ TO USER_NAME;


Then when the user tries to access this table, he must do this:

SQL> SET ROLE XYZ IDENTIFIED BY ABC;

Role set.

SQL> Then he can access the table. Otherwise he cannot.


There is no other way to password protect a particular table.


sora
0
 
LVL 4

Expert Comment

by:asimkovsky
ID: 6936842
There is a way to work around it. You can have a table that only a single role has privileges to (except for the owner of the table of course). Grant the role to all the users that need it, but don't set it as default role. When a user needs to get to the table, they have to ALTER SESSION SET ROLE =role_name, and you can have it configured to require a password.


CREATE ROLE unlock_table IDENTIFIED BY tiger;

CONNECT table_owner@connect_string

GRANT SELECT ON table_name TO unlock_table;

CONNECT dba@connect_string

GRANT unlock_table TO accessing_user;

ALTER USER accessing_user DEFAULT ROLE ALL EXCEPT unlock_table;


Andrew
0

Featured Post

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

Question has a verified solution.

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

Why doesn't the Oracle optimizer use my index? Querying too much data Most Oracle developers know that an index is useful when you can use it to restrict your result set to a small number of the total rows in a table. So, the obvious side…
Shell script to create broker configuration file using current broker Configuration, solely for purpose of backup on Linux. Script may need to be modified depending on OS-installation. Please deploy and verify the script in a test environment.
This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.

704 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