oauth 怎么存数据库

OAuth作为一种开放标准授权协议，广泛应用于第三方应用快速接入各类服务平台的场景，在实际业务系统中，合理设计数据库存储方案是保障系统安全性、可扩展性和性能的关键，以下从核心数据项分类、数据库表结构设计、安全存储规范、典型场景实现四个维度展开详细说明,并提供完整示例供参考。

---

OAuth核心数据项及存储需求分析

数据类型	典型示例	存储目标	关键特性要求
客户端凭证	Client ID / Client Secret	oauth_clients表	唯一标识+机密性保护
授权关系	User-App绑定关系	oauth_user_app_relations表	多对多关联+状态管理
访问令牌	Access Token / Expiration Time	oauth_access_tokens表	短期有效+自动过期清理
刷新令牌	Refresh Token / Revocation Status	oauth_refresh_tokens表	长期有效但可主动撤销
审计日志	Grant Request Logs	oauth_audit_logs表	操作追溯+防改动

▶ 重点说明：

1. 客户端凭证：由服务端颁发的唯一标识符（Client ID）及其配套的保密字符串（Client Secret）,需严格保密且禁止前端暴露；
2. 授权关系：建立用户与第三方应用间的映射关系，需记录授权范围（Scope）、创建时间等元数据；
3. 访问令牌：短期有效的临时凭证（通常15分钟~2小时）,用于API调用身份验证；
4. 刷新令牌：长期有效的补充凭证,可用于获取新的访问令牌而无需重新授权；
5. 审计日志：记录每次授权请求的详细信息,用于安全事件回溯。

---

数据库表结构设计示例（MySQL语法）

客户端信息表 oauth_clients

CREATE TABLE oauth_clients (
    id INT PRIMARY KEY AUTO_INCREMENT,
    client_id VARCHAR(80) NOT NULL UNIQUE,      -OAuth标准要求的客户端ID
    client_secret VARCHAR(256) NOT NULL,        -随机生成的加密字符串（建议使用BCrypt哈希）
    name VARCHAR(255) NOT NULL,                 -应用名称（展示用）
    redirect_uris JSON NOT NULL,                -允许的回调地址列表（数组形式）
    scopes JSON NOT NULL,                       -默认授权范围（如["read", "write"]）
    is_active BOOLEAN DEFAULT TRUE,             -是否启用该客户端
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_client_id (client_id)              -加速按ID查询
);

字段说明：

• client_secret应通过bcrypt算法进行哈希存储,而非明文；
• redirect_uris采用JSON数组存储,便于动态增减合法回调地址；
• is_active标记用于紧急禁用可疑客户端。

用户-应用关系表 oauth_user_app_relations

CREATE TABLE oauth_user_app_relations (
    id INT PRIMARY KEY AUTO_INCREMENT,
    user_id BIGINT NOT NULL,                     -关联用户表的主键
    client_id VARCHAR(80) NOT NULL,             -对应oauth_clients.client_id
    scopes JSON NOT NULL,                       -本次授权的实际范围
    granted_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    revoked_at TIMESTAMP NULL,                  -撤销时间（NULL表示未撤销）
    FOREIGN KEY (user_id) REFERENCES users(id),
    FOREIGN KEY (client_id) REFERENCES oauth_clients(client_id),
    UNIQUE KEY uniq_user_client (user_id, client_id) -同一用户对同一应用仅一条有效记录
);

设计要点：

• 联合唯一索引uniq_user_client确保用户对同一应用只能存在一条有效授权记录；
• revoked_at字段用于标记已撤销的授权关系。

访问令牌表 oauth_access_tokens

CREATE TABLE oauth_access_tokens (
    id INT PRIMARY KEY AUTO_INCREMENT,
    access_token VARCHAR(256) NOT NULL UNIQUE,  -JWT或随机字符串生成的令牌
    client_id VARCHAR(80) NOT NULL,             -所属客户端ID
    user_id BIGINT NOT NULL,                    -归属用户ID
    expires_at TIMESTAMP NOT NULL,              -精确到期时间（非相对时间）
    scopes JSON NOT NULL,                       -本次令牌的权限范围
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (client_id) REFERENCES oauth_clients(client_id),
    FOREIGN KEY (user_id) REFERENCES users(id),
    INDEX idx_access_token (access_token)        -快速查找令牌有效性
);

注意事项：

• 建议使用JWT（JSON Web Token）作为访问令牌,其本身包含过期时间和签名验证；
• 即使使用JWT,仍需在数据库中留存记录以便主动撤销未过期的令牌。

刷新令牌表 oauth_refresh_tokens

