This project requires you to implement some security measures to protect your database. The scenario is as follows:

After intensive investigation, you found out that two (2) users (VPD_CLERK1 and DBSEC_CLERK) were abusing their privileges. Everyone else on their profile have no problems. You decided to implement a solution to prevent the two (2) users from creating any tables or any other database objects. You are also going to monitor the activities by the two (2) users. Specifically, you need to:

1. Create a trigger named "trigger_before_create_object." (The period is not part of the trigger name.) The trigger will not allow the two (2) users to create any database objects including tables. Exact spelling on the trigger name is required. (30 points)
2. Raise an error message whenever the above two (2) users attempt to create any table or other database objects. The message will read "You do not have the privilege to create tables or other database objects. Please contact your manager if you need the privilege." (10 points)
3. Use Oracle built-in audit function to set up the audit for the two (2) users' activities including CREATE. (30 points)
4. Use one statement of your choice to test your trigger on each user on CREATE statement, respectively. FYI: In the past, this is the place where code copying problems were caught. (15 points)
5. Show the audit results of the above attempts including operating system user name, database user name, date and action name. (15 points)
6. Perform all the above functions in one SQL script file.

Tips:

1. The project was under the assumption that the two (2) users have already been created with passwords from the previous projects. If for some reason the users were dropped, please recreate them.
2. You may have to refer to chapter 9 of your textbook.
3. Please discuss on Community Solutions on the ways to include more than one user name for the control in the trigger.
4. Since this is the last project, I intentionally left out the details for you to define. My grading criterion will then be: it is fine as long as it works.
5. While grading, I will login as scott/tiger to begin with. In other words, this will be the assumed condition for your project.
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.