Slide 1

Slide 1 text

ಁաత҉߸Խͱ44- ࢖ͬͯΈͨ ೔ຊ.Z42-Ϣʔβձ.Z/" !NJUB

Slide 2

Slide 2 text

͜Μʹͪ͸ʂ w !NJUB w Ͳ͔ͬͷձࣾͷΦϯϓϨ.Z42-%#" w ̌ࡀࣇͷӡ༻࢝Ί·ͨ͠

Slide 3

Slide 3 text

ຊ೔ͷ಺༰ w*OOP%#5BCMFTQBDF&ODSZQUJPOͱ͸ w*OOP%#5BCMFTQBDF&ODSZQUJPOͷͰͷมߋ఺ w44-5-4ͱ͸ w.Z42-ʹ͓͚Δ44-ػೳ w44-Ͱͷมߋ఺ wূ໌ॻ࡞੒ΊΜͲ͍໰୊

Slide 4

Slide 4 text

ຊ೔ͷ಺༰ w*OOP%#5BCMFTQBDF&ODSZQUJPOͱ͸ w*OOP%#5BCMFTQBDF&ODSZQUJPOͷͰͷมߋ఺ w44-5-4ͱ͸ w.Z42-ʹ͓͚Δ44-ػೳ w44-Ͱͷมߋ఺ wূ໌ॻ࡞੒ΊΜͲ͍໰୊

Slide 5

Slide 5 text

ಁաత҉߸Խ w *OOPEC5BCMFTQBDF&ODSZQUJPO w .Z42-Ͱొ৔ w σΟεΫ্ͷσʔλΛࣗಈతʹ҉߸Խ͢Δ w 4&-&$5͢Δͱ͖΋ɺࣗಈతʹ෮߸Խ͢Δ NZTRME σΟεΫ */4&35*/50 UCM 7"-6&4 l)*.*546z ҉߸Խ

Slide 6

Slide 6 text

ͳΜͷͨΊʹ࢖͏ͷʁ w ҎԼͷϦεΫʹର͢Δରࡦ w ෺ཧత౪೉ w σΟεΫΛ࣋ͪڈΒΕΔͳͲ w ࿦ཧత౪೉ w TUSJOHTWBSMJCNZTRMEBUBTFDSFU@EBUBJCE

Slide 7

Slide 7 text

ΞϓϦͰ҉߸Խ͢Δ৔߹ͱͷൺֱ w ΞϓϦέʔγϣϯʹΑΔ҉߸ԽͷσϝϦοτ w ҉߸ԽϩδοΫͷ࣮૷ෛ୲ w ιʔτ΍෦෼Ұகʢ-*,&ʣݕࡧ͕ෆՄೳʹ w ΞϓϦέʔγϣϯʹΑΔ҉߸ԽͷϝϦοτ w σʔλϕʔεΞΫηεʢ4&-&$5ʣʹରͯ͠΋ରࡦͰ͖Δ ಁաత҉߸ԽͰղܾ ^

Slide 8

Slide 8 text

