MySQL 透過的暗号化とSSLを使ってみた

Transcript

1. ಁաత҉߸Խ
   ͱ
   44-
   ࢖ͬͯΈͨ 
   ೔ຊ.Z42-Ϣʔβձ
   .Z/"
   !NJUB
2. ͜Μʹͪ͸ʂ w
   
   
   
   
   
   
   
   
   
   !NJUB
   w Ͳ͔ͬͷձࣾͷΦϯϓϨ
   .Z42-
   %#"
   w ̌ࡀࣇͷӡ༻࢝Ί·ͨ͠
3. ຊ೔ͷ಺༰ w*OOP%#
   5BCMFTQBDF
   &ODSZQUJPO
   ͱ͸
   w*OOP%#
   5BCMFTQBDF
   &ODSZQUJPO
   ͷ
   
   Ͱͷมߋ఺
   w44-5-4ͱ͸
   w.Z42-
   ʹ͓͚Δ44-ػೳ
   w44-
   
   Ͱͷมߋ఺
   wূ໌ॻ࡞੒ΊΜͲ͍໰୊
4. ຊ೔ͷ಺༰ w*OOP%#
   5BCMFTQBDF
   &ODSZQUJPO
   ͱ͸
   w*OOP%#
   5BCMFTQBDF
   &ODSZQUJPO
   ͷ
   
   Ͱͷมߋ఺
   w44-5-4ͱ͸
   w.Z42-
   ʹ͓͚Δ44-ػೳ
   w44-
   
   Ͱͷมߋ఺
   wূ໌ॻ࡞੒ΊΜͲ͍໰୊
5. ಁաత҉߸Խ w *OOPEC
   5BCMFTQBDF
   &ODSZQUJPO
   w .Z42-
   
   Ͱొ৔
   w σΟεΫ্ͷσʔλΛࣗಈతʹ҉߸Խ͢Δ
   w 4&-&$5͢Δͱ͖΋ɺࣗಈతʹ෮߸Խ͢Δ
   NZTRME σΟεΫ */4&35
   */50
   UCM
   7"-6&4 l)*.*546z ҉߸Խ
6. ͳΜͷͨΊʹ࢖͏ͷʁ w ҎԼͷϦεΫʹର͢Δରࡦ
   w ෺ཧత౪೉
   w σΟεΫΛ࣋ͪڈΒΕΔͳͲ
   w ࿦ཧత౪೉
   w 
   TUSJOHT
   WBSMJCNZTRMEBUBTFDSFU@EBUBJCE
7. ΞϓϦͰ҉߸Խ͢Δ৔߹ͱͷൺֱ w ΞϓϦέʔγϣϯʹΑΔ҉߸ԽͷσϝϦοτ
   w ҉߸ԽϩδοΫͷ࣮૷ෛ୲
   w ιʔτ΍෦෼Ұகʢ-*,&ʣݕࡧ͕ෆՄೳʹ
   w ΞϓϦέʔγϣϯʹΑΔ҉߸ԽͷϝϦοτ
   w σʔλϕʔεΞΫηεʢ4&-&$5ʣʹରͯ͠΋ରࡦͰ͖Δ ಁաత҉߸ԽͰղܾ ^
8. w ֤ςʔϒϧΛ҉߸Խ͢ΔςʔϒϧΩʔ
   w ςʔϒϧ໊JCE
   ϑΝΠϧΛ҉߸Խ
   w ςʔϒϧΩʔΛ͞ΒʹϚελʔΩʔͰ҉߸Խ
   w ςʔϒϧΩʔ͸
   JCE
   ϑΝΠϧʹॻ͔ΕΔ
   w ྆Ωʔͱ΋ʹࣗಈతʹੜ੒͞ΕΔ
   w ϝϞϦ্͸෮߸Խ͞Εͨঢ়ଶ ࢓૊Έ )0(&@5#-JCE 1*:0@5#-JCE ςʔϒϧΩʔ ςʔϒϧΩʔ ϚελʔΩʔ
9. 伴؅ཧ  w ϚελʔΩʔΛͲ͏؅ཧ͢Δ͔͕ॏཁ
   w Ұॹʹ౪·Εͳ͍Α͏ʹ͠ͳ͍ͱɾɾɾ
   w $PNNVOJUZ
   &EJUJPO
   Ͱ͸ϑΝΠϧͷΈαϙʔτ
   w 伴Λஔ͘ϘϦϡʔϜΛ෼͚͓͖ͯɺNZTRME
   ىಈޙ
   VNPVOU͓ͯ͘͜͠ͱ͸ग़དྷͨ
   w ͨͩ͠ɺϚελʔΩʔͷϩʔςʔγϣϯ࣌ʹ͸ཁ࠶Ϛ΢ϯτ
10. 伴؅ཧ  w .Z42-
    &OUFSQSJTF
    &EJUJPO
    w 伴؅ཧγεςϜͱ࿈ܞ͕Մೳ
    w 0SBDMF
    ,FZ
    7BVMU
    
    "84
    ,FZ
    .BOBHFNFOU
    4FSWJDF
    w 1FSDPOB
    4FSWFS
    w )BTIJ
    $PSQ
    7BVMU
