Home | 簡體中文 | 繁體中文 | 雜文 | Search | ITEYE 博客 | OSChina 博客 | Facebook | Linkedin | 知乎專欄 | Email

5.4. 為數據安全而分庫

我們通常使用一個資料庫開發,該資料庫包含了前後台所有的功能,我建議將前後台等等功能進行分庫然後對應各種平台分配用戶權限,例如

我們創建三個資料庫cms,frontend,backend 同時對應創建三個用戶 cms,frontend,backend 三個用戶只能分別訪問自己的資料庫,注意在系統的設計之初你要考慮好這樣的劃分隨之系統需要做相應的調整。

CREATE DATABASE `cms` /*!40100 COLLATE 'utf8_general_ci' */;
CREATE DATABASE `frontend` /*!40100 COLLATE 'utf8_general_ci' */;
CREATE DATABASE `backend` /*!40100 COLLATE 'utf8_general_ci' */;
		

backend 負責後台,權限最高

mysql> SHOW GRANTS FOR 'backend'@'localhost';
+--------------------------------------------------------------------------------------+
| Grants for backend@localhost                                                         |
+--------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'backend'@'localhost'                                          |
| GRANT SELECT, INSERT, UPDATE, DELETE ON `cms`.* TO 'backend'@'localhost'             |
| GRANT SELECT, INSERT, UPDATE, DELETE ON `frontend`.* TO 'backend'@'localhost'        |
| GRANT SELECT, INSERT, UPDATE, DELETE, CREATE ON `backend`.* TO 'backend'@'localhost' |
+--------------------------------------------------------------------------------------+
4 rows in set (0.04 sec)		
		

frontend 是前台權限,主要是用戶用戶中心,用戶註冊,登錄,用戶信息資料編輯,查看新聞等等

mysql> SHOW GRANTS FOR 'frontend'@'localhost';
+------------------------------------------------------------------------+
| Grants for frontend@localhost                                          |
+------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'frontend'@'localhost'                           |
| GRANT SELECT, INSERT, UPDATE ON `frontend`.* TO 'frontend'@'localhost' |
| GRANT SELECT ON `cms`.`news` TO 'frontend'@'localhost'                 |
+------------------------------------------------------------------------+
3 rows in set (0.00 sec)		
		

cms 用戶是網站內容管理,主要負責內容更新,但登陸CMS後台需要`backend`.`Employees`表用戶認證,所以他需要讀取權限,但不允許修改其中的數據。

mysql> SHOW GRANTS FOR 'cms'@'localhost';
+----------------------------------------------------------------------+
| Grants for cms@localhost                                             |
+----------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'cms'@'localhost'                              |
| GRANT SELECT, INSERT, UPDATE, DELETE ON `cms`.* TO 'cms'@'localhost' |
| GRANT SELECT ON `backend`.`Employees` TO 'cms'@'localhost'           |
+----------------------------------------------------------------------+
3 rows in set (0.00 sec)