Sireeynta MySQL: Keystore

Iyadoo la filayo bilowga diiwaangelinta cusub ee koorsada "Database" Waxaan kuu diyaarinay tarjumaad maqaal faa'iido leh.

Xogta Daah-furnaanta ah (TDE) ayaa ka dhex muuqatay Percona Server ee MySQL iyo MySQL in muddo ah. Laakiin waligaa ma ka fikirtay sida ay uga shaqeyso daboolka iyo saamaynta TDE ku yeelan karto serverkaaga? Maqaalladan taxanaha ah waxaan ku eegi doonaa sida TDE u shaqeyso gudaha. Aan ku bilowno kaydinta muhiimka ah, maadaama tan looga baahan yahay sir kasta si uu u shaqeeyo. Ka dib waxaan si dhow u eegi doonaa sida sirtu u shaqeyso Percona Server ee MySQL/MySQL iyo waxa dheeraadka ah ee Percona Server ee MySQL uu leeyahay.

MySQL Keyring

Fureyaashu waa furayaal u oggolaanaya adeegaha inuu weydiiyo, abuuro, oo tirtiro furayaasha faylka maxalliga ah (keyring_file) ama server-ka fog (sida HashiCorp Vault). Furayaasha mar walba waxaa lagu kaydiyaa gudaha si loo dedejiyo soo saaristooda.

Plugins waxaa loo qaybin karaa laba qaybood:

• Kaydinta degaanka. Tusaale ahaan, fayl maxalli ah (waxaanu tan ugu yeernaa furayaal ku salaysan faylka).
• Kaydinta fog. Tusaale ahaan, Vault Server (waxaanu tan ugu yeernaa furaha ku salaysan server-ka).

Kala soocidaani waa muhiim sababtoo ah noocyada kala duwan ee kaydinta ayaa u dhaqma si ka duwan, kaliya maahan marka la kaydinayo oo la soo celinayo furayaasha, laakiin sidoo kale marka la wado.

Marka la isticmaalayo kaydinta faylka, marka la bilaabo, dhammaan waxa ku jira kaydinta waxaa lagu shubaa kaydinta: furaha id, isticmaalaha furaha, nooca furaha, iyo furaha laftiisa.

Marka laga hadlayo dukaanka dhinaca server-ka ah (sida Vault Server), kaliya aqoonsiga furaha iyo isticmaaleha muhiimka ah ayaa lagu shubaa bilowga, markaa helitaanka dhammaan furayaasha ma hoos u dhigayso bilowga. Furayaasha si caajisnimo ayaa loo rartay. Taasi waa, furaha laftiisa ayaa laga soo raray Vault kaliya marka dhab ahaantii loo baahdo. Marka la soo dejiyo, furaha waxa lagu kaydiyaa xusuusta si aanu u baahnayn in laga galo xidhiidhka TLS ee Server-ka Vault mustaqbalka. Marka xigta, aan eegno macluumaadka ku jira dukaanka muhiimka ah.

Macluumaadka muhiimka ah ayaa ka kooban kuwan soo socda:

• id furaha - aqoonsiga muhiimka ah, tusaale ahaan:
  INNODBKey-764d382a-7324-11e9-ad8f-9cb6d0d5dc99-1
• nooca muhiimka ah - nooca muhiimka ah ee ku salaysan sirta algorithm ee la isticmaalay, qiimayaasha suurtagalka ah: "AES", "RSA" ama "DSA".
• dhererka muhiimka ah - dhererka muhiimka ah ee bytes, AES: 16, 24 ama 32, RSA 128, 256, 512 iyo DSA 128, 256 ama 384.
• user - milkiilaha furaha. Haddii furuhu yahay nidaam, tusaale ahaan, Master Key, markaa goobtani waa madhan. Haddii fure la sameeyo iyadoo la adeegsanayo keyring_udf, markaas goobtan ayaa aqoonsanaysa milkiilaha furaha.
• furaha laftiisa

Furaha waxaa si gaar ah u aqoonsaday lamaanaha: key_id, isticmaale.

Waxa kale oo jira kala duwanaansho xagga kaydinta iyo tirtiridda furayaasha.

Kaydinta feylku waa dhakhso badan tahay. Waxaa laga yaabaa inaad u maleyneyso in dukaanka muhiimka ah uu si fudud u qorayo furaha faylka hal mar, laakiin maya, wax badan ayaa halkan ka socda. Mar kasta oo wax ka beddelka kaydinta faylka la sameeyo, koobiga kaydinta dhammaan waxa ku jira ayaa marka hore la abuuraa. Aynu nidhaahno faylka waxaa loo yaqaan my_biggest_secrets, ka dib koobi kaabashadu waxay noqon doontaa my_biggest_secrets.backup. Marka xigta, khasnadda waa la beddelaa (furayaasha waa lagu daraa ama la tirtiraa) iyo, haddii wax walba ay guuleystaan, khasnadda ayaa dib loo dejinayaa faylka. Xaalado dhif ah, sida fashilka server-ka, waxaa laga yaabaa inaad aragto faylkan kaydinta ah. Faylka kaydinta waa la tirtiraa marka xigta ee furayaasha la soo shubo (badanaa ka dib marka server-ka dib loo bilaabo).

