Get the Preplance app for a seamless learning experience. Practice offline, get daily streaks, and stay ahead with real-time interview updates.
Get it on
Google Play
4.9/5 Rating on Store
SQL & Databases · Question Set
Security, Backup & Recovery (DBA Essentials) interview questions for placements and exams.
Questions
14
Included in this set
Subject
SQL & Databases
Explore more sets
Difficulty
Mixed
Level of this set
Go through each question and its explanation. Use this set as a focused practice pack for SQL & Databases.
Least privilege shrinks blast radius. If a credential leaks, the attacker is limited to the granted actions. Start with read-only and add specific INSERT, UPDATE, or EXECUTE rights as needed. Prefer role-based grants over per-user grants.
CREATE ROLE app_read; GRANT SELECT ON ALL TABLES IN SCHEMA public TO app_read;
For complete preparation, combine this set with full subject-wise practice for SQL & Databases. You can also explore other subjects and sets from the links below.
Mask or anonymize in the database before export or restore. Replace direct identifiers (email, phone, name) with deterministic but fake values so joins and uniqueness still work. Randomize quasi-identifiers and dates within safe bands to preserve distribution while breaking re-identification. Automate the process as a repeatable pipeline. Store the masking logic in version control, run it as part of backup-to-test jobs, and restrict access to the pre-masked dump only. Validate that constraints and key relationships remain intact after masking.
UPDATE users
SET email = CONCAT('user', id, '@example.test'),
phone = LPAD(id::text, 10, '0');Backups can be corrupt, incomplete, or incompatible. A restore is the proof that recovery works. Without tests, failures appear during incidents when time is scarce. Automate periodic restores to a sandbox, replay logs to a target timestamp, and run canary queries and row-count checks. Compare checksums or counts against baselines, and alert if validation fails. Record RTO and update runbooks.
1) Restore latest full 2) Replay logs to T 3) run: SELECT COUNT(*) FROM critical_tables; -- compare to baseline
GRANT gives a principal rights on objects (tables, views, procedures). REVOKE removes those rights. Grant to roles, then attach users to roles for clean, auditable access.
GRANT SELECT, INSERT ON orders TO app_writer; REVOKE INSERT ON orders FROM app_writer;
Parameters keep code and data separate. The engine treats the parameter as a literal, not executable SQL. Combine with allow-list validation and least-privilege accounts to layer defenses.
SELECT * FROM users WHERE email = $1; -- bind at driver
RLS adds a row predicate evaluated per user or role. It enables multi-tenant isolation inside a single table. Keep predicates SARGable and index the filtering columns to avoid slow plans.
ALTER TABLE invoices ENABLE ROW LEVEL SECURITY;
CREATE POLICY p_tenant ON invoices
USING (tenant_id = current_setting('app.tenant')::int);Encrypt at rest to protect media theft. Use TLS in transit to protect the wire. For highly sensitive fields, consider app-level encryption or tokenization with secure key management and rotation.
ssl = on -- enable TLS per engine -- TDE or disk encryption configured at server/storage layer
Authn answers “who are you.” Authz answers “what can you access.” Use centralized roles and scoped object permissions to enforce authorization consistently.
CREATE ROLE reporting; GRANT SELECT ON SCHEMA analytics TO reporting;
Grant SELECT on a view while denying base tables. This hides sensitive columns, encodes business rules, and narrows the surface area for callers.
CREATE VIEW v_orders_public AS SELECT id, created_at, total FROM orders WHERE status <> 'DELETED';
A good audit entry ties user, timestamp, target, and the change. Use append-only audit tables or built-in extensions. Keep the path tamper-evident and searchable.
INSERT INTO audit_log(actor, action, table_name, row_id, old_val, new_val) VALUES (current_user, 'UPDATE', 'orders', :id, :old, :new);
A full backup gives you a base. Archiving WAL or transaction logs lets you replay to a chosen timestamp. Practice restores so you can execute under stress.
# Concept base_full.tar + wal_archive/0000... -> recover to '2025-10-15 10:30:00'
RPO is tolerated data loss. It drives log backup cadence and replication mode. A near-zero RPO needs synchronous replication or continuous log shipping. RTO is tolerated downtime. It drives automation, hot standbys, and failover drills. Short RTO favors pre-provisioned replicas, scripted role swaps, and frequent restore testing.
Policy: RPO 5 min (logs every 5 min); RTO 15 min (warm standby + automated failover)
Asynchronous replicas are great for read scale and reporting. They do not slow writes, but can lag. On failover you may lose recent commits equal to the lag. They suit relaxed RPO. Synchronous replicas acknowledge commits only after a replica confirms. This reduces or eliminates data loss at the cost of higher write latency and tighter coupling. Choose per workload, per region, and document expected RPO/RTO.
-- Concept: sync commit waits for replica ACK; async returns after local log flush
Allow only whitelisted identifiers (columns, sort keys). Bind user values as parameters so the engine can reuse plans and injection is blocked. Expose a wrapper stored procedure with EXECUTE permissions, not direct table access. Log the final statement and role for audit. Add resource guards such as row limits and timeouts, and index the columns your dynamic filters hit most.
-- SQL Server sample DECLARE @sql nvarchar(max)=N'SELECT col_list FROM t WHERE status=@s'; EXEC sp_executesql @sql, N'@s nvarchar(10)', @s=@in_status;