Database Design and Security

• The table BOOKING contains seven columns (fields) used to store information about a given booking.
• This table has one primary key: id.
• The table GUESTS likewise contains columns used to describe a guest, and contains only one primary key: id.
• The RESERVATION table has two primary keys, which are also foreign keys referencing the primary keys from the two tables described above: guest_id and booking_id.
• Based solely on these images, it is not completely clear how foreign keys are created. The next section will examine the SQL code used to create these tables.
• In this table, the two foreign keys are used to form a composite primary key.
• Observe how each column has a specified data type; this is one example of enforcing constraints upon a database.

create table booking( 
    id INT PRIMARY KEY AUTO_INCREMENT, 
    arrive DATE NOT NULL, 
    depart DATE NOT NULL, 
    special VARCHAR(60), 
    type VARCHAR(30), 
    event VARCHAR(30),
    referral VARCHAR(30)
); 

create table guests(
	id INT AUTO_INCREMENT PRIMARY KEY, 
	fname VARCHAR(20) NOT NULL, 
	lname VARCHAR(20) NOT NULL, 
	dob DATE, 
	phone VARCHAR(18), 
	email VARCHAR(40), 
	age INT, 
	street VARCHAR(30), 
	city VARCHAR(20), 
	state VARCHAR(20), 
	country VARCHAR(20), 
	postal VARCHAR(10)
);

create table reservation(
	guest_id INT, 
	booking_id INT, 
	occurred DATETIME NOT NULL, 
	status VARCHAR(20) NOT NULL, 
	bank VARCHAR (20), 
	acct VARCHAR(20), 
	PRIMARY KEY (guest_id, booking_id), 
	FOREIGN KEY (guest_id) REFERENCES guests (id),
	FOREIGN KEY (booking_id) REFERENCES booking (id)
);
                        

The following image provides a high level overview of the database organization:

1. Tables are identified as diamonds or rectangles.
2. A rectangle indicates a table. A rectangle with double lines (CHILD, and DEPENDENT) indicates a table with no key attributes of its own. Such a table would not be able to exist without a related table. In this case, the CHILD and DEPENDENT tables are strongly related to and dependent upon the GUEST and STAFF tables.
3. A diamond indicates a relationship. Relationships may be of the type one-to-one (1,1), one-to-many (1,M), or many-to-many (0 / N, M).
Ovals indicate attributes. An oval with text underlined indicates a key attribute.

The following image describes how tables are related to one-another:

An arrow originates in a table's foreign key, and terminates at the referenced primary key.

The following image clearly defines foreign-key to primary-key relationships:

CRUD Operations and Advanced SQL Queries

CRUD is an acronym describing four common operations on any structured data (not necessarily databases, but any database management system should support these operations).

• C - Create
• R - Read
• U - Update
• D - Delete

• Create - INSERT (Inserts a row into an existing table, creating a new record)
• Read - SELECT (Selects a record meeting specified requirements for other operations such as printing)
• Update - UPDATE (Replaces some or all attributes of an entry with new attribute values)
• Delete - DELETE (Removes one or more rows from a table according to specified conditions)

Next we will examine some CRUD operations performed using SQL on the project database, and get a better idea for how specific conditions can be provided with SQL commands in order to target these operations to specific data.

create table activities(
    name VARCHAR(30) PRIMARY KEY,
    price DECIMAL(6, 2),
    location VARCHAR(20)
 );
                    

insert into activities (name, price, location) values
    ('Whitewater Rafting', 700.00, 'Blackfoot River'),
    ('Trap Shooting', 450.00, 'East Range'), 
    ('Archery', 300.00, 'Archery Range'),
    ('Horseback Riding', 500.00, 'Ovando'),
    ('Horseback Riding Overnight', 1500.00, 'Bob Marshall'), 
    ('ATV Track', 400.00, 'ATV Track'), 
    ('ATV Backcountry Tour', 2000.00, 'Ovando'), 
    ('Cross Country Skiing', 200.00, 'Blast Park'), 
    ('Snow Tubing', 200.00, 'Blast Park'), 
    ('Canoeing', 300.00, 'Seely Lake'), 
    ('Dog Sledding', 1200, 'Ovando'), 
    ('Fitness Center', 30.00, 'Fitness Center'), 
    ('Fly Fishing', 400.00, 'Blackfoot River'), 
    ('Garnet Ghost Town Tour', 500.00, 'Garnet Ghost Town'), 
    ('Hot Air Balooning', 7000.00, 'Launch Pad One');
                    

