Oracle Profile Implementation

**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