Database SecuritySillabus

Fənn kodu

İF-2209y-EN

Kredit

5

Tələbənin iş yükü

90

Semestr

Yaz semestri

Akademik il

3

Fənnin tipi

Məcburi

Təhsil forması

Əyani

Tədris dili

İngilis

Auditoriyadaxili saatlar

a) 30 hours - lectures b) 30 hours - laboratory

Auditoriyadan kənar saatlar

a) 60 hours – independent work b) 30 hours - project work or consultation hours

This course focuses on the principles, technologies, and practices used to protect databases and the information they contain from unauthorized access, modification, disclosure, and destruction. It covers database security architecture, authentication and authorization mechanisms, access control models, encryption, secure database configuration, auditing and monitoring, SQL injection attacks, data protection, backup and recovery security, and database security policies. Students will also examine common database vulnerabilities and learn how to apply appropriate security controls to ensure the confidentiality, integrity, and availability of database systems.

• mühazirə
• interaktiv müzakirə
• laboratoriya işi
• problem-based learning

1. 1

   CLO 1: Knows the main functions of database management systems. Is able to work with several DBMSs currently in use. Knows how the system catalogs of relational DBMSs operate and what information they contain. Is able to perform database auditing and logging for security and reliability.

2. 2

   CLO 2: Is able to implement initial security measures in DBMSs by managing user profiles, password policies, permissions (rights/access), and roles.

3. 3

   CLO 3: Knows the causes of database availability problems and understands backups, synchronization between them, and load-balancing methods.

4. 4

   CLO 4: Knows methods for preserving integrity in databases. Is able to implement various encryption methods and solutions at different levels in databases (file system, table, column, etc.).

5. 5

   CLO 5: Is aware of threats originating from application software. Knows SQL injection and related threats, as well as methods for preventing them.

6. 6

   CLO 6: Knows NoSQL-type databases, their operating principles, practical significance, and their advantages and disadvantages compared with traditional DBMSs.

1. 1.
   Introduction to Databases (Lecture). Database concept and purpose; types of data models (hierarchical, network, relational, document-oriented); DBMS architecture.Mühazirə

   This topic introduces the fundamental concepts and purposes of databases and database management systems (DBMS). It examines the role of databases in storing, organizing, managing, and retrieving structured information. Different types of data models, including hierarchical, network, relational, and document-oriented models, are discussed, along with their main characteristics and applications. The topic also provides an overview of DBMS architecture and its core components, explaining how database systems support efficient data management and access.

2. 2.
   Relational data model, introduction to SQL (Lecture). Tables, rows, columns, primary and foreign keys, relationships. Creating a database and applying constraints in a DBMS (Lab).Mühazirə

   This topic introduces the relational data model and the fundamental concepts of SQL. It covers the structure of relational databases, including tables, rows, columns, primary keys, foreign keys, and relationships between tables. Students will learn how to create databases and tables, define data types, and apply integrity constraints such as PRIMARY KEY, FOREIGN KEY, NOT NULL, UNIQUE, and CHECK constraints. Practical laboratory activities focus on creating and configuring a relational database using a DBMS and applying SQL commands to ensure data integrity and consistency.

3. 3.
   SQL - Structured Query Language (Lecture). DDL, DML, DCL, TCL commands; WHERE, JOIN,ORDER BY,GROUP BY,HAVING, aggregate functions. Configuring users, roles, and access rights in a DBMS, GRANT/REVOKE (Lab).Mühazirə

   This topic introduces SQL as the standard language for managing and interacting with relational databases. It covers the main categories of SQL commands, including DDL, DML, DCL, and TCL, as well as data retrieval and manipulation techniques using WHERE, JOIN, ORDER BY, GROUP BY, HAVING, and aggregate functions. The practical laboratory component focuses on database access control, including the creation and configuration of users and roles and the management of permissions using GRANT and REVOKE commands to ensure secure and controlled access to database resources.

