> Bhaskar Sarma- Hide quoted text -
>
> - Show quoted text -
No, it isn't. There is no USERS table by default in Oracle, it's
DBA_USERS and the password is hashed to make it unreadable. This is
for an application table not connected to the data dictionary.
Here is an example of using the dbms_crypto package to encrypt and
decrypt a password:
SQL> CREATE OR REPLACE PACKAGE p_encrypt AS
2
3 FUNCTION encrypt_pwd( p_pwd IN VARCHAR2 ) RETURN RAW;
4
5 FUNCTION decrypt_pwd( p_pwd IN RAW ) RETURN VARCHAR2;
6
7 END p_encrypt;
8 /
Package created.
SQL>
SQL> show errors
No errors.
SQL>
SQL> CREATE OR REPLACE PACKAGE BODY p_encrypt AS
2 --DO NOT FORGET TO WRAP THIS BEFORE LOADING INTO DATABASE
3 --IF IT IS NOT WRAPPED, THE KEY WILL BE EXPOSED
4 --THE WRAP UTILITY IS LOCATED IN THE \BIN DIRECTORY (WRAP.EXE)
5
6 G_CHARACTER_SET VARCHAR2(10) := 'AL32UTF8';
7 G_STRING VARCHAR2(32) :=
'12345678901234567890123456789012';
8 G_KEY RAW(250) := utl_i18n.string_to_raw
9 ( data => G_STRING,
10 dst_charset => G_CHARACTER_SET );
11 G_ENCRYPTION_TYPE PLS_INTEGER :=
dbms_crypto.encrypt_aes256
12 + dbms_crypto.chain_cbc
13 + dbms_crypto.pad_pkcs5;
14
15 FUNCTION encrypt_pwd( p_pwd IN VARCHAR2 ) RETURN RAW IS
16 l_pwd RAW(32) := UTL_I18N.STRING_TO_RAW( p_pwd,
G_CHARACTER_SET );
17 l_encrypted RAW(32);
18 BEGIN
19 l_pwd := utl_i18n.string_to_raw
20 ( data => p_pwd,
21 dst_charset => G_CHARACTER_SET );
22 l_encrypted := dbms_crypto.encrypt
23 ( src => l_pwd,
24 typ => G_ENCRYPTION_TYPE,
25 key => G_KEY );
26 RETURN l_encrypted;
27 END encrypt_pwd;
28
29 FUNCTION decrypt_pwd( p_pwd IN RAW ) RETURN VARCHAR2 IS
30 l_decrypted RAW(32);
31 l_decrypted_string VARCHAR2(32);
32 BEGIN
33 l_decrypted := dbms_crypto.decrypt
34 ( src => p_pwd,
35 typ => G_ENCRYPTION_TYPE,
36 key => G_KEY );
37 l_decrypted_string := utl_i18n.raw_to_char
38 ( data => l_decrypted,
39 src_charset => G_CHARACTER_SET );
40 RETURN l_decrypted_string;
41 END decrypt_pwd;
42
43 END p_encrypt;
44 /
Package body created.
SQL>
SQL> show errors
No errors.
SQL>
SQL> create table users(
2 username varchar2(100),
3 password varchar2(100));
Table created.
SQL>
SQL> insert into users
2 select 'norbert', p_encrypt.encrypt_pwd('norbert') from dual;
1 row created.
SQL>
SQL> select * from users;
USERNAME
--------------------------------------------------------------------------------
PASSWORD
--------------------------------------------------------------------------------
norbert
1A77BE272647A5CDEA3A26E05C07108C
SQL>
SQL> select username, p_encrypt.decrypt_pwd(password) from users;
USERNAME
--------------------------------------------------------------------------------
P_ENCRYPT.DECRYPT_PWD(PASSWORD)
--------------------------------------------------------------------------------
norbert
norbert
SQL>
Notice the password is encrypted; this could be put into a trigger on
the USERS table so the encryption would be transparent to the user:
SQL> create or replace trigger encrypt_pw_trg
2 before insert or update of password on users
3 for each row
4 begin
5 :new.password := p_encrypt.encrypt_pwd(:new.password);
6 end;
7 /
Trigger created.
SQL>
SQL> insert into users
2 values('plinko','plinko');
1 row created.
SQL>
SQL> select * from users;
USERNAME
--------------------------------------------------------------------------------
PASSWORD
--------------------------------------------------------------------------------
norbert
1A77BE272647A5CDEA3A26E05C07108C
plinko
2EA3BBB6B2277E7B9DC2ED98F2C01E45
SQL>
SQL> select username, p_encrypt.decrypt_pwd(password) from users;
USERNAME
--------------------------------------------------------------------------------
P_ENCRYPT.DECRYPT_PWD(PASSWORD)
--------------------------------------------------------------------------------
norbert
norbert
plinko
plinko
SQL>
David Fitzjarrell