CSC452 Assignment 5. Please answer the question exactly as requested in the document and also include screenshots of the output.

Unless prior arrangements are made, homework turned in late will not be accepted. However, homework turned in within 24 hours late will be graded at 50% credit.

If there is a syntax error anywhere in your program, you will receive 0 points for the program.

Please read the assignment carefully. You will receive 0 points if you use different tables (names, columns, or data types) or procedure headers (names, parameters, or data types).

Please note that only TEXT files will be accepted. All other file types (e.g., DOC, DOCX, RTF, PDF, JPG, or ZIP) will be rejected. In D2L, only the most recent submission is kept.

Please review your assignment file before submitting it to make sure you have the correct one. It is your responsibility to upload the correct assignment file.

1) (0 Point)

The TBL_CDM_USER table stores information about user accounts.

TBL_CDM_USER(

USER_ID,

PASSWORD,

LAST_SUCCESSFUL_LOGIN_TIME,

LAST_FAILED_LOGIN_TIME,

FAILED_PASSWORD_ATTEMPT_COUNT,

IS_ACCOUNT_LOCKED_OUT,

LAST_ACCOUNT_LOCKED_OUT_TIME,

LAST_PASSWORD_CHANGED_TIME);

USER_ID: Primary Key

FAILED_PASSWORD_ATTEMPT_COUNT: the number of consecutive failed password attempts

IS_ACCOUNT_LOCKED_OUT: 'Y' the account is locked out

'N' the account is not locked out

Create and populate the TBL_CDM_USER table as described below.

CREATE TABLE TBL_CDM_USER

( USER_ID NUMBER PRIMARY KEY,

PASSWORD VARCHAR2(30) NOT NULL,

LAST_SUCCESSFUL_LOGIN_TIME DATE,

LAST_FAILED_LOGIN_TIME DATE,

FAILED_PASSWORD_ATTEMPT_COUNT NUMBER,

IS_ACCOUNT_LOCKED_OUT CHAR NOT NULL

CONSTRAINT CK_TBL_CDM_USER CHECK

(IS_ACCOUNT_LOCKED_OUT IN ('Y','N')),

LAST_ACCOUNT_LOCKED_OUT_TIME DATE,

LAST_PASSWORD_CHANGED_TIME DATE);

/

INSERT INTO tbl_cdm_user

VALUES(82001, '99CpsBTKpN1',TO_DATE('02-APR-2015 14:05:08', 'DD-MON-YYYY HH24:MI:SS'),

NULL, 0, 'N', NULL, TO_DATE('02-DEC-2014 13:05:08', 'DD-MON-YYYY HH24:MI:SS'));

INSERT INTO tbl_cdm_user

VALUES(82002, 'ZWNWnQJT901', TO_DATE('02-DEC-2014 16:15:01', 'DD-MON-YYYY HH24:MI:SS'),

NULL, 0, 'N', NULL, TO_DATE('02-NOV-2014 11:11:18', 'DD-MON-YYYY HH24:MI:SS'));

INSERT INTO tbl_cdm_user

VALUES(82003, 'gc88Wmvpx81', TO_DATE('01-APR-2015 19:15:08', 'DD-MON-YYYY HH24:MI:SS'),

TO_DATE('05-MAY-2015 21:45:18', 'DD-MON-YYYY HH24:MI:SS'), 1, 'N', NULL, TO_DATE('30-JAN-2015 23:01:01', 'DD-MON-YYYY HH24:MI:SS'));

INSERT INTO tbl_cdm_user

VALUES(82004, 'KcxweSYg5551', TO_DATE('03-JAN-2015 14:12:33', 'DD-MON-YYYY HH24:MI:SS'),

TO_DATE('06-MAY-2015 09:12:22', 'DD-MON-YYYY HH24:MI:SS'), 5, 'Y', TO_DATE('06-MAY-2015 09:12:22', 'DD-MON-YYYY HH24:MI:SS'), NULL);

INSERT INTO tbl_cdm_user

VALUES(82005, 'CDYe44BBXd11', TO_DATE('22-MAR-2015 05:22:18', 'DD-MON-YYYY HH24:MI:SS'),

NULL, 0, 'N', NULL, NULL);

INSERT INTO tbl_cdm_user

VALUES(82006, 'vhSDHMDg6661', TO_DATE('07-FEB-2015 04:00:08', 'DD-MON-YYYY HH24:MI:SS'),

NULL, 0, 'N', NULL, TO_DATE('01-FEB-2015 04:35:01', 'DD-MON-YYYY HH24:MI:SS'));

COMMIT;

2) (30 Points)

Based on the TBL_CDM_USER table created in (1), create a procedure for validating user login.

The procedure header is

CREATE OR REPLACE PROCEDURE check_tbl_cdm_user_login

(

p_user_id NUMBER,

p_password VARCHAR2,

p_ret_code OUT NUMBER,

p_ret_message OUT VARCHAR2

)

(You cannot change the procedure header. You will receive 0 points if you use a different procedure header. You will receive 0 points if you submit more than one procedure. No DBMS_OUTPUT statement is needed in the procedure.)

An account will be locked out after 5 consecutive failed login attempts.

A password is case-sensitive.

The logical steps are as follows;

The value of p_user_id is not in the USER_ID column of the TBL_CDM_USER table. The following parameters will be updated:

Parameter p_ret_code <== -1.

Parameter p_ret_message <== 'ERR_BAD_ID'.

The value of p_user_id is in the USER_ID column of the TBL_CDM_USER table, but the corresponding account is locked out (column IS_ACCOUNT_LOCKED_OUT = 'Y'). The following parameters will be updated:

