Усиление безопасности: SQL-базы данных (Oracle, PostgreSQL, MySQL, SQL Server)

Симптом

Цель: усилить защиту реляционных баз данных в стиле CIS — учётные записи по умолчанию, строгая парольная политика, минимальные привилегии, сетевые ограничения, TLS, аудит и шифрование (TDE).

1. Удалите или заблокируйте стандартные/ненужные учётные записи, удалите демонстрационные схемы

Учётные записи по умолчанию и демонстрационные схемы, поставляемые с установкой, — хорошо известная поверхность атаки. Заблокируйте или удалите неиспользуемые; переименуйте учётные записи верхнего уровня/служебные.

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. Введите строгую парольную политику и срок действия паролей

Слабые или никогда не меняющиеся пароли — самая распространённая точка входа. Ограничьте число попыток входа, задайте сложность и срок действия паролей; храните пароли со стойким хешем.

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. Минимальные привилегии: отзовите PUBLIC/широкие привилегии, выдавайте привилегии через роли

Широкие привилегии, выданные всем (PUBLIC), облегчают горизонтальное перемещение. Отзовите их, создайте роли и выдавайте только необходимое.

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. Ограничьте сеть/доступ — подключаться могут только известные клиенты

Не допускайте, чтобы база данных была открыта для всего мира. Ограничьте адрес прослушивания, примените правила доступа по хосту/IP и ограничьте порт межсетевым экраном.

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. Требуйте TLS/зашифрованные подключения

Сетевой трафик уязвим для пассивного перехвата. Настройте сертификат сервера и требуйте от клиентов зашифрованных подключений.

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. Включите аудит — сделайте видимым, кто что сделал

Журнал аудита необходим для контроля привилегированного доступа и подозрительной активности. Регистрируйте входы, изменения привилегий и доступ к чувствительным объектам.

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. Примените шифрование данных (at rest / TDE)

При краже диска, бэкапа или файла данные должны оставаться нечитаемыми. Включите шифрование хранимых данных (TDE) на уровне столбца/табличного пространства/базы данных.

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. Своевременно устанавливайте патчи и отключайте ненужные функции

Известные уязвимости — наиболее эксплуатируемый риск. Регулярно устанавливайте патчи безопасности; отключайте неиспользуемые протоколы, плагины и функции (сокращайте поверхность атаки).

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;

Открыть в DBAtlas →