GK One-Line Question & Answer

Application-level deadlock: occurs in application code (e.g. Thread A holds Java lock L1 waiting for Java lock L2; Thread B holds L2 waiting for L1) without involving database locks - the database itself is not deadlocked but the application threads are stuck. Different from DB deadlock where DBMS transactions hold DB locks circularly

A resource allocation algorithm that determines whether granting a resource request leaves the system in a safe state; requires: for each transaction - maximum resource needs declared in advance current allocation and remaining need; safe state = exists a sequence in which all transactions can complete using available resources

Hold-and-wait: a transaction holds one or more resources (locks) while waiting to acquire additional resources it needs. Elimination strategies: (1) Require all-or-nothing lock acquisition (acquire ALL needed locks at once before starting) (2) Release all current locks before requesting new ones (3) Use timeout-based retry after releasing

No preemption means that resources (locks) cannot be forcibly taken from a transaction holding them. Preemption can be introduced by: aborting (preempting) a deadlocked transaction (victim selection) which is how deadlock resolution works - the DBMS forcibly rolls back a chosen victim transaction releasing its locks for other waiting transactions

Detection frequency: how often the deadlock detector runs (e.g. every 100ms or every N lock requests); Detection latency: how long a deadlock persists before being detected and resolved (= time to next detection run + detection + abort time). Trade-off: higher frequency = lower latency but higher CPU overhead from running detection; lower frequency = lower overhead but deadlocked transactions blocked longer

Cycle prevention: design the system so that cycles in the wait-for graph cannot form (e.g., lock ordering, Wait-Die, Wound-Wait, no-wait) - proactive approach that adds overhead to every transaction. Cycle detection: allow cycles to form then detect and break them periodically (e.g., wait-for graph cycle detection) - reactive approach lower per-transaction overhead but deadlocks persist briefly

innodb_deadlock_detect=ON (default): InnoDB actively detects deadlocks using a wait-for graph and immediately resolves them by aborting a victim. innodb_deadlock_detect=OFF: deadlock detection is disabled; the lock wait timeout (innodb_lock_wait_timeout) becomes the only mechanism to break deadlocks - transactions wait until timeout then abort. Disabling detection reduces overhead in very high-concurrency scenarios but increases worst-case deadlock resolution time.

In SAGA-based distributed transactions traditional database deadlocks at the local level can still occur within each services database; additionally SAGA introduces higher-level semantic deadlocks where multiple sagas wait for each other to complete compensating actions or where resource locks across services create circular dependencies - resolved by designing sagas to acquire resources in consistent order and implementing timeouts for saga steps

RBAC: privileges are assigned to roles (logical groups) and roles are assigned to users; changing a roles privileges automatically affects all users with that role. Direct grants: privileges assigned directly to individual users making management complex and error-prone at scale

RLS is a database feature where security policies are automatically applied to queries at the database level based on the executing user/role transparently filtering rows without requiring application code or view modifications; views require explicit usage and can be bypassed if user has direct table access

Database-level encryption where data files log files and backups are encrypted on disk automatically by the DBMS without application changes; protects against: theft of physical storage media unauthorized access to database file copies but does NOT protect against authorized database users or SQL injection

Static data masking: permanently replaces sensitive data with realistic but fictitious values in non-production copies. Dynamic data masking: applies masking rules at query time for specific users returning masked values while original data remains stored unchanged

DAC: resource owners control access permissions (GRANT/REVOKE by object owner) - flexible but owners can grant to anyone. MAC: system-enforced labels on data and users (e.g. TOP SECRET data accessible only by TOP SECRET cleared users) owner cannot override - used in government/defense systems

Recording and monitoring database activities for security analysis compliance and forensics; key events to audit: all DDL changes privilege grants/revokes failed login attempts access to sensitive tables (PII financial) privileged user actions and unusual query patterns

Application database accounts should have only the minimum privileges required for their specific function: SELECT only for read-only applications INSERT/UPDATE/DELETE on specific tables for write operations no DDL permissions no GRANT permissions no access to system tables or other schemas

Encryption at rest: protects data stored on disk (TDE filesystem encryption) from physical theft/unauthorized file access. Encryption in transit: protects data moving between client and database server (TLS/SSL) from network eavesdropping. Both needed because threat model has both physical and network vectors

A security vulnerability where an attacker gains higher privileges than intended e.g. through SQL injection granting admin access exploiting stored procedure definer rights or GRANT OPTION abuse. Prevented by: least privilege principle removing unnecessary privileges auditing privilege assignments

Restricting access to specific columns rather than entire tables; implementations: (1) View-based: create views that SELECT only permitted columns. (2) Column-level privileges: GRANT SELECT(col1 col2) ON table TO user. (3) Column-level encryption: columns encrypted with keys only authorized users possess

Combining multiple non-sensitive queries or pieces of information to deduce sensitive information that is not directly accessible; e.g. if a statistical query reveals the average salary of a 1-person department that reveals the single persons salary even if individual salary queries are blocked

