Password Encryption

[aghosh]

I have a password column in one of my tables.

The column is populated through the Front End.

I need to have a trigger on that column, which is going to encrypt the password while storing and another trigger which which will decrypt the password while selecting.


Can anybody give me an idea of how to accomplish this.


Thanks

Amit

[Shestakov]

U may:

for insert/update password
use some trigger:

create or replace trigger BERNIES.BI_IMAGE_SHOW_RULES
before insert or update on BERNIES.IMAGE_SHOW_RULES
for each row
begin :new.pwd_fileld := my_enscript(:new.pwd_fileld);
end;
/

for read (select) rows from this tables use view:
1. write some store function for descript field

2. use view for read rows from real table:

create or replace view v_descript_pwd
( ...
pwd,
...
)
as select
...
descript(pwd_field),
..
from user_list;

[aghosh]

Thanks for the reply,

Actually I was expecting a sample code for encryption and decryption, if anybody has that already available.



Thanks

Amit

[Shestakov]

U need some mathematical function(s), that has:
-- direct conversion from open password to enscripted
-- revert conversion from enscripted password to open

On asktom.oracle.com u can get 1 way conversion
function, that using hash function to create enscripted password :

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 /

-----------------------------------------------------------------------
But in ur case this function doesn't work.
U need 2-way transformation some like "long gamma with marcant group".
I don't thing, that u can find it on pl/sql so easy.