The DPU_USER table stores information about DPU user accounts. DPU_USER( DPU_ID

ID: 3840597 • Letter: T

Question

The DPU_USER table stores information about DPU user accounts.

DPU_USER(

DPU_ID,

PASSWORD,

LAST_SUCCESSFUL_LOGIN_TIME,

LAST_FAILED_LOGIN_TIME,

FAILED_PASSWORD_ATTEMPT_COUNT,

IS_LOCKED_OUT,

LAST_LOCKED_OUT_TIME,

LAST_PASSWORD_CHANGED_TIME);

DPU_ID: Primary Key

FAILED_PASSWORD_ATTEMPT_COUNT: the number of consecutive failed password attempts

IS_LOCKED_OUT: 'Y' – the account is locked out

'N' – the account is not locked out

Create and populate the DPU_USER table as described below.

CREATE TABLE DPU_USER

( DPU_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_LOCKED_OUT CHAR NOT NULL

CONSTRAINT CK_DPU_USER CHECK (IS_LOCKED_OUT IN ('Y','N')),

LAST_LOCKED_OUT_TIME DATE,

LAST_PASSWORD_CHANGED_TIME DATE);

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

The procedure header is

CREATE OR REPLACE PROCEDURE check_dpu_user_login

(

in_dpu_id NUMBER,

in_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. Submitting more than one procedure will receive 0 points. 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;

The value of in_dpu_id is not in the DPU_ID column of the DPU_USER table.

You assign 'err1' to the out_code parameter.

The value of in_dpu_id is in the DPU_ID column of the DPU_USER table, but the corresponding account is locked out (IS_LOCKED_OUT = 'Y').

You assign 'err2' to the out_code parameter.

The value of in_dpu_id is in the DPU_ID column of the DPU_USER table and the value of in_password matches the corresponding password in the table. The corresponding row in the DPU_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.

The value of in_dpu_id is in the DPU_ID column of the DPU_USER table, but the value of in_password does not match the corresponding password in the table. You update the corresponding row in the DPU_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 'err3' to the out_code parameter.

Case 2: FAILED_PASSWORD_ATTEMPT_COUNT = 5

IS_LOCKED_OUT <== 'Y'

LAST_LOCKED_OUT_TIME <== SYSDATE

You assign 'err4' to the out_code parameter.

Hints: UPDATE DPU_USER SET …… WHERE DPU_ID = in_dpu_id;

out_code := ……;

Explanation / Answer

This is the procedure for the given logical steps:

CREATE OR REPLACE PROCEDURE check_dpu_user_login
(
   IN in_dpu_id NUMBER,
   IN in_password VARCHAR2,
   OUT out_code OUT VARCHAR2 )

BEGIN
IF(in_dpu_id = DPU_ID)
   BEGIN
       UPDATE DPU_USER
       SET out_code = 'err1'
       WHERE DPU_ID = in_dpu_id;
   END
ELSE IF(in_dpu_id = DPU_ID AND IS_LOCKED_OUT = 'Y')
   BEGIN
       UPDATE DPU_USER
       SET out_code = 'err2'
       WHERE DPU_ID = in_dpu_id;
   END
ELSE IF(in_dpu_id = DPU_ID AND in_password = PASSWORD)
   BEGIN
       UPDATE DPU_USER
       SET LAST_SUCCESSFUL_LOGIN_TIME = SYSDATE,
       FAILED_PASSWORD_ATTEMPT_COUNT = 0,
       LAST_FAILED_LOGIN_TIME = NULL,
       out_code = OK
       WHERE DPU_ID = in_dpu_id;
   END
ELSE IF(in_dpu_id = DPU_ID AND in_password != PASSWORD)
   BEGIN
       UPDATE DPU_USER
       SET FAILED_PASSWORD_ATTEMPT_COUNT = FAILED_PASSWORD_ATTEMPT_COUNT + 1,
       LAST_FAILED_LOGIN_TIME = SYSDATE,
   CASE(
   WHEN FAILED_PASSWORD_ATTEMPT_COUNT != 5 THEN out_code = 'err3'
   ELSE FAILED_PASSWORD_ATTEMPT_COUNT = 5
       IS_LOCKED_OUT = 'Y',
       LAST_LOCKED_OUT_TIME = SYSDATE,
       out_code = 'err4'
       WHERE DPU_ID = in_dpu_id;
   END
END

Navigate

The DPU_USER table stores information about DPU user accounts. DPU_USER( DPU_ID

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.

The DPU_USER table stores information about DPU user accounts. DPU_USER( DPU_ID

Question

Explanation / Answer

Related Questions

Navigate