Security, Projects and Placement — DBMS Level 18

01 • PROTECT THE RIGHT PROPERTY

Database Security Balances Confidentiality, Integrity and Availability

C

Confidentiality

Only authorized identities can read protected data.

Controls: access rules, encryption, masking

I

Integrity

Data changes remain valid, attributable and protected from unauthorized modification.

Controls: constraints, transactions, privileges, audit

A

Availability

Authorized users can access the system when needed.

Controls: redundancy, backups, monitoring, capacity

IDENTIFICATIONWho claims access?

User or service presents an identity.

→

AUTHENTICATIONCan the claim be verified?

Password, key, certificate or federated identity.

→

AUTHORIZATIONWhat may it do?

Roles and policies permit specific operations.

→

AUDITINGWhat actually happened?

Relevant activity becomes reviewable evidence.

02 • GRANT THE MINIMUM REQUIRED CAPABILITY

Roles Make Least Privilege Manageable

USERS / SERVICESAshareporting-apiadmissions-api

assigned to

ROLESreport_readeradmissions_writerdb_operator

receive

PRIVILEGESSELECT approved viewsINSERT applicationsmonitor, not business data

LEAST PRIVILEGE

Grant only required operations, objects and scope.

SEPARATION OF DUTIES

Avoid giving one identity unnecessary control over development, approval and production.

DEFAULT DENY

Unspecified capability remains unavailable.

REGULAR REVIEW

Remove unused accounts, inherited privileges and temporary access.

CREATE ROLE report_reader;
GRANT SELECT ON monthly_sales TO report_reader;
GRANT report_reader TO analyst_asha;

REVOKE SELECT ON monthly_sales FROM report_reader;

GRANT and REVOKE change authorization

Exact syntax, role inheritance, ownership and cascading behavior differ by product. Test revocation: access may still arrive through another role, ownership or a broader privilege.

Avoid using the database owner or administrator account inside an application.

03 • KEEP DATA OUT OF SQL STRUCTURE

Parameterization Prevents Input from Becoming SQL Code

UNSAFE CONCATENATION

sql = "SELECT * FROM users
WHERE email = '" + email + "'";

An attacker may supply syntax that changes the intended statement.

Input: ' OR '1'='1

PARAMETERIZED QUERY

sql = "SELECT * FROM users
WHERE email = ?";
execute(sql, [email]);

The driver sends SQL structure and the value separately, so the value stays data.

Input remains one email value

Use parameters

For values in SELECT, INSERT, UPDATE and DELETE statements.

Allow-list identifiers

Table names, column names and sort directions often cannot be value parameters; map choices to trusted identifiers.

Limit database privileges

If injection occurs, a restricted application role reduces reachable damage.

Do not rely on escaping alone

Correct escaping is context and product dependent; prepared/parameterized APIs are the primary defense.

04 • PROTECT DATA THROUGH ITS LIFECYCLE

Encryption, Secrets and Audit Logs Solve Different Problems

IN TRANSIT

TLS-protected connection

Reduces interception between clients, applications and database endpoints.

AT REST

Storage encryption

Protects database files, disks and backups from some forms of offline access.

FIELD LEVEL

Application or column protection

Useful for highly sensitive fields, with added key-management and query limitations.

SECRETS

External secret storage

Keep credentials out of source code, rotate them and scope each secret to one environment.

AUDIT

Relevant security events

Record who performed sensitive actions, what changed, when and from where.

RETENTION

Keep only what is justified

Minimize collected personal data and delete it according to policy and legal obligations.

Logs are sensitive data too.

Avoid recording passwords, access tokens or unnecessary personal values. Protect audit integrity, restrict access and define retention.

05 • DESIGN FOR RESTORATION, NOT JUST BACKUP CREATION

A Backup Is Valuable Only When It Can Be Restored

RPORecovery Point Objective

Maximum acceptable data loss measured backward from disruption.

RPO = 15 minutes

RTORecovery Time Objective

Target time to restore an acceptable service after disruption.

RTO = 2 hours

FULL

Complete backup baseline; simple restore foundation but larger and slower to create.

INCREMENTAL / LOG

Captures changes since a defined point; reduces backup volume but adds restore steps.

POINT-IN-TIME

Uses a base backup plus continuous/archived log records to restore near a chosen moment.

REPLICA

Improves availability but is not a substitute for independent backups; corruption or mistakes may replicate.

1
Define RPO and RTO
Match business impact, not guesswork.
2
Create and protect backups
Encrypt, separate access and keep independent/off-site copies.
3
Restore into an isolated environment
Verify schema, data, permissions and application behavior.
4
Measure and document
Record achieved recovery point/time and improve the runbook.