Marka la kaydinayo ama la tirtirayo furaha kaydinta server-ka, kaydinta waa in ay ku xidhataa server-ka MySQL oo wata amarrada "dir furaha" / "codsiga tirtirka furaha".

Aan ku soo laabano xawaaraha bilowga server-ka. Marka laga soo tago xaqiiqda ah in xawaaraha furitaanka uu saameeyay khasnadda lafteeda, waxaa sidoo kale jira arrin ku saabsan inta fure ee khasnadda u baahan in la soo saaro marka la bilaabayo. Dabcan, tani waxay si gaar ah muhiim ugu tahay kaydinta server-ka. Bilawga, adeeguhu waxa uu hubinayaa furaha looga baahan yahay miisaska/goobaha miisaska sir ah oo ka codsada furaha kaydinta. Seerfar “nadiif ah” oo leh sirta Keydka Master-ka, waa in uu jiraa hal Furaha Master, kaas oo ay tahay in laga soo saaro kaydinta. Si kastaba ha ahaatee, tiro badan oo furayaal ah ayaa loo baahan karaa, tusaale ahaan, marka server-ka kaydku uu dib u soo celinayo kaydka server-ka aasaasiga ah. Xaaladahan oo kale, wareejinta Furaha Master-ka waa in la bixiyaa. Tan waxaa si faahfaahsan loogu dabooli doonaa maqaallada mustaqbalka, inkastoo halkan aan jeclaan lahaa in aan ogaado in server-ka isticmaalaya furayaasha Master-ka ee badan ay qaadan karto wax yar si loo bilaabo, gaar ahaan marka la isticmaalayo dukaanka muhiimka ah ee dhinaca server-ka.

Hadda aan ka hadalno wax yar oo ku saabsan keyring_file. Markii aan horumarinayay keyring_file, waxaan sidoo kale ka walwalsanahay sida loo hubiyo isbeddelada keyring_file inta uu serverku socdo. 5.7, jeega ayaa la sameeyay iyadoo lagu salaynayo tirakoobka faylka, taas oo aan ahayn xal ku haboon, 8.0 waxaa lagu bedelay jeegaga SHA256.

Marka ugu horeysa ee aad furto keyring_file, faylka statistics iyo checksum ayaa la xisaabiyaa, kuwaas oo uu xasuusto server-ku, isbeddeladana waxaa lagu dabaqaa oo kaliya haddii ay ku habboon yihiin. Marka feylku isbedelo, jeeggu waa la cusboonaysiiyaa.

Waxaan horey u soo daboolnay su'aalo badan oo ku saabsan khasnadaha muhiimka ah. Si kastaba ha ahaatee, waxaa jira mawduuc kale oo muhiim ah oo inta badan la ilaaway ama la fahmi waayay: wadaagista furayaasha server-yada.

Maxaan ula jeedaa? Server kasta (tusaale ahaan, Percona Server) ee kutlada waa in uu ku lahaadaa meel gooni ah oo ku taal Vault Server-ka kaas oo Percona Server ay tahay in uu ku kaydiyo furihiisa. Fure kasta oo Master ah oo lagu kaydiyo waxa ku jira GUID-ka Server-ka Percona ee ku dhex jira aqoonsigiisa. Maxay muhiim u tahay? Bal qiyaas in aad haysato hal kayd oo kaliya iyo dhammaan adeegayaasha Percona ee kooxdu ay isticmaalaan hal kayd oo Vault ah. Dhibaatadu waxay u muuqataa mid cad. Haddii dhammaan Servers-yada Percona ay isticmaalaan Furaha Master-ka oo aan lahayn aqoonsiyo gaar ah, sida id = 1, id = 2, iwm., markaa dhammaan server-yada kooxda waxay isticmaali doonaan isla Keydka Master-ka. Waxa GUID ay bixiso waa faraqa u dhexeeya server-yada. Maxaa markaa uga hadlayaa wadaagista furayaasha dhexmara adeegayaasha haddii GUID gaar ah uu hore u jiray? Waxaa jira plugin kale - keyring_udf. Plugin-kan, adeegsadahaaga server-ka waxa uu ku kaydin karaa furahooda seerfarka Vault. Dhibaatadu waxay dhacdaa marka isticmaaluhu uu ku abuuro furaha server1, tusaale ahaan, ka dibna isku dayo inuu abuuro fure leh aqoonsi isku mid ah server2, tusaale ahaan:

--server1:
select keyring_key_store('ROB_1','AES',"123456789012345");
1
--1 значит успешное завершение
--server2:
select keyring_key_store('ROB_1','AES',"543210987654321");
1

