KMC数据库设计

存一下密钥管理中心数据库，免得找不到了

具体表格：

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)
);