Tokenization: replacing sensitive data (credit card numbers) with randomly generated tokens that have no mathematical relationship to the original data; tokens are stored in a secure token vault. Encryption: mathematically transforms data using a key (can be decrypted with key). Tokenization cannot be reversed without vault access

A mathematical framework that adds calibrated noise to query results providing a formal guarantee: the probability of learning any specific fact about an individual is nearly the same whether or not that individuals data is in the database (epsilon-differential privacy)

A privacy model ensuring each record is indistinguishable from at least k-1 other records with respect to quasi-identifying attributes; limitations: vulnerable to homogeneity attacks (if all k records have same sensitive value) and background knowledge attacks

Allows the grantee to further grant the same privilege to other users; security risk: privilege can propagate uncontrollably (Alice grants to Bob WITH GRANT OPTION Bob grants to 100 other users without DBA knowledge) creating uncontrolled access expansion that is difficult to audit and revoke

An independent security layer that monitors all database traffic in real-time without relying on native DBMS audit features; benefits over native auditing: cannot be disabled by DBAs (separation of duties) lower performance impact (passive network monitoring) cross-DBMS policy enforcement real-time alerting

A systematic security assessment of database configurations and code identifying: misconfigured privileges (public grants excessive DBA accounts) weak authentication (default passwords weak policies) unpatched software (known CVEs) insecure configurations (remote root login unnecessary features enabled) SQL injection vulnerabilities in stored procedures

REVOKE privilege FROM user CASCADE: also revokes the privilege from all users who received it from that user via GRANT WITH GRANT OPTION (cascading revocation); security concern: unexpected cascade may revoke privileges from many users not intended for revocation

Post-incident analysis of database systems to reconstruct what happened: examining transaction logs (redo/undo) audit trails access logs network packet captures OS logs and potentially deleted/recovered data to determine the sequence of events identify the attacker and assess the scope of a breach

A security layer that sits between the application and the database server monitoring and filtering SQL statements in real-time based on whitelisted allowed query patterns blocking anomalous or malicious SQL - detects SQL injection unauthorized queries and policy violations

The process of categorizing data based on its sensitivity level (e.g. public internal confidential restricted/top-secret) so that appropriate security controls can be applied proportionally - critical because it determines access controls encryption requirements audit levels backup policies and regulatory compliance obligations

The process of reducing the attack surface of a database by applying security best practices: removing unnecessary features/users/sample databases changing default passwords patching known vulnerabilities disabling dangerous SQL functions restricting network access and following vendor security guidelines (e.g. CIS Benchmarks)

A security principle that requires splitting privileged operations among multiple people so no single person can perform all steps of a sensitive operation alone - prevents insider threats and requires collusion for fraud. Implemented by: separating DBA roles (application DBA vs security DBA) keeping DBAs out of application user tables using DAM tools that DBAs cannot disable

A list of the most critical security risks for web applications; key database-related vulnerabilities include: (1) Injection (SQL injection is most common) (2) Broken access control (unauthorized data access) (3) Security misconfiguration (default passwords public grants) (4) Sensitive data exposure (unencrypted PII) and (5) Insufficient logging and monitoring (no audit trail)

An approach where privacy protections are built into database systems from the beginning (by design) rather than added later (by afterthought); includes: minimizing data collection (only store what is needed) purpose limitation (data used only for stated purpose) data retention policies (auto-delete after retention period) privacy-preserving queries (differential privacy k-anonymity) and privacy impact assessments before schema changes

Stored procedure: a named block that executes logic and may return values via OUT/IN OUT parameters called with EXECUTE/CALL cannot be used in SQL expressions. Stored function: MUST return a single value via RETURN can be called from within SQL expressions (SELECT WHERE) and is side-effect restricted in SQL contexts

A compiler directive that associates a user-defined exception name with an Oracle error number allowing you to catch specific Oracle errors by name rather than using WHEN OTHERS and checking SQLCODE

Implicit cursor: automatically created by PL/SQL for single-row SELECT INTO or DML statements (attributes: SQL%ROWCOUNT SQL%FOUND SQL%NOTFOUND SQL%ISOPEN). Explicit cursor: developer-declared named cursor for multi-row queries with full control over OPEN FETCH CLOSE lifecycle

BULK COLLECT: fetches multiple rows from a cursor into a collection in a single context switch. FORALL: executes a DML statement for all elements of a collection in a single context switch. Both minimize expensive PL/SQL-to-SQL engine context switches

A pointer to a query result set that can be passed as a parameter between programs or returned from a function; advantage: the query can be determined at runtime (dynamic) and the cursor can be returned to a calling program (Java/Python) as a result set

Used to execute dynamic SQL strings built and executed at runtime allowing PL/SQL to execute DDL DML with runtime-determined table names/conditions or PL/SQL anonymous blocks not known at compile time

Associative Array (INDEX BY): unbounded sparse indexed by PLS_INTEGER or VARCHAR2 exists only in PL/SQL memory. Nested Table: initially dense can have gaps (DELETE) can be stored in database column. VARRAY: fixed maximum size always dense (no gaps) ordered can be stored in database column