ACTIVITIES:
+----------------------------+---------+-------------------+
| name                       | price   | location          |
+----------------------------+---------+-------------------+
| Archery                    |  300.00 | Archery Range     |
| ATV Backcountry Tour       | 2000.00 | Ovando            |
| ATV Track                  |  400.00 | ATV Track         |
| Canoeing                   |  300.00 | Seely Lake        |
| Cross Country Skiing       |  200.00 | Blast Park        |
| Dog Sledding               | 1200.00 | Ovando            |
| Fitness Center             |   30.00 | Fitness Center    |
| Fly Fishing                |  400.00 | Blackfoot River   |
| Garnet Ghost Town Tour     |  500.00 | Garnet Ghost Town |
| Horseback Riding           |  500.00 | Ovando            |
| Horseback Riding Overnight | 1500.00 | Bob Marshall      |
| Hot Air Balooning          | 7000.00 | Launch Pad One    |
| Snow Tubing                |  200.00 | Blast Park        |
| Trap Shooting              |  450.00 | East Range        |
| Whitewater Rafting         |  700.00 | Blackfoot River   |
+----------------------------+---------+-------------------+
                

UPDATE ACTIVITIES SET price = 500.0 WHERE name = "Archery";
                    

SELECT * FROM ACTIVITIES WHERE name = "Archery"; 
                    

ACTIVITIES:
+----------------------------+---------+-------------------+
| name                       | price   | location          |
+----------------------------+---------+-------------------+
| Archery                    |  500.00 | Archery Range     |
+----------------------------+---------+-------------------+
                

DELETE FROM ACTIVITIES WHERE name = "Cross Country Skiing";
                    

                    ACTIVITIES:
                    +----------------------------+---------+-------------------+
                    | name                       | price   | location          |
                    +----------------------------+---------+-------------------+
                    | Archery                    |  300.00 | Archery Range     |
                    | ATV Backcountry Tour       | 2000.00 | Ovando            |
                    | ATV Track                  |  400.00 | ATV Track         |
                    | Canoeing                   |  300.00 | Seely Lake        |
                    | Dog Sledding               | 1200.00 | Ovando            |
                    | Fitness Center             |   30.00 | Fitness Center    |
                    | Fly Fishing                |  400.00 | Blackfoot River   |
                    | Garnet Ghost Town Tour     |  500.00 | Garnet Ghost Town |
                    | Horseback Riding           |  500.00 | Ovando            |
                    | Horseback Riding Overnight | 1500.00 | Bob Marshall      |
                    | Hot Air Balooning          | 7000.00 | Launch Pad One    |
                    | Snow Tubing                |  200.00 | Blast Park        |
                    | Trap Shooting              |  450.00 | East Range        |
                    | Whitewater Rafting         |  700.00 | Blackfoot River   |
                    +----------------------------+---------+-------------------+
                 

$booking = $_POST["booking"];

$check = "SELECT * FROM booking WHERE id = '$$booking'";

$check_result = mysqli_query($con, $check);

$check_numrows = mysqli_num_rows($check_result);

