DBAtlas · Atlas d'exploitation des bases de données

Durcissement de la sécurité : bases SQL (Oracle, PostgreSQL, MySQL, SQL Server)

Symptôme

Objectif : durcir les bases relationnelles selon les recommandations CIS : comptes par défaut, politique de mots de passe robuste, moindre privilège, restriction réseau, TLS, audit et chiffrement (TDE).

1. Supprimez ou verrouillez les comptes par défaut/inutiles, supprimez les schémas d'exemple

Les comptes par défaut et les schémas d'exemple fournis avec l'installation constituent une surface d'attaque bien connue. Verrouillez ou supprimez ceux qui ne servent pas ; renommez les comptes de haut niveau/de service.

Oracle

-- Lock unused default accounts and expire their passwords:
ALTER USER SCOTT ACCOUNT LOCK PASSWORD EXPIRE;
ALTER USER HR ACCOUNT LOCK PASSWORD EXPIRE;
-- Audit locked/open accounts:
SELECT username, account_status FROM dba_users ORDER BY account_status;
-- Remove sample schemas: $ORACLE_HOME/demo/schema/drop_sch.sql

PostgreSQL

-- Keep the default superuser 'postgres' role; do NOT drop/rename it.
-- Audit roles:
SELECT rolname, rolsuper, rolcanlogin FROM pg_roles ORDER BY rolsuper DESC;
-- Remove unneeded login roles:
DROP ROLE IF EXISTS test_user;
-- Drop sample/test databases: DROP DATABASE IF EXISTS test;

MySQL

# Interactive hardening wizard (removes anonymous users, the test DB, remote root):
mysql_secure_installation
-- Manual: remove anonymous and test accounts:
DROP USER IF EXISTS ''@'localhost';
DROP USER IF EXISTS ''@'%';
DROP DATABASE IF EXISTS test;
DELETE FROM mysql.db WHERE Db LIKE 'test%'; FLUSH PRIVILEGES;

SQL Server

-- Disable and rename the 'sa' account:
ALTER LOGIN sa DISABLE;
ALTER LOGIN sa WITH NAME = [sa_disabled_acct];
-- Restrict the guest user — only in USER databases
-- (guest must stay enabled in master, tempdb and msdb; disabling it in tempdb/msdb causes problems):
USE appdb;
REVOKE CONNECT FROM guest;
-- Remove sample DBs: DROP DATABASE AdventureWorks;

2. Imposez une politique de mots de passe robuste et leur expiration

Les mots de passe faibles ou jamais changés sont le point d'entrée le plus courant. Imposez des limites de tentatives de connexion, une complexité et une durée de vie des mots de passe ; stockez les mots de passe avec un hachage robuste.

Oracle

-- Attempt limit, lifetime and complexity via a profile:
CREATE PROFILE app_profile LIMIT
  FAILED_LOGIN_ATTEMPTS 5
  PASSWORD_LIFE_TIME 90
  PASSWORD_REUSE_MAX 10
  PASSWORD_VERIFY_FUNCTION ora12c_verify_function;
ALTER USER app_user PROFILE app_profile;

PostgreSQL

-- Strong hash algorithm (SCRAM):
SHOW password_encryption;              -- should be 'scram-sha-256'
ALTER SYSTEM SET password_encryption = 'scram-sha-256';
-- Load the passwordcheck extension for complexity:
-- shared_preload_libraries = 'passwordcheck'
-- Lifetime: ALTER ROLE app_user VALID UNTIL '2026-12-31';

MySQL

-- Install the validate_password component and set the policy:
INSTALL COMPONENT 'file://component_validate_password';
SET PERSIST validate_password.policy = 'STRONG';
SET PERSIST validate_password.length = 14;
-- Attempt limit and lifetime:
ALTER USER app_user PASSWORD EXPIRE INTERVAL 90 DAY
  FAILED_LOGIN_ATTEMPTS 5 PASSWORD_LOCK_TIME 1;

SQL Server

