How to Secure Phone Numbers in Databases: Threat Modeling, Envelope Encryption, and High‑Performance Key Rotation

Threat Modeling

Identify three primary threats to stored mobile numbers:

Database leakage (e.g., external breach, backup exposure, development‑environment leaks)

Insider abuse (DBA full‑access, ops dump, accidental logging)

Collision attacks (unsalted hash or suffix enumeration)

Mitigations:

Encrypt mobile numbers with AES‑GCM (or SM4) using keys stored in a KMS/HSM.

Create irreversible hash indexes with per‑field salts.

Enforce zero‑trust service communication and full audit logging for any plaintext access.

Architecture Overview

Design goals:

Ciphertext can be decrypted only by an authorized backend service.

Support exact‑match and suffix queries without exposing plaintext.

Provide masked display for UI.

Keep encryption keys invisible to application code.

Enable full auditability and seamless key rotation.

Database Schema

CREATE TABLE users (
    id BIGINT PRIMARY KEY,
    mobile_ciphertext VARCHAR(128) NOT NULL,   -- AES‑GCM ciphertext
    mobile_key_version INT NOT NULL DEFAULT 1,  -- key version for rotation
    mobile_hash CHAR(64) NOT NULL,               -- exact‑match hash (salted)
    mobile_suffix_hash CHAR(64),                  -- suffix hash (salted)
    mobile_masked VARCHAR(20),                    -- e.g., 138****5678
    mobile_suffix_plain CHAR(4),                 -- optional plain suffix for fast lookup
    created_at TIMESTAMP,
    updated_at TIMESTAMP,
    INDEX idx_mobile_hash (mobile_hash),
    INDEX idx_suffix_hash (mobile_suffix_hash),
    INDEX idx_suffix_plain (mobile_suffix_plain)
);

The layered fields balance security with business usability.

Envelope Encryption Strategy

Master Key (stored in KMS/HSM)
    ↓
Data Encryption Key (DEK, cached locally ~10 min)
    ↓
AES‑GCM encrypts the mobile number → mobile_ciphertext

Backend Encryption Service (Python)

Encryption Service

from Crypto.Cipher import AES
import hashlib, base64, os, time

class MobileEncryptionService:
    def __init__(self, kms_client):
        self.kms = kms_client
        self.data_key_cache = None
        self.data_key_expire_ts = 0

    def _get_data_key(self):
        # Local cache avoids frequent KMS calls (≈99% performance gain)
        if time.time() < self.data_key_expire_ts:
            return self.data_key_cache
        data_key, key_version = self.kms.generate_data_key()
        self.data_key_cache = (data_key, key_version)
        self.data_key_expire_ts = time.time() + 600  # 10 minutes
        return data_key, key_version

    def encrypt_mobile(self, mobile: str):
        data_key, key_version = self._get_data_key()
        cipher = AES.new(data_key, AES.MODE_GCM)
        ciphertext, tag = cipher.encrypt_and_digest(mobile.encode())
        return {
            "ciphertext": base64.b64encode(cipher.nonce + tag + ciphertext).decode(),
            "key_version": key_version
        }

Search Index Generation

def create_mobile_indexes(mobile: str):
    # Exact‑match index (salted SHA‑256)
    salt_exact = os.environ["MOBILE_EXACT_SALT"].encode()
    mobile_hash = hashlib.sha256(salt_exact + mobile.encode()).hexdigest()
    # Suffix index (last 4 digits, salted)
    suffix = mobile[-4:]
    salt_suffix = os.environ["MOBILE_SUFFIX_SALT"].encode()
    suffix_hash = hashlib.sha256(salt_suffix + suffix.encode()).hexdigest()
    masked = f"{mobile[:3]}****{suffix}"
    return {
        "mobile_hash": mobile_hash,
        "suffix_hash": suffix_hash,
        "masked": masked,
        "suffix_plain": suffix
    }

Record Assembly for Insertion