if ($check_numrows == 0) {
    echo "The booking ID you entered does not exist";
}
else {

    $update_reservation = "UPDATE reservation
        SET reservation.status = 'cancelled'
        WHERE reservation.booking_id = '$$booking'";

    $update_activities = "UPDATE assign_activities SET status = 'cancelled' 
                        WHERE assign_activities.id IN (SELECT guest_id 
                            FROM reservation WHERE booking_id = '$$booking')";

    $update_staff = "UPDATE assign_staff SET feedback = 'Reservation cancelled by guest'
                        WHERE booking_id = '$$booking'";

    $delete = "DELETE FROM assign_lodging WHERE booking_id = '$booking'";

    if (mysqli_query($con, $update_reservation)) {
        echo "Reservation table updated
";
    } else {
        echo "ERROR updating table reservation ", mysqli_error($con);
    }

    if (mysqli_query($con, $update_activities)) {
        echo "Activities table updated
";
    } else {
        echo "ERROR updating table assign_activities ", mysqli_error($con);
    }

    if (mysqli_query($con, $update_staff)) {
        echo "Staff table updated
";
    } else {
        echo "ERROR updating table assign_staff ", mysqli_error($con);
    }

    if (mysqli_query($con, $delete)) {
        echo "Lodging assignments cancelled
";
    } else {
        echo "ERROR cancelling lodging assignments", mysqli_error($con);
    }
}
                    

$staff = $_POST["staff"];


$check = "SELECT fname, lname FROM staff WHERE id = '$staff'";
$check_result = mysqli_query($con, $check);
$numrows = mysqli_num_rows($check_result);
$staff_name_query = mysqli_fetch_array($check_result);
$staff_name = $staff_name_query['fname'];
$staff_last_name = $staff_name_query['lname'];

if ($numrows == 0) {
    echo "Staff ID not found";
}
else {

$query = "SELECT booking.arrive AS Guest_Arrival,
    booking.depart AS Guest_Depart, guests.fname AS Guest_First_Name, guests.lname AS Guest_Last_Name, assign_staff.feedback AS Feedback
    FROM staff, booking, guests, assign_staff, reservation
    WHERE staff.id = '$staff'
    AND assign_staff.staff_id = staff.id
    AND booking.id = assign_staff.booking_id
    AND reservation.booking_id = booking.id
    AND guests.id = reservation.guest_id";


    if (mysqli_query($con, $query)) {
        echo "
";
    } else {
        echo "ERROR Printing ", mysqli_error($con);
    }

    $result = mysqli_query($con, $query);
    $num_relationships = mysqli_num_rows($result);

    if ($num_relationships == 0) {
        echo "No relationships found";
    }

// show results of table with new data inserted
    echo "All Guests Served By $staff_name $staff_last_name:
";
    echo 'Note that feedback is provided by booking, not individual guests.';
    echo " \n";
    echo "\t\n";
    while ($fieldinfo = $result->fetch_field()) {
        echo "\t\t\n";
    }
    echo "\t\n";
    while ($row = mysqli_fetch_array($result, MYSQLI_ASSOC)) {
        echo "\t\n";
        foreach ($row as $col_value) {
            echo "\t\t\n";
        }
        echo "\t\n";
    }
    echo "
$fieldinfo->name
$col_value
\n";
}
echo "
";
echo "
";
mysqli_free_result($result);
    

$guest = $_POST["guest"];


$check = "SELECT * FROM guests WHERE id = '$guest'";
$check_result = mysqli_query($con, $check);
$numrows = mysqli_num_rows($check_result);

if ($numrows == 0) {
    echo "Guest ID not found";
}
else {

    $query = "SELECT distinct guests.fname AS Guest_First_Name, guests.lname AS Guest_Last_Name, booking.arrive, booking.depart, 
        booking.type AS Type_Of_Stay, booking.event AS Special_Event, 
        assign_lodging.site_name AS Site, assign_lodging.lodging_number AS Number
        FROM guests, booking, assign_lodging, reservation
        WHERE guests.id = '$guest'
        AND guests.id = assign_lodging.guest_id
        AND guests.id = reservation.guest_id
        AND booking.id = reservation.booking_id";

    if (mysqli_query($con, $query)) {
        echo "
";
    } else {
        echo "ERROR: ", mysqli_error($con);
    }

    $result = mysqli_query($con, $query);
    $num_lodging = mysqli_num_rows($result);

    if ($num_lodging == 0) {
        echo "Guest does not have lodging history yet";
    } else {

        echo "
";
        echo "
";

// show results of table with new data inserted
        echo "All Guest Lodging History 
";
        echo " \n";
        echo "\t\n";
        while ($fieldinfo = $result->fetch_field()) {
            echo "\t\t\n";
        }
        echo "\t\n";
        while ($row = mysqli_fetch_array($result, MYSQLI_ASSOC)) {
            echo "\t\n";
            foreach ($row as $col_value) {
                echo "\t\t\n";
            }
            echo "\t\n";
        }
        echo "
$fieldinfo->name
$col_value
\n";
    }
}
echo "
";
echo "
";
mysqli_free_result($result);
                        

