Parent : [[INDEX-CYBERSECU]]

Schéma de base de données tracker - 13.7M utilisateurs, hardening et anti-patterns

Problème

L'archive Ygg contient le schéma complet de la table users d'un tracker BitTorrent avec 13.7M utilisateurs (AUTO_INCREMENT=13720815). Ce schéma contient des anti-patterns critiques (charset latin1, colonnes text NOT NULL, pas de foreign keys) mais aussi des patterns intéressants pour la gestion d'utilisateurs à grande échelle.

Contexte archive Ygg

• Fichier : 03_DATABASES/ygg_tracker_redacted/users_schema.sql
• Engine : InnoDB, MariaDB 10.4.34
• Charset : latin1 (pas utf8mb4)
• Utilisateurs : ~13,720,815

Schéma identifié

CREATE TABLE `users` (
  `id` int(11) NOT NULL AUTO_INCREMENT,           -- ❌ int(11) = limité à 2.1M (déjà dépassé)
  `rank` smallint(6) NOT NULL DEFAULT 0,           -- 0=user, 1-3=staff/admin
  `nickname` varchar(255) NOT NULL,
  `email` varchar(255) NOT NULL,
  `pass` text NOT NULL,                             -- ❌ text NOT NULL (pas de DEFAULT)
  `salt` text DEFAULT NULL,
  `avatar` varchar(255) DEFAULT NULL,
  `age` varchar(11) NOT NULL,                      -- ❌ varchar(11) pour un age
  `gender` smallint(6) NOT NULL,
  `country` varchar(255) NOT NULL,                  -- ❌ trop large
  `profile_desc` text NOT NULL,
  `join_date` int(11) DEFAULT NULL,                 -- ❌ timestamp UNIX (pas DATETIME)
  `last_activity_date` int(11) NOT NULL DEFAULT 0,
  `last_comment_date` int(11) DEFAULT 0,
  `passkey` varchar(255) DEFAULT NULL,             -- Clé tracker
  `reset_token` varchar(40) NOT NULL,
  `notified` int(11) NOT NULL DEFAULT 0,
  `ban` smallint(6) NOT NULL DEFAULT 0,
  `ban_reason` text NOT NULL,
  `allow_porn` smallint(6) NOT NULL DEFAULT 0,
  `view_disabled_announce` smallint(6) DEFAULT 0,
  `torrent_pass` varchar(32) NOT NULL,             -- Pass pour auth tracker
  `torrent_pass_version` int(11) NOT NULL DEFAULT 0,
  `can_leech` tinyint(4) NOT NULL DEFAULT 1,         -- Permission de télécharger
  `downloaded` bigint(20) unsigned NOT NULL DEFAULT 0,
  `uploaded` bigint(20) unsigned NOT NULL DEFAULT 0,
  `download_multiplier` float NOT NULL DEFAULT 1,
  `upload_multiplier` float NOT NULL DEFAULT 1,
  `end_freeleech` int(11) NOT NULL DEFAULT 0,
  `favourites` text DEFAULT NULL,
  `last_comment_timestamp` int(11) DEFAULT 0,
  `downloads` text DEFAULT NULL,
  `is_valid` smallint(6) DEFAULT 0,                   -- Validation email
  `token_validation` varchar(255) DEFAULT NULL,
  `notifications` int(11) DEFAULT 0,
  `validations` text DEFAULT NULL,
  `flagged` int(11) DEFAULT 0,                        -- Marqué suspect
  `sanctions` text DEFAULT NULL,                      -- Historique sanctions
  `forum_id` int(11) DEFAULT 0,
  `settings` text DEFAULT NULL,
  `ignored` text DEFAULT NULL,
  `count_pm` int(11) DEFAULT 0,
  `unread_pm` int(11) DEFAULT 0,
  `status_auto_messages` text DEFAULT NULL,
  `unread_auto_pm` int(11) NOT NULL DEFAULT 0,
  `tokens` int(11) NOT NULL DEFAULT 0,                -- Tokens de upload
  `is_donator` smallint(6) DEFAULT 0,
  `tracker_id` smallint(6) DEFAULT -1,                -- Multi-tracker
  `adult_content_banned` tinyint(1) NOT NULL DEFAULT 0,
  `premium_until` int(11) DEFAULT 0,
  PRIMARY KEY (`id`),
  UNIQUE KEY `nickname` (`nickname`),
  UNIQUE KEY `email` (`email`),
  KEY `rank_idx` (`rank`),
  KEY `stats_index` (`uploaded`,`downloaded`),
  KEY `token_validation` (`token_validation`),
  KEY `reset_token` (`reset_token`)
) ENGINE=InnoDB AUTO_INCREMENT=13720815 DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci;

Failles du schéma

1. CHARSET latin1 (ligne 83)

DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci

Problème : Pas de support UTF-8 (caractères spéciaux, emojis). Peut causer des problèmes de stockage pour les noms internationaux.

