CODEWITHSUNDEEP
oracle-databasevariablesencryptionpackage
Adder
Adder

Reputation: 5868

Retrieving password from an oracle package

I have the following code, my question is how can I be sure the encryption key in l_key has not been changed?

create or replace PACKAGE BODY                                  "ENCRYPT_DECRYPT_PASSWORD"
AS

  l_key RAW(128) := utl_raw.cast_to_raw('secret');

  ------------------------------------------------------------------------
  FUNCTION encrypt_val( p_val IN VARCHAR2 ) RETURN VARCHAR2
  IS
    l_encrypted RAW(2048);
      l_val  RAW(2048) := utl_raw.cast_to_raw(p_val);

  BEGIN

  l_encrypted := dbms_crypto.encrypt
                   ( src => l_val,
                     typ => dbms_crypto.des_cbc_pkcs5,
                     key => l_key );

  return utl_raw.cast_to_varchar2(l_encrypted);

  END encrypt_val;

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

 FUNCTION decrypt_val( p_val IN varchar2 ) RETURN VARCHAR2
  IS
    l_decrypted RAW(2048);
    l_val RAW(2048) := utl_raw.cast_to_raw(p_val);
   BEGIN
         l_decrypted := dbms_crypto.decrypt
                ( src =>  l_val,
                  typ => dbms_crypto.des_cbc_pkcs5,
                  key => l_key );

         return utl_raw.cast_to_varchar2(l_decrypted);

  END decrypt_val;
-----------------
-----------------
-----------------
PROCEDURE encrypt_table_passwords(table_name  IN varchar2,
                                 column_name IN varchar2,
                                 table_id    IN varchar2) IS
BEGIN
  EXECUTE IMMEDIATE
  'begin
  for c1 in (select * from ' || table_name ||') loop
  update ' || table_name || ' set ' || column_name || ' = ENCRYPT_DECRYPT_PASSWORD.encrypt_val(c1.' || column_name || ') where ' || table_id || ' = c1.'||table_id||' and ' || column_name ||
                    ' is not null; end loop; end;';
END encrypt_table_passwords;
-----------------
-----------------
-----------------

FUNCTION get_decrypted_password( table_name IN varchar2,column_name IN varchar2,table_id IN varchar2,table_id_val IN varchar2 ) RETURN VARCHAR2
  IS
     encrypted_pas varchar2(100);
     decrypted_pas varchar2(100);

   BEGIN
      EXECUTE IMMEDIATE 'select ' || column_name || ' from ' || table_name || ' where ' || table_id || ' = ' || table_id_val
         INTO encrypted_pas;
         Select decrypt_val(encrypted_pas) into decrypted_pas from dual;
        --return decrypt_val(encrypted_pas);
        return decrypted_pas;
  END get_decrypted_password;

END encrypt_decrypt_password;

I tried the following which I found on the web, but it appears it doesn't work for my oracle version:

DECLARE
    v_x   RAW(128);
BEGIN
    SELECT ENCRYPT_DECRYPT_PASSWORD.l_key x
    INTO v_x
    FROM DUAL;
    DBMS_OUTPUT.put_line (v_x);
END;
/

I get "component 'L_KEY' must be declared" and "invalid identifier".

The reason I want to investigate is that I had decryption errors which went away after I changed the password column from varchar(99) to nvarchar2(100) and after I regenerated the encrypted password.

Upvotes: 0

Views: 50

Answers (1)

APC
APC

Reputation: 146249

I get "component 'L_KEY' must be declared" and "invalid identifier".

L_KEY is declared in your package BODY. That means its scope is private, restricted to the code of the body. Only things declared in the package SPEC are public and can be accessed outside the package scope.

If you need to check its actual value you need to extend your package with a function which returns L_KEY. You probably don't want to expose it in Production so be careful. You may wish to consider using conditional compilation just to be sure nothing accidentally leaks.

Upvotes: 2

Related Questions