보안 강화: 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)에게 부여된 광범위 권한은 lateral movement를 쉽게 만든다. 이를 회수하고 역할을 만들어 필요한 것만 부여한다.

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. 네트워크/접근을 제한한다 — 알려진 클라이언트만 연결 허용

데이터베이스가 전 세계에 열려 listen하지 않도록 한다. listen 주소를 제한하고, 호스트/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에서 열기 →