Encryption

Dúvidas, dicas e truques de PL/SQL. Aqui também vão assuntos relacionados a pacotes, triggers, funções, Java-Stored Procedures, etc
  

Poston Thu, 30 Sep 2004 4:43 pm

I wonder how I do to encrypt a password and then be able to decrypt. How can I do that?! is there any Oracle function or would have to create some script to this q?!
LC_JK
Location: São Paulo

Poston Fri, 01 Oct 2004 11:44 am

Depends on the case. You said you want to encrypt and then decrypt a password. In this case, the best way is to use a HASH, i.e. you generates a unique key of your password that is impossible to decrypt. Hence, you just compare this key with the key that the user entered. (that way, not even a DBA or the people who created the system can see the password that the user registered).

Here's an example, taken from the AskTom: http://asktom.oracle.com/pls/ask/f?p=49 ... 5412348059
Code: Select all
declare
  function digest( p_username in varchar2, p_password in varchar2 ) return varchar2
  is
  begin
    return ltrim( to_char( dbms_utility.get_hash_value(
      upper(p_username)||'/'||upper(p_password),
      1000000000,
      power(2,30) ),
      rpad( 'X',29,'X')||'X' ) );
  end digest;
begin
  for x in ( select username from all_users where rownum < 20 )
  loop
    dbms_output.put_line( 'User: ' || rpad( x.username , 30 ) ||
                          ' digest: ' || digest( x.username, 'TIGER' )
    );
  end loop;
end;
/
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.
Now, if you want to encrypt and decrypt information (for example, the column of salaries of employees). Then you can use an oracle DBMS. The Obfuscation toolkit. This example was also taken from the asktom ... http://asktom.oracle.com/pls/ask/f?p=49 ... 1026226790
Code: Select all
ops$tkyte@DEV816> variable x varchar2(25)
ops$tkyte@DEV816>
ops$tkyte@DEV816> exec :x := 'How Now Brown Cow';

PL/SQL procedure successfully completed.

ops$tkyte@DEV816>
ops$tkyte@DEV816> declare
  2      l_data  varchar2(255);
  3  begin
  4      l_data := rpad( :x, (trunc(length(:x)/8)+1)*8, chr(0) );
  5 
  6      dbms_obfuscation_toolkit.DESEncrypt
  7          ( input_string => l_data,
  8        key_string   => 'MagicKey',
  9        encrypted_string=> :x );
10  end;
11  /

PL/SQL procedure successfully completed.

ops$tkyte@DEV816>
ops$tkyte@DEV816> print x

X
-------------------------
Òr
dr_gori
Location: Portland, OR USA

Thomas F. G

Você já respondeu a dúvida de alguém hoje?
http://glufke.net/oracle/search.php?search_id=unanswered

Poston Fri, 07 Oct 2005 2:42 pm

I come from the mysql, where has the function crypt () and md5 ().
I just want to save the passwords of the users of a system written in delphi, but as I have a written part and php, I want this encryption is done by the Bank
albandes
Location: Pelotas - RS

---
Rogerio Albandes
PipeGrep Conectividade Ip - www.pipegrep.com.br
Fone: 53 3222-3366 Cel: 53 8401-4872

Poston Fri, 07 Oct 2005 2:48 pm