-- Enforce the Windows password policy and expiration on the login:
ALTER LOGIN app_user WITH CHECK_POLICY = ON, CHECK_EXPIRATION = ON;
-- Audit policy/expiration status:
SELECT name, is_policy_checked, is_expiration_checked
FROM sys.sql_logins;

3. Moindre privilège : révoquez les privilèges PUBLIC/étendus, accordez des privilèges par rôle

Les privilèges étendus accordés à tous (PUBLIC) facilitent les mouvements latéraux. Révoquez-les, créez des rôles et n'accordez que le nécessaire.

Oracle

-- Revoke dangerous PUBLIC privileges:
REVOKE EXECUTE ON UTL_HTTP FROM PUBLIC;
REVOKE EXECUTE ON UTL_FILE FROM PUBLIC;
REVOKE EXECUTE ON DBMS_SCHEDULER FROM PUBLIC;
-- Role-based privileges:
CREATE ROLE app_read;
GRANT SELECT ON app.orders TO app_read;
GRANT app_read TO app_user;

PostgreSQL

-- Revoke the default PUBLIC privileges on the schema:
REVOKE ALL ON DATABASE appdb FROM PUBLIC;
REVOKE CREATE ON SCHEMA public FROM PUBLIC;
-- Role-based, read-only access:
CREATE ROLE app_read NOLOGIN;
GRANT USAGE ON SCHEMA app TO app_read;
GRANT SELECT ON ALL TABLES IN SCHEMA app TO app_read;
GRANT app_read TO app_user;

MySQL

-- Audit and revoke broad global privileges:
SHOW GRANTS FOR app_user;
REVOKE ALL PRIVILEGES, GRANT OPTION FROM app_user;
-- Role-based, object-level privileges (MySQL 8+):
CREATE ROLE app_read;
GRANT SELECT ON appdb.* TO app_read;
GRANT app_read TO app_user;
SET DEFAULT ROLE app_read TO app_user;

SQL Server

-- Revoke excessive privileges granted to the public role:
REVOKE SELECT ON SCHEMA::dbo FROM public;
-- Role-based privileges:
CREATE ROLE app_read;
GRANT SELECT ON SCHEMA::app TO app_read;
ALTER ROLE app_read ADD MEMBER app_user;
-- At server level: limit fixed server roles (sysadmin).

4. Restreignez le réseau/l'accès : seuls les clients connus peuvent se connecter

Évitez que la base écoute ouvertement le monde entier. Restreignez l'adresse d'écoute, appliquez des règles d'accès par hôte/IP et limitez le port avec un pare-feu.

Oracle

# listener.ora / sqlnet.ora — valid node checking:
TCP.VALIDNODE_CHECKING = YES
TCP.INVITED_NODES = (10.0.0.5, 10.0.0.6)
TCP.EXCLUDED_NODES = (0.0.0.0/0)
# After the change: lsnrctl reload

PostgreSQL

# postgresql.conf — listen only on specific interfaces:
listen_addresses = '10.0.0.10,localhost'
# pg_hba.conf — host/IP + strong authentication:
hostssl appdb app_user 10.0.0.0/24 scram-sha-256
host    all      all       0.0.0.0/0      reject
# Reload: SELECT pg_reload_conf();

MySQL

# my.cnf — bind only to a specific interface:
[mysqld]
bind-address = 10.0.0.10
-- Host-based user (only from the application server):
CREATE USER 'app_user'@'10.0.0.5'
  IDENTIFIED BY '****' REQUIRE SSL;
-- Never give remote root the '%' host.

SQL Server