Sug Labada adeegeba waxay isticmaalayaan isla Vault Server, miyaanay shaqada keyring_key_store ku fashilmin server2? Waxa xiiso leh, haddii aad isku daydo inaad sidaas oo kale ku samayso hal server, waxaad heli doontaa cilad:

--server1:
select keyring_key_store('ROB_1','AES',"123456789012345");
1
select keyring_key_store('ROB_1','AES',"543210987654321");
0

Taasi waa sax, ROB_1 ayaa hore u jiray.

Aynu ka wada hadalno tusaalaha labaad marka hore. Sidaan horey u sheegnay, keyring_vault ama furayaasha kale ee furaha ayaa kaydiya dhammaan aqoonsiga muhiimka ah ee xusuusta. Marka, ka dib markii la abuuro fure cusub, ROB_1 waxaa lagu daraa server1, ka sokow u dirida furaha Vault, furaha sidoo kale waxaa lagu daraa kaydka. Hadda, marka aan isku dayno inaan ku darno isla furaha mar labaad, keyring_vault wuxuu hubiyaa in furaha uu ku jiro khasnad oo uu tuurayaa qalad.

Marka hore xaaladdu way ka duwan tahay. Server1 iyo server2 waxay leeyihiin khasnado kala duwan. Ka dib marka lagu daro ROB_1 khasnadda furaha ee server1 iyo serferka Vault, kaydka furaha ee server2 waa uu ka baxay wada shaqayn. Ma jiro wax fure ah oo ROB_2 ah oo ku jira kaydka server1. Haddaba, furaha ROB_1 waxa loo qoraa keyring_key_store iyo server-ka Vault, kaas oo runtii dib u qoraya (!) qiimihii hore. Hadda ROB_1 furaha server-ka Vault waa 543210987654321

Hadda waxaan arki karnaa sababta qaybinta server-ka ee Vault ay muhiim u noqon karto - marka aad isticmaalayso keyring_udf oo aad rabto inaad furayaasha ku kaydiso Vault. Sidee lagu gaari karaa kala-soociddan server-ka Vault?

Waxaa jira laba siyaabood oo loo qaybiyo Vault. Waxaad u abuuri kartaa dhibco kala duwan oo ka mid ah server kasta, ama waxaad isticmaali kartaa wadooyin kala duwan isla barta buurta dhexdeeda. Tan waxaa ugu wanaagsan tusaaleyaal. Haddaba aan marka hore eegno qodobbada dulsaarka shakhsi ahaaneed:

--server1:
vault_url = http://127.0.0.1:8200
secret_mount_point = server1_mount
token = (...)
vault_ca = (...)

--server2:
vault_url = http://127.0.0.1:8200
secret_mount_point = sever2_mount
token = (...)
vault_ca = (...)

Halkan waxaad ku arki kartaa in server1 iyo server2 ay isticmaalayaan dhibco kala duwan. Marka la kala qaybinayo waddooyinka, qaabayntu waxay u ekaan doontaa sidan:

--server1:
vault_url = http://127.0.0.1:8200
secret_mount_point = mount_point/server1
token = (...)
vault_ca = (...)
--server2:
vault_url = http://127.0.0.1:8200
secret_mount_point = mount_point/sever2
token = (...)
vault_ca = (...)

Xaaladdan oo kale, labada server waxay isticmaalaan isla barta buurta "mount_point", laakiin wadooyin kala duwan. Markaad ku abuurto sirta koowaad ee server1 adoo isticmaalaya dariiqan, server-ka Vault wuxuu si toos ah u abuurayaa "server1" directory. Server2 wax walba waa la mid. Markaad tirtirto sirtii ugu dambeysay ee mount_point/server1 ama mount_point/server2, server-ka Vault wuxuu kaloo tirtiraa hagayaashaas. Haddii ay dhacdo inaad isticmaasho kala-soocidda dariiqa, waa inaad abuurtaa hal dhibic oo keliya oo aad beddesho faylasha qaabeynta si ay adeegayaashu u isticmaalaan waddooyin kala duwan. Barta buurta waxa lagu samayn karaa codsi HTTP ah. Isticmaalka CURL tan waxaa loo samayn karaa sidan:

curl -L -H "X-Vault-Token: TOKEN" –cacert VAULT_CA
--data '{"type":"generic"}' --request POST VAULT_URL/v1/sys/mounts/SECRET_MOUNT_POINT

Dhammaan goobaha (TOKEN, VAULT_CA, VAULT_URL, SECRET_MOUNT_POINT) waxay u dhigmaan cabbirrada faylka qaabeynta. Dabcan, waxaad isticmaali kartaa utility Vault si aad sidaas oo kale u sameyso. Laakiin way fududahay in si otomaatig ah loo sameeyo abuurista barta buurta. Waxaan rajeynayaa inaad xogtan faa'iido u yeelan doonto, waxaana ku arki doonnaa maqaallada xiga ee taxanahan.

Akhri wax dheeraad ah:

• Sysbench iyo qaybinta doorsoomayaasha random

Source: www.habr.com