Implementing Database, Users, Privileges, SQL Injection Testing, Backup Strategies In An Essay.

You will need to implement the database, create users and grant privileges to the users,perform SQL injection testing, and develop backup strategies for the database. You may work in a group of up to 3 members, and ONE member of each group needs to submit.
1. A pdf report detailing your work including (but not limited to):
1) Explanations of your database design choices, and screenshots of your database structure and data
2) Descriptions of user privileges (using access matrix) with explanations, and screenshots of the SQL commands you used
3) Descriptions of your SQL injection test (in steps) with screenshots and explanations of your observations
4) Descriptions of your backup strategies with clear justifications.

To eliminate data redundancy and capture all the constraints, a further normalization of the tables was done, which introduced additional tables. The main improvements include;

• The Student table was left as it is, because it was already in 3rd Normal Form
• Academic_Staff and Admin_Staff tables were merged to create a single Staff table which holds all the staff data.
• The position and duty of members of staff is captured in the new Role table, which holds data about the designation of members of staff
• A new table ; Staff_Category was introduced which holds data that shows whether a member of staff is an Academic staff or admin staff
• A number of fields were removed from the table to eliminate redundancy; only two attributes were left in the table; course_id and course_name
• Redundancy in the Enrolment table was eliminated by eliminating the student_name attribute and creating a relationship with the student table, by making the attribute Student_id to be the foreign key in this table.
• To ensure that each grade record is for a given enrolment in the enrolment table, the enrolment_id attribute in table grade was made to be the primary key for the table, as well as the foreign key, linking grade to enrolment. This ensures that a given grade is related to an existing record in the enrolment table and that for a given enrolment, only one grade can be recorded.
• The user table was modified to introduce a mutually exclusive relationship, where a given user can either be a student or a staff but not both.

1. Create Users

Create Students users

• CREATE USER 's01'@'localhost' IDENTIFIED BY 's01';
• CREATE USER 's02'@'localhost' IDENTIFIED BY 's02';

Create Admin staff users

• CREATE USER 'f01'@'localhost' IDENTIFIED BY 'f01';
• CREATE USER 'f02'@'localhost' IDENTIFIED BY 'f02';

Create Academic staff users

• CREATE USER 'a01'@'localhost' IDENTIFIED BY 'a01';
• CREATE USER 'a02'@'localhost' IDENTIFIED BY 'a02';

2. Assign Table-level Privileges

Grant Students right to select courses

• GRANT SELECT ON gradingsystemdb.course TO ‘S01’@'localhost’;
• GRANT SELECT ON gradingsystemdb. course TO ‘S02’@'localhost’;

Grant Select, Insert, Update on Enrolments to admin staff in charge of enrolments

• GRANT Select,Insert,Update ON enrolment TO ‘f01’@'localhost’;

Grant Select, Insert, Update on Enrolments to admin staff in charge of enrolments

• GRANT Select,Insert,Update ON course TO ‘f02’@'localhost’;

3. Create Views and Related Privileges

View to show grades for a given student; for this case we create a view for student with student id "s01"

CREATE VIEW student_grade AS

SELECT student.first_name

    , student.last_name

    , course.course_name

    , grade.score

    , grade.grade

FROM

    gradingsystemdb.enrolment

    INNER JOIN gradingsystemdb.course

        ON (enrolment.course_id = course.course_id)

    INNER JOIN gradingsystemdb.student

        ON (enrolment.student_id = student.student_id)

    INNER JOIN gradingsystemdb.grade

        ON (grade.enrolment_id = enrolment.enrolment_id)

    INNER JOIN gradingsystemdb.student_user

        ON (student_user.student_id = student.student_id)

WHERE (student.student_id = 's01') ;

Assign the View to the specific user;

• GRANT SELECT ON student_grade TO s01;

Test if the user can use the view by selecting the view

A view for an Academic staff ; can only see the enrolment of the courses they teachThe view shows courses taught by academic staff ; a01 : Professor  Seb Binary

CREATE VIEW enrolments AS

SELECT DISTINCT

    course.course_name, student.first_name, student.last_name, enrolment.year

    , enrolment.trimester, enrolment.campus, enrolment.enrolment_id

FROM

    gradingsystemdb.taught_by

    INNER JOIN gradingsystemdb.staff

        ON (taught_by.staff_id = staff.staff_id)

    INNER JOIN gradingsystemdb.course

        ON (taught_by.course_id = course.course_id)

    INNER JOIN gradingsystemdb.enrolment

        ON (enrolment.course_id = course.course_id)

    INNER JOIN gradingsystemdb.student

        ON (enrolment.student_id = student.student_id)

WHERE (staff.staff_id ='a01');

Grant Select rights on enrolments to a01 

• GRANT SELECT ON enrolments TO a01

Test if the professor can view enrolments for courses he teaches

SQL Injection Test

