In a previous project, you have created DBSEC user and CUSTOMER table.

In this project, you will implement a FGA case as illustrated on pages 296-301 in your textbook. However, the CUSTOMER table will be the one you created on the previous project. Your mission is to design and implement the following auditing functions on the CUSTOMER table.

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

You need to generate one SQL script file to perform all the following tasks:

1. Add a policy to implement audit task 1 (SELECT). The policy will be named as AUDIT_POLICY_1_SELECT. (15 points)
2. Add a policy to implement audit task 2 (CREDIT_LIMIT). The policy will be named as AUDIT_POLICY_2_CREDIT. (15 points)
3. Add a policy to implement audit task 3 (DELETE on CUSTOMER table). The policy will be named as AUDIT_POLICY_3_DELETE. (15 points)
4. Turn on the audit function. (15 points)
5. Connect to user DBSEC, and perform the following DML functions for testing purpose: (10 points)
   
   

   SELECT CUSTOMER_ID, FIRST_NAME,LAST_NAME,CREDIT_LIMIT FROM customer WHERE CUSTOMER_ID=201340;

   UPDATE customer SET CREDIT_LIMIT=2500000 WHERE CUSTOMER_ID=201340;

   DELETE from CUSTOMER WHERE CUSTOMER_ID=201340;

   
   
   
6. Show the audit trail including when and who has used/modified the CUSTOMER table. (10 points)
   
   
7. Roll back the above change. (5 points)

Tips:

1. You may use a drop user statement to refresh your database and enable testing. Then, you need to recreate your CUSTOMER table. But, it is not required as a part of this project.
2. The sequence above was tested. Please make sure to do it right on every step. If any step is missed, it may cause a lot of headache on your part.
3. Make sure to use the exact spelling as specified. Misspelling is considered wrong by computer system. My grading script will not like it either.
4. Some details were intentionally left out to challenge students. Please test your code and make sure they work.
5. I will login as scott/tiger before grading your project.  Thus, please code your script accordingly.
6. 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;"
   

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. Thus, please test it so that you can perform all above tasks in one file.

Convention for File Name: 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.

 

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