w ֤ςʔϒϧΛ҉߸Խ͢ΔςʔϒϧΩʔ w ςʔϒϧ໊JCEϑΝΠϧΛ҉߸Խ w ςʔϒϧΩʔΛ͞ΒʹϚελʔΩʔͰ҉߸Խ w ςʔϒϧΩʔ͸JCEϑΝΠϧʹॻ͔ΕΔ w ྆Ωʔͱ΋ʹࣗಈతʹੜ੒͞ΕΔ w ϝϞϦ্͸෮߸Խ͞Εͨঢ়ଶ ࢓૊Έ )0(&@5#-JCE 1*:0@5#-JCE ςʔϒϧΩʔ ςʔϒϧΩʔ ϚελʔΩʔ

Slide 9

Slide 9 text

伴؅ཧ w ϚελʔΩʔΛͲ͏؅ཧ͢Δ͔͕ॏཁ w Ұॹʹ౪·Εͳ͍Α͏ʹ͠ͳ͍ͱɾɾɾ w $PNNVOJUZ&EJUJPOͰ͸ϑΝΠϧͷΈαϙʔτ w 伴Λஔ͘ϘϦϡʔϜΛ෼͚͓͖ͯɺNZTRMEىಈޙVNPVOU͓ͯ͘͜͠ͱ͸ग़དྷͨ w ͨͩ͠ɺϚελʔΩʔͷϩʔςʔγϣϯ࣌ʹ͸ཁ࠶Ϛ΢ϯτ

Slide 10

Slide 10 text

伴؅ཧ w .Z42-&OUFSQSJTF&EJUJPO w 伴؅ཧγεςϜͱ࿈ܞ͕Մೳ w 0SBDMF,FZ7BVMU"84,FZ.BOBHFNFOU4FSWJDF w 1FSDPOB4FSWFS w )BTIJ$PSQ7BVMU

Slide 11

Slide 11 text

ຊ೔ͷ಺༰ w*OOP%#5BCMFTQBDF&ODSZQUJPOͱ͸ w*OOP%#5BCMFTQBDF&ODSZQUJPOͷͰͷมߋ఺ w44-5-4ͱ͸ w.Z42-ʹ͓͚Δ44-ػೳ w44-Ͱͷมߋ఺ wূ໌ॻ࡞੒ΊΜͲ͍໰୊

Slide 12

Slide 12 text

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 ςʔϒϧΩʔ ςʔϒϧΩʔ ϚελʔΩʔ

Slide 13

Slide 13 text

ຊ೔ͷ಺༰ w*OOP%#5BCMFTQBDF&ODSZQUJPOͱ͸ w*OOP%#5BCMFTQBDF&ODSZQUJPOͷͰͷมߋ఺ w44-5-4ͱ͸ w.Z42-ʹ͓͚Δ44-ػೳ w44-Ͱͷมߋ఺ wূ໌ॻ࡞੒ΊΜͲ͍໰୊

Slide 14

Slide 14 text

44-5-4 w ௨৴૬खͷೝূɺ௨৴಺༰ͷ҉߸Խɺվ᜵ͷݕग़Λఏڙ͢Δ
 XJLJQFEJBΑΓ w ୅දྫʣ)55144- 5-4 )5514

Slide 15

Slide 15 text

5-4ͬ͘͟Γ ઀ଓཁٻ ϧʔτ $"ূ໌ॻ αʔό ূ໌ॻ NZTRMHSKQ ΁઀ଓ͠Α͏ αʔό ূ໌ॻ αʔό αʔό؅ཧऀ NZTRMHSKQ ͷ؅ཧऀͰ͢Α ೝূہ9 ʢ$" ূ໌ॻ ൃߦਃ੥ ൿີ伴 ͔֬ʹɺ ৴པ͢Δ 9͔Β ൃߦ͞Εͨূ໌ॻ ͔ͭ NZTRMHSKQͷ΋ͷͩ ެ։伴 $PNNPO/BNF NZTRMHSKQ σδλϧॺ໊ νΣοΫ ڞ௨伴ɹަ׵ ҉߸௨৴ αʔό ূ໌ॻ

Slide 16

Slide 16 text

5-4ͬ͘͟Γ ઀ଓཁٻ ϧʔτ $"ূ໌ॻ αʔό ূ໌ॻ NZTRMHSKQ ΁઀ଓ͠Α͏ αʔό ূ໌ॻ αʔό αʔό؅ཧऀ NZTRMHSKQ ͷ؅ཧऀͰ͢Α ೝূہ9 ʢ$" ূ໌ॻ ൃߦਃ੥ ൿີ伴 ͔֬ʹɺ ৴པ͢Δ 9͔Β ൃߦ͞Εͨূ໌ॻ ͔ͭ NZTRMHSKQͷ΋ͷͩ ެ։伴 $PNNPO/BNF NZTRMHSKQ σδλϧॺ໊ νΣοΫ ڞ௨伴ɹަ׵ ҉߸௨৴ αʔό ূ໌ॻ

Slide 17

Slide 17 text

5-4ͬ͘͟Γ ઀ଓཁٻ ϧʔτ $"ূ໌ॻ αʔό ূ໌ॻ NZTRMHSKQ ΁઀ଓ͠Α͏ αʔό ূ໌ॻ αʔό αʔό؅ཧऀ NZTRMHSKQ ͷ؅ཧऀͰ͢Α ೝূہ9 ʢ$" ূ໌ॻ ൃߦਃ੥ ൿີ伴 ͔֬ʹɺ ৴པ͢Δ 9͔Β ൃߦ͞Εͨূ໌ॻ ͔ͭ NZTRMHSKQͷ΋ͷͩ ެ։伴 $PNNPO/BNF NZTRMHSKQ σδλϧॺ໊ νΣοΫ ڞ௨伴ɹަ׵ ҉߸௨৴ αʔό ূ໌ॻ

Slide 18

