asked on

TRIGGER for CRYPTING the PASSWORD field when INSERTING or UPDATING a new record.

Good evening!

Please suppose you have a table called CREDENTIALS, with two fields: USERNAME VARCHAR2(30), and PASSWORD VARCHAR2(30).

Of course I can insert new credentials when I wish to allow a new user to use our system:

INSERT INTO CREDENTIALS VALUES ('SCOTT', 'TIGER');
COMMIT;

But the password, 'TIGER', is not encrypted, so other users - as well as DBAs - can see the password simply SELECTing the CREDENTIALS table...!! For me it is a security problem.

I would like to write a TRIGGER that, on every insert or update in the PASSWORD field, automatically encrypts the field PASSWORD.

Can you help me?

CRISTIANO_CORRADI

ASKER

Please assume I am obliged to use these functions for crypting / encrypting:

FUNCTION encrypt (p_text IN VARCHAR2)
RETURN RAW
IS
v_text VARCHAR2 (32767) := p_text;
v_encrypted RAW (32767);
BEGIN
padstring (v_text);
DBMS_OBFUSCATION_TOOLKIT.desencrypt
(input => UTL_RAW.cast_to_raw
(v_text),
KEY => g_key,
encrypted_data => v_encrypted
);
RETURN v_encrypted;
END;

FUNCTION decrypt (p_raw IN RAW)
RETURN VARCHAR2
IS
v_decrypted VARCHAR2 (32767);
BEGIN
DBMS_OBFUSCATION_TOOLKIT.desdecrypt (input => p_raw,
KEY => g_key,
decrypted_data => v_decrypted
);
RETURN RTRIM (UTL_RAW.cast_to_varchar2 (v_decrypted), g_pad_chr);
END;

CRISTIANO_CORRADI

ASKER

   FUNCTION encrypt (p_text IN VARCHAR2)
      RETURN RAW
   IS
      v_text        VARCHAR2 (32767) := p_text;
      v_encrypted   RAW (32767);
   BEGIN
      padstring (v_text);
      DBMS_OBFUSCATION_TOOLKIT.desencrypt
                                       (input               => UTL_RAW.cast_to_raw
                                                                       (v_text),
                                        KEY                 => g_key,
                                        encrypted_data      => v_encrypted
                                       );
      RETURN v_encrypted;
   END;
 
 
   FUNCTION decrypt (p_raw IN RAW)
      RETURN VARCHAR2
   IS
      v_decrypted   VARCHAR2 (32767);
   BEGIN
      DBMS_OBFUSCATION_TOOLKIT.desdecrypt (input               => p_raw,
                                           KEY                 => g_key,
                                           decrypted_data      => v_decrypted
                                          );
      RETURN RTRIM (UTL_RAW.cast_to_varchar2 (v_decrypted), g_pad_chr);
   END;

Open in new window

GGuzdziol

Probably you want field "password" in your table to become RAW datatype in this case.
Then you can write

create or replace trigger <put_name_here>
  before insert or update of password
  on credentials
  for each row
begin
  :new.password := encrypt(:new.password);
end;

Open in new window

ASKER CERTIFIED SOLUTION

GGuzdziol

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

CRISTIANO_CORRADI

ASKER

The trigger you've written is perfect for my objective ;-) THANKS