You Asked

We are planning to all our Business users to use our read only Active data guard standby database and restrict them using the Primary production database using logon trigger.

But as part of security, users have to change their passwords every 60 days. If they are authorized to connect only to Active data guard Read only database , they will not be able to change their passwords.

Can you please suggest some workaround for it.

and Connor said...

I would build a little app (eg in Apex) that allows a user to reset their password. There is a chicken-and-egg issue there, in that for anyone to use that app, they need to authenticate...and you're back at the same problem.

You could workaround it using a key-based system, for example:

SQL> create or replace
  2  trigger catch_password_expired
  3  after servererror on database
  4  declare
  5    l_link varchar2(1000);
  6    l_crypto raw(1000);
  7  begin
  8    if ( is_servererror(28001) )
  9    then
 10      l_crypto := dbms_crypto.encrypt(
 11                     UTL_I18N.STRING_TO_RAW (user||to_char(sysdate,'ddmmyyyyhh24miss'),  'AL32UTF8'),
 12                     DBMS_CRYPTO.ENCRYPT_AES256+DBMS_CRYPTO.CHAIN_CBC+DBMS_CRYPTO.PAD_PKCS5,
 13                     utl_raw.cast_to_raw(rpad(user||'secret_sauce',32,'x'))
 14                     );
 15
 16      raise_application_error(-20000,'Password expired, please reset using http://myapp/reset?key='||rawtohex(l_crypto));
 17    end if;
 18  end;
 19  /

Trigger created.

SQL> conn demo/demo
ERROR:
ORA-00604: error occurred at recursive SQL level 1
ORA-20000: Password expired, please reset using http://myapp/reset?key=67181B00316EEE459E0A737ED9D06452E38C352584D429606F5E534615C429D0
ORA-06512: at line 13
ORA-28001: the password has expired

The key passed to the app can be decrypted to get the username and the time the login attempt was made. The app would then let them choose a new password and issue the 'alter user' command on the primary database on their behalf. For a little extra security:

1) I included the time, so you could have that key expire after (say) 10 mins.

2) Notice also I used the userid in the encryption key, so even with the link, the app would ask them for their userid to proceed.

Hope this helps.

Rating

(1 rating)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Arun Duraisamy, July 05, 2016 - 8:11 pm UTC

Hi Connor,

Thanks for the response. It is most useful for us. We are also thinking of creating an application which can be used to reset the users password across the primary database. If possible can you please send me the decryption which should be done in the database side to get the userid and then alter the user password.

July 06, 2016 - 2:28 am UTC

SQL> set serverout on
SQL> declare
  2    l_link varchar2(1000);
  3    l_crypto raw(1000);
  4  begin
  5      l_crypto := dbms_crypto.encrypt(
  6                     UTL_I18N.STRING_TO_RAW(user||to_char(sysdate,'ddmmyyyyhh24miss'),  'AL32UTF8'),
  7                     DBMS_CRYPTO.ENCRYPT_AES256+DBMS_CRYPTO.CHAIN_CBC+DBMS_CRYPTO.PAD_PKCS5,
  8                     utl_raw.cast_to_raw(rpad(user||'secret_sauce',32,'x'))
  9                     );
 10
 11      dbms_output.put_line(l_crypto);
 12
 13  end;
 14  /
169A15EB63BDF32D6500AE42E15D88AE45EB57D3895C2280E7F907FA31477217

PL/SQL procedure successfully completed.

SQL>
SQL> accept userid char prompt 'Enter your userid: '
Enter your userid: MCDONAC
SQL> accept cyrpt char prompt 'Enter your key: '
Enter your key: 169A15EB63BDF32D6500AE42E15D88AE45EB57D3895C2280E7F907FA31477217
SQL> declare
  2    l_user varchar2(30) := '&&userid';
  3    l_link varchar2(1000);
  4    l_crypto raw(1000) := hextoraw('&&cyrpt');
  5    l_decrypt raw(1000);
  6  begin
  7     l_decrypt := DBMS_CRYPTO.DECRYPT
  8        (
  9           src => l_crypto,
 10           typ => DBMS_CRYPTO.ENCRYPT_AES256+DBMS_CRYPTO.CHAIN_CBC+DBMS_CRYPTO.PAD_PKCS5,
 11           key => utl_raw.cast_to_raw(rpad(l_user||'secret_sauce',32,'x'))
 12        );
 13     DBMS_OUTPUT.PUT_LINE(UTL_I18N.RAW_TO_CHAR (l_decrypt, 'AL32UTF8'));
 14  end;
 15  /
MCDONAC06072016102641

PL/SQL procedure successfully completed.

Breadcrumb

Question and Answer

You Asked

and Connor said...

Rating

Comments

More to Explore

PL/SQL demos

PL/SQL docs

Block user account to PRIMARY production server and move them to the REPORTING STANDBY Db.

Breadcrumb

Question and Answer

You Asked

and Connor said...

Rating

Comments

More to Explore

PL/SQL demos

PL/SQL docs