Encrypted password column

[Mohammed Mostafa]

Dear Experts,

I have one application connecting to oracle database and reading the passwords from USERS tables which contains 2 columns (Username and password) , the developer needs to encrypt the password column in the database , can u help me please

database version is 10g

Thank you in advance

Mohammed

[Agustin Utrera]

Dbms_crypto or dbms_obfuscation_toolkit

--
You received this message because you are subscribed to the Google Groups "ORACLE_DBA_EXPERTS" group.
To post to this group, send email to oracle_db...@googlegroups.com.
To unsubscribe from this group, send email to oracle_dba_expe...@googlegroups.com.
For more options, visit this group at http://groups.google.com/group/oracle_dba_experts?hl=en.

[Mohammed Mostafa]

Thank you , can u please send me one simple example how to use it

my table is users

TABLE tests.users

 Name                                      Null?    Type                        

 ----------------------------------------- -------- ----------------------------

 USERNAME                                           VARCHAR2(100)               

 PASSWORD                                           VARCHAR2(100)               

I need teh passwords in password table to be blank or encrypted when any one browse teh table from Toad or SQL Plus

Thank you in advance

[Bhaskar Sarma]

Hi ,

Password stored in USERS Table is not clear text. Its an encrypted
format of password. Why do you like to encrypt the same value again ?

Bhaskar.

> --
> You received this message because you are subscribed to the Google Groups
> "ORACLE_DBA_EXPERTS" group.
> To post to this group, send email to oracle_db...@googlegroups.com.
> To unsubscribe from this group, send email to
> oracle_dba_expe...@googlegroups.com.
> For more options, visit this group at
> http://groups.google.com/group/oracle_dba_experts?hl=en.



--

Thank You
Bhaskar Sarma

[ddf]

> 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

[Bhaskar Sarma]

My bad I have mistaken USERS to USER$ table and David Example looks
perfect to encrypt the password.

Bhaskar

[ddf]

> > ---------------------------------------------------------------------------­-----
> > 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
>
> > --
> > You received this message because you are subscribed to the Google Groups "ORACLE_DBA_EXPERTS" group.
> > To post to this group, send email to oracle_db...@googlegroups.com.
> > To unsubscribe from this group, send email to oracle_dba_expe...@googlegroups.com.

> > For more options, visit this group athttp://groups.google.com/group/oracle_dba_experts?hl=en.

>
> --
> Thank You
> Bhaskar Sarma- Hide quoted text -
>
> - Show quoted text -

To ensure the key is not visible the package body code needs to be run
through the wrap utility so it could look like this:

CREATE OR REPLACE PACKAGE BODY p_encrypt wrapped
a000000
b2
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
b
47e 24c
pdNlOAEZIyeQSBkE0lxXpA6V5/Qwg5Dx19xqfC9GvoOuLcToUeWDiafmwxTGf6gnuGIFY/
z6
W9uUXOOZIK7fqjLvmS+8FJviFzyaVH
+8Ab6p9Zyk7l0ZGQ8kvNxP3XSV6cDGT3XJaJ7NX0ul
FSFI27PP30D4sl9UvuP+M1IA8ksxVEyF5GgN4xPiQjRTk+JIsb8DpQ/
HI9VKDjGoGJzwzhmn
4nVkQ6D3Mp/
B5WVs85FHZbR6Z8FXazbKgmFBb8HY9pyM0PYoqL4ixbqKGgOAbGeN1JQlGHER
oEclF4b8SlWgeTJZdTC+URqjsR+yjT
+opK5CDuhUi3enEwHmKYaHwPVG3MykwueOPw7IAGIE
1m
+fZGzmphZ0bAFV1lmj8TgUO7QUMMHCmAN9jdPsGDTIjrgNGySlM6l4Io7nOpCfsD9hKB3i
sl+LFl7fkkbD7iHC1t1LQLsa4u4ohy2Vi5NsZwJHz48/y1jnQ6l3+OeSaYN2g
+8ihKp40psH
iXLTHct5r8EO+a/7q0nn10OwukymjJWtlsKBSmsjKvpP4bnuKC63e
+HQMpT7uU250fo9eF2i
ysw=

/

The extension is changed from .sql (for example) to .plb but you can
still execute it with @ syntax from the SQL> prompt;

SQL> @encrypt_pw_ex_2.plb

Package body created.

SQL>

and now no one can see the internal key for encryption/decryption. A
short tutorial on wrap can be found here:

http://oratips-ddf.blogspot.com/2008/06/thats-wrap.html



David Fitzjarrell

[Mohammed Mostafa]

Dear  David,

Thank you so much, your plan is excellent.

Appreciated!

Mohammed