Whilst developing our customer support tool, one of the requirements I have is to capture and display password information for systems which we support. Even though we will ensure the application is SSL enabled and we will enforce some password expiration and complexity requirements we still felt there was a need to add additional security to the report which will show the information in encrypted format until a correct pass phrase is entered to decrypt the data server side.

I’m not entirely sure of the design and I’m hoping some people could provide feedback on the pro’s and cons of the approach and also let me know if you think it’s kind of redundant.

The design goes like this:

1. We have a page process which runs “Before Header” which creates/truncates/creates our password report collection (based on a SQL query) on every page load. The password field in this collection is encrypted using “dbms_obfuscation_toolkit.DESEncrypt”. Note: the underlying password field is encrypted in the database and we use a static phrase to decrypt it within the collection build procedure and then re-encypt it with our changing passphrase. Also for every APEX page load the report data will be shown encrypted, but AJAX refreshes of the report i.e. pagination/filtering etc. will display unencrypted data if the user has decrypted the passwords).
2. We provide a region button called “Decrypt” which pops up a dialog box for the user to enter a pass phrase.
3. The pass phrase is sent to the server via an AJAX call and a test decrypt operation is performed, if it was successful we re-create the collection with unencrypted data and reload our report via an AJAX call (as the report queries the collection) you can use either Ext or $a_report. The reload will show the report data with unencrypted passwords. If the decrypt was unsuccessful we simply display an alert that it was unsuccessful, so a second attempt to decrypt can be made (rather than garbling the text).
4. The pass phrase it self comes from a table with several thousand combinations and is selected based on the row number and a numeric representation of to_char on sysdate e.g. to_char(sysdate,’DDHH’). In order to randomize the data we reload the table nightly (or hourly depending up upon the required interval) and change the data ordering using “dbms_random.value” . The reason we have chosen this approach is that we need to ensure that we can return the same pass phrase for the given period, but at the same time ensure that there is no ability to guess what the phase might be for any given day, given our to_char format on sysdate is hard coded in the PLSQL.

     FUNCTION get_random_phrase
     ( p_date_string IN VARCHAR2
     ) RETURN VARCHAR2
     AS
   
     BEGIN
   
       FOR c IN
       ( SELECT phrase_adj||' '||phrase_noun phrase
         FROM (
           SELECT p.*
           ,      rownum rn
           ,      r.adj_rownum
           FROM   portal_pass_phrases p
           ,      (SELECT portal_utils.baseX(to_number(to_char(sysdate, p_date_string)),r.r)+1 adj_rownum
                   FROM   ( SELECT count(*) r
                            FROM   portal_pass_phrases
                          ) r
                   ) r
         )
         WHERE rn = adj_rownum
       ) LOOP
         RETURN c.phrase;
       END LOOP;
       RETURN NULL;
   
     EXCEPTION
       WHEN OTHERS THEN
         -- your custom error handling goes here
         RAISE;
   
     END get_random_phrase;
   

5. The users are notified of the pass phrase by publishing the phrase to a completely separate system which is visible to the support team, it’s embedded within a larger message (for example a news title) so it’s not obvious to anyone but those who have been told about it.
6. The function for generating the pass phrase is a private function in the package body, the decrypt function is public but requires a valid pass phrase to be entered, however the decrypt with the static passphrase which decrypts the encrypted table data for rencryption with our changing pass phrase is a private procedure and only called during the build of our collection. Because we have the pass phrase generator as a private function we can encrypt a test string, decrypt it with the current random pass phrase, and check it against the pass phrase entered by the user for our checking purpose so everything is locked down .e.g.

   f_decrypt(f_encrypt('testphrase'),get_random_phrase(gc_key_string)) = f_decrypt(f_encrypt('testphrase'),p_pass_phrase)
   

   The pass phrase is only valid as long as the length between the pass phrase table re-ordering which is scheduled by the dbms_scheduler, so we can easily change the frequency of the pass phrase lifetime by modifying the job schedule.

Here’s a couple of screenshots to give you a visual idea of how it hangs together, I’d be very grateful if someone could provide some feedback/discussion on the concept and provide any potential changes or recommendations…

« Previous Next »

1 2 3 4