Protecting Passwords

Passwords and hints that you declare with SET ENCRYPTION PASSWORD are not stored as plain text in any table of the system catalog, which also maintains no record of which columns or tables contain encrypted data.

To prevent other users from accessing the plain text of encrypted data or of a password, however, you must avoid actions that might compromise the secrecy of a password:

• Do not create a functional index using a decryption function. (This would store plain-text data in the database, defeating the purpose of encryption.)
• On a network that is not secure, always work with encrypted data, or use session encryption, because the SQL communication between client and server sends passwords, hints, and the data to be encrypted as plain text.
• Do not store passwords in a trigger or in a UDR that exposes the password to the public.
• Do not set the session password prior to creating any view, trigger, procedure, or UDR. Set the session password only when you use the object. Otherwise, the password might be visible in the schema to other users, and queries executed by other users might return unencrypted data. The following example shows a procedure that includes an encrypted password:

  -- reset session encryption password
  set encryption password null;
  
  -- create procedure without password
  create procedure p1 ();
    insert into tab2 select (decrypt_char (col1))
    from tab1;
  end procedure;
  
  -- set session encryption password
  set encryption password ("PASSWD2");
  
  -- insert data
  insert into tab1 values (encrypt_aes ('WXY'));
  
  -- call procedure

Output from the SET EXPLAIN statement always displays the password and hint parameters as XXXXX, rather than displaying actual password or hint values.