ORACLE提供加密函数吗?

hellobb 2003-05-20 11:03:08

比如要对某字段数据进行加密后存储，能在SQL语句中直接加密?

...全文

616 6 打赏收藏转发到动态举报

写回复

用AI写文章

6 条回复

切换为时间正序

请发表友善的回复…

发表回复

jiezhi 2003-05-20

打赏
举报

FUNCTION get_user_name ( p_user_code USER_V.user_code%TYPE ) RETURN USER_V.user_name%TYPE IS
/*===========================================================================
Description: Get User Name
Input : p_user_code : User Code
Output : User Name
===========================================================================
*/
v_user_name USER_V.user_name%TYPE;
v_ora_status PKG_ORA.StatTYPE%TYPE;
BEGIN
SELECT user_name
INTO v_user_name
FROM USER_V
WHERE user_code = upper(p_user_code);
v_ora_status := PKG_ORA.c_sql_success;
RETURN (v_user_name);
EXCEPTION
When no_data_found then
v_ora_status := PKG_ORA.c_sql_notfound;
RETURN (v_user_name);
When too_many_rows then
v_ora_status := PKG_ORA.c_sql_toomanyrows;
RETURN (v_user_name);
When others then
v_ora_status := substr(sqlerrm,1,PKG_ORA.c_max_col);
RETURN (v_user_name);
END get_user_name;
FUNCTION crypt ( p_user_name IN USER_V.user_name%TYPE,
p_password IN USER_V.user_password%TYPE,
p_pw_method IN VARCHAR2,
p_pw_salt IN VARCHAR2,
p_ora_status OUT PKG_ORA.StatTYPE%TYPE)
RETURN USER_V.user_password%TYPE IS
/*===========================================================================
Description: Password Encryption
Input : p_username - User Name
p_password - User Password
p_pw_method - method of encryption - defaults to 'A'
p_pw_salt - random number used for cryption (only for pw_method 'B')
This requires the storage of pw_salt value in db
Output : pw - encrypted password
p_ora_status - PKG_ORA.sql_success if the operation is sucessful
===========================================================================
*/
pw Varchar2(20);
pw_len Integer;
iterations Integer;
x1 Integer;
x2 Integer;
temp Varchar2(20);
s1 Integer := ascii (substr (p_pw_salt, 1, 1));
s2 Integer := ascii (substr (p_pw_salt, 2, 1));
BEGIN
-- No encryption
if p_pw_method = '0' then
pw := lower (substr (p_password, 1, 20));
end if;
-- Encrypt using method A
if p_pw_method = 'A' then
pw := upper (substr (p_password, 1, 20));
pw_len := length (pw);
if pw_len > 4 then
iterations := ascii (substr (upper (p_user_name), 1, 1));
for i in 1..iterations loop
temp := '';
for j in 1..pw_len loop
x1 := ascii (substr (pw, j, 1));
x2 := ascii (substr (pw, mod (j, pw_len) + 1, 1));
temp := temp || chr (mod (mod ((x1 + j) * x2, 53), 26) + 65);
end loop;
pw := temp;
end loop;
-- return pw;
end if;
end if;
-- Encrypt using method B
if p_pw_method = 'B' then
pw := lower (substr (p_password, 1, 20));
pw_len := length (pw);
for i in 1..25 loop
temp := '';
for j in 1..pw_len loop
x1 := mod (ascii (substr (pw, j, 1)) * s1, 256);
x2 := mod (ascii (substr (pw, mod (j, pw_len) + 1, 1)) * s2, 256);
temp := temp || chr (mod ((x1 + j) * (x2 + 17), 26) + 97);
end loop;
pw := temp;
end loop;
-- return pw;
end if;
p_ora_status := PKG_ORA.c_sql_success;
return (pw);
EXCEPTION
WHEN others THEN
p_ora_status := substr(sqlerrm,1,PKG_ORA.c_max_col);
RETURN ( pw );
END crypt;

