The DPU_USER table stores information about DPU user accounts. DPU_USER( DPU_ID
ID: 3840868 • 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;
1) Based on the DPU_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_dpu_user_password
(
in_dpu_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. Submitting more than one procedure will receive 0 points. No DBMS_OUTPUT statement is needed in the procedure.)
The logical steps are as follows;
The value of in_dpu_id is not in the DPU_ID column of the DPU_USER table. The password cannot be reset. You assign 'err10' to the out_code parameter.
The current password is not correct. The password cannot be reset. You assign 'err11' to the out_code parameter.
The new password is the same as the current password. The password cannot be reset. You assign 'err12' to the out_code parameter.
The new password is too long or too short. The password cannot be reset. You assign 'err13' to the out_code parameter.
The new password does not include an uppercase alphabetic character. The password cannot be reset. You assign 'err14' to the out_code parameter.
The new password does not include a lowercase alphabetic character. The password cannot be reset. You assign 'err15' to the out_code parameter.
The new password does not include a numeric character. The password cannot be reset. You assign 'err16' to the out_code parameter.
The password can be reset. You update the corresponding row in the DPU_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.
Explanation / Answer
Hey,
Please find below the updated code. It has compiled successfully. I have not tried it so far.
CREATE OR REPLACE PROCEDURE check_dpu_user_password(in_dpu_id NUMBER,in_current_password VARCHAR2,in_new_password VARCHAR2,out_code OUT VARCHAR2) is
v_n Number(10);
v_1 Varchar2(30);
begin
SELECT COUNT(*) INTO v_n FROM DPU_USER WHERE DPU_ID=in_dpu_id and PASSWORD=in_current_password;
if v_n=0 then
out_code:='er11';
end if;
SELECT COUNT(*) INTO v_n FROM DUAL WHERE REGEXP_LIKE(v_1, '[A-Z]');
if v_n=0 then
out_code:='er14';
end if;
SELECT COUNT(*) INTO v_n FROM DUAL WHERE REGEXP_LIKE(v_1, '[a-z]');
if v_n=0 then
out_code:='er15';
end if;
SELECT COUNT(*) INTO v_n FROM DUAL WHERE REGEXP_LIKE(v_1, '[0-9]');
if v_n=0 then
out_code:='er16';
end if;
if (length(in_new_password)<=5 OR length(in_new_password)>=20) then
out_code:='er13';
end if;
if (in_new_password =in_current_password) then
out_code:='er12';
end if;
update DPU_USER set password=in_new_password where DPU_ID=in_dpu_id;
end check_dpu_user_password;
Related Questions
drjack9650@gmail.com
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.