Database Security Measures: Crazy Cat Public Houses Coursework

This assignment examines the security measures implemented for a database system designed for Crazy Cat Public Houses, a chain of venues. The document explores various aspects of database security, including encryption to protect sensitive data, access control mechanisms to restrict unauthorized access, and user authentication protocols to verify user identities. It discusses the importance of backup and recovery procedures to ensure data availability and integrity in case of system failures. Furthermore, the assignment identifies potential threats to the database, such as excessive privileges, SQL injections, malware, and denial-of-service attacks, and provides detailed countermeasures to mitigate these risks. The document also addresses security issues related to confidentiality, integrity, availability, and non-repudiation. It describes the database architecture using a 3-Tier Client Server model, detailing the roles of the client, database application server, and database server. The assignment also touches upon performance requirements, including the importance of instant and accurate transactions, and optimization techniques such as creating primary keys, using appropriate indexes, and employing temporary tables and triggers to enhance database performance.

DATABASE PRINCIPLES.
By
Name ----------------------------------------------------------------------------------------
Lecturer
Name -------------------------------------------------------
March, 2019.

Security measures.
Encryption.
According to Jelling wood (2015) explained that encryption is a method of changing data in a
format it cannot be read or understandable by anyone apart from those who have keys for that
data to unhide it.
The main administrator (manager) and should be able to login using a user name and
password. He should also be able to perform the following tasks, Create and manage accounts
for the sales venues. There can only be 60 venue at a time each operating only at a locations. The
details of the sales persons include (first name, last name, user name, phone number, password,
address, employee shift, time of shift).
Access control.
Access control allows only principles who have authority to access database. Reading and
writing actions are prohibited from unauthorized principles. Access controls ensure information
flow in database conforms to organization principles. Access control help in minimizing
situation which may rise to harm a database and affects security of database. Example when a
principle which is not authorized to access a database deletes or Modifies tables vital information
can be lost.
Access Control measures includes:
Permissions of file.
Creating, reading, editing or deleting files in database. Sales person should be able to delete,
stock control and keeps record of foods and drinks on the accounts.
Permissions of software.
Rights of executing a software database application.
Rights of Data. Give rights of retrieving and updating data in a database to specific principles.
Crazy Cat database system will used for capturing sales of all items.
Identification.
User Identification Knowing principle who uses database is very important and it a good
practice of security need.it is good to know database users before giving them permissions and
rights of access this makes auditing ease. Identification involves identity of principal, user of
system, host of system and service of system. The user of this database system are Crazy cat,
employee of crazy cat and Customer. All details of customer, employees and crazy cat are being
captured in the database system for easy identification. Identification helps to know customers
who frequent the venue, comparing sales of different venues and planning for employees.
User Authentication.
Before your start using database it is advised to authenticate database.
Authentication of database involves what user know they include locks, pattern, password and
pin numbers, what users possess cards and what user is includes biometrics. .Employee to access
the user interface of database system they should be registered and their details captured for
example username and password

Accountability and auditing.
. To monitor and record configuration of way database works for database users it is called
Auditing. Users and non-database users. Integrity is ensured through accounting .All actions on
database most be accounted for. Payment is only through smart card and cash to access services.
Capturing sales is made through electronic tills.
Backup and recovery.
Backup it is reserving details of database and file logs to storage devices .Backup is a security
measure which data is safe. Recovery is the process of bring back database into normal use.
The Crazy Cat system should be able to auto-logout after 2 minutes of inactivity.
Threats and Countermeasures.
Excessive privileges.
Excessive privileges are harmful and causes risks which are not wanted. Most attack on
company information are usually done by company workers because they have full permissions
and access control on the system.
Countermeasures.
Access Control policy
Restrict privileges of employees by creating levels. Do not grant unnecessary privileges to
the use
SQL Injections.
According to Almutairi and Alruwaili (2016) explained that Malicious code are inserted in
database
Countermeasures.
Installing firewalls.
Malware.
According to According to Goud (2017) explained that malware is a software that propagates
itself and affects a system.
Countermeasures of Malware Enable firewall protection and Install Antivirus.
According to Schneier (2016) explained that firewall is a software that restrict access of
service from external user on a network. You can restrict or control access of database according
groups for example public service, private and internal services.
Denial of Service Denial of Service.
This means a user cannot access some functionality of database.
Countermeasures of Denial of Service.
Intrusion Detection System it detects and responses to the cause of denial of service.
Weak authentication.
It means some can use creditatials of legal databases users and access it.
Counter measures.
Use of two-way factor authentication is allowed and use of strong passwords. These SSH
Keys are cryptograph and helps in authentication of SSH Database as another option of using
password and username for login. It uses private and public keys .Private key is safe and no one
knows it while public keys it is known to everyone and available anytime for anyone who want
to access database.

Security Issues.
Confidentiality.
Information is able to be used only to principals who are permitted to use it.Example price of
food and beverages should be confidential.
Integrity. This means information cannot be corrupted by anyone it is keeping data into its
original form. Example changing price of item.
Availability
Data is able to be used anytime. - Its mean data is available when needed.
Non –repudiation.
No disloyal and expressing dissatisfaction but in actual sense it is not.
Crazy Cat database system fits 3-Tier Client Server Architecture.
Client.
According to Connolly and Begg(2015) explained that client is the front end of the database
which captures data from a user. It is where employees inputs and view data. For example
employee of crazy cat enters data of sales made by customer on sales details page.
Database application server.
According to Connolly and Begg(2015) stated that Database application server is a
middleware because it runs between client and Database server. It is where data processing and
data logic take place.
Database server.
Data manipulation takes place here deleting, viewing and inserting. This is where actual
storage of sale’s take place. Example number of items sold is stored .Item can be edited,
retrieved and viewed.
Ansi Sparc 3-tier Conceptional Model and 3-Tier Client Server Architecture Relationship.
According to Richa (2015) explained that both Conceptional Model and Client Server
architecture uses 3-Tier architectures.
According to Roman S (2017) conceptual model has three levels which are external,
conceptual and internal and according to Korth and Sudarshan(2016) has also three levels which
are client, application server and database server.
According to Connolly and Begg (2015) explained that both external level and client captures
data inputs from users.
Both conceptional level and application server is about logical manipulation.
Both internal level and database server it is where actual database storage take place.