4. 4.
   Database backup and recovery (Lecture). Backup types: full and incremental backup. Recovering a database from lost data. Practice in creating and restoring backups in PostgreSQL using pg_dump and pg_restore (Lab).Mühazirə

   This topic introduces the principles and techniques of database backup and recovery to ensure data availability and minimize the impact of data loss or system failures. It covers different backup strategies, including full and incremental backups, as well as the main stages of database recovery. Students will examine methods for restoring databases after accidental data deletion, corruption, or system failure. The practical laboratory component focuses on creating and restoring PostgreSQL database backups using the pg_dump and pg_restore utilities.

5. 5.
   Database backup and recovery (Lecture). Backup types: full and incremental backup. Recovering a database from lost data. Practice in creating and restoring backups in PostgreSQL using pg_dump and pg_restore (Lab).Mühazirə

   This topic introduces the principles and techniques of database backup and recovery to ensure data availability and minimize the impact of data loss or system failures. It covers different backup strategies, including full and incremental backups, as well as the main stages of database recovery. Students will examine methods for restoring databases after accidental data deletion, corruption, or system failure. The practical laboratory component focuses on creating and restoring PostgreSQL database backups using the pg_dump and pg_restore utilities.

6. 6.
   SQL - View, function, procedure, trigger (Lecture). Access control and security analysis using views (Lab).Mühazirə

   This topic covers advanced SQL database objects, including views, functions, stored procedures, and triggers. It explains their purpose, structure, and practical applications in automating database operations, simplifying data access, and enforcing business rules. Particular attention is given to the use of views as a security mechanism for controlling access to sensitive data and limiting users to authorized information. The laboratory component focuses on creating and configuring views and applying them to database access control and security analysis.

7. 7.
   Database design (Lecture). Requirements gathering, ER diagrams, normalization (1NF-3NF, Boyce-Codd). Logging and auditing user activities (Lab).Mühazirə

   This topic introduces the fundamental principles of database design, including requirements gathering, conceptual data modeling, and the development of Entity-Relationship (ER) diagrams. It covers database normalization techniques from First Normal Form (1NF) through Third Normal Form (3NF) and Boyce-Codd Normal Form (BCNF) to reduce data redundancy and maintain data integrity. The laboratory component focuses on configuring database logging and auditing mechanisms to monitor and record user activities, supporting security analysis, accountability, and compliance.

8. 8.
   Database design (Lecture). Requirements gathering, ER diagrams, normalization (1NF-3NF, Boyce-Codd). Logging and auditing user activities (Lab).Mühazirə

   This topic covers the fundamental principles of secure database design, including requirements gathering, data modeling, and the development of Entity-Relationship (ER) diagrams. It introduces database normalization techniques, including First, Second, and Third Normal Forms (1NF–3NF) and Boyce-Codd Normal Form (BCNF), with emphasis on reducing data redundancy and maintaining data integrity. The laboratory component focuses on configuring database logging and auditing mechanisms to monitor and analyze user activities, identify security-relevant events, and support accountability within database systems.

9. 9.
   Indexes and query optimization (Lecture). Types of indexes, execution plans. Working with SQL DCL: GRANT/REVOKE practice, creating an access policy (Lab).Mühazirə

   This topic introduces database indexes and query optimization techniques used to improve the performance and efficiency of database operations. It covers different types of indexes, their structure and applications, as well as the use of execution plans to analyze and optimize SQL queries. The laboratory component focuses on applying SQL Data Control Language (DCL) commands, particularly GRANT and REVOKE, to manage user permissions and develop appropriate database access policies based on security requirements.

10. 10.
    Database security issues. Fundamentals of access management (Lecture).Mühazirə

    This topic introduces common database security issues and the fundamental principles of access management. It covers threats such as unauthorized access, data leakage, privilege misuse, SQL injection, data modification, and other security vulnerabilities affecting database systems. The topic also examines authentication, authorization, access control, user privileges, roles, and the principle of least privilege. Emphasis is placed on implementing effective access management mechanisms to protect the confidentiality, integrity, and availability of database resources.

