Week 1: Introduction To Databases & Security
Databases, DBMS and Security
What Are Databases?
- Databases are a structured collection of data which itself is meaningful information
- In a database, data is organised into tables consisting of rows and columns where each row corresponds to an entity and each column represents an attribute associated with that entity
- Databases play a crucial role in modern applications, serving as the backbone for storing and managing critical data in industries like finance, healthcare, e-commerce and many others
Practice Question
In the table below, select all true answers about the Phone_Number of Customers?
Database Management Systems (DBMS)
- A DBMS is a software package designed to define, manipulate, retrieve and manage data stored in a database
- A DBMS is required to enable automation which is the key to efficiency and humans can easily be overwhelmed with large volumes of information hence a DBMS is better than manually processing data
- The main operations a DBMS is used to perform are creating, reading, updating and deleting data (CRUD)
- A DBMS can be of
either of the two following types:
-
Structured Query Language (SQL) -
- Also referred to as relational database management system (RDBMS) as data is stored in a tabular form with interrelated tables
- Some of the most popular implementations include MySQL (used in COMP23111), PostgresQL, Oracle and Microsoft SQL Server
-
Not Only Structured Query Language (NoSQL) -
- They don't follow all the rules of a RDBMS and are typically massive databases
- Some of the most popular implementations include MongoDB (used in COMP23111), Cassandra, Amazon's SimpleDB and DynamoDB
-
Structured Query Language (SQL) -
- There are
typically 3 levels of abstraction in a DBMS:
-
Physical Level -
- This is the lowest level often hidden from the programmer which describes how data is actually stored in the database
-
Logical Level -
- This level describes the data that is stored in the form and stores the relationship among the entities
-
View Level -
- This is the level the user interacts with through the help of a GUI where data is displayed in row and column form
-
Physical Level -
Types Of Database Languages
- Data Definition
Language (DDL):
- This is the language that specifies the database schema and is used to create database/tables, alter them, etc.
- The DDL statements include CREATE, ALTER, DROP and RENAME
- Data
Manipulation Language (DML):
- This is the language used to manipulate the database by inserting, updating and deleting records that are stored in tables
- The DML statements include INSERT, UPDATE and DELETE
- Data Query
Language (DQL):
- This is a pure expression language as it returns results without altering the database
- An example of a DQL statement is SELECT
Database Security And Common Threats
- Database security are the tools designed to establish and preserve a databases integrity, availability and confidentiality
- Having a secure database is important for an organisations reputation, financial status, legal compliance and for general functionality
- There are 3
common threats to databases:
- A loss of integrity where improper modification is made on information
- A loss of availability where you lose access to the database
- A loss of confidentiality where there is unauthorised disclosure to the database
- Some common
security threats include:
- Insider threats where an individual who has authorised access to the database but uses it inappropriately
- Database software vulnerabilities that are exploited by attackers
- SQL/NoSQL injection attacks where malicious code is inserted into queries to trick the database into executing unintended operations
- Buffer overflow exploitations where attackers try to overwrite adjacent memory if the program stores more data in a buffer than it can hold
- Denial of Service (DoS) attacks where attackers try to overwhelm the databases resources by flooding it with excessive requests, consume its processing power, memory or bandwidth in order to disrupt the availability of the database
- Backup attacks which involve compromising database backups by trying to gain unauthorised access or tampering them
Protecting Databases From Security Threats
- Restrict access by implementing strong authentication mechanisms and employing role-based access control to ensure that users have the necessary privileges based on their roles and responsibilities
- Validate and sanitise input to prevent SQL or NoSQL injection attacks by using parametrised queries or prepared statements to avoid executing malicious code embedded in user input
- Implement auditing mechanisms to monitor database activities by logging and tracking all database events to identify any suspicious activity and respond accordingly
- Protect sensitive data by encrypting it using strong encryption algorithms to ensure that even if data becomes compromised, it remains unreadable without the proper decryption keys
- Implement a robust backup and recovery strategy to ensure data availability and integrity and test the restoration process periodically to ensure that backups are valid and can be successfully recovered
Laboratory 01 - Eliciting Data Requirements
For this weeks Lab you will first need to study the Data_Requirements_from_Data_Flow.pdf document. The goal of this
activity is for you to practice the technique of deriving a data requirements specification
(DRS) from a process model captured as a level-0 data flow diagram (DFD).
This technique is often carried out in real-world software development by a data analyst and is
preparatory to engaging in database design. In practice, one or more data analysts have one or
more joint, interactive face-to-face sessions with one or more stakeholders to understand the
process model captured in the level-0 DFD. From such interactions, gradually, through a few
iterations, a DRS emerges.
Note that, in this course unit, we cannot teach you process modelling, and hence, you won’t be
taught the techniques for drawing DFDs (which, of course, also means that you won’t be examined
on your ability to draw DFDs). However, a data analyst must work from a process model, so you do
need to learn how to read a DFD, which is a popular formalism for process modelling.
In this activity, the narrower goal is for you to practice the skill of inferring from the DFD
questions that, when posed to the stakeholders, elicit answers that allow you, as a data
analyst, to capture one or more data requirements to go into the DRS.
Tasks:
- Using what you have learned by studying the ‘Data Requirements Specifications from Data Flow Diagrams’ document, write down five questions from the question types in Figure 4 from it.
- Provide common-sense, intuitive answers (which conform with 1) for the questions you wrote down in response to the previous task.
- Write down the DRS items that follow from the answers you gave in response to the previous task.
End Of Week Quiz
Note: A score of 70% or higher would mean you
have successfully completed the week 1 workshop