Unless prior arrangements are made, homework turned in late will not be accepted
ID: 3603728 • Letter: U
Question
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
Explanation / Answer
If you wish to share a file with members of a departmental e-mail list (e.g., abc staff), please do not send it as an attachment. This causes the file to be copied many times (once for each list member), which causes heavy traffic on both the network server (or user's hard drive) and e-mail server. The best way to share a file within your department is to store one copy of it in the "shared" area on the network server (L:Shared) and refer to it in an e-mail note.
Limit the size of your attachments
When you send an attachment be aware that the recipient may have a limit to the size of the attachment they can receive. Office 365 has a maximum send and receive message size (and optional attachment) set at 25 MB. Also, when you attach a file to a message, the file size of the attachment increases (it can increase as much as 30% in size). Keep in mind that someone's mailbox on the Office 365 server will be prohibited from receiving messages if they exceed the maximum quota limit. The default maximum quota for receiving is 50 GB.
If you need to send the same document multiple times, change the name each time
Multiple versions of the same document can become confusing and occasionally you might send a different version of the document than what you had expected. A better way to manage multiple iterations of the same document is to change the name each time by adding a revision date and/or time to the filename. That way, each document name will be unique and it is easy to tell by the name, which version is the most recent. Then the older versions can be deleted.
Related Questions
drjack9650@gmail.com
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.