11. 11.
    Access management (Lecture): use of views, security logs, and audit trails. Data encryption: symmetric vs asymmetric encryption (Lab).Mühazirə

    This topic examines access management mechanisms used to protect database resources and monitor user activities. It covers the use of database views for restricting access to sensitive information, as well as security logs and audit trails for recording and analyzing user actions and security-related events. The laboratory component introduces data encryption techniques and compares symmetric and asymmetric encryption, focusing on their principles, key management, practical applications, and role in protecting sensitive database information.

12. 12.
    Introduction to NoSQL (Lecture): document-oriented databases (e.g., MongoDB). Protection against SQL Injection: attack demonstration, parameterized queries (Lab).Mühazirə

    This topic introduces NoSQL databases, with a focus on document-oriented database systems such as MongoDB. It examines the main characteristics, structure, and use cases of document-oriented databases and compares them with traditional relational database systems. The laboratory component focuses on SQL injection vulnerabilities, demonstrating how malicious input can affect database queries and how parameterized queries and secure input-handling techniques can be used to prevent such attacks and improve application security.

13. 13.
    ISecurity in PostgreSQL (Lecture). Network-level protection: firewall, proxy, VPN (simulation) (Lab).Mühazirə

    This topic introduces the security mechanisms and best practices used to protect PostgreSQL database systems. It covers PostgreSQL authentication, authorization, access control, secure configuration, and protection of database resources against unauthorized access. The laboratory component focuses on network-level database protection through the simulation and configuration of security mechanisms such as firewalls, proxy servers, and VPNs, with emphasis on controlling and securing network connections to database systems.

14. 14.
    Transactions and concurrency control (Lecture): ACID properties, isolation levels, locks.Mühazirə

    This topic introduces database transactions and the principles of concurrency control used to maintain data consistency and integrity in multi-user database environments. It covers the ACID properties of transactions—Atomicity, Consistency, Isolation, and Durability—and examines different transaction isolation levels and their impact on concurrent operations. The topic also discusses locking mechanisms and their role in preventing data conflicts, concurrency anomalies, and inconsistent database states.

15. 15.
    SQL injection (Lecture).Mühazirə

    This topic introduces SQL injection as a common web application and database security vulnerability. It examines the causes and mechanisms of SQL injection attacks, including how improperly validated user input can alter database queries and lead to unauthorized access or data manipulation. The topic also covers common SQL injection techniques, potential security impacts, and defensive measures such as input validation, parameterized queries, prepared statements, least-privilege access, and secure database configuration.

16. 16.
    Database security on the Internet (Lecture): encryption, proxy servers, firewalls, digital signatures, certificate authorities. Vulnerability analysis and audit: SQLMap, DBShield, Greenbone/OpenVAS, nmap (Lab).Mühazirə

    This topic examines the security of databases operating in Internet-connected environments and introduces key mechanisms for protecting database communications and services. It covers encryption, proxy servers, firewalls, digital signatures, and certificate authorities, with emphasis on secure communication, authentication, and protection against unauthorized access. The laboratory component focuses on database vulnerability assessment and security auditing using tools such as SQLMap, DBShield, Greenbone/OpenVAS, and Nmap to identify potential vulnerabilities, misconfigurations, exposed services, and security risks.

17. 17.
    Final course assessment.Mühazirə

    This assessment evaluates students’ overall knowledge and practical skills acquired throughout the course. It covers key database security concepts, including access control, authentication and authorization, encryption, backup and recovery, SQL injection protection, auditing and logging, secure database configuration, and vulnerability assessment. The assessment may include theoretical questions and practical tasks designed to evaluate students’ ability to apply database security principles and implement appropriate security controls in real-world scenarios.

1. Christopher Diaz - Database Security: Problems and Solutions. Mercury Learning and Information, 2022. ISBN 978-1-68392-663-4.
2. Ettore Galluccio, Edoardo Caselli, Gabriele Lombari - SQL Injection Strategies: Practical Techniques to Secure Old Vulnerabilities Against Modern Attacks. Packt Publishing, 2020. ISBN 978-1-83921-564-3.
3. Dimitri Fontaine - The Art of PostgreSQL: Turn Thousands of Lines of Code into Simple Queries, 2020.
4. PostgreSQL 17.6 Documentation.