# 如何善用MySQL的安全機制打造固若金湯的系統 - 杜修文

{%hackmd tGp8pt49Q2aT5cD5TFyaIA %}

> 請從這裡開始

## 資料庫可能存在的弱點
- Poor Configurations
- Set controls and change default setting
- Over Privileegd Accounts
- Privilege Policies
- Weak Access Control
- Dedicated Adminstrative Accounts
- Weak Authentication
- Strong Password Enforcement
- Weak Auditing
- Compliance & Audit Policies
- 對於 DBA 的操作需要有紀錄
- Lack of Encryption
- Data, Backup, Network Encryption
- Proper Credential & Key management
- Use mysql_config_editor, Key Vaults
- Unsecured Backups
- Encrypted Backups
- No Monitoring
- Security Monitoring, Users, Objects
- Poorly Coded Applications
- Database Firewall

在 5.6 之前的版本裝了就能直接用
使用者都用 default 大家隨便登
5.7 版後需要 initiate，會給一組一次性密碼讓你登入

## 資料庫可能會面臨的攻擊
- SQL Injection
- Prevention: DB Firewall, White List, Input Validation
- Buffer Overflow
- prevention: Frequently apply Database software updates, DB Firewall, White List, Input Validation
- Brute Force Attack
- Prevention: lock out accounts after a defined number of incorrtct attempts.
- 通常人類密碼3~5次過不了就會嘗試忘記密碼功能，不太會爆破
- Network Eavesdropping
- Prevention: Reqire SSL/TLS for all Connections and Transport
- 乖乖加密加鹽
- Malware
- Prevention: Tight Access Conrols, Limited Network IP access, Change default settings, Encryption

:::warning
:warning: MySQL 不太會出補丁，通常只出 Update，裡面通常是修復 bug 與漏洞
:::

## 資料庫安控操作
- Account / Privilege Management
- Key management ( store / rotate / backup )
- Data classification & prioritization
- Patch and update database
- 在 5.7 前，每個小版本中不太會有什麼新功能
- 沒事多更新
- Monitoring / detect security breach
- Auditing
- 留下審核/稽核紀錄
- 主動通知異常
- Remedy security breach

## 資料庫安控的方方面面

- 注意傳 DB 的時候中間的網路可不可信任
DB 有沒有好好加密

## MYSQL 認證
- Bulit in Authentication
- `user` table stores users and encrypted passwords
- X.509
- Server authenticates client certifcates
- MySQL Native , SHA 256 Password plugin
- Native uses SHA1 or plugin with SHA-256 hashing and per user-salting for user account passowrds
- MySQL Enterprise Authentication
- Microsoft Active Directory
- Linux PAMs ( Pluggable Authentication Modules )
- Support LDAP and more
- Custom Authentication

## MySQL 的增強：Password Policies
- Enforce Strong Passowrd Policies
- Password Hashing
- Password Expiration
- Password Validation Plugin
- validate_password_policy
- validate_password_dictionary_file
- 密碼字典, 若substring出現在裡面（不分大小寫）則拒絕此密碼
- valicate_password_length
- validate_password_special_char_count
- validate_password_mixed_case_count
- Authentication Plugin
- Inherits the password policies from the component
- LDAP, Windows Active Directory, etc.
- Disable accounts when not in use
- Account lockout (5.7+)
- ~~直接請怪怪的人吃水桶~~
- MySQL_native_password relies on SHA1 algorithm (peformance and security)
- Password History (8.0 NF)

## MYSQL企業版的 **認證**
-
- PAM (Pluggable ...?)
- Standard Interface
- LDAP
- Unix / Linux
- Proxy Users
- Windows
- Windows Active Directory
- Windows Native Services

## 應用和憑證-最佳實行原則
- Applications - 儘量不要共用帳戶和密碼
- (總之這邊是上面那行的英文版)
- 要支援密碼輪換
- Do not require all passwords to be changes in synchronization
- Facilitates better troubleshooting and root-cause analysis
- 可追溯密碼變更(8.0)
- 密碼的變更應該以安全且直接的方式進行
- Not embedded in your code
- Can be changed without redeploying an application
- Should never be stored in version control and must differ between environments.
- Applications should get credentials using a secure configuration methodology

## 資料庫安控的方方面面
- 授權

## MySQL 權限管理
- 持續的評估
- 稽核和評估活動
- Who -- does activity match expectation
- What -- is this it limited as expected
- When -- acts often are at odd / off peak times
- Where -- Connections should be from expected hosts
- Role (8.0 NF)
- MySQL 能簡單的控制權限和設全限制
- set account resource limits (max_user_connections, max_connection_per_hour, max_updates_per_hour)

- 例:

CREATE USER 'francis'@'localhost' IDENTIFIED BY 'frank'
WITH MAX_OUERIES_PER_HOUR 20
MAX_UPDATES_PER_HOUR 10
MAX_CONNECTIONS_PER_HOURS
MAX_USER_CONNECTION 2;

### 舉例
- `MAX_CONNECTIONS_PER_HOURS`
這個帳號同時可以有 10 條連線，若有 11 條以上則有問題，MySQL 可以直接做這個設定

## ＭySQL 授權
- Administrative Privileges
- 限制root只能從特定host連進來
- Database Privileges
- Session Limits and Object Privileges
- Fine grained controls over user privileges
- Creating altering and deleteing datbases
- Creating altering and deleting tables
- Execute INSERT, SELECT, UPADATE, DELETE queries
- Create, execute, or delete stored procedures and???
- Create ????

## MySQL 加密
- SSL / TLS Encryption
- Betewwn MySQL clients and Server
- Replication: between Master & Slave
- Data Encryption
- AES Encrypt/Decrypt
- MySQL Enterprise TDE
- Transparent Data Encryption
- Key Management(MKIP)
- MySQL Enterprise Encryption
- ???
- ???
- ???
- MySQL Enterprise Backup
- ?
> 因為時間關係我們不談了

## 保護應用端到資料庫的傳輸
- In my.cnf of the database
- Create User R???
-
- Connecting via SSL

$ mysql --ssl-ca

## 因為 Transparent Data Encryption MySQL 加了哪些功能？
- SQL
- New otion in CREATE TABLE
- ENCRYPTION="Y"
- New SQL: `Alter... ?????`
- Header跟data row分開加密
- Plugin Infrastructure
- ?????

- Keyring plugin
-

## MySQL 企業備份
- Online ...?
-
-
## MySQL Repository makes update easier
- Linux binary
- Yum
> sudo rpm ....
- apt
- SUSE
- Windows installer
- Linux

## MySQL Enterprise Monitor

> 總之如果有 slide 就看 slide 吧

## 聊天區
> 獨立一下好了
> 實務上，常更新真的會gg qaq
> 不更新要祈禱不會被入侵，更新要祈禱不會死掉，所以不少機器就乾脆不更新，省事情
> 不更新應該都是比較常見的做法吧
> 要更新真的會有很多需要注意的事情
> 總之看看 patch note 再說吧 補洞就升阿
> 他跳好快啊啊啊啊啊啊啊啊啊
> 等他給slide好了 G_G
> 啊啊啊啊啊啊
> 他會給slide嗎？
> :confused: 可...能 吧?
> 這是在考驗大家的視讀能力與手速
> 沒動力繼續打了..
> 好吧 大家慢慢等閉幕吧(X
> 接下來聽 Key note 打廣告了(X
> 大家可以去吃東西喝飲料了(X

###### tags: `COSCUP2018` `misc`