11. ຊ೔ͷ಺༰ w*OOP%#
    5BCMFTQBDF
    &ODSZQUJPO
    ͱ͸
    w*OOP%#
    5BCMFTQBDF
    &ODSZQUJPO
    ͷ
    
    Ͱͷมߋ఺
    w44-5-4ͱ͸
    w.Z42-
    ʹ͓͚Δ44-ػೳ
    w44-
    
    Ͱͷมߋ఺
    wূ໌ॻ࡞੒ΊΜͲ͍໰୊
12. w 3&%0
    ϩάɺ6/%0
    ϩάͷ҉߸Խͷαϙʔτ
    w JOOPEC@SFEP@MPH@FODSZQU
    Φϓγϣϯ
    w JOOPEC@VOEP@MPH@FODSZQU
    Φϓγϣϯ
    w ࢒Δ͸όΠφϦϩάͷΈ
    w ͦΖͦΖରԠ͞ΕΔʁ Ͱͷมߋ఺ 3&%0 6/%0 #*/-0( )0(&@5#-JCE 1*:0@5#-JCE ςʔϒϧΩʔ ςʔϒϧΩʔ ϚελʔΩʔ
13. ຊ೔ͷ಺༰ w*OOP%#
    5BCMFTQBDF
    &ODSZQUJPO
    ͱ͸
    w*OOP%#
    5BCMFTQBDF
    &ODSZQUJPO
    ͷ
    
    Ͱͷมߋ఺
    w44-5-4ͱ͸
    w.Z42-
    ʹ͓͚Δ44-ػೳ
    w44-
    
    Ͱͷมߋ఺
    wূ໌ॻ࡞੒ΊΜͲ͍໰୊
14. 44-5-4 w ௨৴૬खͷೝূɺ௨৴಺༰ͷ҉߸Խɺվ᜵ͷݕग़Λఏڙ͢Δ
    
 XJLJQFEJB
    ΑΓ
    w
    ୅දྫʣ)551
    
    44- 5-4
    
    )5514
15. 5-4ͬ͘͟Γ ઀ଓཁٻ ϧʔτ
    $"ূ໌ॻ
    αʔό
    ূ໌ॻ
    NZTRMHSKQ
    ΁઀ଓ͠Α͏ αʔό
    ূ໌ॻ
    αʔό αʔό؅ཧऀ NZTRMHSKQ
    ͷ؅ཧऀͰ͢Α ೝূہ9
    ʢ$" ূ໌ॻ
    ൃߦਃ੥ ൿີ伴 ͔֬ʹɺ ৴པ͢Δ 9͔Β
    ൃߦ͞Εͨূ໌ॻ
    ͔ͭ
    NZTRMHSKQͷ΋ͷͩ ެ։伴 $PNNPO/BNF
    NZTRMHSKQ σδλϧॺ໊ νΣοΫ ڞ௨伴ɹަ׵ ҉߸௨৴ αʔό
    ূ໌ॻ
    
