LINK TO DATA FILE:

http://s000.tinyupload.com/download.php?file_id=72853632711803396154&t=7285363271180339615444062

Week 8 Final Project

The final project is meant to be comprehensive. It requires you to pull all your knowledge together to implement database security.,

Deliverables:

You are required to submit your scripts and screen captures.

Specifications:

The following lists the specifications on the final project:

Required tasks:

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

User	Password	Other Parameters
DBSEC_ADMIN	admin	Tablespace: USERS Temporary tablespace: TEMP
DBSEC_CLERK	clerk	Same as above
DBSEC_DEV	dev	Same as above
DBSEC	dev#1	Same as above
VPD_CLERK1	john$22	Same as above
VPD_CLERK2	nancy$46	Same as above

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.

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

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

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.

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.

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.

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