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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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.

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

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

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

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

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

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

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

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

΍ͬͯΈ·ͨ͠

μϝͰͨ͠

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

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

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

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

ແ೦

·ͱΊ

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

5IBOLZPV

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

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

More Decks by Satoshi MITANI

Other Decks in Technology

Featured

Transcript