$booking = $_POST["booking"];
$billable = $_POST["billable"];
$occurred = $_POST["occurred"];

$charge = "INSERT INTO charge (booking_id, billable_id, occurred) VALUES ('$booking', '$billable', '$occurred')";

if (mysqli_query($con,$charge)){
    echo "Charge Posted";
} else {
    echo "ERROR Updating Table " , mysqli_error($con);
}

$show_charges = "SELECT * FROM charge WHERE booking_id = '$booking'";
$result_charge = mysqli_query($con, $show_charges);

echo "
";
echo "
";

// show results of table with new data inserted
echo "All Booking Charges 
";
echo " \n";
echo "\t\n";
while ($fieldinfo = $result_charge -> fetch_field()){
    echo "\t\t\n";
}
echo "\t\n";while ($row = mysqli_fetch_array($result_charge, MYSQLI_ASSOC)) {
    echo "\t\n";
    foreach ($row as $col_value) {
        echo "\t\t\n";
    }
    echo "\t\n";
}
echo "
$fieldinfo->name
$col_value
\n";

echo "
";
echo "
";
mysqli_free_result($result_charge);
                        

$fname = $_POST["fname"];
$lname = $_POST["lname"];
$guest = $_POST["guest"];
$phone = $_POST["phone"];
$email = $_POST["email"];
$booking = $_POST["booking"];

$search_reservation = "SELECT * FROM reservation WHERE guest_id = '$guest' OR booking_id = '$booking'";

if (mysqli_query($con,$search_reservation)){
    echo "Searching Reservations Complete 
";
} else {
    echo "ERROR Searching " , mysqli_error($con);
}

$reservation_result = mysqli_query($con, $search_reservation);

$numrowsreservation = mysqli_num_rows($reservation_result);

$search_guest = "SELECT * FROM reservation WHERE guest_id IN (SELECT id FROM guests 
    WHERE fname = '$fname' OR lname = '$lname' OR phone = '$phone' OR email = '$email')";

if (mysqli_query($con,$search_guest)){
    echo "Searching Guests Complete 
";
} else {
    echo "ERROR Searching " , mysqli_error($con);
}

$guest_result = mysqli_query($con, $search_guest);

$numrowsguest = mysqli_num_rows($guest_result);


echo "
";
echo "
";

if ($numrowsguest == 0 and $numrowsreservation == 0) {
    echo "No guests or reservations found with information provided 
 
 ";
}
else {
// show results of query
}
                        

$fname = $_POST["fname"];
$lname = $_POST["lname"];
$guest = $_POST["guest"];
$phone = $_POST["phone"];
$email = $_POST["email"];
$booking = $_POST["booking"];

$sanitized_fname =
    mysqli_real_escape_string($con, $fname);
$sanitized_lname =
    mysqli_real_escape_string($con, $lname);
$sanitized_guest =
    mysqli_real_escape_string($con, $guest);
$sanitized_phone =
    mysqli_real_escape_string($con, $phone);
$sanitized_email =
    mysqli_real_escape_string($con, $email);
$sanitized_booking =
    mysqli_real_escape_string($con, $booking);

$search_reservation = "SELECT * FROM reservation WHERE guest_id = '$sanitized_guest' OR booking_id = '$sanitized_booking'";

