owned this note   changed 7 years ago  
Linked with GitHub

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

歡迎來到 https://hackmd.io/c/COSCUP2018 共筆

Image Not Showing Possible Reasons
  • The image file may be corrupted
  • The server hosting the image is unavailable
  • The image path is incorrect
  • The image format is not supported
Learn More →

點擊本頁上方的 開始用 Markdown 一起寫筆記！
手機版請點選上方 按鈕展開議程列表。

請從這裡開始

資料庫可能存在的弱點

  • 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

Image Not Showing Possible Reasons
  • The image file may be corrupted
  • The server hosting the image is unavailable
  • The image path is incorrect
  • The image format is not supported
Learn More →
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嗎？

Image Not Showing Possible Reasons
  • The image file may be corrupted
  • The server hosting the image is unavailable
  • The image path is incorrect
  • The image format is not supported
Learn More →
可…能 吧?
這是在考驗大家的視讀能力與手速
沒動力繼續打了..
好吧 大家慢慢等閉幕吧(X
接下來聽 Key note 打廣告了(X
大家可以去吃東西喝飲料了(X

tags: COSCUP2018 misc
Select a repo