Parameter p_ret_code <== -2.

Parameter p_ret_message <== 'ERR_LOCKED_OUT'.

The value of p_user_id is in the USER_ID column of the TBL_CDM_USER table and the value of p_password matches the corresponding password in the table. The corresponding row in the TBL_CDM_USER table will be updated:

Column LAST_SUCCESSFUL_LOGIN_TIME <== SYSDATE

Column FAILED_PASSWORD_ATTEMPT_COUNT <== 0

Column LAST_FAILED_LOGIN_TIME <== NULL

The following parameters will be updated:

Parameter p_ret_code <== 1.

Parameter p_ret_message <== 'OK'.

The value of p_user_id is in the USER_ID column of the TBL_CDM_USER table, but the value of p_password does not match the corresponding password in the table. You update the corresponding row in the TBL_CDM_USER table and the procedure parameters:

Column FAILED_PASSWORD_ATTEMPT_COUNT <==

column FAILED_PASSWORD_ATTEMPT_COUNT + 1

Column LAST_FAILED_LOGIN_TIME <== SYSDATE

Case 1: Column FAILED_PASSWORD_ATTEMPT_COUNT != 5

Parameter p_ret_code <== -3.

Parameter p_ret_message <== 'ERR_BAD_PWD'.

Case 2: Column FAILED_PASSWORD_ATTEMPT_COUNT = 5

Column IS_ACCOUNT_LOCKED_OUT <== 'Y'

Column LAST_ACCOUNT_LOCKED_OUT_TIME <== SYSDATE

Parameter p_ret_code <== -4.

Parameter p_ret_message <==

'ERR_BAD_PWD_AND_LOCKED_OUT'.

Hints: UPDATE TBL_CDM_USER SET WHERE USER_ID = p_user_id;

p_ret_code := ;

p_ret_message := '';

You need to test your procedure with different parameters in a PL/SQL block.

3) (30 Points)

Based on the TBL_CDM_USER table created in (1), create a procedure to change the password for a given user ID.

A password is case-sensitive.

A password must be between 8 and 20 characters in length.

A password must include at least one uppercase alphabetic character (A-Z), one lowercase alphabetic character (a-z), and one numeric character (0-9).

You need to update the PASSWORD and LAST_PASSWORD_CHANGED_TIME columns if the password can be reset. (Oracle SYSDATE function returns the current date and time.)

The procedure header is

CREATE OR REPLACE PROCEDURE check_tbl_cdm_user_password

(

p_user_id NUMBER,

p_current_password VARCHAR2,

p_new_password VARCHAR2,

p_ret_code OUT NUMBER,

p_ret_message OUT VARCHAR2

)

(You cannot change the procedure header. You will get a zero point if a different procedure header is used. You will receive 0 points if you submit more than one procedure. No DBMS_OUTPUT statement is needed in the procedure.)

The logical steps are as follows;

The value of p_user_id is not in the USER_ID column of the TBL_CDM_USER table. The password cannot be reset. The following parameters will be updated:

Parameter p_ret_code <== -1.

Parameter p_ret_message <== 'ERR_BAD_ID'.

The current password is not correct. The password cannot be reset. The following parameters will be updated:

Parameter p_ret_code <== -2.

Parameter p_ret_message <== 'ERR_BAD_CURR_PWD'.

The new password is the same as the current password. The password cannot be reset. The following parameters will be updated:

Parameter p_ret_code <== -3.

Parameter p_ret_message <== 'ERR_SAME_PWD'.

The new password is too long or too short. The password cannot be reset. The parameters will be updated:

Parameter p_ret_code <== -4.

Parameter p_ret_message <== 'ERR_BAD_PWD_LEN'.

The new password does not include an uppercase alphabetic character. The password cannot be reset. The following parameters will be updated:

Parameter p_ret_code <== -5.

Parameter p_ret_message <== 'ERR_BAD_PWD_UC'.

The new password does not include a lowercase alphabetic character. The password cannot be reset. The following parameters will be updated:

Parameter p_ret_code <== -6.

Parameter p_ret_message <== 'ERR_BAD_PWD_LC'.

The new password does not include a numeric character. The password cannot be reset. The following parameters will be updated:

Parameter p_ret_code <== -7.

Parameter p_ret_message <== 'ERR_BAD_PWD_NUM'.

The password can be reset. You update the corresponding row in the TBL_CDM_USER table:

Column PASSWORD <== p_new_password

Column LAST_PASSWORD_CHANGED_TIME <== SYSDATE

The following parameters will be updated:

Parameter p_ret_code <== 1.

Parameter p_ret_message <== 'OK'.

Hint: To test a string for alphabetic and numeric characters, you may use the REGEXP_LIKE function.

(REGEXP_LIKE is similar to the LIKE condition, except REGEXP_LIKE performs regular expression matching instead of the simple pattern matching performed by LIKE. This condition evaluates strings using characters as defined by the input character set.)

Examples:

1) SELECT COUNT(*) INTO v_n FROM DUAL WHERE REGEXP_LIKE(v_1, '[A-Z]');

v_n = 0: v_1 does not include an uppercase alphabetic character.

2) SELECT COUNT(*) INTO v_n FROM DUAL WHERE REGEXP_LIKE(v_1, '[a-z]');

v_n = 0: v_1 does not include a lowercase alphabetic character.

3) SELECT COUNT(*) INTO v_n FROM DUAL WHERE REGEXP_LIKE(v_1, '[0-9]');

v_n = 0: v_1 does not include a numeric character.

You need to test your procedure with different parameters in a PL/SQL block.

Please submit a text file containing all the source codes to D2L by the due date.