jiezhi 2003-05-20

打赏
举报

CREATE OR REPLACE
Package BODY PKG_USER
IS
/*===========================================================================
System : Change Request Management System
Package Name : PKG_USER
Language : PL/SQL Release 2.2.2.3.0
Oralce Server Release 7.2.2.3.0
Description : Standard datatypes and routines for manipulating
information in USER_CTRL
Written By : QSUN 09-MAY-2000
Calls From : Oracle products/tools
DBO Read : Nil
DBO Updated : Nil
Update History
--------------
Seq By Date Remarks
1.
===========================================================================
*/
/*===========================================================================
PRIVATE PROCEDURES/FUNCTIONS
===========================================================================
*/
pr_pw_method VARCHAR2(1) := PKG_USER.c_pw_methodA;
FUNCTION get_salt RETURN VARCHAR2 IS
temp VARCHAR2(2);
/*===========================================================================
Description: Get random number
Input : Nil
Output : Nil
===========================================================================
*/
BEGIN
select rawtohex (chr (mod (to_number (to_char (sysdate, 'SSSSS')), 256)))
into temp from dual;
RETURN temp;
EXCEPTION
When Others then
Null;
END get_salt;

jiezhi 2003-05-20

打赏
举报

写一个加密的过程，下面是三千兄写的：
CREATE OR REPLACE
PACKAGE PKG_USER IS
/* COPIED FROM SRDB*/
/*===========================================================================
System : Change Request Management System
Package Name : PKG_USER
Language : PL/SQL Release 2.2.2.3.0
Oralce Server Release 7.2.2.3.0
Description : Standard datatypes and routines for manipulating
information in USER_CTRL
Written By : QSUN 09-MAY-2000
Calls From : Oracle products/tools
DBO Read : Nil
DBO Updated : Nil
Update History
--------------
Seq By Date Remarks
1.
===========================================================================*/

c_pw_methodA CONSTANT VARCHAR2(1) := 'A';
c_pw_methodB CONSTANT VARCHAR2(1) := 'B';

-- After test, change this to private package!
-- FUNCTION get_salt RETURN VARCHAR2;
-- After test, change this to private package!
-- FUNCTION get_user_name ( p_user_code USER_V.user_code%TYPE ) RETURN USER_V.user_name%TYPE;
-- After test, change this to private package!
-- FUNCTION crypt ( p_user_name IN USER_V.user_name%TYPE,
-- p_password IN USER_V.user_password%TYPE,
-- p_pw_method IN VARCHAR2,
-- p_pw_salt IN VARCHAR2,
-- p_ora_status OUT PKG_ORA.StatTYPE%TYPE) RETURN USER_V.user_password%TYPE;

PROCEDURE verify_user(p_user_code USER_V.user_code%TYPE,
p_user_password USER_v.user_password%TYPE,
p_user_name OUT USER_V.user_name%TYPE,
p_user_role OUT USER_v.user_role%TYPE,
p_bu_type OUT USER_V.bu_type%TYPE,
p_bu_code OUT USER_V.bu_code%TYPE,
p_ora_status OUT PKG_ORA.StatType%TYPE);
PROCEDURE change_password(p_pgm_id USER_V.pgm_id_ins%TYPE,
p_user_code USER_V.user_code%TYPE,
p_user_password USER_v.user_password%TYPE
);
PROCEDURE create_user(p_pgm_id USER_V.pgm_id_ins%TYPE,
p_user_code USER_V.user_code%TYPE,
p_user_password USER_V.user_password%TYPE,
p_user_name USER_V.user_name%TYPE,
p_user_role USER_V.user_role%TYPE,
p_bu_type USER_V.bu_type%TYPE,
p_bu_code USER_V.bu_code%TYPE,
p_ora_status OUT PKG_ORA.StatType%TYPE);
END PKG_USER; -- Package spec
/

hellobb 2003-05-20