def build_user_record(mobile):
    enc = encryption_service.encrypt_mobile(mobile)
    indexes = create_mobile_indexes(mobile)
    return {
        "mobile_ciphertext": enc["ciphertext"],
        "mobile_key_version": enc["key_version"],
        **indexes
    }

Query Adaptation

Exact Match Query

SELECT id, mobile_masked FROM users WHERE mobile_hash = :mobile_hash;

Suffix Query (plain)

SELECT id, mobile_masked FROM users WHERE mobile_suffix_plain = '5678';

Suffix Query (hashed, preferred)

SELECT id, mobile_masked FROM users WHERE mobile_suffix_hash = :suffix_hash;

Controlled Decryption

def get_plain_mobile(user_id):
    # Enforce approval workflow
    assert user_has_permission()
    record = db.query(
        "SELECT mobile_ciphertext, mobile_key_version FROM users WHERE id=?",
        user_id
    )
    plaintext = kms.decrypt(record.mobile_ciphertext, record.mobile_key_version)
    log_access(user_id)
    return plaintext

Key Rotation Procedure

Maintain a mobile_key_version column to track which key encrypted each row.

ALTER TABLE users ADD COLUMN mobile_key_version INT NOT NULL DEFAULT 1;

Three‑phase rotation:

Generate a new data‑key version (e.g., v2) in the KMS.

New writes automatically use the new version; existing rows remain at the old version.

Run an asynchronous re‑encryption job that scans all rows, decrypts with the old version, re‑encrypts with the new version, and updates mobile_key_version.

for row in db.scan("SELECT id, mobile_ciphertext FROM users"):
    old_plain = kms.decrypt(row.mobile_ciphertext, row.mobile_key_version)
    new_enc = kms.encrypt(old_plain, new_version)
    db.update(id=row.id, mobile_ciphertext=new_enc, mobile_key_version=new_version)

After completion, disable decryption with the old key to contain any future leakage.

Performance Optimizations

Cache DEK locally (10‑minute TTL) to avoid per‑request KMS calls.

Batch KMS requests when generating new DEKs.

Use the envelope‑encryption pattern to keep cryptographic operations fast.

Deploy the encryption service as a stateless component for horizontal scaling.

Typical throughput:

Raw AES‑GCM on a single node ≈150 k ops/sec.

With KMS round‑trip without caching ≈200‑300 ops/sec.

Local DEK cache restores performance close to raw AES speed.

Index choices: mobile_hash: B‑Tree for exact lookups. mobile_suffix_plain: Simple equality index for fast suffix matches. mobile_suffix_hash: Index required for high‑frequency suffix queries.

Auditing

CREATE TABLE mobile_access_log (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    user_id BIGINT,
    mobile_hash CHAR(64),
    accessor VARCHAR(64),
    access_type ENUM('masked','decrypt'),
    reason TEXT,
    approved_by VARCHAR(64),
    access_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_mobile_hash (mobile_hash)
);

All plaintext accesses must be recorded in this table.

Anomaly Detection

if full_mobile_queries > threshold:
    alert("Anomaly: excessive full‑mobile queries")

Similar thresholds can be set for suffix‑query spikes or unexpected decryption‑service calls.

Common Pitfalls

Omitting per‑field salts on hashes makes them vulnerable to collision attacks.

Hard‑coding encryption keys in source code defeats KMS protection.

Encrypting without searchable indexes renders the data unusable for business logic.

Logging raw mobile numbers leaks sensitive data.

Providing a decryption API without audit logging creates an untracked attack surface.

Benefits Summary

Strong security : AES‑GCM ciphertext, salted irreversible hashes, keys managed by KMS/HSM, comprehensive audit and alerting.

High availability : Supports exact and suffix queries, masked display, and risk‑control scenarios without exposing plaintext.

Operational friendliness : Built‑in key rotation, audit trails, data migration paths, and horizontal scalability across shards or multiple mobile‑number versions.