KMC資料庫設計

存一下金鑰管理中心資料庫，免得找不到了

具體表格：

表名	列名	資料型別	主鍵	外來鍵
Keys	KeyID	INT	PK
	KeyValue	VARBINARY(256)
	KeyType	VARCHAR(50)
	CreatedAt	DATETIME
	ExpirationDate	DATETIME
	Status	ENUM
Key_Pools	PoolID	INT	PK
	PoolName	ENUM
	Description	VARCHAR(255)
Key_Pool_Assignment	AssignmentID	INT	PK
	KeyID	INT		FK (Keys.KeyID)
	PoolID	INT		FK (Key_Pools.PoolID)
	AssignedAt	DATETIME
Key_Usage	UsageID	INT	PK
	KeyID	INT		FK (Keys.KeyID)
	UserID	INT		FK (Users.UserID)
	Action	ENUM
	Timestamp	DATETIME
Users	UserID	INT	PK
	Username	VARCHAR(50)
	PasswordHash	VARBINARY(256)
	CreatedAt	DATETIME
	LastLogin	DATETIME
	Role	ENUM
Roles	RoleID	INT	PK
	RoleName	ENUM
	Description	VARCHAR(255)
User_Roles	UserRoleID	INT	PK
	UserID	INT		FK (Users.UserID)
	RoleID	INT		FK (Roles.RoleID)
Key_Policies	PolicyID	INT	PK
	KeyID	INT		FK (Keys.KeyID)
	AllowedUsers	VARCHAR(255)
	ExpirationDuration	INT
Audit_Log	LogID	INT	PK
	KeyID	INT		FK (Keys.KeyID)
	UserID	INT		FK (Users.UserID)
	Action	VARCHAR(255)
	Timestamp	DATETIME
Key_Revocation	RevocationID	INT	PK
	KeyID	INT		FK (Keys.KeyID)
	RevocationReason	VARCHAR(255)
	RevocationDate	DATETIME
Key_Operation_Sessions	SessionID	VARCHAR(50)	PK
	KeyID	INT		FK (Keys.KeyID)
	Operation	ENUM
	UserID	INT		FK (Users.UserID)
	Timestamp	DATETIME

sql設計：

CREATE TABLE Keys (
    KeyID INT PRIMARY KEY AUTO_INCREMENT,
    KeyValue VARBINARY(256) NOT NULL,
    KeyType VARCHAR(50) NOT NULL,
    CreatedAt DATETIME DEFAULT CURRENT_TIMESTAMP,
    ExpirationDate DATETIME,
    Status ENUM('ACTIVE', 'BACKUP', 'DESTROYED', 'REVOKED') DEFAULT 'ACTIVE'
);


CREATE TABLE Key_Pools (
    PoolID INT PRIMARY KEY AUTO_INCREMENT,
    PoolName ENUM('ACTIVE_POOL', 'BACKUP_POOL', 'DESTROYED_POOL') NOT NULL,
    Description VARCHAR(255)
);


CREATE TABLE Key_Pool_Assignment (
    AssignmentID INT PRIMARY KEY AUTO_INCREMENT,
    KeyID INT,
    PoolID INT,
    AssignedAt DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (KeyID) REFERENCES Keys(KeyID),
    FOREIGN KEY (PoolID) REFERENCES Key_Pools(PoolID)
);


CREATE TABLE Key_Usage (
    UsageID INT PRIMARY KEY AUTO_INCREMENT,
    KeyID INT,
    UserID INT,
    Action ENUM('ENCRYPT', 'DECRYPT', 'SIGN', 'VERIFY') NOT NULL,
    Timestamp DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (KeyID) REFERENCES Keys(KeyID),
    FOREIGN KEY (UserID) REFERENCES Users(UserID)
);


CREATE TABLE Users (
    UserID INT PRIMARY KEY AUTO_INCREMENT,
    Username VARCHAR(50) UNIQUE NOT NULL,
    PasswordHash VARBINARY(256) NOT NULL,
    CreatedAt DATETIME DEFAULT CURRENT_TIMESTAMP,
    LastLogin DATETIME,
    Role ENUM('SYSTEM_ADMIN', 'AUDITOR', 'OPERATOR') NOT NULL -- 使用者角色
);


CREATE TABLE Key_Policies (
    PolicyID INT PRIMARY KEY AUTO_INCREMENT,
    KeyID INT,
    AllowedUsers VARCHAR(255),
    ExpirationDuration INT,
    FOREIGN KEY (KeyID) REFERENCES Keys(KeyID)
);


CREATE TABLE Audit_Log (
    LogID INT PRIMARY KEY AUTO_INCREMENT,
    KeyID INT,
    UserID INT,
    Action VARCHAR(255),
    Timestamp DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (KeyID) REFERENCES Keys(KeyID),
    FOREIGN KEY (UserID) REFERENCES Users(UserID)
);


CREATE TABLE Key_Revocation (
    RevocationID INT PRIMARY KEY AUTO_INCREMENT,
    KeyID INT,
    RevocationReason VARCHAR(255),
    RevocationDate DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (KeyID) REFERENCES Keys(KeyID)
);

CREATE TABLE Roles (
    RoleID INT PRIMARY KEY AUTO_INCREMENT,
    RoleName ENUM('SYSTEM_ADMIN', 'AUDITOR', 'OPERATOR') UNIQUE NOT NULL,
    Description VARCHAR(255)
);

CREATE TABLE User_Roles (
    UserRoleID INT PRIMARY KEY AUTO_INCREMENT,
    UserID INT,
    RoleID INT,
    FOREIGN KEY (UserID) REFERENCES Users(UserID),
    FOREIGN KEY (RoleID) REFERENCES Roles(RoleID)
);

CREATE TABLE Key_Operation_Sessions (
    SessionID VARCHAR(50) PRIMARY KEY,
    KeyID INT,
    Operation ENUM('調金鑰', '凍結', '銷燬') NOT NULL,
    UserID INT,
    Timestamp DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (KeyID) REFERENCES Keys(KeyID),
    FOREIGN KEY (UserID) REFERENCES Users(UserID)
);