Solved

Encrypting and Decrypting the table

Posted on 2002-04-11
6
1,136 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
6 Comments
 
LVL 5

Expert Comment

by:sora
Comment Utility
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 47

Accepted Solution

by:
schwertner earned 120 total points
Comment Utility
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
Comment Utility
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 1

Author Comment

by:shyampaliyath
Comment Utility
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
Comment Utility
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
Comment Utility
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

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

Suggested Solutions

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 post first appeared at Oracleinaction  (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…
This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.

772 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

12 Experts available now in Live!

Get 1:1 Help Now