16. 5-4ͬ͘͟Γ ઀ଓཁٻ ϧʔτ
    $"ূ໌ॻ
    αʔό
    ূ໌ॻ
    NZTRMHSKQ
    ΁઀ଓ͠Α͏ αʔό
    ূ໌ॻ
    αʔό αʔό؅ཧऀ NZTRMHSKQ
    ͷ؅ཧऀͰ͢Α ೝূہ9
    ʢ$" ূ໌ॻ
    ൃߦਃ੥ ൿີ伴 ͔֬ʹɺ ৴པ͢Δ 9͔Β
    ൃߦ͞Εͨূ໌ॻ
    ͔ͭ
    NZTRMHSKQͷ΋ͷͩ ެ։伴 $PNNPO/BNF
    NZTRMHSKQ σδλϧॺ໊ νΣοΫ ڞ௨伴ɹަ׵ ҉߸௨৴ αʔό
    ূ໌ॻ
    
17. 5-4ͬ͘͟Γ ઀ଓཁٻ ϧʔτ
    $"ূ໌ॻ
    αʔό
    ূ໌ॻ
    NZTRMHSKQ
    ΁઀ଓ͠Α͏ αʔό
    ূ໌ॻ
    αʔό αʔό؅ཧऀ NZTRMHSKQ
    ͷ؅ཧऀͰ͢Α ೝূہ9
    ʢ$" ূ໌ॻ
    ൃߦਃ੥ ൿີ伴 ͔֬ʹɺ ৴པ͢Δ 9͔Β
    ൃߦ͞Εͨূ໌ॻ
    ͔ͭ
    NZTRMHSKQͷ΋ͷͩ ެ։伴 $PNNPO/BNF
    NZTRMHSKQ σδλϧॺ໊ νΣοΫ ڞ௨伴ɹަ׵ ҉߸௨৴ αʔό
    ূ໌ॻ
    
18. ຊ೔ͷ಺༰ w*OOP%#
    5BCMFTQBDF
    &ODSZQUJPO
    ͱ͸
    w*OOP%#
    5BCMFTQBDF
    &ODSZQUJPO
    ͷ
    
    Ͱͷมߋ఺
    w44-5-4ͱ͸
    w.Z42-
    ʹ͓͚Δ44-ػೳ
    w44-
    
    Ͱͷมߋ఺
    wূ໌ॻ࡞੒ΊΜͲ͍໰୊
19. ར༻έʔε w ΫϥΠΞϯτɾαʔόؒͷ௨৴ͷ҉߸Խɾվ͟Μ๷ࢭ
    w ϚελʔɾεϨʔϒؒͷ௨৴ͷ҉߸Խ
    w ϏουԽ͚ରࡦʢաڈʣ
20. ར༻ํ๏ w NZDOG
    ʹҎԼΛઃఆ
    w TTM@LFZ
    ʢൿີ伴ͷύεʣɺTTM@DFSUʢαʔόূ໌ॻͷύεʣ
    w αʔόূ໌ॻ͸ॳճىಈ࣌ʹࣗಈతʹੜ੒
    w ͍ΘΏΔʮΦϨΦϨূ໌ॻʯʢࣗݾॺ໊ʣ
21. 44-઀ଓͷಈ࡞ͷྫ # mysql -h db01.example.jp --ssl-ca=ca.pem --ssl-mode=VERIFY_CA -unativeuser -p mysql> \s -------------- mysql Ver 8.0.11 for Linux on x86_64 (MySQL Community Server - GPL) Connection id: 42 Current database: Current user: nativeuser@client01.csceaidcfcloud.internal SSL: Cipher in use is DHE-RSA-AES128-GCM-SHA256 Current pager: stdout Using outfile: '' Using delimiter: ; Server version: 8.0.11 MySQL Community Server - GPL Protocol version: 10 Connection: db01.example.jp via TCP/IP Server characterset: utf8mb4 Db characterset: utf8mb4 Client characterset: utf8mb4 Conn. characterset: utf8mb4 TCP port: 3306 Uptime: 8 days 8 hours 33 min 53 sec
22. TTMNPEF
    Φϓγϣϯ w > TTM
    NPEF ޮՌ %*4"#-&% 44-઀ଓΛར༻͠ͳ͍ 13&'&33&%
    %&'"6-5 44-઀ଓ͕࢖͑ͳ͚Ε͹ɺ
    ฏจ઀ଓΛར༻ 3&26*3&% 44-઀ଓͷΈΛར༻͢Δ
    ূ໌ॻ͸ݕূ͠ͳ͍ 7&3*':@$" 44-Λར༻͢Δ
    ৴པ͢Δೝূہ $" ͔Βൃߦ͞Εͨূ໌ॻ͔֬ೝ͢Δ 7&3*':@*%&/5*5: 44-Λར༻͢Δ
    ͞Βʹࢦఆͨ͠઀ଓઌͱূ໌ॻͷ$/͕Ұக͢Δ͔֬ೝ͢Δ ͔ͬ͠Γ
    νΣοΫ
