DBST668

Database Security DBMS 668

Lab Exercise 5

Based upon the relation shown below generate Oracle SQL queries to create the appropriate level of security for each scenario. Your assignment must be turned in as ONE document in either Word or PDF format. You may use this outline as your template.

All solutions to the scenarios below must be based on the techniques covered in this week’s lesson. So, NO VPDs or Views like those that were used in Lab 2. Everything should be based on one of the following methods: 1) invisible columns; 2) decode; 3) full redaction and 4) partial redaction.

Generate test scripts for each scenario user to demonstrate the access allowed and what is redacted or hidden.

NOTE: If you run into trouble with the table below (things are not working, and you can’t figure out why it doesn’t work) there are two recommended workarounds. 1) Drop the table and start with a fresh table or 2) create another version of the table and use it (say you successfully complete steps 1-4 and can’t get step 5 to work--you keep getting error messages or you don’t get any error messages, but you don’t get the expected results). The second version of the table would be Employee2. It is very important to keep track of what you have done, the order you do things, what user you were when you did things, and what rights you had when you did things. There are a lot of Users to keep track of.

Step 1a: Write the SQL code necessary to create this table. The bonus column in the table is to be invisible. 1b: Create User Accounts with passwords, grant create session and select rights on the employee table for All Employees.

Employee

EmployeeId	NAME	ORGCD	CC	SALARY	BONUS	TITLE
0980	MARTIN	IM	4560123450001234	250,000	250,000	CEO
5430	SEAVER	IS	4560123450002345	180,000	50,000	CIO
7650	LOONEY	IT	4560123450003456	120,000	25,000	CISO
9870	MILLS	SA	4560123450004567	90,000	10,000	CFO
9990	BOND	INT	4560123450005678	158,000		SPY

Step 2: User Martin, the CEO, owns the employee table and is the only user that knows there is a bonus column in the table. The rest of the users don’t know there is a bonus column. Show how user Martin can manipulate the bonus data (update it) or review it (select bonus information in combination with other data, e.g., select name, bonus from employee.

Step 3: The CEO has announced the bonuses. A notice has gone to each employee that will receive a bonus. The existence of bonus column is now known so a different way to restrict who can view the data is needed. The new rule for access to the bonus information is it can be accessed by the employee receiving the bonus AND the CEO. The CEO is still the only one who can update the bonus data. Show how to achieve this result. [Note: The solution is not a view with a Where clause of User = Name clause or a VPD].

Step 4: The CISO is concerned that having both the ORGCD and TITLE in the same table reveals too much information--for example, Bond, ORGCD INT (Intelligence), TITLE SPY. Something must be done immediately to protect this information. As an interim step, TITLE should be completely redacted. Show how to achieve this result.

Step 5-6: The CEO is irate about a hack against credit card numbers that the firm experienced. He has tasked the CIO with fixing the problem. The CIO is to have a recommendation for the CEO by the next Wednesday. The CIO in turn has task the CISO to have a set of proposed alternatives by this Friday. The CISO has some ideas and quickly checks the recommendations from the National Credit Card Bureau (NCCB). The recommendation is to redact 12 of the 16 numbers for normal use (only the last 4 numbers will be visible (except to the CIO and CISO). The question is which design will be the most attractive to the CEO (what will she like?). They quickly decide to present 2 options for the redaction:

1) Completely blank the first 12 numbers. The CC list will look like:

CC

------------------------

1234

2345

3456

4567

4560

2) Mask the first 12 numbers with a special character such as #. The CC list will look like:

--------------------

############1234

############2345

############3456

############4567

############5678

-----------------------

Show how you can achieve this result.

Step 7: One of the programs that access the CC file is terminating because of the use of ## to mask the first 12 position of the credit card number (############1234 – the program expects CC to be an all numeric field and the use of alpha-numeric characters (#) in a numeric field (CC) raises an exception/causing an error). The CISO recommends changing the masking to all 9’s so the data is all numeric (999999999991234).

Step 8-9: The CISO has identified a couple of vulnerabilities where a hacker could get to the EMPLOYEEID and ORGCD data by using a SQL injection attack. He has requested a demonstration of the use of random redaction to protect the data in those two columns (random numbers to replace the EMPLOYEEID query response and random characters to protect the ORGCD). Show how you can achieve this result.

Step 10: The CIO suggested employees that sell the most security services receive a commission. This is to be a trial program so has been decided to add a column COMIS (for commission rate) to the employee table. The COMIS column is to be redacted until a final decision is made on the commissions. Show how you can achieve this result.

�I would get rid of this one or put it right after Step 6.