06 • BUILD COMPLETE DATABASE EVIDENCE

Three Portfolio-Ready Project Blueprints

Each project must demonstrate requirements, design, implementation, testing, security and measurable improvement.

FOUNDATION

Campus Course & Results System

Manage students, courses, enrolments, attendance and results.

ER model with cardinalities
3NF schema and integrity constraints
Role-specific faculty/student views
Result and backlog reports

Evidence target: correctness and normalization

INTERMEDIATE

E-commerce Orders & Inventory

Coordinate customers, carts, orders, payments and stock.

Transactional order placement
Concurrency-safe stock update
Sales window-function reports
Indexes justified by query plans

Evidence target: transactions and performance

ADVANCED

Hospital Access & Audit Platform

Protect patients, appointments, prescriptions and clinical notes.

Least-privilege role matrix
Protected sensitive fields
Audit events and retention plan
Backup and restore drill

Evidence target: security and operations

Requirements→ER model→Relational schema→SQL & transactions→Tests & plans→Demo & reflection

07 • SHOW DECISIONS, NOT ONLY SCREENSHOTS

A Strong Portfolio Makes Technical Reasoning Verifiable

PROBLEM

Who needs the system, what workflow fails today and what scope you chose.

MODEL

ER diagram, assumptions, keys, constraints and relational mapping.

IMPLEMENTATION

Migration/schema scripts, representative queries, transactions and seed data.

VERIFICATION

Constraint tests, edge cases, concurrency checks and expected results.

PERFORMANCE

Before/after plan evidence with representative data—not unsupported “faster” claims.

SECURITY

Role matrix, parameterized access, secret handling and audit/backup decisions.

DEMO

A short reproducible path showing the main workflow and one failure case.

REFLECTION

Trade-offs, limitations, what you would improve and what you personally implemented.

WEAK CLAIM

“Created a database management system using SQL.”

→EVIDENCE-BASED CLAIM

“Designed a 12-table 3NF order schema, protected stock updates with transactions, and reduced a representative report plan from a full scan to a selective composite-index path.”

08 • APPLY LEAST PRIVILEGE

Interactive Role & Privilege Simulator

Select an identity, object and operation. The simulator explains whether access should be allowed and shows the smallest relevant authorization statement.

Identity roleDatabase objectRequested operation

MINIMAL AUTHORIZATION EXAMPLE

Educational policy example. Real authorization must also consider row-level rules, ownership, inherited roles and the selected database product.

09 • TURN A PROJECT INTO PROOF

Portfolio Project Readiness Planner

Choose a blueprint and mark only evidence you can actually demonstrate. Progress is saved in this browser.

0%evidence ready

10 • CHECK YOUR UNDERSTANDING

Ten Formative Concept Checks

1. Authentication answers:

2. Least privilege means granting:

3. The primary defense against SQL injection in values is:

4. Dynamic sort-column input should normally be handled by:

5. Encryption at rest mainly protects:

6. RPO describes the maximum acceptable:

7. A read replica is not a complete backup because:

8. The strongest proof that a backup works is:

9. A strong database project portfolio should include:

10. The best interview explanation begins by:

Answered correctly: 0 of 10

11 • EXPLAIN UNDER PRESSURE

Placement, University and Viva Preparation

2-MARK / VIVA

Authentication versus authorization?
Define least privilege.
What is SQL injection?
RPO versus RTO?

5-MARK / DESIGN

Design roles for a college database.
Explain layered injection defenses.
Create a backup and restore plan.
Defend one project schema.

INTERVIEW

How did you validate normalization?
Why did you choose each index?
How does your project handle concurrent updates?
What would fail first at ten times the load?

Show the project interview framework

State the user problem and success criterion.
Define scope, assumptions and key risks.
Explain the model and integrity constraints.
Walk through one important transaction.
Show one security and one recovery decision.
Present measured test or plan evidence.
Close with limitations and the next improvement.

DESIGN

ER model → keys → mapping → normalization

SQL

DDL → DML → joins → subqueries → windows

SYSTEMS

Indexes → plans → transactions → recovery

RESPONSIBILITY

Privileges → injection defense → backup → audit

LEVEL 18 & COURSE SUMMARY

You Have Completed the DBMS Learning Path

Model requirements and enforce integrity.
Normalize schemas and write expressive SQL.
Analyze performance and concurrency.
Protect access and plan recovery.
Build, test and explain portfolio evidence.
Continue through practice, projects and revision.

FINAL COURSE CHECKPOINT

Mark Level 18 when you can demonstrate security decisions and present one complete database project.

Saved in this browser only.