Solved-assignment #5 -SOLUTION

Description

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 DPUCC_USER table stores information about user accounts.

DPUCC_USER(

DPUCC_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);

DPUCC_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 DPUCC_USER table as described below.

CREATE TABLE DPUCC_USER

( DPUCC_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_DPUCC_USER CHECK

(IS_ACCOUNT_LOCKED_OUT IN (‘Y’,’N’)),

LAST_ACCOUNT_LOCKED_OUT_TIME DATE,

LAST_PASSWORD_CHANGED_TIME DATE);

/

INSERT INTO DPUCC_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 DPUCC_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 DPUCC_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 DPUCC_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 DPUCC_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 DPUCC_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 DPUCC_USER table created in (1), create a procedure for validating user login.

The procedure header is

CREATE OR REPLACE PROCEDURE check_dpucc_user_login

(

in_dpucc_id NUMBER,

in_password VARCHAR2,

out_code 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 after 5 consecutive failed login attempts.

A password is case-sensitive.

The logical steps are as follows;

1. The value of in_dpucc_id is not in the DPUCC_ID column of the DPUCC_USER table.

You assign ‘err_bad_id’ to the out_code parameter.

2. The value of in_dpucc_id is in the DPUCC_ID column of the DPUCC_USER table, but the corresponding account is locked out (IS_ACCOUNT_LOCKED_OUT = ‘Y’).

You assign ‘err_locked_out’ to the out_code parameter.

3. The value of in_dpucc_id is in the DPUCC_ID column of the DPUCC_USER table and the value of in_password matches the corresponding password in the table. The corresponding row in the DPUCC_USER table will be updated:

• LAST_SUCCESSFUL_LOGIN_TIME <== SYSDATE

• FAILED_PASSWORD_ATTEMPT_COUNT <== 0

• LAST_FAILED_LOGIN_TIME <== NULL

You assign ‘ok’ to the out_code parameter.

4. The value of in_dpucc_id is in the DPUCC_ID column of the DPUCC_USER table, but the value of in_password does not match the corresponding password in the table. You update the corresponding row in the DPUCC_USER table and the out_code parameter:

• FAILED_PASSWORD_ATTEMPT_COUNT <==

FAILED_PASSWORD_ATTEMPT_COUNT + 1

• LAST_FAILED_LOGIN_TIME <== SYSDATE

• Case 1: FAILED_PASSWORD_ATTEMPT_COUNT != 5

• You assign ‘err_bad_pwd’ to the out_code parameter.

• Case 2: FAILED_PASSWORD_ATTEMPT_COUNT = 5

• IS_ACCOUNT_LOCKED_OUT <== ‘Y’

• LAST_ACCOUNT_LOCKED_OUT_TIME <== SYSDATE

• You assign ‘err_bad_pwd_5’ to the out_code parameter.

Hints: UPDATE DPUCC_USER SET …… WHERE DPUCC_ID = in_dpucc_id;

out_code := ……;

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

3) (30 Points)

Based on the DPUCC_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 15 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_dpucc_user_password

(

in_dpucc_id NUMBER,

in_current_password VARCHAR2,

in_new_password VARCHAR2,

out_code 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;

1. The value of in_dpucc_id is not in the DPUCC_ID column of the DPUCC_USER table. The password cannot be reset. You assign ‘err_bad_id’ to the out_code parameter.

 

2. The current password is not correct. The password cannot be reset. You assign ‘err_bad_curr_pwd’ to the out_code parameter.

3. The new password is the same as the current password. The password cannot be reset. You assign ‘err_bad_new_pwd_1’ to the out_code parameter.

4. The new password is too long or too short. The password cannot be reset. You assign ‘err_bad_new_pwd_2’ to the out_code parameter.

5. The new password does not include an uppercase alphabetic character. The password cannot be reset. You assign ‘err_bad_new_pwd_3’ to the out_code parameter.

6. The new password does not include a lowercase alphabetic character. The password cannot be reset. You assign ‘err_bad_new_pwd_4’ to the out_code parameter.

7. The new password does not include a numeric character. The password cannot be reset. You assign ‘err_bad_new_pwd_5’ to the out_code parameter.

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

• PASSWORD <== in_new_password

• LAST_PASSWORD_CHANGED_TIME <== SYSDATE

You assign ‘ok’ to the out_code parameter.

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.