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.

  1. 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.
  2. 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.
  3. 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.
  4. 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.
  5. 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.
  6. 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.
  7. 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.
  8. 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.
  9. 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

  1. 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.
  2. 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.
  3. 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.
  4. 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.
  5. 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.
  6. 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.
  7. 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

  1. 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.
  2. 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.
  3. 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.
  4. 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.
  5. 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.
  6. 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.
  7. 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.
  8. 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.
  9. 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.
  10. 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.
  11. 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.
  12. Statistical Database Security
    • Protecting statistical data to prevent inference attacks.
    • Example: Ensuring that aggregate query results do not reveal sensitive individual records.
  13. 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.
  14. 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

  1. 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.
  2. 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.
  3. 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.
  4. 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.
  5. 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.
  6. 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.
  7. 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

  1. 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.
  2. 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.
  3. 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.


Leave a Reply