【登录注册】用户表结构详细设计及实现

业务需求

1. 支持密码登录、手机号登录、三方账号登录（如微信、邮箱、QQ）
2. 支持退出登录、账号注销
3. 一个用户只能绑定一个手机号
4. 用户名30天只能修改一次
5. 敏感信息脱敏处理

表结构设计

• 用户基本信息表 users
• 用户扩展信息表 user_extents
• 用户认证信息表 user_auth

users和user_extends是一对一关系，users和user_auth是一对多关系。

表字段

用户基本信息表 users

field	type	comment
id	bigint	pk
username	varchar(50)	用户名
password_hash	varchar(128)	密码哈希
salt	varchar(128)	密码盐值
avatar	varchar	用户头像
gender	smallint	性别
birth	date	出生日期
email	varchar	电子邮件
phone	varchar(15)	电话号码
user_type	varchar(20)	用户类型
create_time	bigint	创建时间
update_time	bigint	更新时间
status	smallint	状态
deleted	boolean	是否已删除
delete_id	bigint	删除ID

联合唯一约束:
(phone, delete_id)
其中delete_id字段是为了解决用户逻辑删除之后导致唯一约束冲突的问题

用户扩展信息表 user_extents

field	type	comment
id	bigint	pk
user_id	bigint	用户ID
introduce	varchar(50)	简介
is_realname	boolean	是否实名
realname	varchar(50)	真实姓名
last_login_ip	varchar(40)	最后登录IP
last_login_address	varchar	最后登录地址
last_login_time	integer	最后登录时间
last_login_type	varchar(32)	最后登录类型
create_time	integer	创建时间
update_time	integer	更新时间
device_id	varchar	设备ID
last_username_utime	integer	最近一次用户名更新的时间

唯一约束：
user_id

用户认证表 user_auth

field	type	comment
id	bigint	pk
user_id	bigint	用户ID
auth_type	varchar(32)	认证类型
openid	varchar	三方应用唯一标识
credential	varchar	三方应用颁发的凭证
union_id	varchar	wx unionid
create_time	int	创建时间

联合唯一约束：
(user_id, auth_type)
(auth_type, openid)

功能实现

密码登录

注意：用户表中不能直接存明文密码，也不能直接存密码hash值（容易被彩虹表攻击），而是以hash+盐存储，加密方式推荐使用 动态盐 + 非固定加密算法

以下为bcrypt和sha-256加解密实现：

import hashlib
import secrets
import bcrypt
import time
from collections import namedtuple


User = namedtuple("User", "uid, username, pwd_hash, salt")

def create_user(uid, username, pwd, algorithm="sha-256"):
    if algorithm == "sha-256":
        salt = gen_salt(uid, username)
        hashed = gen_hashed_pwd(salt, pwd)
    elif algorithm == "bcrypt":
        salt = bcrypt.gensalt(rounds=12)
        hashed = bcrypt.hashpw(pwd.encode(), salt)
        
    return User(uid=uid, username=username, pwd_hash=hashed, salt=salt)

def gen_salt(uid, username):
    # 组合随机盐值:时间戳:id:用户名
    salt_str = f"{secrets.token_hex(16)}:{int(time.time())}:{uid}:{username}"
    return hashlib.sha256(salt_str.encode("utf-8")).hexdigest()

def gen_hashed_pwd(salt, password):
    """使用 SHA-256 算法将 salt 和 password 进行 hash"""
    hashed = hashlib.sha256((salt + password).encode("utf-8")).hexdigest()
    return hashed

def check_pwd(user, password, algorithm="sha-256"):
    """验证密码是否正确"""
    if algorithm == "sha-256":
        return user.pwd_hash == hashlib.sha256((user.salt + password).encode("utf-8")).hexdigest()
    elif algorithm == "bcrypt":
        return bcrypt.checkpw(password.encode(), user.pwd_hash)


Alice = create_user(10000236, "Alice", "abc123", algorithm="bcrypt")
is_pass = check_pwd(Alice, "abc123", algorithm="bcrypt")

print("user: ", Alice)
print("is_pass: ", is_pass)

两种算法对比：

• bcrypt不需要额外存储salt，是直接存储于hash值中，而sha-256必须额外存储salt
• bcrypt加密程序片段执行时间约288ms，sha-256执行时间约0.2ms，相差1000个量级；解密耗时和加密耗时基本相同
• bcrypt算法安全性比sha-256更高，生成盐值时可以设置工作因子rounds，大小代表了hash次数，越高越安全，但解密耗时也会更高

三方应用登录

以小程序微信授权登录为例，下图展示了登录授权流程：

sequenceDiagram
     Note over Client: Enter App 
    Client ->> Client:  check custom login state <br> if not login:  wx.login() get code
    Client ->> Server: wx.request() send code
    Server ->> Wechat: credential checking <br> appid + secret + code
    Wechat ->> Server: session_key + openid ..
 
    Server ->> Server: link to openid & session_key
    Server ->> Client: custom token
    Client ->> Client: storage token
    Client ->> Server: send request with token

• 请求三方平台获取openid及credential
• 通过auth_type + openid查询user_auth表中user是否存在，不存在则创建一个新用户
• 服务端生成自定义token，返回给客户端

JWT介绍

参考 jwt.io/introductio…

JWT(Json Web Token)包含3部分：
包含三部分，会对其进行Base64Url编码

• Header 头部通常包含令牌类型和签名算法
• Payload 载荷包含一些声明（claims）及附加数据
• Signature 签名用于验证发送者的身份，信息在传输过程中是否被更改

access token & refresh token:

OAUTH2.0中使用到了refresh token，专门用来刷新 access token，其中有效期 access token > refresh token，使用双token可以实现无感刷新：

• 登录成功后返回access token 和 refresh token
• access token 如果过期, 使用 refresh token 重新获取 access_token；
• refresh token过期则重新授权

jwt存储方案:

• 黑名单机制
  主动撤销时加入黑名单，过期时间设为当前token剩余有效期

• 白名单机制
  发放令牌时将jti作为key存储到redis中，但是相较于黑名单，会占用更多的内存，相较于黑名单机制可以主动管理用户登录态，比如踢人下线

这里就有一个疑问了，jwt的一个鲜明特点就是无状态，不用占用服务端资源，但是又必须结合服务端来管理用户登录态，这就和传统的session + redis方案没什么区别了

退出登录

直接撤销token

数据脱敏

最简单的方式：
存储源数据，序列化时按特定规则对数据局部遮掩，比如隐藏手机号中间4位