TEC 5363 Database Security

Syllabus
Schedule
Reading
Discussion
Project
Test
Resource
Tip

Project 5 Password Complexity

Task:

Your mission is to develop a single SQL script that will perform all the following tasks of enforcing password complexity:

1. Connect to SYS user. (5 points)

2. Create a password complexity function (named as "tec5363_password") to enforce the following requirements so that the password (40 points):

  • Is at least six (6) characters long

  • Differs from the user name

  • Has at least one alpha, one numeric, and one punctuation mark character

  • Is not simple or obvious, such as welcome, account , oracle, database , or user.

  • Differs from the previous password by at least 3 characters

Please note the exact spelling on the function name is REQIRED.

Please note the double quotation mark (") was not a part of the specified function name.

3. Create a profile named tec5363_prof with the following specifications. Please note the exact spelling on the profile is REQIRED. (40 points) Test your code before submission.

Number of days the password is valid before it is aged out. 60 days
Number of days ahead of expiration the user is warned about the password expiration. 1 day
Number of days before a password can be reused. 30 days
Number of times a password can be reused. UNLIMITED
Number of failed login tries allowed before the account is locked. 3
Number of days an account is locked due to failed login tries 13 days
PASSWORD_VERIFY_FUNCTION tec5363_password

4. Assign the above profile to user DBSEC that you have already created. (15 points)

Tips:

1. One SQL(PL/SQL) file is required to complete all the above tasks.

2. You may use the password complexity example posted on the course web site. You may have to make corresponding changes for the project.

3. Typical syntax on connecting to SYS is: "conn sys/password as sysdba;"

 

Report:

One and only one SQL script file will be required and submitted through WebCT. Please note that 20 points will be taken off if the script file is not executable under SQL Plus, or is not in one file. Thus, please test it so that you can perform all above tasks in one file.

Convention for File Name: Please name your file as firstname_lastname_project5.sql, where firstname and lastname are your first and last name, respectively, and "_" is underscore. Starting from this point, the file naming convention will be enforced throughout this semester. Five (5) points will be taken off if the file is not named according to the convention.

.

Due:

At the end of this week. Please see the course schedule for exact due date.

Unrelated Matter:
Author: Paras Pradhan (ppradhan)
Date: Saturday, September 9, 2006 8:27am

A joke: A fool sent a SMS to his pregnant wife.

Two seconds later a report came to his phone and he started dancing. The report said, "DELIVERED". Cheers