if (mysqli_query($con,$search_reservation)){
    echo "Searching Reservations Complete 
";
} else {
    echo "ERROR Searching " , mysqli_error($con);
}

$reservation_result = mysqli_query($con, $search_reservation);

$numrowsreservation = mysqli_num_rows($reservation_result);

$search_guest = "SELECT * FROM reservation WHERE guest_id IN (SELECT id FROM guests 
    WHERE fname = '$sanitized_fname' OR lname = '$sanitized_lname' OR phone = '$sanitized_phone' OR email = '$sanitized_email')";
                    

That's it for the original project, completed for a course in Database Design. Be sure to visit the links below in order to visit the database portal and explore the project further. You are free to enter information into the database, but default data has already been entered. You can return to the default state of the database by using the Reset Database link on the Main Menu. Next, security enhancements discussed in a course on Database Security will be examined.

Security Enhancements Specific to OracleDB

OracleDB is a DBMS that uses the MySQL (also owned by Oracle) engine but also includes some notable features attractive to large organizations intended to enhance security by implementing access control and by supporting hierarchical views according to a user's level of clearance. This is accomplished in several ways which will be discussed below:

• Creation of roles and granting specific privileges to specific roles.
• These privileges include certain operations (SELECT, INSERT, DELETE, UPDATE) on specific tables.
• Views can be defined that specify how data can be read, for example: create view charges as select booking_id, billable_id, occurred from charge;
• The charges view will display the three columns of the CHARGE table along with any data contained therein.
• Data suppression allows for sensitive data to be obfuscated in the event that unauthorized persons access the data.
• As discussed later, this is useful to conceal sensitive values such as credit card numbers.

Why OracleDB? The course I took in Database Security was taught by an adjunct employed full time by the United States Department of Defense. I can only assume he has the greatest professional experience using OracleDB and prefers to base his course off of this DBMS. Alternatives exist, but they will not be discussed on this page.

The project outlined earlier on this page is a fully-implemented database intended to be used by a resort. Specifically, I designed this database in preparation for an internship with The Resort at Paws Up, located near Missoula, MT. While the course in database design imposed no security requirements whatsoever, it is worth noting that such a database would require extensive security measures considering that so many of this particular resort’s guests are well-known celebrities, musicians, actors, politicians, and other persons of interest to the general public. It is ostensible that such a database would be targeted not only by external attackers, but would also be exposed to a significant internal threat (rogue employees). For this reason, redaction, roles, views, and grants will play a central role in controlling which users can access what information.

Key users include employees with varied roles and, therefore, varying levels of access to data contained within the database. For instance, some office staff will be responsible for creating guest reservations and entering guest information into the database. Other staff will be responsible for reaching out to former guests to inquire about how satisfied they were with their experience, and also inform them of special events or promotions. Additional staff will be responsible for coordinating guest lodging and activity reservations. Certain staff will oversee the billing of guests and calculate all charges incurred against a booking. Additional staff will be responsible for supervising employees, assigning them to specific bookings, completing payroll, mailing out tax documents, etc.

The database can be divided into two main departments: one concerned with guests, and another concerned with employees. The department concerned with guests will contain tables to record information about guests (name, DOB, contact information, address, etc). Most of this information must be protected for obvious reasons (home address, phone number, birthday, etc.) Additionally, tables will be implemented to track guest booking information, reservation details, payment information, etc. and this information is likewise sensitive. Other guest details will not be considered sensitive, such as which lodging guests have been assigned to, which items guests have purchased, and what activities they have registered for.

The department concerned with staff will have privileges to view and modify (as appropriate) tables concerned with employees. Certain employee information regarding employee dependents will be protected. Staff assignments and general staff information will not be considered sensitive, as this could be needed by employees within the staff department for various reasons and does not present a security risk.

In OracleDB, one or more DBMS users will need to define roles, views, grants, and redaction. In the code samples below, this user is named CIO, and has full privileges across the DBMS. Additionally, four levels of clearance will be defined in order to stratify DBMS access across a hierarchy of privilege:

• Unclassified designation: All guests fit in this category, as do some employees with no need to access DBMS data or other resort files (spreadsheets, word documents, etc.).
• Classified designation: Most employees match this designation. Some DBMS data is considered classified as it should not be shared without necessity, but is not immediately sensitive. Such DBMS information includes the arrival and departure dates of specific guests, which activities certain guests have registered for, which items they have purchased, lodging assignments, and more. It is logical that a wide variety of employees may need to access this information, and it is not of high value for attackers.
• Secret Designation: Fewer employees have this level of security clearance, and only some DBMS information is considered secret. DBMS data with secret designation includes guest payment information, booking information, reservation history, survey feedback, and employee dependent information. As such, employees responsible for processing payments. making reservations, and human resources will have appropriate secret clearance.
• Top-Secret Designation: Very few employees have this level of security clearance. DBMS data considered top secret includes guest addresses, phone numbers, and DOBs. Only a few supervisory employees are able to access this information, and they must log a valid reason when doing so.

The code snippets below illustrate how to accomplish this within OracleDB:

SQL> create user CIO identified by c;

User created.

SQL> grant create session to CIO;

Grant succeeded.

SQL> grant alter session to CIO;

Grant succeeded.

SQL> connect CIO/c;
Connected.
                

SQL> create role guest_classified; 

SQL> Role created. 

SQL> create role guest_secret; 

SQL> Role created. 

SQL> create role guest_top_secret; 

SQL> Role created. 

SQL> create role employee_unclassified; 

SQL> Role created. 

SQL> create role employee_classified; 

SQL> Role created. 

SQL> create role employee_secret; 

SQL> Role created. 

SQL> create role employee_top_secret; 

SQL> Role created. 
    

SQL> grant select, update on activities, assign_activities, lodging, assign_lodging, billables, charge to guest_classified; 

SQL> Grant succeeded.

SQL> grant select, update on activities, assign_activities, lodging, assign_lodging, billables, charge, booking to guest_secret; 

SQL> Grant succeeded.

SQL> grant select, update on activities, assign_activities, lodging, assign_lodging, billables, charge, booking, guest, reservation, child to guest_top_secret;

SQL> Grant succeeded.

SQL> grant select on activities, lodging, role to employee_unclassified;

SQL> Grant succeeded. 

SQL> grant select, update on activities, lodging, assign_activities, assign_lodging, billables, staff, role, assign_Staff, assign_role to employee_classified; 

SQL> Grant succeeded. 

SQL> grant select on charge to employee_classified, employee_secret, employee_top_secret;

SQL> Grant succeeded. 

SQL> grant select, update on activities, lodging, assign_activities, assign_lodging, billables, staff, role, assign_staff, assign_role, dependent to employee_secret; 

SQL> Grant succeeded. 

SQL> grant select, update on activities, lodging, assign_activities, assign_lodging, billables, staff, role, assign_staff, assign_role, dependent, guest, child, booking, reservation, charge, to employee_top_secret;  
    

SQL> create view guest_contact_info as select fname, lname, phone, email from guest;

SQL> View created. 

SQL> create view guest_itinerary as select arrive, depart, special, type, event from booking;

SQL> View created. 

SQL> create view guest_schedule as select id, name, scheduled, status from assign_activities;

SQL> View created. 

SQL>  create view guest_lodging as select guest_id, site_name, lodging_number from assign_lodging; 

SQL> View created. 

SQL> create view charges as select booking_id, billable_id, occurred from charge;

SQL> View created.

SQL> create view staff_roster as select fname, lname, dept, phone, email from staff;

SQL> View created.

SQL> create view staff_assignments as select role_name, staff_id, start, end from assign_role; 

SQL> View created.

SQL> create view staff_guests as select staff_id, booking_id, feedback from assign_staff;

SQL> View created.
    

SQL> grant select, update on guest_contact_info to guest_top_secret;

SQL> Grant succeeded. 

