LINK TO DATA FILE: http://s000.tinyupload.com/download.php?file_id=7285363271180
ID: 3846524 • Letter: L
Question
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)
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
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
Explanation / Answer
--------------------------------creating a password complexity function------------------------------- connect sys as sysdba; CREATE OR REPLACE FUNCTION complexity_function (username varchar2, password varchar2, old_password varchar2) RETURN boolean IS n boolean; m integer; differ integer; isdigit boolean; ischar boolean; ispunct boolean; digitarray varchar2(25); punctarray varchar2(25); chararray varchar2(52); BEGIN digitarray:= '0123456789'; chararray:= 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ'; punctarray:='!"#$%&()``*+,-/:;?_'; -- Check if the password is same as the username IF NLS_LOWER(password) = NLS_LOWER(username) THEN raise_application_error(-20001, 'Password same as or similar to user'); END IF; -- Check for the minimum length of the password IF length(password) < 6 THEN raise_application_error(-20002, 'Password length less than 6'); END IF; -- Check if the password is too simple. A dictionary of words may be -- maintained and a check may be made so as not to allow the words -- that are too simple for the password. IF NLS_LOWER(password) IN ('welcome', 'database', 'account', 'user', 'oracle') THEN raise_application_error(-20002, 'Password too simple'); END IF; -- Check if the password contains at least one letter, one digit and one -- punctuation mark. -- 1. Check for the digit isdigit:=FALSE; m := length(password); FOR i IN 1..10 LOOP FOR j IN 1..m LOOP IF substr(password,j,1) = substr(digitarray,i,1) THEN isdigit:=TRUE; GOTO findchar; END IF; END LOOP; END LOOP; IF isdigit = FALSE THEN raise_application_error(-20003, 'Password should contain at least one digit, one character and one punctuation'); END IF; -- 2. Check for the character ischar:=FALSE; FOR i IN 1..length(chararray) LOOP FOR j IN 1..m LOOP IF substr(password,j,1) = substr(chararray,i,1) THEN ischar:=TRUE; GOTO findpunct; END IF; END LOOP; END LOOP; IF ischar = FALSE THEN raise_application_error(-20003, 'Password should contain at least one digit, one character and one punctuation'); END IF; -- 3. Check for the punctuation ispunct:=FALSE; FOR i IN 1..length(punctarray) LOOP FOR j IN 1..m LOOP IF substr(password,j,1) = substr(punctarray,i,1) THEN ispunct:=TRUE; GOTO endsearch; END IF; END LOOP; END LOOP; IF ispunct = FALSE THEN raise_application_error(-20003, 'Password should contain at least one digit, one character and one punctuation'); END IF; -- Check if the password differs from the previous password by at least -- 3 characters IF old_password IS NOT NULL THEN differ := length(old_password) - length(password); IF abs(differ) < 3 THEN IF length(password) 'DBSEC', object_name => 'CUSTOMER', policy_name => 'MY_OWNER_POLICY', function_schema => 'DBSEC', policy_function => 'DBSEC_ROW_OWNER', statement_types => 'select, insert, update, delete' , update_check => TRUE ); end; / ------------------------------------------------------performing the query by connecting to the different user--------------------- connect VPD_CLERK1/jessie$22; select * from DBSEC.CUSTOMER; -----------------------------------------------------Auditing the customer table by creating the following auditing functions-------------------- connect DBSEC/tec5363#1; connect sys as sysdba; ---------------------------droping the select policy------------------- EXEC DBMS_FGA.DROP_POLICY (OBJECT_SCHEMA => 'DBSEC', - OBJECT_NAME => 'customer', - POLICY_NAME => 'AUDIT_POLICY_1_SELECT'); --------------------------creating the first policy_select---------------- EXEC DBMS_FGA.ADD_POLICY (object_schema => 'DBSEC', - object_name => 'CUSTOMER', - policy_name => 'AUDIT_POLICY_1_SELECT', - audit_condition => NULL, - audit_column => NULL, - handler_schema => NULL, - handler_module => NULL, - enable => TRUE, - statement_types => 'SELECT'); ---------------------------droping the credit policy------------------ EXEC DBMS_FGA.DROP_POLICY (OBJECT_SCHEMA => 'DBSEC', - OBJECT_NAME => 'customer', - POLICY_NAME => 'AUDIT_POLICY_2_CREDIT'); ---------------------------creating the second policy_credit----------- EXEC DBMS_FGA.ADD_POLICY (object_schema => 'DBSEC', - object_name => 'CUSTOMER', - policy_name => 'AUDIT_POLICY_2_CREDIT', - audit_condition => 'CREDIT_LIMIT > 50000', - audit_column => NULL, - handler_schema => NULL, - handler_module => NULL, - enable => TRUE, - statement_types => 'UPDATE'); ---------------------------droping the delete policy---------------------- EXEC DBMS_FGA.DROP_POLICY (OBJECT_SCHEMA => 'DBSEC', - OBJECT_NAME => 'customer', - POLICY_NAME => 'AUDIT_POLICY_3_DELETE'); ---------------------------creating the third policy_delete------------- EXEC DBMS_FGA.ADD_POLICY (object_schema => 'DBSEC', - object_name => 'CUSTOMER', - policy_name => 'AUDIT_POLICY_3_DELETE', - audit_condition => NULL, - audit_column => NULL, - handler_schema => NULL, - handler_module => NULL, - enable => TRUE, - statement_types => 'DELETE'); audit SELECT, UPDATE, DELETE on sys.aud$ by access; connect DBSEC/tec5363#1; -----------------------------giving permissions to the specific user----------------------------------- grant SELECT, INSERT, UPDATE, DELETE on CUSTOMER to VPD_CLERK1; grant SELECT, INSERT, UPDATE, DELETE on CUSTOMER to VPD_CLERK2; ------------------------------------connecting to the vpd user to perform audit function-------------------- connect VPD_CLERK1/jessie$22; select first_name, last_name from DBSEC.CUSTOMER where CUSTOMER_ID = 103244; update DBSEC.CUSTOMER set credit_limit=50000 where CUSTOMER_ID=103244; delete from DBSEC.CUSTOMER where CUSTOMER_ID=103244; rollback; commit; ------------------------------------connecting to VPD2 to perform the audit function------------------------------- connect VPD_CLERK2/lassie$46; select first_name, last_name from DBSEC.CUSTOMER where CUSTOMER_ID = 103244; update DBSEC.CUSTOMER set credit_limit=50000 where CUSTOMER_ID=103244; delete from DBSEC.CUSTOMER where CUSTOMER_ID=103244; rollback; commit;Related Questions
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.