23. 44-઀ଓͷڧ੍ w αʔό·Δ͝ͱࢦఆ
    w Ϣʔβ୯ҐͰ઀ଓΛڧ੍ mysql> CREATE USER ‘appuser’@‘192.168.1.123’ REQUIRE SSL; mysql> GRANT SELECT ON important.secret TO ‘appuser’@‘192.168.1.123’; $ vi /etc/my.cnf [mysqld] require_secure_transport = on $ mysql -h db.example.jp --ssl-mode=DISABLED ERROR 3159 (HY000): Connections using insecure transport are prohibited 
 while —require_secure_transport=ON.
24. ಁաత҉߸ԽΛ࢖͏ͳΒ44-΋ w *OOP%#
    5BCMFTQBDF
    &ODSZQUJPO
    ͸σΟεΫ্͔͠҉߸Խͯ͘͠Εͳ͍
    w ௨৴͸44-Ͱ҉߸Խ
25. ຊ೔ͷ಺༰ w*OOP%#
    5BCMFTQBDF
    &ODSZQUJPO
    ͱ͸
    w*OOP%#
    5BCMFTQBDF
    &ODSZQUJPO
    ͷ
    
    Ͱͷมߋ఺
    w44-5-4ͱ͸
    w.Z42-
    ʹ͓͚Δ44-ػೳ
    w44-
    
    Ͱͷมߋ఺
    wূ໌ॻ࡞੒ΊΜͲ͍໰୊
26. Ͱͷมߋ఺ w NZTRM
    ίϚϯυͷ
    TTM
    Φϓγϣϯ͕ഇࢭ
    w TTMNPEF
    ΦϓγϣϯͷΈʹ
    w .Z42-
    $PNNVOJUZ
    &EJUJPOͷϥΠϒϥϦ͕0QFO44-ʹ
27. Ͱͷมߋ఺ w NZTRM
    ίϚϯυͷ
    TTM
    Φϓγϣϯ͕ഇࢭ
    w TTMNPEF
    ΦϓγϣϯͷΈʹ
    w.Z42-
    $PNNVOJUZ
    &EJUJPOͷϥΠϒϥϦ͕0QFO44-ʹ
28. 44-ϥΠϒϥϦͷมߋ w XPMG44- ZB44-
    ˠ
    0QFO44-
    w 5-4
    W
    ͷαϙʔτ
    w 4UBUJD
    -JOL
    Ͱ͸ͳ͘
    %ZOBNJD
    -JOLʹ
    w 0QFO44-ʹ੬ऑੑ౳͕͋ͬͯ΋.Z42-ͷϦϦʔεΛ଴ͭඞཁ͕ͳ͍ &EJUJPO44-
    MJC WFS
    
     WFS
    
     $PNNVOJUZ
    &EJUJPO XPMG44-
    TUBUJD
    MJOL 0QFO44-
    EZOBNJD
    MJOL &OUFSQSJTF
    &EJUJPO 0QFO44-
    TUBUJD
    MJOL # MySQL 8.0 $ ldd `which mysqld` | grep ssl libssl.so.10 => /lib64/libssl.so.10 # MySQL 5.7 $ ldd `which mysqld` | grep ssl
29. ຊ೔ͷ಺༰ w*OOP%#
    5BCMFTQBDF
    &ODSZQUJPO
    ͱ͸
    w*OOP%#
    5BCMFTQBDF
    &ODSZQUJPO
    ͷ
    
    Ͱͷมߋ఺
    w44-5-4ͱ͸
    w.Z42-
    ʹ͓͚Δ44-ػೳ
    w44-
    
    Ͱͷมߋ఺
    wূ໌ॻ࡞੒ΊΜͲ͍໰୊