Unfortunately in ORACLE this function does not exist. You will have to create one of his own. (This DBMS used in the second example above is the solution for your problem: dbms_obfuscation_toolkit.

It is installed together with ORACLE. Gives a studied her! Any questions, send it to us! But basically what you have to do is create a function with the routines used in the example above. (Which encrypts and decrypts) ...:-
dr_gori
Location: Portland, OR USA

Thomas F. G

Você já respondeu a dúvida de alguém hoje?
http://glufke.net/oracle/search.php?search_id=unanswered

Poston Thu, 13 Oct 2005 12:22 pm

This encryption package accepts what size maximum text to be encrypted. Did a test through here with a text and with a key atmanho and increase the size and decreased the size of the key or the text gave error.

Do you have any references about optimal size of text and key.

Another thing, when we wrap the code that is enclosed in quotation marks (text) and not wrapeado. Stay visible, any tips?
alexander
Location: Rio de Janeiro

Poston Fri, 14 Oct 2005 2:25 pm

I'm not taking time to test the maximum size. All I know is that it must be multiple of 8 characters. (see example, has a RPDAD that fills with chr (0) string.
dr_gori
Location: Portland, OR USA

Thomas F. G

Você já respondeu a dúvida de alguém hoje?
http://glufke.net/oracle/search.php?search_id=unanswered

Poston Thu, 09 Feb 2012 3:33 pm

Good afternoon guys, I'm new in Oracle, and don't quite understand the encryption part, I know that it exists in the Oracle packages which can be used to perform this operation. But how do I create a function using DBMS_OBFUSCATION_TOOLKIT. DESEncrypt? I would have to use a function to encrypt and decrypt is it?, in case DBMS_OBFUSCATION_TOOLKIT. DESDecrypt?

Could someone write me a function of simple example of cryptographic function, do you understand how to use this the DBMS_OBFUSCATION_TOOLKIT:?

It would be something like this?

Code: Select all
CREATE OR REPLACE FUNCTION fn_criptografia
(
  p_login IN RAW
,p_senha IN RAW
,p_senha_encriptada OUT RAW
) RETURN RAW AS
  v_result RAW(128);
BEGIN
DBMS_OBFUSCATION_TOOLKIT.DESEncrypt (p_login, p_senha, p_senha_encriptada);

RETURN v_result;
END;
Thank you o/
Maikew

Poston Fri, 10 Feb 2012 7:51 am

[quote = " Maikew "]Good afternoon guys, I'm new in Oracle, and I don't quite understand the encryption part, I know that it exists in the Oracle packages which can be used to perform this operation. But how do I create a function using DBMS_OBFUSCATION_TOOLKIT.DESEncrypt? I would have to use a function to encrypt and another to decrypt is this?, in case DBMS_OBFUSCATION_TOOLKIT.DESDecrypt?

could someone give me a simple example function of encryption function, because I didn't understand how to use the DBMS_OBFUSCATION_TOOLKIT:?

It would be something like this?

CREATE OR REPLACE FUNCTION fn_criptografia (p_login IN RAW, p_senha IN RAW, p_senha_encriptada OUT RAW) RETURN the RAW v_result RAW (128);
BEGIN DBMS_OBFUSCATION_TOOLKIT.DESEncrypt (p_login, p_senha, p_senha_encriptada);

RETURN v_result;
END;


Thanking you in advance the/[/quote] just like to a very simple example, for after I create my own:) key
Maikew

Poston Tue, 28 Feb 2012 11:12 pm

You can create a function to use MD5 this way in oracle
Code: Select all
create or replace function fnc_md5 (p_valor varchar) return varchar2 is
     v_entrada varchar2(2000) := p_valor;
     hexkey varchar2(32) := null;
begin
   hexkey := rawtohex(dbms_obfuscation_toolkit.md5(input => utl_raw.cast_to_raw(v_entrada)));
   return nvl(hexkey,'');
end;
/
ricards
Location: Araraquara-SP

Ricardo Neves
Analista e Instrutor Oracle Developer
Java Developer (JPA/JSF/Hibernate/WebServices/EJB)

Poston Fri, 02 Mar 2012 3:31 pm

[quote = " ricards "]You can create a function to use MD5 this way in oracle
Code: Select all
create or replace function fnc_md5 (p_valor varchar) return varchar2 is
     v_entrada varchar2(2000) := p_valor;
     hexkey varchar2(32) := null;
begin
   hexkey := rawtohex(dbms_obfuscation_toolkit.md5(input => utl_raw.cast_to_raw(v_entrada)));
   return nvl(hexkey,'');
end;
/
[/quote] Thanks. I already knew this MD5 function, however I need to decrypt the password, I need the original value it, because I'm not going to compare the hash value with another hash generated from the password entered, I will need to use the original password in another location, só wanted an example of how to build a key, the simplest possible using dbms_obfuscation_toolkit.DESEncrypt () and dbms_obfuscation_toolkit.DESDEcrypt () if possible.

:)
Maikew

Poston Tue, 06 Mar 2012 1:40 pm

The 10 g onwards use DBMS_CRYPTO.
The DBMS_OBFUSCATION_TOOLKIT is already discontinued.

Hugs,
burga
Location: SP

Ricardo H. Tajiri


  • See also
    Replies
    Views
    Last Post


      Return to PL/SQL

      Who is online

      Users browsing this forum: No registered users and 7 guests