DBMS: Week 15 - Database Security and Access Control

DBMS: Week 15 - Database Security and Access Control

• 1.
  International Islamic UniversityH-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
• 2.
   Understand theimportance 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
• 3.
   Protecting datafrom 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?
• 4.
   Authentication:  Confirminguser 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
• 5.
   Description:  caching_sha2_passwordis 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!';
• 6.
   Description:  InMySQL, 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.
• 7.
   Examples:  CREATEUSER '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
• 8.
   Description:  InMySQL, 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
• 9.
   Examples:  GrantSELECT 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 Authorization: Granting Access to Resources
• 10.
   Privilege Typesin MySQL:  Example: GRANT ALL PRIVILEGES ON *.* TO 'admin'@'localhost' WITH GRANT OPTION; Authorization: Granting Access to Resources 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
• 11.
   Access Levels: Authorization:Granting Access to Resources 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
• 12.
   Description:  InMySQL, 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
• 13.
   Description:  Encryptiontransforms 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
• 14.
   What isData 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
• 15.
   Definition: Theprocess 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
• 16.
   Database securityis 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