2. id int(11) avec AUTO_INCREMENT=13720815

Problème : int(11) max = 2,147,483,647. Avec 13.7M et une croissance rapide, risque d'overflow.

3. pass text NOT NULL

Problème : Mot de passe stocké en text (hash?). Pas de contrainte de longueur.

4. age varchar(11) NOT NULL

Problème : Age en varchar(11)? Devrait être DATE ou TINYINT.

5. Pas de FOREIGN KEYS

Problème : Aucune contrainte d'intégrité référentielle. Les suppressions en cascade doivent être gérées manuellement.

6. settings, validations, sanctions en text

Problème : Stockage JSON dans des colonnes text (pas JSON type). Pas de validation de structure.

7. reset_token varchar(40) NOT NULL avec KEY

Problème : Token de reset indexé = risque d'énumération. Devrait être unique et temporaire.

Solution protectif — Schéma de tracker sécurisé

1. Schéma modernisé

-- users_secure.sql — Schéma modernisé et sécurisé
CREATE TABLE `users` (
  `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,   -- ✅ bigint pour 18M+ users
  `rank` tinyint(3) UNSIGNED NOT NULL DEFAULT 0,        -- ✅ tinyint suffisant (0-255)
  `nickname` varchar(50) NOT NULL,                      -- ✅ Limité à 50 chars
  `email` varchar(255) NOT NULL,
  `password_hash` varchar(255) NOT NULL,                -- ✅ Nom explicite
  `salt` varchar(255) DEFAULT NULL,                   -- ✅ varchar au lieu de text
  `avatar_url` varchar(500) DEFAULT NULL,               -- ✅ Nom explicite
  `birth_date` date DEFAULT NULL,                       -- ✅ DATE au lieu de varchar(11)
  `gender` tinyint(1) DEFAULT NULL,                   -- ✅ NULL autorisé
  `country_code` char(2) DEFAULT NULL,                  -- ✅ ISO 3166-1 alpha-2
  `profile_desc` text DEFAULT NULL,                     -- ✅ NULL autorisé
  `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP, -- ✅ DATETIME
  `updated_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `last_activity_at` datetime DEFAULT NULL,
  `passkey` char(32) DEFAULT NULL,                      -- ✅ char(32) = hex fixed
  `reset_token_hash` char(64) DEFAULT NULL,            -- ✅ Hash du token, pas le token
  `reset_token_expires` datetime DEFAULT NULL,
  `is_notified` tinyint(1) NOT NULL DEFAULT 0,
  `ban_status` tinyint(1) NOT NULL DEFAULT 0,           -- ✅ 0=active, 1=banned, 2=suspended
  `ban_reason` text DEFAULT NULL,                      -- ✅ NULL autorisé
  `ban_expires` datetime DEFAULT NULL,
  `can_access_adult` tinyint(1) NOT NULL DEFAULT 0,
  `view_disabled_announce` tinyint(1) NOT NULL DEFAULT 0,
  `torrent_pass` char(32) NOT NULL,                     -- ✅ char(32) fixed
  `torrent_pass_version` int(11) NOT NULL DEFAULT 0,
  `can_leech` tinyint(1) NOT NULL DEFAULT 1,
  `bytes_downloaded` bigint(20) UNSIGNED NOT NULL DEFAULT 0,
  `bytes_uploaded` bigint(20) UNSIGNED NOT NULL DEFAULT 0,
  `download_multiplier` decimal(3,2) NOT NULL DEFAULT 1.00, -- ✅ decimal au lieu de float
  `upload_multiplier` decimal(3,2) NOT NULL DEFAULT 1.00,
  `end_freeleech_at` datetime DEFAULT NULL,
  `favourites_json` json DEFAULT NULL,                  -- ✅ Type JSON natif MariaDB 10.2+
  `last_comment_at` datetime DEFAULT NULL,
  `downloads_json` json DEFAULT NULL,
  `is_valid` tinyint(1) NOT NULL DEFAULT 0,
  `email_validation_token` char(64) DEFAULT NULL,
  `email_validation_sent_at` datetime DEFAULT NULL,
  `notification_count` int(11) NOT NULL DEFAULT 0,
  `validations_json` json DEFAULT NULL,
  `is_flagged` tinyint(1) NOT NULL DEFAULT 0,
  `sanctions_json` json DEFAULT NULL,
  `forum_id` int(11) DEFAULT NULL,
  `settings_json` json DEFAULT NULL,
  `ignored_users_json` json DEFAULT NULL,
  `pm_count` int(11) NOT NULL DEFAULT 0,
  `unread_pm_count` int(11) NOT NULL DEFAULT 0,
  `auto_messages_json` json DEFAULT NULL,
  `unread_auto_pm_count` int(11) NOT NULL DEFAULT 0,
  `upload_tokens` int(11) NOT NULL DEFAULT 0,
  `is_donator` tinyint(1) NOT NULL DEFAULT 0,
  `tracker_id` smallint(6) DEFAULT -1,
  `adult_content_banned` tinyint(1) NOT NULL DEFAULT 0,
  `premium_until` datetime DEFAULT NULL,
  `two_factor_secret` varchar(255) DEFAULT NULL,       -- ✅ 2FA
  `two_factor_enabled` tinyint(1) NOT NULL DEFAULT 0,
  `failed_login_count` tinyint(3) UNSIGNED NOT NULL DEFAULT 0,
  `locked_until` datetime DEFAULT NULL,                -- ✅ Rate limiting login
  `last_login_ip` varbinary(16) DEFAULT NULL,          -- ✅ IPv4/IPv6 binaire
  `last_login_at` datetime DEFAULT NULL,
  `created_from_ip` varbinary(16) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_nickname` (`nickname`),
  UNIQUE KEY `uk_email` (`email`),
  KEY `idx_rank` (`rank`),
  KEY `idx_stats` (`bytes_uploaded`, `bytes_downloaded`),
  KEY `idx_email_validation` (`email_validation_token`),
  KEY `idx_reset_token` (`reset_token_hash`),
  KEY `idx_last_activity` (`last_activity_at`),
  KEY `idx_created_at` (`created_at`),
  KEY `idx_is_valid` (`is_valid`),
  KEY `idx_flagged` (`is_flagged`)
) ENGINE=InnoDB AUTO_INCREMENT=13720815 
  DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci   -- ✅ UTF-8
  ROW_FORMAT=COMPRESSED                                -- ✅ Compression InnoDB
  KEY_BLOCK_SIZE=8;

