DATABASE THREATS, VULNERABILITIES AND SECURITY TESTING
DATABASE THREATS
Definition
A database threat is any potential danger that can exploit a weakness in a database system and
compromise:
• Confidentiality
• Integrity
• Availability
Types of Database Threats
1. Unauthorized Access
• Hackers gaining access without permission.
• Caused by weak passwords or poor access control.
2. SQL Injection
• Malicious SQL commands inserted into input fields.
• Can expose or delete data.
3. Privilege Escalation
• A normal user gaining admin rights.
4. Data Breach
• Sensitive information stolen.
5. Malware / Ransomware
• Database encrypted or destroyed.
6. Insider Threat
• Employee misusing access rights.
7. Denial of Service (DoS)
• Flooding server to make it unavailable.
DATABASE VULNERABILITIES
Definition
A vulnerability is a weakness in a database system that can be exploited by a threat.
Common Vulnerabilities
• Weak passwords
• No password policy
• Excessive user privileges
• Unpatched database software
• Default credentials
• No encryption
• Poor backup configuration
DATABASE TESTING (AS PER MANUFACTURER’S MANUAL – MySQL)
According to the official Oracle Corporation documentation for MySQL, database testing
must follow vendor-approved procedures to ensure:
• ✔ Proper functionality
• ✔ System stability
• ✔ Security enforcement
• ✔ Data protection
• ✔ Compliance with best practices
Below is a complete structured practical guide with commands you can perform in your
lab.
A. FUNCTIONALITY TESTING
Functional testing in databases is a type of software testing that validates the data operations
and transactions against the defined business and functional requirements to ensure the
database behaves as expected from the user's perspective.
Test Server Status
Before testing the server status ensure the mysql database is open either by opening it from
the cmd or by opening from the windows interface:-
How to open mysql in the cmd
The command:
mysql -u root -p
is used to log into the MySQL server from the Command Prompt (CMD) or terminal.
Let’s break it down clearly
mysql
This is the MySQL client program.
It tells the system:
“Open the MySQL command-line interface so I can type SQL queries.”
When it runs successfully, you’ll see:
mysql>
That means you are inside the MySQL shell.
-u root
• -u means user
• root is the username
So:
-u root
Means:
Log in as the MySQL user called root
The root user is the default MySQL administrator account.
It has full privileges (create databases, users, tables, etc.).
-p
• -p means password
When you include -p, MySQL will prompt you to enter a password:
Enter password:
Important:
• When typing the password, nothing will show on the screen.
• That is normal (security feature).
What Happens When You Run It?
You type:
mysql -u root -p
Then:
1. It asks for password
2. If correct → you enter MySQL shell
3. If wrong → you get:
Access denied for user 'root'@'localhost'
Why Sometimes It Opens Without Asking for Password?
Since you've been working with MySQL Workbench and CMD recently:
Possible reasons:
1. Root password was set to empty during installation.
2. You're using Windows authentication.
3. MySQL is configured with auth_socket plugin.
4. You're already logged in via another session.
Example Full Login
C:\> mysql -u root -p
Enter password: ********
Welcome to the MySQL monitor.
mysql>
Now you can run queries like:
SHOW DATABASES;
Alternative Forms
You can also write:
mysql -u root -pMyPassword
But this is NOT recommended because the password becomes visible in command history.
Since you're learning SQL seriously, this command is your gateway to server-level control,
not just database queries.
To test for server status write the command in sql
STATUS;
Check:
• Server version
• Uptime
• Threads
• Current database
If status displays correctly → server functioning.
Test Database Creation
CREATE DATABASE test_db;
SHOW DATABASES;
If database appears → functionality confirmed.
Test Table Creation
USE test_db;
CREATE TABLE employees (
id INT PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(50),
salary DECIMAL(10,2)
);
Check:
SHOW TABLES;
Test Data Insertion
INSERT INTO employees (name, salary)
VALUES ('John', 50000);
Verify:
SELECT * FROM employees;
If record appears → CRUD operations working.
Test Update Operation
UPDATE employees
SET salary = 55000
WHERE id = 1;
Test Delete Operation
DELETE FROM employees WHERE id = 1;
This confirms full data manipulation functionality.
B. SECURITY CONTROL TESTING
Security Control Testing is the systematic evaluation of security safeguards to ensure they are
properly implemented, functioning as intended, and effectively mitigating risks.
Purpose:
Ensure access controls and authentication mechanisms are working.
Test User Creation
CREATE USER 'testuser'@'localhost'
IDENTIFIED BY 'Strong@123';
Grant Limited Privileges
GRANT SELECT ON test_db.* TO 'testuser'@'localhost';
TO APPLY THE PRIVILEGES:
Flush privileges;
Test Unauthorized Action
Login as testuser:
mysql -u testuser -p
Then try:
DROP TABLE employees;
Expected:
ERROR: Access denied
✔ Security working.
View Privileges
SHOW GRANTS FOR 'testuser'@'localhost';
Admin should show:
ALL PRIVILEGES ON *.* WITH GRANT OPTION
Normal user should show limited rights.
C. PASSWORD POLICY TESTING
Check Policy
SHOW VARIABLES LIKE 'validate_password%';
Test Weak Password
CREATE USER 'weakuser'@'localhost'
IDENTIFIED BY '123';
Expected:
ERROR 1819
Confirms password enforcement.
D. INTEGRITY TESTING
Purpose:
Ensure no corruption or inconsistency.
Check Table Health
CHECK TABLE employees;
Expected:
status: OK
Test Referential Integrity
Create department table:
CREATE TABLE departments (
id INT PRIMARY KEY,
dept_name VARCHAR(50)
);
Add foreign key:
ALTER TABLE employees
ADD department_id INT,
ADD FOREIGN KEY (department_id)
REFERENCES departments(id);
Now try inserting invalid reference:
INSERT INTO employees (name, salary, department_id)
VALUES ('Alice', 60000, 99);
If rejected → integrity working.
E. BACKUP AND RESTORE TESTING
Purpose:
Ensure availability and recovery.
Backup (Command Prompt)
mysqldump -u root -p test_db > test_db_backup.sql
Restore
mysql -u root -p test_db < test_db_backup.sql
If restore successful → backup system verified.
F. CONNECTIVITY TESTING
Test Local Connection
mysql -u root -p
If login works → local connectivity OK.
Check Current User
SELECT CURRENT_USER();
Check Listening Port
SHOW VARIABLES LIKE 'port';
Default should be 3306.
⚙ G. PERFORMANCE TESTING
Check Running Processes
SHOW PROCESSLIST;
Check Query Execution Plan
EXPLAIN SELECT * FROM employees;
Ensures indexes are used.
H. LOG AND ERROR TESTING
Check Error Log Location
SHOW VARIABLES LIKE 'log_error';
Review file for warnings.
🛡 I. VULNERABILITY CHECK
Check for Blank Passwords
SELECT user, host
FROM [Link]
WHERE authentication_string = ''
OR authentication_string IS NULL;
Remove Anonymous Users
SELECT user FROM [Link] WHERE user='';
If exists → remove:
DROP USER ''@'localhost';
SUMMARY OF DATABASE TESTING AREAS
Area What You Test Command Example
Functionality CRUD operations INSERT / SELECT
Security Privilege control SHOW GRANTS
Password Policy enforcement validate_password
Integrity Table health CHECK TABLE
Backup Data recovery mysqldump
Connectivity Login test mysql -u
Performance Query efficiency EXPLAIN
Vulnerability Weak configs SELECT from [Link]
CONNECTIVITY TESTING
Meaning
Testing whether the database server can be accessed correctly.
A. Test Local Connection
In Command Prompt:
mysql -u root -p
If login succeeds → connectivity works.
B. Check Current User
SELECT CURRENT_USER();
C. Check Server Status
STATUS;
DATABASE INTEGRITY TESTING
Meaning
Integrity testing ensures data has not been corrupted or altered improperly.
A. Check Table Integrity
CHECK TABLE employees;
Result:
status: OK
If corrupted → MySQL will report errors.
B. Verify Data
SELECT * FROM employees;
Check for:
• Missing records
• Unexpected values
• Duplicate entries
C. Referential Integrity (Foreign Keys)
Ensure relationships are valid.
Example:
SELECT * FROM employees WHERE department_id NOT IN
(SELECT id FROM departments);
If results appear → integrity violation.
ACCESS AND SECURITY CONTROL TESTING
Meaning
Ensuring users only access what they are allowed.
A. View User Privileges
SHOW GRANTS FOR 'username'@'localhost';
B. Identify Admin User
Admin will have:
ALL PRIVILEGES ON *.* WITH GRANT OPTION
Normal user will have limited rights like:
GRANT SELECT ON company_db.*
C. Test Privilege Escalation
If user runs:
GRANT SELECT ON company_db.* TO 'testuser'@'localhost';
And receives:
ERROR 1410
It means security is working.
BACKUP TESTING
Meaning
Testing if database can be backed up and restored.
A. Backup
Run in Command Prompt:
mysqldump -u root -p company_db > company_db_backup.sql
B. Restore
mysql -u root -p company_db < company_db_backup.sql
Backup testing ensures data availability.
PASSWORD TESTING
Meaning
Testing strength and enforcement of password policies.
A. Check Policy
SHOW VARIABLES LIKE 'validate_password%';
B. Test Weak Password
CREATE USER 'weakuser'@'localhost' IDENTIFIED BY '123';
If rejected → policy working.
C. Policy Levels
Level Description
LOW Length only
MEDIUM Length + complexity
STRONG Strict + dictionary checks
HASHED PASSWORDS
MySQL stores passwords as hashes.
View hash:
SELECT user, authentication_string FROM [Link];
You see:
$A$005$randomhash
Not the real password.
Can Admin See Password?
No.
Admin sees only hash.
Admin can reset password but not view it.