Retrieve MD5 encrypted password and MD5 encrypted database data __ Database

Sometimes, in the development process, if accidentally changed the project Administrator account password and forgot, there is the password in the database is MD5 encryption, this time how to do. The most rookie of me, just at the beginning is also very confused, but to the predecessors consulted for advice, I also checked the data, deliberately collated, and share to everyone.

The premise is that you know which table the password exists in which field, but is encrypted, if it is Oracle database, you can use Dbms_obfuscation_toolkit. MD5 (Input => utl_raw.cast_to_raw (' preset password ') to get a MD5 encrypted password,

So we can password=dbms_obfuscation_toolkit the update user set. MD5 (Input => utl_raw.cast_to_raw (' preset password ')) where Name= ' SYS ' to replace the password you don't know with the preset password, so we can forget the password of the account login again, if it is MySQL, as if there is a built-in MD5 () function, can also play such an effect, specific you can check the details of MySQL MD5 usage, if it is SQL Server words, as if there is a corresponding MD5 home function, remember not too clear, should be hashbyte (' encryption mode ', ' to be encrypted value '), can also be retrieved by this way.

       Dbms_obfuscation_toolkit. MD5 is a function provided by Oracle to obtain MD5 values, which can be directly used by Dbms_obfuscation_toolkit. MD5 (Input => utl_raw.cast_to_raw (' preset password ')) A from dual to obtain the encrypted data, otherwise the raw type of data is obtained, need to use utl_raw.cast_to_ Raw is converted into our usual MD5 format, and the above MD5 values are all uppercase, if the database is stored in lowercase, you need to use the lower function to convert.
        We can also use stored procedures to implement MD5 encryption:
DECLARE
    v_string VARCHAR2 (50);
    v_n      number;
Begin
    v_string: = Utl_raw.cast_to_raw (sys.dbms_obfuscation_toolkit.md5 (input_string = > ' 123456 '));
    v_n     : = Length (v_string);
    dbms_output.put_line (v_string | | '--' || V_n);
End;

You can also write a function and call implement MD5 encryption:
Create or Replace function fun_get_md5 (i_username in Varchar2, I_password in varchar2)
Return VARCHAR2 is
Begin
Return Utl_raw.cast_to_raw (dbms_obfuscation_toolkit.md5 input_string => (i_username| | I_password)));
End FUN_GET_MD5;

Sql> Select FUN_GET_MD5 (' Zhangwz ', ' 123456 ') from dual;
FUN_GET_MD5 (' zhangwz ', ' 123456 ')
--------------------------------------------------------------------------------
0d8df9100cd33ef80af0527858136e0b

Below is an example of accessing the user's password information on the Internet for your reference:

CREATE TABLE Sys_user (ID number,username varchar2 (m), password varchar2 (50));

The process of accessing a user's password should be written to a stored procedure to facilitate later invocation, which is omitted to write as much as possible.

User Login Password:
sql> INSERT into Sys_user values (1001, ' Zhangwz ', fun_get_md5 (' zhangwz ', ' 123456 '));
Sql> commit;

Remove password when user logs on:
Create or replace procedure P_login (i_uname  varchar2
                                     i_passwd varchar2) is
    v_id          number;
    v_error_text varchar2;

Begin
Select ID
Into v_id
From Sys_user
where username = I_uname
and password = FUN_GET_MD5 (I_uname, I_PASSWD);

exception
When others then
V_error_text: = ' username or password is incorrect. ' || ', Sqlcode: ' | | Sqlcode | |
' SQLERRM: ' | | SUBSTR (SQLERRM, 1, 200);

End P_login;