Unit 1: Fundamentals of Databases
- What is a Database?
- DBMS – Purpose of DB and Users of DB
- Components of DB
- Concepts of RDBMS
- Basic SET Concepts (SET, Subset)
- Set of Ordered Tuples – Relations as a DB (Concepts of PK, FK, Surrogate Keys, Composite Keys, Candidate Keys)
- Relational DB Operators (Cartesian Product, Union, Intersect, Difference)
- Relational DB Normal Forms (1NF, 2NF, 3NF) – E-R Model.
- What is a Database?
- Definition: A structured collection of data that is organized and stored for easy retrieval and management.
- Example: Think of a library catalog where books are systematically arranged, each with details like title, author, and genre.
- DBMS – Purpose of DB and Users of DB
- DBMS (Database Management System): Software that facilitates the creation, maintenance, and usage of databases.
- Purpose: Efficiently manage and organize large volumes of data.
- Users: Administrators, application developers, and end-users.
- Example: MySQL, Oracle, or Microsoft SQL Server.
- Components of DB
- Key components: Tables, fields (attributes), records (rows), and relationships between tables.
- Example: In an employee database, components include tables for employees, departments, and a relationship between them based on department ID.
- Concepts of RDBMS
- RDBMS (Relational Database Management System): Organizes data into tables with predefined relationships.
- Example: In a university database, there are tables for students, courses, and a relationship table that links students to the courses they are enrolled in.
- Basic SET Concepts (SET, Subset)
- SET: A collection of distinct elements.
- Subset: A set whose elements are all contained in another set.
- Example: A set of all students (SET) and a subset of students majoring in computer science.
- Set of Ordered Tuples – Relations as a DB
- Definition: Data organized as a set of ordered tuples (rows) forming relations (tables) in a database.
- Example: A table of customers where each row is an ordered tuple representing a customer and their details.
- Concepts of PK, FK, Surrogate Keys, Composite Keys, Candidate Keys
- PK (Primary Key): Unique identifier for a record in a table.
- FK (Foreign Key): Links a field in one table to the primary key of another table.
- Surrogate Key: Artificial key introduced to uniquely identify records.
- Composite Key: Primary key composed of multiple fields.
- Candidate Key: A set of fields that can uniquely identify a record.
- Example: In an order database, OrderID can be a PK, CustomerID can be an FK linking to a Customers table, and a composite key might include both ProductID and OrderID.
- Relational DB Operators (Cartesian Product, Union, Intersect, Difference)
- Cartesian Product: Combination of all rows from two or more tables.
- Union: Combines rows from two tables while eliminating duplicates.
- Intersect: Retrieves common rows from two tables.
- Difference: Retrieves rows from one table that do not exist in another.
- Example: Cartesian Product of tables A and B includes all possible combinations of rows from A and B.
- Relational DB Normal Forms (1NF, 2NF, 3NF) – E-R Model
- 1NF (First Normal Form): Ensures atomic values in each column.
- 2NF (Second Normal Form): No partial dependencies on a composite primary key.
- 3NF (Third Normal Form): Eliminates transitive dependencies.
- E-R Model (Entity-Relationship Model): Represents entities, relationships, and attributes in a graphical form.
- Example: Ensuring that a customer’s address is not stored in multiple fields but in a separate table to achieve 2NF.
Unit 2: Database Security Lifecycle
- Concept of DB Security Lifecycle
- Creating Data Risk Assessment
- Analyzing data threats, risks & vulnerabilities
- Need for database security architecture
- Implementing feedback mechanisms
- Adjusting policies & practices based on feedback mechanisms using different security models
- Concept of DB Security Lifecycle
- Definition: A systematic approach to safeguarding a database throughout its lifecycle.
- Example: Similar to the way we secure a physical building from its construction phase to its daily operations, a database needs continuous protection from development to production.
- Creating Data Risk Assessment
- Process of evaluating potential risks associated with database data.
- Example: Identifying the risk of unauthorized access to sensitive customer information in a healthcare database.
- Analyzing Data Threats, Risks & Vulnerabilities
- Threats: Potential dangers to database security.
- Risks: Likelihood and impact of a threat occurring.
- Vulnerabilities: Weaknesses that can be exploited.
- Example: Threat – Malicious insiders; Risk – High if data is not properly restricted; Vulnerability – Weak password policies.
- Need for Database Security Architecture
- Designing a secure structure for the database to prevent unauthorized access.
- Example: Implementing access controls, encryption, and audit trails within the architecture to protect sensitive financial data.
- Implementing Feedback Mechanisms
- Continuous monitoring and evaluation of database security measures.
- Example: Using intrusion detection systems to detect and alert administrators about potential security breaches in real-time.
- Adjusting Policies & Practices Based on Feedback Mechanisms
- Modifying security policies and practices based on the insights gained from feedback.
- Example: If a security incident reveals a new vulnerability, updating access controls and authentication mechanisms promptly.
- Using Different Security Models
- Security models: Frameworks defining how security is implemented.
- Example: Applying the Bell-LaPadula model to enforce mandatory access controls, ensuring that sensitive data is only accessed by users with the appropriate clearance level.
Unit 3: Database Security
- Models
- Access Matrix Models
- Objects & Subjects
- Types of Objects & Subjects
- Access Modes (Static & Dynamic)
- Access Levels
- Issues in Database Security
- Database Access Controls
- Security Logs and Audit Trails
- Encryption
- SQL Data Control Language
- Security in Oracle
- Statistical Database Security
- SQL Injection
- Database Security and the Internet
- Access Matrix Models
- Definition: A matrix representing the access rights between subjects (users) and objects (data).
- Example: A matrix where rows represent users, columns represent data tables, and each cell indicates the access level a user has to a specific table.
- Objects & Subjects
- Objects: Data entities such as tables, views, or stored procedures in a database.
- Subjects: Users or processes interacting with the database.
- Example: In a university database, a student (subject) may interact with a “Grades” table (object) to view their academic performance.
- Types of Objects & Subjects
- Objects: Tables, views, stored procedures.
- Subjects: Users, applications, system processes.
- Example: An “Employees” table (object) can be accessed by HR personnel (subject) for managing employee records.
- Access Modes (Static & Dynamic)
- Static Access: Access permissions set in advance and remain unchanged during a session.
- Dynamic Access: Permissions can change dynamically during a user’s session.
- Example: Static access is granting a user read-only access to a table, while dynamic access might allow write access after a specific condition is met.
- Access Levels
- Levels of access indicating the degree of permission (e.g., read, write, execute).
- Example: Read-only access allows a user to view data but not modify it, while full access grants the ability to both view and modify.
- Issues in Database Security
- Common challenges include unauthorized access, data breaches, and insider threats.
- Example: An employee with access to financial data selling sensitive information to a competitor.
- Database Access Controls
- Mechanisms to regulate access to data and ensure data integrity.
- Example: Role-based access control (RBAC) where users are assigned roles (e.g., admin, analyst), and access is granted based on these roles.
- Security Logs and Audit Trails
- Logs record activities for security monitoring and forensic analysis.
- Example: Recording user logins, failed login attempts, and data modification in a security log for later review.
- Encryption
- Process of converting data into a secure format to prevent unauthorized access.
- Example: Encrypting sensitive credit card information in a database to protect it from being accessed by unauthorized parties.
- SQL Data Control Language
- SQL DCL includes commands like GRANT and REVOKE for controlling access privileges.
- Example: Granting SELECT privilege on a table to a specific user using the GRANT command.
- Security in Oracle
- Oracle Database provides robust security features like Virtual Private Database (VPD) and Transparent Data Encryption (TDE).
- Example: Using Oracle Advanced Security to implement encryption for sensitive columns in a table.
- Statistical Database Security
- Protecting statistical data to prevent inference attacks.
- Example: Ensuring that aggregate query results do not reveal sensitive individual records.
- SQL Injection
- An attack where malicious SQL code is injected into input fields to manipulate the database.
- Example: Entering “1′ OR ‘1’=’1” in a login form to bypass authentication and gain unauthorized access.
- Database Security and the Internet
- Ensuring databases remain secure in web-based applications.
- Example: Implementing firewalls, secure sockets layer (SSL), and strong authentication to protect databases accessed over the internet.
Unit 4: Password Management
- Authentication and Password Security
- Choosing an appropriate authentication option
- Understanding system administration privileges
- Choosing strong passwords, Implementing account lockout after failed login attempts
- Creating and enforcing password profiles
- Using passwords for all database components
- Understand and secure authentication back doors
- Authentication and Password Security
- Authentication: Process of verifying the identity of a user or system.
- Password Security: Ensuring passwords are strong, confidential, and resistant to attacks.
- Example: Using a combination of uppercase, lowercase, numbers, and symbols in a password for increased security.
- Choosing an Appropriate Authentication Option
- Options include password-based, biometric, token-based, and multi-factor authentication.
- Example: Choosing multi-factor authentication, requiring a password and a unique code sent to a mobile device for access.
- Understanding System Administration Privileges
- System administrators have elevated privileges, granting them extensive control.
- Example: Limiting the number of administrators and strictly controlling their access to minimize the risk of unauthorized actions.
- Choosing Strong Passwords, Implementing Account Lockout after Failed Login Attempts
- Strong Passwords: Complex, unique, and not easily guessable.
- Account Lockout: Temporarily suspending an account after a certain number of failed login attempts.
- Example: Requiring passwords with a mix of characters and locking an account for 30 minutes after three consecutive failed login attempts.
- Creating and Enforcing Password Profiles
- Password Profiles: Policies dictating password requirements and expiration.
- Example: Enforcing a password profile that mandates users to change their passwords every 90 days and includes a minimum length of 12 characters.
- Using Passwords for All Database Components
- Extending password protection to all components (e.g., applications, servers, databases) within a system.
- Example: Ensuring that not only database logins but also application interfaces and server access are password-protected.
- Understand and Secure Authentication Back Doors
- Authentication Back Doors: Hidden access points that bypass normal authentication.
- Example: Periodically reviewing system configurations to ensure no unintended back doors exist and promptly closing any discovered vulnerabilities.
Unit 5: Virtual Private Databases
- Introduction to Virtual Private Databases (VPDs)
- Need for VPDs
- Implementing VPDs
- Introduction to Virtual Private Databases (VPDs)
- Definition: VPDs are a feature in database management systems that enable data access control at the row or column level.
- Example: In a healthcare database, VPDs can be implemented to restrict access to patient records based on the user’s role, ensuring only authorized personnel can view specific patient information.
- Need for VPDs
- Privacy and Security: Protect sensitive data by limiting access to authorized users.
- Compliance: Meet regulatory requirements by ensuring only authorized personnel can access specific types of data.
- Example: In a financial database, VPDs can be crucial to restrict access to confidential financial records, preventing unauthorized viewing or modification.
- Implementing VPDs
- Steps:
- Define Policies: Specify the conditions under which data should be restricted.
- Create a Policy Function: A function that evaluates the policies and returns a predicate to enforce data access.
- Attach Policies: Associate the policy function with specific database tables.
- Example: For an employee database, a VPD policy can be defined to restrict access to salary information based on the user’s role. The policy function would check the user’s role and return a predicate limiting access to salary details for non-managerial roles. The policy is then attached to the relevant tables.
- Steps:
