DBMS: Week 15 - Database Security and Access Control

International Islamic University H-10, Islamabad, Pakistan
Database Managements Systems
Week 15
Database Security
and Access Control
Engr. Rashid Farid Chishti
http://youtube.com/rfchishti
http://sites.google.com/site/chisht
i

 Understand the importance of database security in protecting data.
 Learn about user roles and access control mechanisms.
 Explore common security threats and how to mitigate them.
 Understand how to implement authentication, authorization, and encryption
in databases.
Learning Objectives

 Protecting data from unauthorized access, misuse, or theft
 Goal:
 Ensuring that only authorized users can access and modify data.
 Risks:
 Data Breach: Unauthorized access to sensitive data.
 Data Corruption: Modification of data to destroy or alter its integrity.
 Denial of Service: Overloading or disabling the database system.
What is Database Security?

 Authentication:
 Confirming user identity
 Authorization:
 Granting access to resources
 Access Control:
 Managing who can do what
 Auditing:
 Monitoring database usage
 Encryption:
 Protecting data at rest or in transit
Database Security Components

 Description:
 caching_sha2_password is the default authentication plugin in MySQL 8.0
and later, replacing the older mysql_native_password.
 Key Features:
 Strong Hashing: Uses SHA-256 to store and verify passwords (stronger than
old methods).
 Default Plugin: All new MySQL users use it by default unless another plugin
is specified.
 Why It’s More Secure:
 Hashes passwords with SHA-256 instead of SHA-1 (used in mysql_native_password).
 Can use RSA encryption to send the password securely.
Authentication: Caching SHA-2 Password in MySQL
CREATE USER
'secure_user'@'localhost'
IDENTIFIED BY 'StrongP@ssw0rd!';

 Description:
 In MySQL, host-based authentication means that access control is based not
only on the username but also on the host (IP address or hostname) from
which the user is connecting.
 mysql.user table and determines whether the user is allowed to connect
from that host.
Authentication: Host-based Authentication
User Meaning
'root'@'localhost' User root can only connect from the local machine using
localhost or a Unix socket.
'admin'@'192.168.1.10' User admin can only connect from the IP 192.168.1.10.
'appuser'@'%' User appuser can connect from any host (wildcard).
'dev'@'%.example.com' User dev can connect from any host in the example.com domain.

 Examples:
 CREATE USER 'chisht'@'localhost' IDENTIFIED BY 'password';
 CREATE USER 'manager'@'192.168.0.5' IDENTIFIED BY
'securepass';
 To see all defined users and their host access
 SELECT user, host, plugin FROM mysql.user;
 Switching to caching_sha2_password
 ALTER USER 'chisht'@'localhost' IDENTIFIED WITH
caching_sha2_password BY 'NewP@ssw0rd!';
Authentication: Host-based Authentication

 Description:
 In MySQL, authorization refers to the process of granting or restricting
access to specific database resources such as databases, tables, columns,
views, stored procedures, etc., based on user privileges.
 After a user is authenticated (i.e., login succeeds), MySQL checks the user's
privileges to determine:
 What databases they can access
 What operations they can perform (e.g., SELECT, INSERT, UPDATE, DELETE)
 Whether they can create or drop objects like tables or users
 MySQL Authorization Mechanism:
 MySQL uses GRANT and REVOKE statements to manage user privileges.
Authorization: Granting Access to Resources

 Examples:
 Grant SELECT Privilege on a Tables
 GRANT SELECT ON mydb.customers TO 'john'@'localhost';
 This allows user john to read (SELECT) from the customers table in mydb.
 Grant All Privileges on a Databases
 GRANT ALL PRIVILEGES ON mydb.* TO 'admin_user'@'%';
 This allows admin_user to do anything (SELECT, INSERT, UPDATE, etc.) on all objects in
the mydb database from any host.
 Revoke Privileges
 REVOKE INSERT ON mydb.orders FROM 'john'@'localhost';
 Removes the ability of john to insert into the orders table

 Privilege Types in MySQL:
 Example: GRANT ALL PRIVILEGES ON *.* TO 'admin'@'localhost' WITH GRANT
OPTION;
Privilege Description
SELECT Read data from a table or view
INSERT Add new rows to a table
UPDATE Modify existing rows
DELETE Remove rows from a table
CREATE Create new databases or tables
DROP Delete databases or tables
GRANT OPTION Allows user to grant privileges to others
ALL PRIVILEGES Grants all of the above

 Access Levels:
Level Example Description
Global GRANT SELECT ON *.* All databases
Database GRANT SELECT ON mydb.* One database
Table GRANT SELECT ON mydb.student One table
Column GRANT SELECT(name) ON mydb.student Specific Columns
Routine GRANT EXECUTE ON PROCEDURE
myproc TO ...
StoredProcedure

 Description:
 In MySQL, Access Control is the mechanism used to manage who (users) can do what
(actions) on which resources (like databases, tables, or views). It is a core part of
MySQL's security model, involving both authentication and authorization.
 Access Control = Authentication + Authorization
Access Control: Managing who can do what

 Description:
 Encryption transforms readable data into an unreadable format using algorithms and
keys. It has two main types:
 Data at Rest (stored data)
 Data in Transit (data being transmitted)
 MySQL Features for Data-at-Rest Encryption
 InnoDB Transparent Data Encryption (TDE)
 Example:
 CREATE TABLE confidential (
id INT, secret_data VARCHAR(255)
) ENCRYPTION='Y';
Encryption: Protecting Data at Rest or in Transit

 What is Data in Transit?
 Data moving over a network (e.g., client-server communication)
 Vulnerable to interception, man-in-the-middle attacks
 MySQL SSL(Secure Sockets Layer)/TLS(Transport Layer Security) Support
 MySQL encrypts communication between Clients and server. It requires SSL certificates.
 Enforcing SSL for Users:
 CREATE USER 'secure_user'@'%' IDENTIFIED BY 'passwd' REQUIRE
SSL;
 Client Connection:
 mysql -u user -p --ssl-ca=ca.pem --ssl-cert=client-cert.pem
--ssl-key=client-key.pem
Encryption in Transit

 Definition: The process of tracking and recording database operations to
detect unauthorized actions.
 Audit Logs:
 Record details about user actions such as logins, queries, and data modifications.
 Example: Tracking which user accessed sensitive customer data and when.
 Audit Requirements:
 Identify who performed an action.
 Identify what data was accessed or modified.
 Identify when and where the action occurred.
Database Auditing

 Database security is critical for protecting sensitive data from unauthorized
access, corruption, or loss.
 Authentication, authorization, and encryption are key components of a strong
security strategy.
 Auditing and access control mechanisms help ensure accountability and
minimize risks.
 Best practices for database security include regular updates, strong
authentication methods, and prevention techniques like parameterized
queries.
Summary

DBMS: Week 15 - Database Security and Access Control

More Related Content

Similar to DBMS: Week 15 - Database Security and Access Control

More from RashidFaridChishti

Recently uploaded

DBMS: Week 15 - Database Security and Access Control