MySQL欄位加密工具：MyDiamo
這是一套主要針對MySQL Fork(MySQL, Percona Server, MariaDB)的欄位進行加密管理的第三方工具
它可以對欄位設定不同的加密方式，且加密、解密的權限可獨立分開設定
另外，它還支援連線管控及稽核的功能
詳細的功能就上官網查詢
Debug集
指令參考官方手冊：https://mydiamo.com/guide/operation/encryption/
它可以對欄位設定不同的加密方式，且加密、解密的權限可獨立分開設定
另外，它還支援連線管控及稽核的功能
詳細的功能就上官網查詢
- MyDiamo運作原理
它是屬於MySQL管理層和Storage Engine層之間的一個Plugin，透過自行設定的原則來對InnoDB或MyISAM表的欄位進行加密。(如圖一)
軟體安裝好後，它有自已的管理介面，要在這個介面中先設定好【原則】，接著在此原則之下設定有哪些【欄位】要使用這個原則，再來就是設定有哪些【使用者】被授予這些欄位的權限為何，最後再執行plugin所屬的migration funcation對資料表欄位進行加密。
 |
| 圖一 |
- 在安裝前要先註冊帳號，它才會把軟體下載的連結寄給你…
- MyDiamo安裝
在下載軟體的時候要注意，它不是全部的版本都支援的，它只支援部份小版本，例如：MySQL 5.7.12不支援…等，所以要先確認目前資料庫的版本再來下載，更多的版本內容請看官網啦：Support Platform
如果你的MySQL是用RPM方式安裝的話，遇到的問題相對會少一些
我的測試環境是以tar安裝，很多路徑是要另外做設定的(我懶沒設啦…)所以會多一些步驟
◆安裝
[root@mysql-course D`Amo_MyDiamo_v3.0.9.4]# ./install-linux-mydiamo-3.0.9.4.run -p /opt/mysql -h 127.0.0.1 -P 3307 ===================================================== MyDiamo Install Script ===================================================== Input MySQL/MariaDB root Password : ===================================================== NDB Cluster : No DBMS : MySQL VERSION : 5.7.16 COMPILE MACHINE : 64 bit MySQL/MariaDB CONFIG : normal MySQL/MariaDB PLUGIN DIR : /opt/mysql/lib/plugin/ DAMO_INST_HOME : /mydiamo CIS_CC_HMAC_PATH : /mydiamo/lib64 MANUAL INSTALLATION : No ======================================================= Program Copy Success ======================================================= 3.0.9.4/64/ha_damo-mysql-5.7.16-normal.so.md5 3.0.9.4/64/ha_damo-mysql-5.7.16-normal.so ha_damo-mysql-5.7.16-normal.so file was decompressed successfully. ha_damo-mysql-5.7.16-normal.so.md5 file was decompressed successfully. ======================================================= Must restart the MySQL/MariaDB service. 1) Yes 2) No Would you like to MySQL/MariaDB restart now? 1 ======================================================= Stop MySQL/MariaDB service ======================================================= Shutting down MySQL.. SUCCESS! ======================================================= Start MySQL/MariaDB service ======================================================= Starting MySQL... SUCCESS! ======================================================= Program Install Success ======================================================= Check your environment... Check DAMO_INST_HOME in the /etc/damo.conf file. Check CIS_CC_HMAC_PATH in the /etc/damo.conf file. ======================================================= [root@mysql-course mydiamo]# ls /mydiamo/ bin damo_kms.conf damo_lms.conf.in lib log sql uninstall_program.sh copyright damo_lms.conf key lib64 readme.txt support version.txt [root@mysql-course mydiamo]# mysql --login-path=mysql root@127.0.0.1 [(none)]>show plugins; +----------------------------+----------+--------------------+-----------------+---------+ | Name | Status | Type | Library | License | +----------------------------+----------+--------------------+-----------------+---------+ | DAMO_MYISAM | ACTIVE | STORAGE ENGINE | ha_damo.so | GPL | | DAMO_INNODB | ACTIVE | STORAGE ENGINE | ha_damo.so | GPL | +----------------------------+----------+--------------------+-----------------+---------+ - 產生新的key
[root@mysql-course mydiamo]# ./bin/keygen -a -N /mydiamo/key/ -L ===================================================================== Warning: Incorrect handling of keygen may lead to a malfunction of MyDiamo. Please check the manual before attempting to make any changes. ===================================================================== Enter the Site-Key Password: Abcd!234 Enter the Site-Key Confirm Password: Abcd!234 Enter the Console-Key Password: Abcd!234 Enter the Console-Key Confirm Password: Abcd!234 Enter the DB-Key Password: Abcd!234 Enter the DB-Key Confirm Password: Abcd!234 Generate Site Key Pair... Generate Console Key Pair... Generate DB Key Pair... [root@mysql-course mydiamo]# ls key/ damo_console.cer damo_db.cer damo_site.cer damo_console.key damo_db.key damo_site.key
- 啟動keymanager
[root@mysql-course mydiamo]# ./bin/keymanager_cis-3.2 -start Log in to start software KeyManager. Enter the DB-key Password : Abcd!220 parent end server port [0] cert file [/mydiamo/key/damo_db.cer] pri file [/mydiamo/key/damo_db.key] encrypt cert data encrypt pri data lnAddress [4924930] shmget [720900]
- 進入Console
[root@mysql-course mydiamo]# ./bin/damo_CLI -start ===================================================== | | | Copyright (C) 2014 PENTA SECURITY SYSTEMS, INC. | | All rights reserved | | | | MyDiamo command line interface(CLI) | | | ===================================================== Login to start CLI. Enter the Console-key Password : [DAMO-2001] 'license.damo' does not exist or correct. There is something wrong with the license file. Please reissue the new AuthID and AuthCode at MyDiamo website, and active MyDiamo again. Input ID : MDFL-xxxxxxxx-2017-03-22_16-55-36 <= Email裡會提供 Input Auth Code : 78c78d22ddjdosw802js5beab0356670a1d43b <= Email裡會提供 Succeeded to receive data from server.
- 設定原則Policy
MyDiamo > create policy nameFull"AES"VIV"0"0"CBC"RAW ###設定整個欄位進行加密 CREATE POLICY policy[nameFull] alg[AES] iv[VIV] partial-enc-pos[0] partial-enc-off[0], block mode[CBC], encode mode[RAW], enc flag[] MyDiamo > create policy namePart"AES"VIV"3"6"CBC"RAW ###設定只對中間部份字串加密 CREATE POLICY policy[namePart] alg[AES] iv[VIV] partial-enc-pos[3] partial-enc-off[6], block mode[CBC], encode mode[RAW], enc flag[] MyDiamo > create policy salary"AES"FIV"0"0"CFB"RAW ###對數字進行加密只能使用FIV、CFB、RAW模式 CREATE POLICY policy[salary] alg[AES] iv[FIV] partial-enc-pos[0] partial-enc-off[0], block mode[CFB], encode mode[RAW], enc flag[] MyDiamo > show policy ------------<< All Policy ID Info. >>------------- +---------+------------+-----------+---------------+------------------+----------+-----------+--------+ |Policy ID|Enc. Alg. |IV mode |Partial Enc Pos|Partial Enc Offset|Block mode|Encode mode|Enc Flag| +---------+------------+-----------+---------------+------------------+----------+-----------+--------+ |nameFull |AES 128 bits|Variable IV|Non-Partial Enc|Non-Partial Enc |CBC |RAW | | |namePart |AES 128 bits|Variable IV|3 |6 |CBC |RAW | | |salary |AES 128 bits|Fixed IV |Non-Partial Enc|Non-Partial Enc |CFB |RAW | | +---------+------------+-----------+---------------+------------------+----------+-----------+--------+ There are 3 policies. ---------------------------------------- SG-KMS Mode : NO KMS ----------------------------------------
- 設定要加密的欄位
語法說明：SET COLUMN <資料庫>"<資料表>"<欄位>"<原則名稱>MyDiamo > set column world"City"Name"nameFull SET COLUMN O[world] T[City] C[Name] P[nameFull] MyDiamo > set column world"Country"Name"namePart SET COLUMN O[world] T[Country] C[Name] P[namePart] MyDiamo > set column world"Salary"salary"salary SET COLUMN O[world] T[Salary] C[salary] P[salary] MyDiamo > show column --------<< All column info >>--------------------- +-----+-------+------+---------+ |Owner|Table |Column|Policy ID| +-----+-------+------+---------+ |world|City |Name |nameFull | |world|Country|Name |namePart | |world|Salary |salary|salary | +-----+-------+------+---------+ There are 3 encrypted columns.
- 使用者授權
使用者要先在資料庫中建立並且給予適當的權限，在此僅設定針對某特定欄位的加、解密權限
語法說明：SET PRIV ENC <使用者帳號>"<資料庫>"<資料表>"<欄位>"<加密0,1>"<解密0,1> MyDiamo > set priv enc app"world"City"Name"1"0 SET PRIV ENC U[app] O[world] T[City] C[Name] EP[1] DP[0] MyDiamo > set priv enc app"world"Country"Name"1"1 SET PRIV ENC U[app] O[world] T[Country] C[Name] EP[1] DP[1] MyDiamo > set priv enc app"world"Salary"salary"1"0 SET PRIV ENC U[app] O[world] T[Salary] C[salary] EP[1] DP[0] MyDiamo > set priv enc accountant"world"Salary"salary"1"1 SET PRIV ENC U[accountant] O[world] T[Salary] C[salary] EP[1] DP[1] MyDiamo > SET ENV DECRYPT DENY MASKING ###這個設定是讓沒有權限解密的使用者看到欄位上出現**符號，沒有設定的話會直接reject查詢 SET ENV DECRYPT DENY MASKING MyDiamo > show priv enc ------<< All User's enc/dec privilege info >>----- +----------+-----+-------+------+--------+--------+ |User |Owner|Table |Column|Enc priv|Dec priv| +----------+-----+-------+------+--------+--------+ |app |world|City |Name |Yes |No | |app |world|Country|Name |Yes |Yes | |app |world|Salary |salary|Yes |No | |accountant|world|Salary |salary|Yes |Yes | +----------+-----+-------+------+--------+--------+ There are 4 enc/dec privilege settings. Decrypt deny mode : Masking MyDiamo > save all SAVE ALL
- 進資料庫將剛剛的設定生效
指令說明：call mysql.damo_table_migration('資料庫','資料表'); "root@127.0.0.1 [(none)]>call mysql.damo_table_migration('world','City'); +-----------+ | PARTITION | +-----------+ | | +-----------+ 1 row in set (0.51 sec) +---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | ENC MIGRATION COMMAND | +---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | CREATE TABLE world.City ( ID int(11) NOT NULL auto_increment, Name char(52) CHARACTER SET latin1 COLLATE latin1_swedish_ci NOT NULL DEFAULT '', CountryCode char(3) CHARACTER SET latin1 COLLATE latin1_swedish_ci NOT NULL DEFAULT '' , District char(20) CHARACTER SET latin1 COLLATE latin1_swedish_ci NOT NULL DEFAULT '' , Population int(11) NOT NULL DEFAULT '0' , KEY CountryCode (CountryCode), PRIMARY KEY (ID) ) ENGINE = DAMO_INNODB DEFAULT CHARSET=latin1 COMMENT ' (Enc.)'; | +---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.51 sec) +---------------------------------------------+ | ORG TABLE RENAME COMMAND | +---------------------------------------------+ | RENAME TABLE world.City TO world.City_damo; | +---------------------------------------------+ 1 row in set (0.51 sec) +-------------------------------------------------------+ | DATA INSERT COMMAND | +-------------------------------------------------------+ | INSERT INTO world.City SELECT * FROM world.City_damo; | +-------------------------------------------------------+ 1 row in set (1.00 sec) +-----------------------------+ | TEMP TABLE DROP COMMAND | +-----------------------------+ | DROP TABLE world.City_damo; | +-----------------------------+ 1 row in set (2.23 sec) +-------------------------------------------------------------------------------------------------------------------------+ | DEC MIGRATION COMMAND | +-------------------------------------------------------------------------------------------------------------------------+ | ALTER TABLE world.City MODIFY Name char(35) COLLATE latin1_swedish_ci NOT NULL DEFAULT '', ENGINE = InnoDB COMMENT ''; | +-------------------------------------------------------------------------------------------------------------------------+ 1 row in set (2.55 sec) Query OK, 0 rows affected (2.55 sec) - 驗證欄位是否有加密
用app使用者登入，他world.City.Name、world.Salary.salary有加密的權限，沒有解密的權限 [root@mysql-course mydiamo]# mysql -h127.0.0.1 -P3307 -uapp -papp world "app@127.0.0.1 [world]>select * from City limit 2; +----+-------------------------------------------------+-------------+----------+------------+ | ID | Name | CountryCode | District | Population | +----+-------------------------------------------------+-------------+----------+------------+ | 1 | *********************************************** | AFG | Kabol | 1780000 | | 2 | *********************************************** | AFG | Qandahar | 237500 | +----+-------------------------------------------------+-------------+----------+------------+ 2 rows in set (0.01 sec) "app@127.0.0.1 [world]>select * from Salary limit 2; +-----+------------+--------+-------+ | eid | name | salary | bonus | +-----+------------+--------+-------+ | 1 | Peter Pan | 0 | 2000 | ###因型態限制關係，加密後只會把正確的值變成0，而不會變成* | 2 | Judy Wendy | 0 | 20000 | +-----+------------+--------+-------+ 2 rows in set (0.01 sec) 用accountant使用者登入，他只有對world.Salary.salary加解密權限，對world.City.Name、world.Country.Name沒有解密權限 [root@mysql-course mydiamo]# mysql -h127.0.0.1 -P3307 -uaccountant -paccountant world "accountant@127.0.0.1 [world]>select * from City limit 2; +----+-------------------------------------------------+-------------+----------+------------+ | ID | Name | CountryCode | District | Population | +----+-------------------------------------------------+-------------+----------+------------+ | 1 | *********************************************** | AFG | Kabol | 1780000 | | 2 | *********************************************** | AFG | Qandahar | 237500 | +----+-------------------------------------------------+-------------+----------+------------+ 2 rows in set (0.01 sec) "accountant@127.0.0.1 [world]>select * from Country limit 2; +------+-------------+---------------+---------------------------+-------------+-----------+------------+----------------+---------+--------+-----------------------+----------------------------------------------+---------------+---------+-------+ | Code | Name | Continent | Region | SurfaceArea | IndepYear | Population | LifeExpectancy | GNP | GNPOld | LocalName | GovernmentForm | HeadOfState | Capital | Code2 | +------+-------------+---------------+---------------------------+-------------+-----------+------------+----------------+---------+--------+-----------------------+----------------------------------------------+---------------+---------+-------+ | ABW | Ar****** | North America | Caribbean | 193.00 | NULL | 103000 | 78.4 | 828.00 | 793.00 | Aruba | Nonmetropolitan Territory of The Netherlands | Beatrix | 129 | AW | | AFG | Af******tan | Asia | Southern and Central Asia | 652090.00 | 1919 | 22720000 | 45.9 | 5976.00 | NULL | Afganistan/Afqanestan | Islamic Emirate | Mohammad Omar | 1 | AF | +------+-------------+---------------+---------------------------+-------------+-----------+------------+----------------+---------+--------+-----------------------+----------------------------------------------+---------------+---------+-------+ 2 rows in set (0.00 sec) "accountant@127.0.0.1 [world]>select * from Salary; +-----+------------+--------+-------+ | eid | name | salary | bonus | +-----+------------+--------+-------+ | 1 | Peter Pan | 300000 | 2000 | | 2 | Judy Wendy | 70000 | 20000 | +-----+------------+--------+-------+ 2 rows in set (0.01 sec)
- 找不到mysqlbug
[root@mysql-course mydiamo]#./install-linux-mydiamo-3.0.9.4.run -p /opt/mysql/bin -h 127.0.0.1 -P 3307 ===================================================== MyDiamo Install Script ===================================================== [root@mysql-course D`Amo_MyDiamo_v3.0.9.4]# mysqlbug file does not exist(/opt/mysql/bin//mysql/mysqlbug).
解法一：把mysqlbug設軟連結
[root@mysql-course bin]# ln -s mysqld-debug mysqlbug [root@mysql-course bin]# ls -l mysqlbug lrwxrwxrwx 1 root root 12 Mar 22 14:01 mysqlbug -> mysqld-debug # ./install-linux-mydiamo-3.0.9.4.run -p /opt/mysql/bin -h 127.0.0.1 -P 3307 ===================================================== MyDiamo Install Script ===================================================== [root@mysql-course D`Amo_MyDiamo_v3.0.9.4]# mysqlbug file does not exist(/opt/mysql/bin//mysql/mysqlbug).結果還是找不到檔案阿ⓞ﹏ⓞ
解法二： 搞了半天，是它在編碼裡面寫死了不管你前面帶的目錄是什麼，它就是依你給的目錄下找~./mysql/mysqlbug
所以最後我只好…把整個bin目錄做了軟連結(有夠蠢)
[root@mysql-course mysql]# ln -s /opt/mysql/bin /opt/mysql/mysql [root@mysql-course mysql]# ls -l /opt/mysql/mysql lrwxrwxrwx 1 root root 14 Mar 22 14:06 /opt/mysql/mysql -> /opt/mysql/bin
- The table has no privileges. Please check enc/dec or access privileges.
我用root權限在做migration的時候遇到這個狀況，是因為我的root沒有設定任何加解密的權，所以沒辬法執行這個指令，只需要把root帳號也設定權限即可
"root@127.0.0.1 [(none)]>call mysql.damo_table_migration('world','Salary'); +----------------------------------------------------------------+ | error | +----------------------------------------------------------------+ | Selected policy is not supported or has incorrect information. | +----------------------------------------------------------------+ 1 row in set (0.41 sec) MyDiamo > set priv enc root"world"City"Name"1"1 SET PRIV ENC U[root] O[world] T[City] C[Name] EP[1] DP[1] MyDiamo > set priv enc root"world"Country"Name"1"1 SET PRIV ENC U[root] O[world] T[Country] C[Name] EP[1] DP[1] MyDiamo > set priv enc root"world"Salary"salary"1"1 SET PRIV ENC U[root] O[world] T[Salary] C[salary] EP[1] DP[1] MyDiamo > save all SAVE ALL "root@127.0.0.1 [(none)]>call mysql.damo_table_migration('world','Salary'); +-----------+ | PARTITION | +-----------+ | | +-----------+ 1 row in set (0.23 sec) ....... Query OK, 0 rows affected (1.06 sec)
指令參考官方手冊：https://mydiamo.com/guide/operation/encryption/
留言
張貼留言