CREATE TABLE oauth_refresh_tokens (
    id INT PRIMARY KEY AUTO_INCREMENT,
    refresh_token VARCHAR(256) NOT NULL UNIQUE, -刷新令牌值
    access_token_id INT NOT NULL,                -关联的访问令牌ID
    client_id VARCHAR(80) NOT NULL,              -所属客户端ID
    user_id BIGINT NOT NULL,                     -归属用户ID
    expires_at TIMESTAMP NOT NULL,               -刷新令牌过期时间（通常较长）
    is_used BOOLEAN DEFAULT FALSE,              -是否已被使用过
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (access_token_id) REFERENCES oauth_access_tokens(id),
    FOREIGN KEY (client_id) REFERENCES oauth_clients(client_id),
    FOREIGN KEY (user_id) REFERENCES users(id)
);

关键逻辑：

• 每个刷新令牌仅能兑换一次新的访问令牌；
• 当访问令牌被撤销时,对应的刷新令牌也应同步失效。

审计日志表 oauth_audit_logs

CREATE TABLE oauth_audit_logs (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    log_type ENUM('AUTHORIZATION', 'TOKEN_ISSUANCE', 'TOKEN_REVOCATION') NOT NULL,
    client_id VARCHAR(80),
    user_id BIGINT,
    request_ip VARCHAR(45),                      -发起请求的IP地址
    request_params JSON,                        -请求参数快照
    response_status SMALLINT,                   -HTTP状态码
    description TEXT,                           -人工可读的描述信息
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_log_type (log_type),               -按日志类型筛选
    INDEX idx_created_at (created_at)           -时间范围查询优化
);

作用：满足GDPR等合规要求,支持安全事件调查。

---

安全存储规范与实施建议

必须遵循的安全准则：

风险点	解决方案	示例代码片段（PHP）
Client Secret泄露	使用强随机字符串+BCrypt哈希存储	password_hash($secret, PASSWORD_BCRYPT)
SQL注入攻击	预处理语句+参数化查询	PDO::prepare() + execute()
令牌伪造	JWT签名验证+数据库二次校验	FirebaseJWTverify() + DB检查
敏感信息泄露	最小化返回字段+HTTPS加密传输	API响应仅返回必要字段
令牌劫持	绑定IP/设备指纹+频率限制	单设备每小时最多生成3个新令牌

推荐的技术组合：

1. 令牌生成：推荐使用firebase/php-jwt库生成带签名的JWT令牌；
2. 密码学安全：PHP中使用random_bytes(32)生成不可预测的随机字符串；
3. 数据库加密：对client_secret等敏感字段启用透明数据加密（TDE）；
4. 缓存层：Redis缓存高频访问的令牌状态,减少数据库压力。

---

典型业务场景实现流程

场景1：用户首次授权第三方应用

1. 用户点击”使用XX登录”按钮 → 跳转至授权服务器；
2. 用户登录成功后选择授权范围 → 生成code并重定向回应用；
3. 应用凭code换取访问令牌 → 存入oauth_access_tokens表；
4. 同时创建oauth_user_app_relations记录 → 建立用户-应用关联。

场景2：刷新访问令牌

1. 应用携带有效刷新令牌调用/token接口；
2. 验证刷新令牌未被使用且未过期；
3. 生成新的访问令牌 → 更新oauth_access_tokens表；
4. 将旧刷新令牌标记为已使用 → is_used=TRUE。

场景3：主动撤销所有令牌

-撤销某用户的所有访问令牌
UPDATE oauth_access_tokens SET deleted_at = NOW() WHERE user_id = ?;
-同时使相关刷新令牌失效
UPDATE oauth_refresh_tokens SET is_valid = FALSE WHERE access_token_id IN (SELECT id FROM oauth_access_tokens WHERE user_id = ? AND deleted_at IS NOT NULL);

---

常见误区与最佳实践

错误做法：

• × 将Client Secret硬编码在前端代码中；
• × 使用自增ID作为访问令牌（易被猜测）；
• × 忽略令牌撤销机制（导致注销后仍能访问）。

️ 最佳实践：

• 采用”滑动过期”策略：每次使用访问令牌时更新其过期时间；
• 实施分级权限控制：不同Scope对应不同的API端点；
• 定期清理历史数据：删除90天前的审计日志和无效令牌；
• 启用双因素认证：对管理后台修改Client Secret的操作强制MFA。

---

FAQs（常见问题解答）

Q1: 如果同一个用户多次授权同一个应用会发生什么？

A: 根据oauth_user_app_relations表的唯一约束，系统会执行以下任一操作：①覆盖原有授权记录（更新scopes和granted_at）；②抛出重复授权异常，推荐采用第一种方式,并在更新时扩展而非缩小原有授权范围。

Q2: 如何处理移动端App的深度链接场景？

A: 可通过两种方式解决：①自定义Scheme协议（如myapp://callback?code=XXX）；②Universal Links（iOS）+ App Links（Android），无论哪种方式，都需要在oauth_clients表的redirect_uris字段中预先注册