SQL> grant select on guest_itinerary to guest_secret;

SQL> Grant succeeded

SQL> grant select, update on guest_schedule to guest_classified;

SQL> Grant succeeded. 

SQL>  grant select, update on guest_lodging to guest_classified; 

SQL> Grant succeeded.

SQL> grant select, update on charges to guest_classified 

SQL> Grant succeeded.

SQL> grant select on staff_roster to employee_unclassified, employee_classified, employee_secret, employee_top_Secret; 

SQL> Grant succeeded.

SQL> grant select, update on staff_assignments to employee_classified, employee_secret, employee_top_secret;

SQL> Grant succeeded.

SQL> grant select, update on staff_guests to employee_classified, employee_secret, employee_top_secret;

SQL> Grant succeeded
    

At this point, individual users (employees) can be created as appropriate and assigned the role that provides them with the least privileged access essential for performing their assigned tasks.

Next, data redaction methods specific to OracleDB will be explored:

SQL> begin
2  dbms_redact.add_policy (
3  object_schema => 'CIO',
4  object_name => 'RESERVATION',
5  policy_name => 'PAYMENT',
6  column_name => 'ACCT',
7  function_type => DBMS_REDACT.REDACT_CCN16_F12,
8  expression => '1=1');
9 end;
10  /
    

SQL> begin
2  dbms_redact.add_policy(
3  object_schema => 'CIO',
4  object_name => 'GUEST',
5  column_name => 'STREET',
6  policy_name => 'STREET',
7  function_type => DBMS_REDACT.RANDOM,
8  expression => '1=1');
9  end;
10  /
    

SQL> begin
2  dbms_redact.add_policy(
3  object_schema => 'CIO',
4  object_name => 'GUEST',
5  column_name => 'DOB',
6  policy_name => 'GUEST_DOB',
7  function_type => DBMS_REDACT.REDACT_DATE_MILLENNIUM,
8  expression => '1=1');
9  end;
10  /
    

SQL> begin
2  dbms_redact.add_policy(
3  object_schema => 'CIO',
4  object_name => 'GUEST',
5  column_name => 'PHONE',
6  policy_name => 'PHONE',
7  function_type => DBMS_REDACT.RANDOM,
8  expression => '1=1');
9  end;
10  /
    

SQL> begin
2  dbms_redact.add_policy(
3  object_schema => 'CIO',
4  object_name => 'CHILD',
5  column_name => 'DOB',
6  policy_name => 'CHILD_DOB’,
7  function_type => DBMS_REDACT.REDACT_DATE_MILLENNIUM,
8  expression => '1=1');
9 end;
10  /
    

SQL> begin
2  dbms_redact.add_policy(
3  object_schema => 'CIO',
4  object_name => 'DEPENDENT',
5  column_name => 'DOB',
6  policy_name => 'DEP_DOB’,
7  function_type => DBMS_REDACT.REDACT_DATE_MILLENNIUM,
8  expression => '1=1');
9 end;
10  /
    

Additionally, this course in Database Security also required that a security plan and policy be implemented by the organization intended to use the DBMS:

Security Plan: As a resort catering to the rich and famous, we are aware that we have a huge crosshair placed upon the data we necessarily maintain regarding our guests and employees. As such, we will need to ensure that our internal network is segmented from the internet, and that services which must access the internet are located within a DMZ. Our IT department will routinely evaluate our network by conducting internal and external penetration tests in order to discover potential vulnerabilities so that they may be mitigated. All software and operating systems will be kept patched and up-to-date.

Security Policies:

1. Strong Password Policy: All users will be required to use a password consisting of twelve or more characters, including at least one number, capitalized character, and symbol. Passwords will expire every sixty days and may not be re-used.
2. Lock-Out Policy: User accounts accruing more than three failed login attempts within a thirty minute period will be locked and require IT staff to unlock. User accounts accruing more than ten failed login attempts within 520 hours (five days) will likewise be locked for review by IT. The resort will maintain a blacklist containing IP addresses known to perform malicious activity and known VPN addresses, and these addresses will not be able to connect to public-facing services within the DMZ.
3. Least-Privilege Policy: Users will only be allowed access to information considered absolutely necessary for performing their specific assignment.
4. Acceptable Use Policy: Users will only utilize resort services (web-browsers, DBMS, spreadsheets) for essential and approved work-related activities.
5. Security Monitoring and Logging Policy: The IT department will maintain and regularly audit logs in order to ensure that all systems and applications are working as expected and in order to detect any potential unauthorized activity.
6. Security Training and Awareness Policy: Employees will undergo security awareness training upon hiring and complete quarterly refresher trainings designed to enforce our security plan, policies, and procedures.
7. Vendor Access Policy: Vendors are not to be granted access to resort file systems (servers) without express consent of CIO.
8. Non-Disclosure of Guest Information Policy: Employees are not permitted, under any circumstances, to disclose guest information to persons not employed by the resort, or employees with a lower security clearance.
9. Data Backup Policy: Resort file systems will be backed up on a weekly basis, and these backups stored securely. Only IT personnel with Top-Secret security clearance are permitted access to these backups.
10. Security Clearance Policy: Employees will be assigned a security clearance based upon their assigned role at the resort. Ranked from lowest to highest in terms of privilege, employees may be assigned as: unclassified, classified, secret, and top-secret.

Security Procedures:

1. Data Disposal Procedure: Should a device capable of storing resort data (server, hard drive, RAM, USB drive) become inoperable, it will be immediately placed in the custody of the IT department. IT personnel will evaluate the devices capability to be repaired, and if this is not possible, it will be physically destroyed. Physical destruction will occur via mechanical destruction (hammer, drill, saw) such that no portion of the device will be able to convey data previously stored therein.
2. Supervised Vendor Access Procedure: When absolutely necessary, external vendors may require access to areas housing sensitive resort information. Such vendors will be continuously monitored and their activities well-documented. Vendors are not to be left unsupervised for any period of time while in sensitive areas for any reason.
3. Data Back-Up Procedure: On a weekly basis, information stored in the resort’s DBMS will be backed up and stored on physical media (Western Digital Drives or equivalent). These drives will be placed within a secure access room within the IT department.
4. Secure Access Policy: Employees will be assigned a security designation (unclassified, classified, secret, and top-secret) describing what data, applications, systems, and facilities that they have access to. Accessing data, applications, and systems will require electronic authentication via username and password, and wherever possible, this authentication will also include two-factor authentication (a security chip embedded within the employee’s ID card). Accessing sensitive areas will require an employee to swipe their card (these swipes will be monitored and recorded) and additionally enter a unique pin on a numeric pin pad. Access to secret and top-secret facilities will be reviewed on a daily basis by IT personnel.
5. Security Incident Reporting Procedure: A security incident may be defined as any incident which may involve the potential for sensitive information to be exposed. Examples may include a vendor removing a piece of network equipment from the resort, an employee with confidential security designation viewing top-secret data regarding guests, or a guest found tampering with resort electronics or within a sensitive area. Any activity determined unessential and suspicious will be immediately reported to both the IT and Security department for immediate review.

Conclusion

An organizational security policy is inherently complex and requires the ability to adapt over time. The security policy for this resort is no exception. Ten different security engineers would likely have ten different implementations for this project. I have based my implementation upon Oracle documentation and the material which has been covered throughout the semester. While conducting additional research for this project, I found that such practices adhered to or closely resembled best practices suggested by industry experts such as the SANS Institute (see references below).

References

https://www.sciencedirect.com/topics/computer-science/principle-of-least-privilege

https://www.sans.org/information-security-policy

https://www.usf.edu/it/about-us/issp0000securityplan.pdf

https://www.bowiestate.edu/files/resources/information-security-public.pdf

https://www.sans.org/white-papers/34222/

https://sansorg.egnyte.com/dl/pnxLOSeZ51

https://sansorg.egnyte.com/dl/u4yJZM1ASX