30. ূ໌ॻ࡞੒ΊΜͲ͍໰୊ w ࣗಈੜ੒͞Εͨূ໌ॻ͸ࣗݾॺ໊
    ͔ͭ
    $PNNPO/BNF͕ ʮ.Z42-@4FSWFS@@"VUP@(FOFSBUFE@$"@$FSUJpDBUFʯ
    w ՝୊
    w ͳΜ͔44-ͰΤϥʔͰΔΜͰ͚͢Ͳʂʂ
    CZ
    %#ར༻ऀ
    w υϥΠόʹΑͬͯ͸7&3*':@*%&/5*5:૬౰͕σϑΥϧτͷ΋ͷ͕͋ΔͬΆ͍
    w TTMNPEF7&3*':@$"7&3*':@*%&/5*5:૬౰ΛཁແޮԽ
31. ղܾ͢Δʹ͸ w ࣗݾॺ໊
    w ͪΌΜͱͨ͠ೝূہ͔ΒൃߦΛड͚Ε͹ྑ͍
    w $PNNPO
    /BNF
    
    αʔό໊
    w %#αʔό෼࡞੒͠ͳ͍ͱ͍͚ͳ͍
    w ࣗݾॺ໊ͳΒ͍͘ΒͰ΋ੜ੒Ͱ͖Δ͕ɺਖ਼نͷೝূہͩͱແཧήʔ
32. ͨ͘͞Μαʔό͋Δͱ w ̍ຕͷαʔόূ໌ॻͰ؅ཧ͍ͨ͠ɾɾɾ
33. ϫΠϧυΧʔυূ໌ॻ w ϫΠϧυΧʔυূ໌ॻ
    w ෳ਺ͷαϒυϝΠϯʹରͯ͠ɺ༗ޮͳূ໌ॻ
    w YZ[FYBNQMFKQ
    w 
    
    8PMG44-
    ͰϫΠϧυΧʔυূ໌ॻ͕࢖͑ͳ͍͜ͱ͸֬ೝࡁΈ
    w ͷ0QFO44-ͳΒϫΠϧυΧʔυূ໌ॻ࢖͑ͨΓ͢ΔͷͰ͸ʁ
34. ΍ͬͯΈ·ͨ͠
35. μϝͰͨ͠
36. ࢼͨ݁͠Ռ $ openssl x509 -text -noout -in /var/lib/mysql/server-cert.pem | head -n 12 Certificate: Data: Version: 1 (0x0) Serial Number: db:86:48:69:9f:07:9b:7e Signature Algorithm: sha256WithRSAEncryption Issuer: CN=MySQL_Server_8.0.11_Auto_Generated_CA_Certificate Validity Not Before: Jul 7 04:43:14 2018 GMT Not After : Jul 4 04:43:14 2028 GMT Subject: C=JP, L=Default City, O=Default Company Ltd, CN=*.example.jp Subject Public Key Info: $ mysql -h db01.example.jp --ssl-mode=VERIFY_IDENTITY --ssl-ca=/var/lib/mysql/ca.pem ERROR 2026 (HY000): SSL connection error: SSL certificate validation failure $ mysql -h ¥*.example.jp —-ssl-mode=VERIFY_IDENTITY —ssl-ca=/var/lib/mysql/ca.pem mysql>
37. ιʔε
    ಡΈ·͢ ೦ͷͨΊʜ
38. TRMDPNNPODMJFOUDD w׬શҰகͷΈʂʂʂʂʂ JG
    TUSDNQ DO
    TFSWFS@IPTUOBNF
    \
39. 44-ϥΠϒϥϦ࢖ͬͯͳ͍
40. ແ೦
41. ·ͱΊ
42. ·ͱΊ w $/Λαʔό໊ͱҰக͚ͤͨ͞Ε͹ɺূ໌ॻΛαʔό͝ͱʹ࡞Δඞཁ͋Γ
    w ϩʔϧʹΑΔݖݶ؅ཧɺಁաత߸ԽͳͲ.Z42-ͷηΩϡϦςΟػೳ͸
 ϓϩϓϥΠΤλϦͳ%#ͱḮ৭Ϩϕϧ·ͰਐԽ͖͍ͯͯ͠Δ
    w .Z42-ηΩϡϦςΟιϦϡʔγϣϯˍίϯϓϥΠΞϯεରԠηϛφʔ
    w ʢਫʣ
    IUUQTDPOOQBTTDPNFWFOU
43. 5IBOL
    ZPV