Slide 18 text

ຊ೔ͷ಺༰ w*OOP%#5BCMFTQBDF&ODSZQUJPOͱ͸ w*OOP%#5BCMFTQBDF&ODSZQUJPOͷͰͷมߋ఺ w44-5-4ͱ͸ w.Z42-ʹ͓͚Δ44-ػೳ w44-Ͱͷมߋ఺ wূ໌ॻ࡞੒ΊΜͲ͍໰୊

Slide 19

Slide 19 text

ར༻έʔε w ΫϥΠΞϯτɾαʔόؒͷ௨৴ͷ҉߸Խɾվ͟Μ๷ࢭ w ϚελʔɾεϨʔϒؒͷ௨৴ͷ҉߸Խ w ϏουԽ͚ରࡦʢաڈʣ

Slide 20

Slide 20 text

ར༻ํ๏ w NZDOGʹҎԼΛઃఆ w TTM@LFZʢൿີ伴ͷύεʣɺTTM@DFSUʢαʔόূ໌ॻͷύεʣ w αʔόূ໌ॻ͸ॳճىಈ࣌ʹࣗಈతʹੜ੒ w ͍ΘΏΔʮΦϨΦϨূ໌ॻʯʢࣗݾॺ໊ʣ

Slide 21

Slide 21 text

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: [email protected] 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

Slide 22

Slide 22 text

TTMNPEFΦϓγϣϯ w > TTMNPEF ޮՌ %*4"#-&% 44-઀ଓΛར༻͠ͳ͍ 13&'&33&% %&'"6-5 44-઀ଓ͕࢖͑ͳ͚Ε͹ɺ ฏจ઀ଓΛར༻ 3&26*3&% 44-઀ଓͷΈΛར༻͢Δ ূ໌ॻ͸ݕূ͠ͳ͍ 7&3*':@$" 44-Λར༻͢Δ ৴པ͢Δೝূہ $" ͔Βൃߦ͞Εͨূ໌ॻ͔֬ೝ͢Δ 7&3*':@*%&/5*5: 44-Λར༻͢Δ ͞Βʹࢦఆͨ͠઀ଓઌͱূ໌ॻͷ$/͕Ұக͢Δ͔֬ೝ͢Δ ͔ͬ͠Γ νΣοΫ

Slide 23

Slide 23 text

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.

Slide 24

Slide 24 text

ಁաత҉߸ԽΛ࢖͏ͳΒ44-΋ w *OOP%#5BCMFTQBDF&ODSZQUJPO͸σΟεΫ্͔͠҉߸Խͯ͘͠Εͳ͍ w ௨৴͸44-Ͱ҉߸Խ

Slide 25

Slide 25 text

ຊ೔ͷ಺༰ w*OOP%#5BCMFTQBDF&ODSZQUJPOͱ͸ w*OOP%#5BCMFTQBDF&ODSZQUJPOͷͰͷมߋ఺ w44-5-4ͱ͸ w.Z42-ʹ͓͚Δ44-ػೳ w44-Ͱͷมߋ఺ wূ໌ॻ࡞੒ΊΜͲ͍໰୊

Slide 26

Slide 26 text

Ͱͷมߋ఺ w NZTRMίϚϯυͷTTMΦϓγϣϯ͕ഇࢭ w TTMNPEFΦϓγϣϯͷΈʹ w .Z42-$PNNVOJUZ&EJUJPOͷϥΠϒϥϦ͕0QFO44-ʹ

Slide 27

Slide 27 text

Ͱͷมߋ఺ w NZTRMίϚϯυͷTTMΦϓγϣϯ͕ഇࢭ w TTMNPEFΦϓγϣϯͷΈʹ w.Z42-$PNNVOJUZ&EJUJPOͷϥΠϒϥϦ͕0QFO44-ʹ

Slide 28

Slide 28 text

44-ϥΠϒϥϦͷมߋ w XPMG44- ZB44- ˠ0QFO44- w 5-4Wͷαϙʔτ w 4UBUJD-JOLͰ͸ͳ͘%ZOBNJD-JOLʹ w 0QFO44-ʹ੬ऑੑ౳͕͋ͬͯ΋.Z42-ͷϦϦʔεΛ଴ͭඞཁ͕ͳ͍ &EJUJPO44-MJC WFS WFS $PNNVOJUZ &EJUJPO XPMG44- TUBUJDMJOL 0QFO44- EZOBNJDMJOL &OUFSQSJTF &EJUJPO 0QFO44- TUBUJDMJOL # MySQL 8.0 $ ldd `which mysqld` | grep ssl libssl.so.10 => /lib64/libssl.so.10 # MySQL 5.7 $ ldd `which mysqld` | grep ssl