# Limit the TCP port with the firewall (application subnet only):
New-NetFirewallRule -DisplayName 'SQL-1433' -Direction Inbound `
  -Protocol TCP -LocalPort 1433 -RemoteAddress 10.0.0.0/24 -Action Allow
-- Audit listening addresses:
SELECT * FROM sys.dm_tcp_listener_states;
-- Disable the SQL Browser service if not used.

5. Imposez TLS/les connexions chiffrées

Le trafic réseau est exposé à l'écoute passive. Configurez un certificat serveur et imposez aux clients une connexion chiffrée.

Oracle

# sqlnet.ora — TCPS/TLS (server certificate in the wallet):
WALLET_LOCATION = (SOURCE=(METHOD=FILE)(METHOD_DATA=(DIRECTORY=/etc/oracle/wallet)))
SSL_CLIENT_AUTHENTICATION = FALSE
SSL_VERSION = 1.2
# Listen only on TCPS (port 2484) in listener.ora. Note: SQLNET.ENCRYPTION_SERVER is Native Network Encryption, not TLS.

PostgreSQL

# postgresql.conf:
ssl = on
ssl_cert_file = 'server.crt'
ssl_key_file  = 'server.key'
ssl_min_protocol_version = 'TLSv1.2'
# Use 'hostssl' lines in pg_hba.conf; reject unencrypted connections over plain 'host'.

MySQL

# my.cnf:
[mysqld]
require_secure_transport = ON
ssl_cert = /etc/mysql/server-cert.pem
ssl_key  = /etc/mysql/server-key.pem
ssl_ca   = /etc/mysql/ca.pem
-- At user level: ALTER USER app_user REQUIRE SSL;

SQL Server

-- Force connection encryption (SQL Server Configuration Manager):
-- Protocols for MSSQLSERVER > Properties > Force Encryption = Yes
-- Assign a certificate; then restart the service.
-- Check the status:
SELECT session_id, encrypt_option FROM sys.dm_exec_connections;

6. Activez l'audit : rendez visible qui a fait quoi

Une piste d'audit est indispensable pour surveiller les accès privilégiés et les activités suspectes. Journalisez les connexions, les changements de privilèges et les accès aux objets sensibles.

Oracle

-- Create and enable a policy with Unified Auditing:
CREATE AUDIT POLICY sec_pol
  ACTIONS LOGON, LOGOFF, ALTER USER, GRANT, CREATE USER, DROP USER;
AUDIT POLICY sec_pol;
-- View the records:
SELECT * FROM unified_audit_trail ORDER BY event_timestamp DESC;

PostgreSQL

# With the pgaudit extension:
# postgresql.conf:
shared_preload_libraries = 'pgaudit'
pgaudit.log = 'ddl, role, write'
-- Create the extension:
CREATE EXTENSION pgaudit;
-- log_connections = on and log_disconnections = on should also be enabled.

MySQL

-- Load the Enterprise/Percona audit_log plugin:
INSTALL PLUGIN audit_log SONAME 'audit_log.so';
SET PERSIST audit_log_policy = 'ALL';
-- MariaDB: server_audit plugin
-- INSTALL SONAME 'server_audit'; SET GLOBAL server_audit_logging=ON;

SQL Server

-- Create and start a Server Audit:
CREATE SERVER AUDIT sec_audit
  TO FILE (FILEPATH = 'C:\SQLAudit\');
ALTER SERVER AUDIT sec_audit WITH (STATE = ON);
CREATE SERVER AUDIT SPECIFICATION sec_spec
  FOR SERVER AUDIT sec_audit
  ADD (FAILED_LOGIN_GROUP), ADD (DATABASE_ROLE_MEMBER_CHANGE_GROUP)
  WITH (STATE = ON);

7. Appliquez le chiffrement des données (at rest / TDE)

En cas de vol d'un disque, d'une sauvegarde ou d'un fichier, les données doivent être illisibles. Activez le chiffrement des données au repos (TDE) au niveau colonne/tablespace/base.

Oracle

-- Transparent Data Encryption (keystore + tablespace):
ADMINISTER KEY MANAGEMENT CREATE KEYSTORE '/etc/oracle/wallet'
  IDENTIFIED BY "WalletPwd1";
ADMINISTER KEY MANAGEMENT SET KEY IDENTIFIED BY "WalletPwd1" WITH BACKUP;
CREATE TABLESPACE sec_ts DATAFILE '/u01/sec01.dbf' SIZE 100M
  ENCRYPTION USING 'AES256' DEFAULT STORAGE(ENCRYPT);

PostgreSQL

-- There is no built-in block-level TDE; two common approaches:
-- 1) Column-level pgcrypto:
CREATE EXTENSION pgcrypto;
INSERT INTO t(ssn) VALUES (pgp_sym_encrypt('123-45', 'my_key'));
SELECT pgp_sym_decrypt(ssn, 'my_key') FROM t;
-- 2) File system/block encryption (LUKS) or an EDB/Cybertec TDE build.

MySQL

# InnoDB tablespace encryption — keyring COMPONENT (8.0.24+; the keyring_file plugin was removed in 8.4)
# 1) Global manifest in the mysqld binary directory: mysqld.my
{ "components": "file://component_keyring_file" }
# 2) In plugin_dir: component_keyring_file.cnf
{ "path": "/var/lib/mysql-keyring/component_keyring_file", "read_only": false }
# 3) Restart the server; verify:
-- SELECT * FROM performance_schema.keyring_component_status;
-- Encrypt the table:
ALTER TABLE app.orders ENCRYPTION = 'Y';
-- For new tables: SET PERSIST default_table_encryption = ON;
# Before 8.0.24 (8.0 only): early-plugin-load = keyring_file.so + keyring_file_data
# WARNING: back up the keyring file SEPARATELY from the data directory and securely;
# if the keyring is lost, encrypted tables and their backups become permanently unreadable.

SQL Server

-- Transparent Data Encryption (DEK + certificate) — the key and certificate must be in master:
USE master;
CREATE MASTER KEY ENCRYPTION BY PASSWORD = '<StrongPassword1>';
CREATE CERTIFICATE tde_cert WITH SUBJECT = 'TDE Cert';
-- MANDATORY: back up the certificate with its private key immediately and store it off the server.
-- Without this backup, if the server is lost the encrypted DB and its backups CANNOT be restored.
BACKUP CERTIFICATE tde_cert TO FILE = 'D:\secure\tde_cert.cer'
  WITH PRIVATE KEY (FILE = 'D:\secure\tde_cert.pvk',
    ENCRYPTION BY PASSWORD = '<CertBackupPassword1>');
USE appdb;
CREATE DATABASE ENCRYPTION KEY
  WITH ALGORITHM = AES_256 ENCRYPTION BY SERVER CERTIFICATE tde_cert;
ALTER DATABASE appdb SET ENCRYPTION ON;
-- Progress: SELECT DB_NAME(database_id), encryption_state, percent_complete FROM sys.dm_database_encryption_keys;

8. Maintenez les patchs à jour et désactivez les fonctionnalités inutiles

Les vulnérabilités connues sont le risque le plus exploité. Appliquez régulièrement les patchs de sécurité ; désactivez les protocoles, plugins et fonctionnalités inutilisés (réduisez la surface d'attaque).

Oracle

-- Check the installed version and patches:
SELECT version_full FROM v$instance;
-- Apply the quarterly Critical Patch Update (CPU): OPatch/opatchauto
-- Remove unused components (e.g. XML DB, APEX) and
-- disable the external procedure (extproc) listener.

PostgreSQL

# Check the version and minor patch level:
SELECT version();
# Upgrade to the current minor release with the package manager (e.g. latest 16.x).
# Remove unused extensions: DROP EXTENSION IF EXISTS ...;
# postgresql.conf: do not load unused language/PL extensions.

MySQL

-- Check the version: SELECT VERSION();
-- Upgrade to the LTS/security release (8.0.x/8.4 LTS).
-- Disable local file reading:
SET PERSIST local_infile = OFF;
-- Remove unused plugins: UNINSTALL PLUGIN ...;

SQL Server

-- Check the product version/patch level:
SELECT SERVERPROPERTY('ProductVersion'), SERVERPROPERTY('ProductLevel');
-- Apply the latest Cumulative Update (CU).
-- Disable unused features (with sp_configure):
EXEC sp_configure 'xp_cmdshell', 0; RECONFIGURE;
EXEC sp_configure 'Ole Automation Procedures', 0; RECONFIGURE;
Ouvrir dans DBAtlas →