The first task was to check if the web interface was vulnerable to SQL injection. To test this, a comma was placed in the "Sex" field; as shown The interface returned the following error message; indicating that the system was vulnerable With these information, an SQL injection can be executed by closing inserts into the database at the "Sex" field 5,, and then using the phone field 6 to send a payload that will cause an SQL injection.For this excersice an SQL injection will be launched that will insert a record into the Users table.

The SQL injection was launched using the two last fields; Sex and Phone

Sex:       M','9666');/*        

• This data ensures that the insert statement into the student database is completed and terminated using the semi colon and bracket.
• The /*is meant to create a block comment which takes care of the comma between Sex and Phone.

Phone: */ INSERT INTO user (user_id) VALUES ('8555'); --

• the data in the phone field starts with a */ which closes the block comment that takes care of apostrophise and the comma between Sex and Phone.
• the insert statement inserts only the user id in the users table
• the statement is terminated using a semi colon
• the hyphens at the end of the insert statement create a single line comment; meaning that anything after the insert statement is considered as a comment ; the use of the comment is to take care of the last single quote and closing bracket.

The full SQL code that is executed on the server is as follows;

INSERT INTO student (student_id, first_name, last_name, DOB, sex, phone) VALUES ('s8555', 'Mag', 'Tudor', '5/8/1995', 'M','9666'); /*', '*/ INSERT INTO `user` (`user_id`) VALUES ('8555'); -- '

A check inside the users table confirmed that the SQL injection was successful as the record was added in that table.

The approach can be used to launch any type of SQL injection.

Backup strategy

A backup strategy is vital to safeguard with the intention of protecting your data against calamities and system failure. A duplicate of the data is restored hence data will be recovered.The student data, the courses and the enrollment should be backed up more often as these tables are updating more often. The database will backup based on the three backup strategies depending on the disk space in the backup server :

User Privileges and Access Matrix

Full back up this strategy allows backing up of the wholedatabasewhich we may chose to run periodically or at the end of the day.

Incremental backup this strategy will result in backing up only data that has changed since the last backup. This has an advantage of size and speed as the data backed up is minimal and takes lesser time.

Differential backups this strategy will only backup data from a set point in time and hence will backup data that changes against a set point it will  require more space and time than an incremental strategy but less time than a full backup

Advanceddata management

MySQL

This database is free to use and open source which means there are no proprietarylicenses required for its use. MySQL is one of the most secure and reliable database management systems used by most open source web applications like Drupal, Facebook, andTwitter. It secure enough to protect client data and even perform safe transactions. MySQLscalability is unmatched to allow small processes even in big data warehouses. It allows to customize the database to unique specifications. It allows large transactions at any speed with no lag. MySQL does not experience downtime since it has solutions like cluster servers and master/slave configurations.

MySQL offers a complete transactional support with features like unrestricted row-level locking, consistent and complete atomic, isolated, durable support even for multi-version transactions. Itssimple configuration allows timely installation and implementation. MySQL is free hencereduces the cost of ownership when deploying web applications.The open sourcelicense allows the enterprise to fully customize the management system(Singh, 2011).

However,MySQL does not have an inbuilt ERP embedded into its management system and thisis by regulations like Sarbanes-Oxley as a crucial entity of database management systems. MySQL does not support OLAT, ETL and Crystal Reports, these services make database management easier especially when handling big data. MySQL does not have the advantage when handling geospatial datasets over SQL server which has native data types that deal with such data. MySQL does not support checkconstraints. MySQL does not support active directory services like Kerberos and Windows authentication. Proprietary database management systems have native algorithms in their functionalities that allow faster processing of large tables.

cloud based databases host their data on remote servers that means the servers are not on site but are based in secure locations. Theseservers connect to the systems via the internet. Most servers are third party arrangementsmeaning that the enterprise may not necessarily own.The enterprise does not have to worry about scaling by adding or subtracting nodes from a cluster to add space as the hired firm will handle those technicalities. Cloud based database reduce administrative burden as the inhouse database manager may focus on more issues other than specializing which can cause team bottlenecks. Cloud based databasesare stored in hidden locations.

However, this technology does not allow full customization as these servers host multiple databases hence they have devised a common architecture to cater for all their clients. Due to the nature of transmission which is the internet, they sometime experience downtime. They also experience large scale outages(Singh, 2011).

Graph databases are designed to treat relationships between data as equally important to the data itself. Graph databases use graph structure for queries with nodes, edges,and properties to display and store data.graph databases offer flexibility as data captured may easily be changed and extended for additional attributes and objects. They also offer fast relationship-based searches. Graphdatabases offer naturally indexed relationships, this provides better access compered to relational database management systems. Graph databases are not efficient to perform large transactions and handling queries that span the entire databases(Singh, 2011).

References

Peter Jacso, P. J. (1999). Build Your Own Database. Chicago: American Library Association.

Powell, G. (2006). Beginning Database Design. New Jersey: John Wiley & Sons.

Singh, S. K. (2011). Database Systems: Concepts, Design and Applications. India: Pearson Education.