ACL

From ChatGPT

在構建網站的 ACL（存取控制列表）時，你可以將權限管理系統與資料庫結合起來，根據使用者的角色、資源的類型，以及允許的操作來控制存取。以下是如何設計一個網站的 ACL 及其在資料庫中的實現步驟：

1. 定義基本概念

通常，ACL 涉及三個核心概念：

• 使用者（Users）：系統中不同的使用者。
• 資源（Resources）：需要被控制存取的物件，如網頁、API、檔案等。
• 權限（Permissions）：允許執行的操作，如讀取、寫入、修改、刪除。

2. 資料庫架構設計

需要在資料庫中設置多個表來儲存使用者、資源和權限的資訊。以下是一個簡單的資料庫架構：

(1) Users 表

這個表用來存放網站中的使用者資訊。

CREATE TABLE users (
  id SERIAL PRIMARY KEY,
  username VARCHAR(255) NOT NULL,
  password VARCHAR(255) NOT NULL
);

(2) Roles 表

這個表用來定義網站中的不同角色，例如 admin、editor、viewer 等。

CREATE TABLE roles (
  id SERIAL PRIMARY KEY,
  role_name VARCHAR(255) NOT NULL
);

(3) Permissions 表

這個表用來定義所有可能的權限（動作），例如 read、write、delete、update。

CREATE TABLE permissions (
  id SERIAL PRIMARY KEY,
  permission_name VARCHAR(255) NOT NULL
);

(4) Resources 表

這個表代表網站中可受權限控制的資源（例如頁面或 API 路徑）。

CREATE TABLE resources (
  id SERIAL PRIMARY KEY,
  resource_name VARCHAR(255) NOT NULL,
  resource_type VARCHAR(255) NOT NULL -- e.g., "page", "api", etc.
);

(5) Role_Permissions 表

這是 ACL 的核心，連結角色與權限，定義每個角色可以對哪些資源進行哪些操作。

CREATE TABLE role_permissions (
  id SERIAL PRIMARY KEY,
  role_id INT REFERENCES roles(id),
  resource_id INT REFERENCES resources(id),
  permission_id INT REFERENCES permissions(id)
);

(6) User_Roles 表

這個表用來管理每個使用者所屬的角色。

CREATE TABLE user_roles (
  id SERIAL PRIMARY KEY,
  user_id INT REFERENCES users(id),
  role_id INT REFERENCES roles(id)
);

3. 範例數據插入

假設有一個網站，資源是「文章頁面」，有 admin、editor 和 viewer 三種角色，各自擁有不同的權限。

插入角色

INSERT INTO roles (role_name) VALUES ('admin'), ('editor'), ('viewer');

插入權限

INSERT INTO permissions (permission_name) VALUES ('read'), ('write'), ('delete');

插入資源

INSERT INTO resources (resource_name, resource_type) VALUES ('article_page', 'page');

定義角色與權限的關係

假設：

• admin 可以讀、寫、刪除文章頁面；
• editor 可以讀和寫文章頁面；
• viewer 只能讀文章頁面。

-- Admin's permissions
INSERT INTO role_permissions (role_id, resource_id, permission_id)
VALUES
  (1, 1, 1), -- admin can read article
  (1, 1, 2), -- admin can write article
  (1, 1, 3); -- admin can delete article

-- Editor's permissions
INSERT INTO role_permissions (role_id, resource_id, permission_id)
VALUES
  (2, 1, 1), -- editor can read article
  (2, 1, 2); -- editor can write article

-- Viewer's permissions
INSERT INTO role_permissions (role_id, resource_id, permission_id)
VALUES
  (3, 1, 1); -- viewer can only read article

分配角色給使用者

假設有三個使用者，Alice 是 admin，Bob 是 editor，Charlie 是 viewer。

INSERT INTO users (username, password) VALUES ('Alice', 'password123'), ('Bob', 'password123'), ('Charlie', 'password123');

-- Assign roles
INSERT INTO user_roles (user_id, role_id)
VALUES
  (1, 1), -- Alice is admin
  (2, 2), -- Bob is editor
  (3, 3); -- Charlie is viewer

4. 檢查使用者權限

當使用者嘗試訪問某個資源時，你需要查詢他們的角色和權限。這可以通過一個 SQL 查詢來完成。例如，檢查 Alice 是否有權限刪除文章頁面：

SELECT 1
FROM user_roles ur
JOIN role_permissions rp ON ur.role_id = rp.role_id
JOIN permissions p ON rp.permission_id = p.id
JOIN resources r ON rp.resource_id = r.id
WHERE ur.user_id = 1
AND p.permission_name = 'delete'
AND r.resource_name = 'article_page';

如果查詢返回結果，表示 Alice 有權刪除文章頁面。

5. 結合應用程式