Slide 29

Slide 29 text

ຊ೔ͷ಺༰ w*OOP%#5BCMFTQBDF&ODSZQUJPOͱ͸ w*OOP%#5BCMFTQBDF&ODSZQUJPOͷͰͷมߋ఺ w44-5-4ͱ͸ w.Z42-ʹ͓͚Δ44-ػೳ w44-Ͱͷมߋ఺ wূ໌ॻ࡞੒ΊΜͲ͍໰୊

Slide 30

Slide 30 text

ূ໌ॻ࡞੒ΊΜͲ͍໰୊ w ࣗಈੜ੒͞Εͨূ໌ॻ͸ࣗݾॺ໊͔ͭ$PNNPO/BNF͕ ʮ.Z42-@4FSWFS@@"VUP@(FOFSBUFE@$"@$FSUJpDBUFʯ w ՝୊ w ͳΜ͔44-ͰΤϥʔͰΔΜͰ͚͢ͲʂʂCZ%#ར༻ऀ w υϥΠόʹΑͬͯ͸7&3*':@*%&/5*5:૬౰͕σϑΥϧτͷ΋ͷ͕͋ΔͬΆ͍ w TTMNPEF7&3*':@$"7&3*':@*%&/5*5:૬౰ΛཁແޮԽ

Slide 31

Slide 31 text

ղܾ͢Δʹ͸ w ࣗݾॺ໊ w ͪΌΜͱͨ͠ೝূہ͔ΒൃߦΛड͚Ε͹ྑ͍ w $PNNPO/BNFαʔό໊ w %#αʔό෼࡞੒͠ͳ͍ͱ͍͚ͳ͍ w ࣗݾॺ໊ͳΒ͍͘ΒͰ΋ੜ੒Ͱ͖Δ͕ɺਖ਼نͷೝূہͩͱແཧήʔ

Slide 32

Slide 32 text

ͨ͘͞Μαʔό͋Δͱ w ̍ຕͷαʔόূ໌ॻͰ؅ཧ͍ͨ͠ɾɾɾ

Slide 33

Slide 33 text

ϫΠϧυΧʔυূ໌ॻ w ϫΠϧυΧʔυূ໌ॻ w ෳ਺ͷαϒυϝΠϯʹରͯ͠ɺ༗ޮͳূ໌ॻ w YZ[FYBNQMFKQ w 8PMG44-ͰϫΠϧυΧʔυূ໌ॻ͕࢖͑ͳ͍͜ͱ͸֬ೝࡁΈ w ͷ0QFO44-ͳΒϫΠϧυΧʔυূ໌ॻ࢖͑ͨΓ͢ΔͷͰ͸ʁ

Slide 34

Slide 34 text

΍ͬͯΈ·ͨ͠

Slide 35

Slide 35 text

μϝͰͨ͠

Slide 36

Slide 36 text

ࢼͨ݁͠Ռ $ 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>

Slide 37

Slide 37 text

ιʔεಡΈ·͢ ೦ͷͨΊʜ

Slide 38

Slide 38 text

TRMDPNNPODMJFOUDD w׬શҰகͷΈʂʂʂʂʂ JG TUSDNQ DO TFSWFS@IPTUOBNF \

Slide 39

Slide 39 text

44-ϥΠϒϥϦ࢖ͬͯͳ͍

Slide 40

Slide 40 text

ແ೦

Slide 41

Slide 41 text

·ͱΊ

Slide 42

Slide 42 text

·ͱΊ w $/Λαʔό໊ͱҰக͚ͤͨ͞Ε͹ɺূ໌ॻΛαʔό͝ͱʹ࡞Δඞཁ͋Γ w ϩʔϧʹΑΔݖݶ؅ཧɺಁաత߸ԽͳͲ.Z42-ͷηΩϡϦςΟػೳ͸
 ϓϩϓϥΠΤλϦͳ%#ͱḮ৭Ϩϕϧ·ͰਐԽ͖͍ͯͯ͠Δ w .Z42-ηΩϡϦςΟιϦϡʔγϣϯˍίϯϓϥΠΞϯεରԠηϛφʔ w ʢਫʣIUUQTDPOOQBTTDPNFWFOU

Slide 43

Slide 43 text

5IBOLZPV