**Programming Languages:** Perl [login to view URL] (CGI, DBI, OraPerl), Oracle PL/SQL [login to view URL]
**Requirements:** In order to satisfy future security audit I need to implement Oracle User Profiles for a web based application (Application User Login is Oracle User Login). Need a couple of Perl scripts to login/update password and a few PL/SQL procedures to change and verify user passwords, suspend inactive accounts and provide a list of all account’s statuses, see deliverables. Code will be integrated to an existing application by myself.
Below is the list of rules:
- The password length is at least 8 characters long
- The passwords contain at least 1 Upper Lower, and Numeric/Symbolic character.
- Accounts are locked for one hour after 6 invalid password attempts.
- The password for an Account do not match the Userid.
- The password for an Account does not match any of the previously used 6 passwords.
- Passwords expire after 180 days
- User Accounts that are inactive for 60 days or more are suspended
- User Accounts that are suspended for 35 days are deleted
## Deliverables
**Deliverables:**
In general deliverables should include Perl CGI scripts (plain HTML, no graphics, design, etc), PL/SQL scripts (stored procedures), unit test scenarios to test each profile exception and brief documentation including [login to view URL] steps.
a) 2 Perl CGI login scripts ??" [login to view URL] (one using OraPerl, the second one - DBI) handling profile’s exceptions including but not limited to the list below:
ORA-28000 the account is locked
ORA-28001 the password has expired
ORA-28002 the password will expire within string days
ORA-28003 password verification for the specified password failed
**HTML form should include “Login†textbox,
“password†textbox and button “Loginâ€,
in case of profile exception response
should be redirected to [login to view URL] (b).**** **
b) 2 Perl CGI script - [login to view URL] (one using OraPerl, the second one - DBI). HTML form should include 3 text boxes ??" “Login?? passed from [login to view URL], “Old Password?? ??" passed from [login to view URL] , “New Password?? and button “Submit?? which triggers PL/SQL stored procedure
c) PL/SQL Stored procedure (package) user_password_admin with login, old_password, new_password as input parameters. The procedure should handle password verification (see requirements) through a custom function verify_password(), see [login to view URL] more information.
d) PL/SQL procedure that suspends accounts after 60 days of inactivity (will be part of automatic job)
e) PL/SQL procedure that deletes inactive accounts after 35 days of suspension date (will be part of automatic job)
f) PL/SQL procedure returning list of all users including account status (active, suspended), date of last password change, date of last login.
## Platform
Perl 5 and above/Oracle 8 and above,
deliverables can be delivered and tested on Windows platform