Procedure for password change

[tlinton]

I am trying to set up password expiration using a web pl/sql app running on 8.1.7 database and 9iAS. Oracle said that I need to build a procedure inside my application for password changes.
Solution Explanation:
=====================

Netscape and IE would have to have some kind of understanding for a
universal call for password expiration and update. Since the development
of webservers is done by serveral companies and there is no common message
sent out from them to the browser when the password has expired, you need
to write a custom application for each webserver make and version.

Anybody ever done something like this, if so can you give me some coding ideas?

[sambavan]

You can achive this in a number of ways. First assign a profile to the user when you were to create the user in the database. Set the password expiration limits. Then write a procedure that would use execute immediate that would eecute the alter user user_name identified by : pwd. This would change the user password for the user and then he would be set to use the new password from then on.

Sam

[tlinton]

okay...to comply with security requirements, we need to have the user change password every 180 days. ideas on this?

[sambavan]

Create a profile for these perticular users. And in that profile, specify the password expiration criterias. Assign that profile to the users, when you create them. That would then take effect immeciatelty for that user. You can even set number of tries a user is allowed to give wrong passwords and then you could temprorily lock that account for a short while and then automatically reopen it for the next login after couple of hours and you can even set an option to specify how many times the user is allowed to resuse his/her password and etc. To get an indepth understanding, check the CREATE PROFILE syntax.


Good luck,
Sam

[tlinton]

Yes, I understand that part, but if the user reaches his expiry date, they cannot log in to application through their browser to even change their password. That is the problem I am trying to figure out.

[sambavan]

I think the profile would let you in to login with the old password and would ask you to change the password immediately, before you could execute the command. O.K now I get the problem you would be facing. On that case, don't use the profile, but have a seperate password expiration table with ACTIVATION_DATE, CHANGE_DATE, then compute the NEXT_CHANGE. Then do the validation using the NEXT_CHANGE and ask for the new password from the user, if the NEXT_CHANGE had passed the due date. This way you would be able to allow the user to execute the procedure. You can even have it under a trigger I beleive.

Good luck,
Sam

[tlinton]

Thanks for your help SAM, really appreciate it.