2. Table des sanctions (séparation des responsabilités)

CREATE TABLE `user_sanctions` (
  `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
  `user_id` bigint(20) UNSIGNED NOT NULL,
  `type` enum('warning','ban','mute','restrict_download') NOT NULL,
  `reason` text NOT NULL,
  `created_by` bigint(20) UNSIGNED NOT NULL,
  `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `expires_at` datetime DEFAULT NULL,
  `revoked_at` datetime DEFAULT NULL,
  `revoked_by` bigint(20) UNSIGNED DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_user` (`user_id`),
  KEY `idx_expires` (`expires_at`),
  FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE,
  FOREIGN KEY (`created_by`) REFERENCES `users` (`id`) ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

3. Table des sessions (pas dans users)

CREATE TABLE `user_sessions` (
  `id` char(64) NOT NULL,                           -- JWT jti ou session ID
  `user_id` bigint(20) UNSIGNED NOT NULL,
  `ip_address` varbinary(16) NOT NULL,
  `user_agent_hash` char(64) NOT NULL,               -- Hash de l'UA
  `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `last_activity_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `expires_at` datetime NOT NULL,
  `is_revoked` tinyint(1) NOT NULL DEFAULT 0,
  `revoked_at` datetime DEFAULT NULL,
  `device_fingerprint` char(64) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_user` (`user_id`),
  KEY `idx_expires` (`expires_at`),
  FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

4. Audit log

CREATE TABLE `audit_log` (
  `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
  `user_id` bigint(20) UNSIGNED DEFAULT NULL,
  `action` varchar(100) NOT NULL,
  `entity_type` varchar(50) NOT NULL,
  `entity_id` varchar(255) DEFAULT NULL,
  `old_values` json DEFAULT NULL,
  `new_values` json DEFAULT NULL,
  `ip_address` varbinary(16) NOT NULL,
  `user_agent` varchar(500) DEFAULT NULL,
  `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_user` (`user_id`),
  KEY `idx_action` (`action`),
  KEY `idx_created` (`created_at`),
  KEY `idx_entity` (`entity_type`, `entity_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
PARTITION BY RANGE (YEAR(created_at)) (
  PARTITION p2025 VALUES LESS THAN (2026),
  PARTITION p2026 VALUES LESS THAN (2027),
  PARTITION p_future VALUES LESS THAN MAXVALUE
);

Checklist de validation

• id en bigint UNSIGNED pour >2M utilisateurs
• Charset utf8mb4 (pas latin1)
• Types JSON natifs pour les données structurées
• NOT NULL uniquement sur les champs obligatoires
• Foreign keys pour l'intégrité référentielle
• Index sur les champs de recherche fréquents
• Partitions sur les tables de log
• Row compression pour les tables volumineuses
• 2FA (two_factor_secret) intégré au schéma
• Rate limiting login (failed_login_count, locked_until)
• IP stockée en binaire (varbinary(16)) pour IPv4/IPv6
• Tokens hashés (pas stockés en clair)
• Table des sessions séparée (pas dans users)
• Audit log avec partitions temporelles

Anti-pattern associé

• KNOW-ANT-012 — Tracker database schema with text NOT NULL and no FK

Références

• Archive Ygg : 03_DATABASES/ygg_tracker_redacted/users_schema.sql
• MariaDB : JSON Data Type (10.2+)
• OWASP : Database Security