The figures below show user interface of Crazy cat database system.

The figures below show backend of Crazy cat’s database system.
Pages usability.
According to Preece , Rogers and Sharp (2016) explained in details different usability goals of
easy to use ,easy to learn ,efficiency ,safety , remembering and effectiveness that someone has to
put in consideration while developing a system without fail,
The pages above are easy to learn, understand, easy to use and they are user friendly.
They are clear and straight on point, well arranged and easy to interpret because they are well
represented.
Safeness of system is put into consideration because of using passwords and username. All users
are identified on system.
The icons representing task are clearly visible, provide feedback, clearly mapped to the real
world, and affordable on clicking.
Page of sales details is well arranged for easy visibility.
System user interface uses web portal metaphor this makes user familiar and understanding
different component of the system better.
Using colors, orders, spaces, underlines and animation tactics on user interface to capture
attentions of customers.

Sensitive transactions.
Keeping records of all items.
Keeping record showing employee shifts.
Customer paying cash.
Performance Requirement.
All transactions should be instant and speed.
All transactions should be accurate.
Ensuring Performance.
After creating this database we had to optimize the search in it, this is necessary
because the database is used by consumers who want to know some information about
this
product.
To optimize the search in this database we need to create primary key in every table
of this database. When we create a primary key in a table, a clustered index tree is created and
all data pages containing the table rows are physically sorted in the file system according to
their primary key values. Each data page contains rows which are also sorted within the data
page according to their primary key values. Each time we ask any row from the table,
the
database server finds the corresponding data page first using the clustered index tree and then
finds the desired row within the data page that contains the primary key value (Fig. 2)
After creating this database we had to optimize the search in it, this is necessary
because the database is used by consumers who want to know some information about
this
product.
To optimize the search in this database we need to create primary key in every table
of this database. When we create a primary key in a table, a clustered index tree is created and
all data pages containing the table rows are physically sorted in the file system according to
their primary key values. Each data page contains rows which are also sorted within the data
page according to their primary key values. Each time we ask any row from the table,
the
database server finds the corresponding data page first using the clustered index tree and then
finds the desired row within the data page that contains the primary key value (Fig. 2).
Use appropriate index.
According to Charvet and Pande (2015) explained that Indexing helps to increase performance
of searching queries. Queries record/ raw in a table being examined.
According to Lee (2017) explained that Minimizing movement of data from client to server
helps to increase performance of database.

According to Shay (2018) explained that Reducing excess data. Using SQL code alternative of
program.
Use tables with temporary data and triggers.
To create temporary table is to INTO statement within a SELECT query.
USE Crazytb;
SELECT name, age, gender
INTO #Male employee
FROM customer
WHERE gender = 'Male'
).
CREATE OR REPLACE TRIGGER Good employee
AFTER INSERT ON employee
FOR EACH ROW
WHEN (NEW. Item=4.0)
BEGIN
Insert into good employee table
(NEW.sid, NEW.cno)
Count=Count+1 END.
Use optimization tools. Basic SQL Tuning:
It optimizes sql.it improves after rewrites sql and same output of the some result.
Reference.
Jellingwood, J. (2015) 7 Security Measures to Protect Your Servers.
Retrieved 24/03/06/2019 from https://digitalocean.com/community/ user/jellwood . .
Charvet, F and Pande, A. (2015).Database Performance Study.
Retrieved 24/0/2015 from https://eversql.com/database performance study.
Lee, K. (2017) Database Optimization.
Retrieve 24/03/2019 from https://makererelearning.com/ Database Optimization.
Shay, T (2018) .5 Easy Ways to Improve Your Database Performance.
Retrieved 23/03/2019.from https://www.everssql.com
Almutairi, A, H and Alruwaili, A, H. (2016) Security in Database Systems.
Global Journal of Computer Science and Technology. Network, Web & Security
Connolly, M. & Begg, C. (2015). Database systems A practical approach to
design, implementation and management. (6th Ed.). Harlow, Essex, England:
Addison-Wesley (Pearson Education Limited).
Roman S. (2017).Access Database Design and Programming. Retrieved 23/03/2019
from https://www.oreilly.com/library/view/access-databasedesign/1565926269/ch07s08.html

Korth, S and Sudarshan. (2016).Database System Concepts. (6th Edition.).
Pearson Education Limited.
Schneier, B. (2016). Security Engineering. (5th Ed) Pearson.
Goud, N. (2017). Cyber Attack with Ransomware hidden inside PDF Documents.
Retrieved March, 23, 2019from https://www.cybersecurity-insiders.com/cyber-attack-with
ransomware-hidden-inside-pdf-documents/.
Preece, J, Rogers, Y and Sharp, H. (2016).
Interaction design: beyond human- computer interaction (6th Ed).Pearson.
RICHA.(2015).DBMS Architecture: An Overview of the 3-Tier ANSI-SPARC Architecture.
Retrieved 24/03/2019 from https://blog.udemy.com/dbms-architecture/.