在你的後端應用（例如 Node.js + Express）中，當使用者嘗試訪問某個資源時，你可以執行類似的查詢來檢查權限，根據查詢結果允許或拒絕操作。

總結

這個 ACL 系統通過角色、資源和權限的關聯，為網站提供了靈活的權限管理。這樣的架構能夠擴展到不同的資源類型與操作，並且可以輕鬆地根據業務需求擴充。

你可以根據具體需求進行進一步的細化，例如增加更多的權限類型（如 create、update）、引入更細緻的資源分類等。

在商用網站中，當一個使用者想刪除某個項目時，應如何在資料庫中設計其結構來表示刪除操作，通常有兩種常見方法：

1. 硬刪除（Hard Delete）

硬刪除是指從資料庫中永久刪除記錄。這是最簡單的方法，但存在一些風險，因為一旦資料被刪除，便無法恢復。這種方式不會在資料庫中額外添加欄位。

優點：

• 節省資料庫空間，簡單明瞭。

缺點：

• 資料無法恢復，可能在業務邏輯上不夠安全。
• 無法保留刪除的記錄或歷史資料。

硬刪除的 SQL 語句範例：

DELETE FROM items WHERE id = 123;

在實務操作中，硬刪除比較適合非關鍵性資料或一些需要及時清理的資料，如系統日誌等。

2. 軟刪除（Soft Delete）

軟刪除是一種常見的替代方案，當使用者選擇刪除資料時，實際上並沒有從資料庫中刪除記錄，而是通過更新某個標誌欄位（通常稱為 deleted_at 或 is_deleted）來標記該記錄為「已刪除」。這樣可以保留數據的完整性並支持恢復。

軟刪除表結構範例：

假設有一個 items 表，用來儲存使用者的項目：

CREATE TABLE items (
  id SERIAL PRIMARY KEY,
  name VARCHAR(255) NOT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  deleted_at TIMESTAMP DEFAULT NULL -- NULL 表示未刪除，否則記錄刪除時間
);

在軟刪除設計中，當使用者「刪除」某項目時，只需更新 deleted_at 欄位：

軟刪除的 SQL 語句範例：

UPDATE items
SET deleted_at = CURRENT_TIMESTAMP
WHERE id = 123;

這樣可以保留已刪除的項目，在系統中仍可查詢和恢復。例如，顯示使用者項目列表時，可以加上條件過濾掉已刪除的項目：

SELECT * FROM items WHERE deleted_at IS NULL;

優點：

• 安全：可以保留歷史記錄，並且允許恢復已刪除的數據。
• 可追溯：可以記錄刪除的具體時間，便於審計。

缺點：

• 資料庫中的記錄不會被實際刪除，隨著時間推移，資料表可能變得很大。
• 需要額外的過濾條件來排除已刪除的資料，可能會影響查詢性能。

3. 誰能刪除的邏輯（ACL 與使用者權限控制）

在商用網站中，刪除操作通常不是每個使用者都可以進行的，因此要結合 ACL（Access Control List） 或權限系統來控制誰可以刪除哪些項目。ACL 系統可以根據角色或個別使用者的權限來控制特定操作。

在資料庫中，使用者可能有一個 roles 表，或者像前面提到的權限控制表 role_permissions，來控制誰可以進行刪除操作。當使用者嘗試刪除某個項目時，後端會先檢查其權限。

權限查詢範例：

SELECT 1
FROM user_roles ur
JOIN role_permissions rp ON ur.role_id = rp.role_id
JOIN permissions p ON rp.permission_id = p.id
JOIN resources r ON rp.resource_id = r.id
WHERE ur.user_id = 1 -- 使用者 ID
AND p.permission_name = 'delete'
AND r.resource_name = 'items';

這樣可以確保只有具備刪除權限的使用者才能進行刪除操作。

4. 實務上的刪除操作與審計

在一些商用網站中，為了滿足合規性和審計需求，可能需要記錄每一次刪除操作，無論是硬刪除還是軟刪除。可以設計一個 logs 表來存儲每個刪除操作的詳細信息：

CREATE TABLE delete_logs (
  id SERIAL PRIMARY KEY,
  user_id INT REFERENCES users(id),
  item_id INT REFERENCES items(id),
  deleted_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  reason TEXT -- 刪除的原因
);

每當使用者進行刪除操作時，都將記錄進 delete_logs 表：

INSERT INTO delete_logs (user_id, item_id, reason)
VALUES (1, 123, 'User requested deletion');

總結

在商用網站中，使用軟刪除是一種更安全且靈活的做法，可以避免永久刪除資料導致的潛在問題，並且允許恢復。此外，結合 ACL 和權限系統，確保只有經過授權的使用者能進行刪除操作，這對於保護網站資料的安全性至關重要。

在資料庫結構中，deleted_at 是一個非常常見的欄位名稱，用來標記記錄是否已刪除。它能讓你有效管理和追蹤資料的刪除狀態。