Specifications:

The final project is meant to be comprehensive. It requires you to pull all your knowledge together to implement database security. Please note that the specifications for the final project may be different from your previous projects.

The following lists the specifications on the final project:

1. Create the following database users with the parameters specified in the table. (10 points)
   
   

   User	Password	Other Parameters
   DBSEC_ADMIN	tec5363admin	Tablespace: USERS Temporary tablespace: TEMP
   DBSEC_CLERK	tec5363clerk	Same as above
   DBSEC_DEV	tec5363dev	Same as above
   DBSEC	tec5363#1	Same as above
   VPD_CLERK1	jessie$22	Same as above
   VPD_CLERK2	lassie$46	Same as above

   
   
2. Create a CUSTOMER table as a sample database object to implement security. (10 points) The DBSEC user is the owner of the CUSTOMER table, which has the following columns.
   
   

   Column Name	Data Type
   SALES_REP_ID	NUMBER(4)
   CUSTOMER_ID	NUMBER(8) NOT NULL
   CUSTOMER_SSN	VARCHAR2(9)
   FIRST_NAME	VARCHAR2(20)
   LAST_NAME	VARCHAR2(20)
   ADDR_LINE	VARCHAR2(60)
   CITY	VARCHAR2(30)
   STATE	VARCHAR2(30)
   ZIP_CODE	VARCHAR2(9)
   PHONE	VARCHAR2(15)
   EMAIL	VARCHAR2(80)
   CC_NUMBER	VARCHAR2(20)
   CREDIT_LIMIT	NUMBER
   GENDER	CHAR(1)
   STATUS	CHAR(1)
   COMMENTS	VARCHAR2(1024)
   CTL_UPD_DTTM	DATE
   CTL_UPD_USER	VARCHAR2(30)
   CTL_REC_STAT	CHAR(1)

   
   Populate data using the file provided. Click here to download the data file.
   You may either put all "insert" statements within this file, or provide a link to another file. Refer to the dbsec_ch4_setup.sql file in your downloaded file if you like to see how it was done. If you use the link, please make sure your link works on your computer.
   
   
3. Create password complexity policy function (named "complexity _function"). (10 points) The password policy will enforce the following complexity so that the password:
   
   

   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

   
   
4. Create profiles and manage all database users using the profiles including enforcing the password complexity. (20 points)
   
   You will create the following profiles:
   

   Profile	Resources	Password
   DBSEC_ADMIN_PROF	SESSIONS_PER_USER=5 CONNECT_TIME=8 hours IDLE_TIME=1 hour	PASSWORD_LIFE_TIME= 1 month PASSWORD_GRACE_TIME=7 days PASSWORD_VERIFY_FUNCTION=complexity _function
   DBSEC_DEV_PROF	CONNECT_TIME=12 hours IDLE_TIME=2 hours CPU_PER_CALL=1 minute	PASSWORD_LIFE_TIME= 1 month PASSWORD_GRACE_TIME=14 days PASSWORD_VERIFY_FUNCTION=complexity _function
   DBSEC_CLERK_PROF	SESSIONS_PER_USER=1 CPU_PER_CALL=5 seconds CONNECT_TIME=8 hours IDLE_TIME=30 minutes LOGICAL_READS_PER_CALL=10 KB	FAILED_LOGIN_ATTEMPTS=3 PASSWORD_LIFE_TIME= 1 month PASSWORD_LOCK_TIME=3 days PASSWORD_GRACE_TIME=14 days PASSWORD_VERIFY_FUNCTION=complexity _function

   
   You will create the following roles:
   

   Role Name	Privileges
   DBSEC_ADMIN_ROLE	SELECT and ALTER on all DBSEC tables
   DBSEC_CLERK_ROLE	SELECT, INSERT, and UPDATE on all DBSEC tables
   DBSEC_SUPERVISOR_ROLE	SELECT, INSERT, UPDATE and DELETE on all DBSEC tables
   DBSEC_QUERY_ROLE	SELECT only on CUSTOMER table owned by DBSEC

   
   You will assign roles and profiles to database users as follows:
   

   User Name	Role	Profile
   DBSEC_ADMIN	DBSEC_ADMIN_ROLE	DBSEC_ADMIN_PROF
   DBSEC_CLERK, VPD_CLERK1, VPD_CLERK2	DBSEC_CLERK_ROLE	DBSEC_CLERK_PROF
   DBSEC_DEV	DBSEC_ADMIN_ROLE plus DBSEC_SUPERVISOR_ROLE	DBSEC_DEV_PROF

   
   
   
5. Implement view on CUSTOMER table. (10 points) You will create a VIEW named as CUSTOMER_F_VIEW to display only rows that belong to the logged on user.
   
   You will enable VPD_CLERK1 to access the CUSTOMER data through the view.
   
   
6. Implement virtual private database (VPD) on CUSTOMER table so that only the owner of data can access their own row. (15 points)
   
   First, you will create a policy function, named "DBSEC_ROW_OWNER" so that only the data that belong to the current user will be accessed. Then, you will add the policy using DBMS_RLS.ADD_POLICY function.
   
   
7. Audit the activities on CUSTOMER table. (15 points)
   
   You will design and implement the following auditing functions on the CUSTOMER table.
   

   Track all "SELECT" activities on the CUSTOMER table, including database user, operating system user, and time when the operation is performed. Track the changes when CREDIT_LIMIT is set to a value above $50,000. Track the CUSTOMER table when a customer record was deleted.

   
   
8. Audit the user activities of two users. You will set up an audit mechanism to monitor all activities by two (2) database users: VPD_CLERK1, VPD_CLERK2. (10 points)

Requirements:

1. Use exact spelling on names as specified above. Those names may be different from your previous projects.
2. When you need to connect to SYSTEM user, please use a syntax so that it will ask the user to type the password. A typical example will be: "connect system;"
3. Your script will be tested under "scott/tiger" schema. In other words, the instructor will login as SCOTT before testing your script.
4. Your script will be tested during grading. Thus, please spend time to test your script before submission.

One SQL script 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. 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_final.sql, where firstname and lastname are your first and last name, respectively, and "_" is underscore. Five (5) points will be taken off if the file is not named according to the convention.

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

Author: Sagun Piya (srpiya2)

Are computers male or female?  

A language instructor was explaining to her class that French nouns, unlike their English counterparts, are grammatically designated as masculine or feminine.   Things like 'chalk' or 'pencil,' she described, would have a gender association although in English these words were neutral.

Puzzled, one student raised his hand and asked, "What gender is a computer?"   The teacher wasn't certain which it was, and so divided the class into two groups and asked them to decide if a computer should be masculine or feminine. One group was comprised of the women in the class, and the other, of men. Both groups were asked to give four reasons for their recommendation.  

The group of women concluded that computers should be referred to in masculine gender because:

1. In order to get their attention, you have to turn them on.

2. They have a lot of data but are still clueless.

3. They are supposed to help you solve your problems, but half the time they ARE the problem.

4. As soon as you commit to one, you realize that, if you had waited a little longer, you could have had a better model.  

The men, on the other hand, decided that computers should definitely be referred to in the feminine gender because:

1. No one but their creator understands their internal logic.

2. The native language they use to communicate with other computers is incomprehensible to everyone else.

3. Even your smallest mistakes are stored in long-term memory for later retrieval.

4. As soon as you make a commitment to one, you find yourself spending half your paycheck on accessories.