【SQL權限管理教學】完整設定登入帳戶、用戶權限與存取控制全攻略

在資料庫管理中，帳戶與權限設定是確保資料安全的關鍵步驟。以下將以 MySQL 為例，說明如何建立帳戶、設定權限，並管理登入安全性。此外，針對 SQL Server 也將介紹伺服器角色設定方式。

一、建立資料庫帳戶

  1. 連線至 MySQL 伺服器：
mysql -u root -p
  1. 創建新使用者：
CREATE USER 'username'@'localhost' IDENTIFIED BY 'password';
  • username：欲新增的帳戶名稱。
  • localhost：限制該帳戶只能從本地連線。
  • password：設定密碼。

若允許從遠端連線：

CREATE USER 'username'@'%' IDENTIFIED BY 'password';

二、授權資料庫操作權限

  1. 賦予全部權限（常用於開發環境，生產環境應謹慎使用）：
GRANT ALL PRIVILEGES ON database_name.* TO 'username'@'localhost';
  • database_name.*：授予特定資料庫的所有表格權限。
  1. 授權特定操作權限：
GRANT SELECT, INSERT, UPDATE, DELETE ON database_name.* TO 'username'@'localhost';
  1. 刷新權限：
FLUSH PRIVILEGES;

三、檢視與管理權限

  1. 查看使用者權限：
SHOW GRANTS FOR 'username'@'localhost';
  1. 撤銷權限：
REVOKE INSERT, DELETE ON database_name.* FROM 'username'@'localhost';
  1. 刪除使用者：
DROP USER 'username'@'localhost';

四、SQL Server 伺服器角色說明

在 SQL Server 中，伺服器角色用於管理伺服器範疇的權限。以下列出常見內建伺服器角色：

  1. sysadmin：擁有最高權限，可執行所有伺服器操作。
  2. serveradmin：管理伺服器設定，如啟動、停止伺服器等。
  3. securityadmin：管理登入帳戶與伺服器層級權限。
  4. processadmin：終止執行中的處理程序。
  5. setupadmin：管理伺服器物件，如連結伺服器等。
  6. diskadmin：管理磁碟檔案。
  7. dbcreator：創建、變更、刪除資料庫。
  8. bulkadmin：執行批次匯入操作。

指派伺服器角色：

ALTER SERVER ROLE sysadmin ADD MEMBER [username];

檢視使用者所屬伺服器角色：

SELECT * FROM sys.server_role_members;

五、安全性建議

  1. 使用強密碼。
  2. 限制管理帳戶連線來源。
  3. 為不同功能劃分不同帳戶，例如：
    • 讀取帳戶（只允許 SELECT）
    • 寫入帳戶（INSERT、UPDATE、DELETE）
    • 管理帳戶（ALTER、DROP 等）
  4. 定期審核使用者權限。
  5. 啟用 SSL 連線保護資料傳輸。

六、常見問題解答

  1. 如何變更使用者密碼？
ALTER USER 'username'@'localhost' IDENTIFIED BY 'newpassword';
  1. 無法連線：
    • 確認使用者存在：
SELECT User, Host FROM mysql.user;
  • 檢查權限：
SHOW GRANTS FOR 'username'@'localhost';
  1. 避免使用 root 帳戶處理日常業務。

透過上述設定，能有效管理資料庫使用者帳戶與權限，確保資料庫安全性並降低潛在風險。

Related Posts

發佈留言