CPSC 4670 Database Security and Auditing

Project 2 -- CPSC 4670 Database Security and Auditing

Implement Discretionary Access Control and Exploit its Vulnerabilities

Due on Feb. 13rd.

Preparation was done in project #1:

  • Create a database DBSEC including CUSTOMERS, EMPLOYEES, ORDERS, JOBS, SALES_COMMISSION, DEPARTMENTS, CATEGORIES, PRODUCT_INVENTORY, PROMOTIONS, PRODUCT_PRICES, SUPPLIERS, PRODUCT_SUPPLIER, PAYMENT_METHOD, SHIPMENT_MEHTOD, AND EMPLOYEE_RANKS tables. Refer to Figure 4-20 in page 161 for details.
  • Run Queries:
  1. Display customer name, product name and purchased price andemployee id.
  2. Displaynumber of orders placed by each customer.

Task One (50 points):

  • Design your own security policies or use table 1 to 3 to define profiles, users, and roles.
  • Define profiles (10 points).
  • Define roles (10 points).
  • Associate users with roles and profiles. (10 points)
  • Test you DAC security policies
  • If Alice can select Table Employee (5 points)
  • If Alice can insert on Table Orders (5 points)
  • If Ford can update Table Customers (5 points)
  • If David can select Table Employee (5 points)

Tip: use name space when you execute your query. For example, Alice will run

Select * from system.employees;

TaskTwo (40 points):

Trojan horse application can exploit vulnerabilities of DAC security policies. Write a Trojan horse application so that David can access data in table Employee.

Trojan horse application. (20 points)

Demo of results. (20 points)

Task Three (10 points):

A written report to document your efforts. (10 points)

Table 1. Profile configuration requirements

Profile / Resource / Password
DBSEC_ADMIN_PROF / Session_per_user = 5
Connect_time = 8 hours
Idle_time = 1 hour / Password_life_time = 1 month
Password_grace_time = 7 days
DBSEC_DEVELOPER_PROF / Connect_time = 12 hours
Idle_time = 2 hours
CPU_per_call = 1 minute / Password_life_time = 1 month
Password_grace_time = 14 days
DBSEC_CLERK_PROF / Session_per_user = 1
CPU_per_call = 5 seconds
Connect_time = 8 hours
Idle_time = 30 minutes
Logical_reads_per_call = 10KB / Failed_login_attempts = 1
Password_lock_time = 3 days
Password_grace_time = 14 days

Table 2. User account configuration

User name / Role / Profile
Alice / Human_resources
Account_Manager / DBSEC_ADMIN_PROF
Bob / Customer_Service / DBSEC_CLERK_PROF
Cathy / Sales
Customer_Service / DBSEC_CLERK_PROF
David / Account_Manager / DBSEC_DEVELOPER_PROF
Edwards / Sales / DBSEC_CLERK_PROF
Ford / Marketing / DBSEC_CLERK_PROF

Table 3. Role and privilege matrix

Human_resources
Table / Select / Insert / Update / Delete
Employee / √ / √ / √ / X
Customer_Service
Table / Select / Insert / Update / Delete
Customers / √ / √ / X
Orders / √ / X
Account_Manager
Table / Select / Insert / Update / Delete
Customers / √ / √ / √ / X
Orders / √ / √ / √ / X
Sales
Table / Select / Insert / Update / Delete
Customers / √ / X
Orders / √ / √ / √ / X
Marketing
Table / Select / Insert / Update / Delete
Customers / √ / X
Orders / √ / X

1