Is there a way using oracle built-in function to encrypt plain text? Actually my problems is that I store all the authentication information (including password) inside a LOGIN_TABLE. My ideal plan is to encrypt the password. So can it be doen within ORACLE?
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:
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.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 in place of the to_char( number, 'XXXXX' )
format).