权限管理系统表设计

----------------------------权限信息管理用到-------------------------------------------
–用户信息表
CREATE TABLE k_user (
id bigint(11) NOT NULL AUTO_INCREMENT COMMENT ‘编号’,
account varchar(50) NOT NULL COMMENT ‘用户名’,
password varchar(100) NOT NULL COMMENT ‘密码’,
user_name varchar(45) NOT NULL COMMENT ‘姓名’,
depart_code varchar(12) NOT NULL COMMENT ‘部门编码’,
user_pic varchar(255) DEFAULT ‘nopic’ COMMENT ‘头像’,
user_type varchar(3) NOT NULL DEFAULT ‘1’ COMMENT ‘用户类型 1 普通用户 2 管理员 3 超级管理员’,
birthday datetime DEFAULT NULL COMMENT ‘生日’,
sex char(1) DEFAULT NULL COMMENT ‘性别’,
email varchar(45) DEFAULT NULL COMMENT ‘邮箱’,
phone varchar(11) DEFAULT NULL COMMENT ‘手机号码’,
qq varchar(32) DEFAULT NULL COMMENT ‘qq号码’,
status varchar(3) NOT NULL default ‘1’ COMMENT ‘用户状态’,
create_time datetime NOT NULL default CURRENT_TIMESTAMP COMMENT ‘创建日期’,
update_time datetime DEFAULT NULL COMMENT ‘更新日期’,
bz varchar(200) COMMENT ‘备注’,
PRIMARY KEY (id),
UNIQUE KEY unique_user_account (account)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COMMENT=‘用户信息表’;

–部门信息表
CREATE TABLE k_department (
id int(11) NOT NULL AUTO_INCREMENT COMMENT ‘编号’,
departname varchar(255) NOT NULL COMMENT ‘部门名称’,
departcode varchar(12) NOT NULL COMMENT ‘部门编码’,
parentid int(11) NOT NULL default 0 COMMENT ‘父节点’,
sort int(11) DEFAULT 1 COMMENT ‘排序’,
klevel int(11) DEFAULT NULL COMMENT ‘层级’,
iconcls varchar(100) DEFAULT NULL COMMENT ‘图标’,
createtime datetime NOT NULL default CURRENT_TIMESTAMP COMMENT ‘创建日期’,
status varchar(3) DEFAULT ‘1’ COMMENT ‘状态’,
bz varchar(200) COMMENT ‘备注’,
PRIMARY KEY (id),
UNIQUE KEY unique_department_departcode (departcode)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COMMENT=‘部门信息表’;

–菜单信息表
CREATE TABLE k_menu (
id bigint(11) NOT NULL AUTO_INCREMENT COMMENT ‘编号’,
menu_code varchar(20) NOT NULL COMMENT ‘菜单编码’,
menu_name varchar(100) NOT NULL COMMENT ‘名称’,
url varchar(200) DEFAULT NULL COMMENT ‘请求地址’,
parent_id int(11) NOT NULL default 0 COMMENT ‘父节点’,
menu_type varchar(3) DEFAULT ‘1’ COMMENT ‘菜单类型’,
klevel int(11) DEFAULT NULL COMMENT ‘层级’,
sort int(11) DEFAULT 1 COMMENT ‘排序’,
status varchar(3) DEFAULT ‘1’ COMMENT ‘状态’,
iconcls varchar(100) DEFAULT NULL COMMENT ‘图标’,
create_time datetime NOT NULL default CURRENT_TIMESTAMP COMMENT ‘创建日期’,
PRIMARY KEY (id),
UNIQUE KEY FK_munu_CODE (menu_code)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COMMENT=‘菜单信息表’;

–角色信息表
CREATE TABLE k_role (
id bigint(11) NOT NULL AUTO_INCREMENT COMMENT ‘编号’,
role_name varchar(150) NOT NULL COMMENT ‘角色名称’,
role_code varchar(20) DEFAULT NULL COMMENT ‘角色编码’,
create_time datetime NOT NULL default CURRENT_TIMESTAMP COMMENT ‘创建日期’,
status char(1) NOT NULL DEFAULT ‘1’ COMMENT ‘状态’,
bz varchar(200) COMMENT ‘备注’,
PRIMARY KEY (id),
UNIQUE KEY unique_role_name (role_name),
UNIQUE KEY unique_role_value (role_code)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COMMENT=‘角色信息表’;

–角色-目录信息表
CREATE TABLE k_rolemenu (
id bigint(11) NOT NULL AUTO_INCREMENT COMMENT ‘编号’,
role_id bigint(11) NOT NULL COMMENT ‘角色id’,
menu_id bigint(11) NOT NULL COMMENT ‘菜单id’,
create_time datetime NOT NULL default CURRENT_TIMESTAMP COMMENT ‘创建日期’,
creator varchar(50) DEFAULT NULL COMMENT ‘创建人’,
PRIMARY KEY (id),
UNIQUE KEY k_rolemenu_unique (roleid,menuid),
KEY fk_k_rolemenu_menu_id (menuid),
CONSTRAINT fk_k_rolemenu_menu_id FOREIGN KEY (menu_id) REFERENCES k_menu (id) ON DELETE NO ACTION ON UPDATE NO ACTION,
CONSTRAINT fk_k_rolemenu_role_id FOREIGN KEY (role_id) REFERENCES k_role (id) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COMMENT=‘角色-目录信息表’;

–用户-角色信息表
CREATE TABLE k_user_role (
id bigint(11) NOT NULL AUTO_INCREMENT COMMENT ‘编号’,
user_id bigint(11) NOT NULL COMMENT ‘用户id’,
role_id bigint(11) NOT NULL COMMENT ‘角色id’,
create_time datetime NOT NULL default CURRENT_TIMESTAMP COMMENT ‘创建日期’,
creator varchar(50) DEFAULT NULL COMMENT ‘创建人’,
PRIMARY KEY (id),
UNIQUE KEY k_userrole_unique (user_id,role_id),
KEY fk_k_userrole_user_id (user_id),
KEY fk_k_userrole_role_id (role_id),
CONSTRAINT fk_k_userrole_role_id FOREIGN KEY (role_id) REFERENCES k_role (id) ON DELETE NO ACTION ON UPDATE NO ACTION,
CONSTRAINT fk_k_userrole_user_id FOREIGN KEY (user_id) REFERENCES k_user (id) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COMMENT=‘用户-角色信息表’;

----------------------------单点登录统一授权用到[oauth_client_details,oauth_access_token,oauth_refresh_token]不涉及管理-------------------------------------------
–生成的token
CREATE TABLE oauth_access_token (
token_id varchar(255) DEFAULT NULL COMMENT ‘加密的access_token的值’,
token longblob COMMENT ‘OAuth2AccessToken.java对象序列化后的二进制数据’,
authentication_id varchar(255) DEFAULT NULL COMMENT ‘加密过的username,client_id,scope’,
user_name varchar(255) DEFAULT NULL COMMENT ‘登录的用户名’,
client_id varchar(255) DEFAULT NULL COMMENT ‘客户端ID’,
authentication longblob COMMENT ‘OAuth2Authentication.java对象序列化后的二进制数据’,
refresh_token varchar(255) DEFAULT NULL COMMENT ‘加密的refresh_token的值’
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT=‘生成的token’;

–授权记录
CREATE TABLE oauth_approvals (
userId varchar(255) DEFAULT NULL COMMENT ‘登录的用户名’,
clientId varchar(255) DEFAULT NULL COMMENT ‘客户端ID’,
scope varchar(255) DEFAULT NULL COMMENT ‘申请的权限范围’,
status varchar(10) DEFAULT NULL COMMENT ‘状态（Approve或Deny）’,
expiresAt datetime DEFAULT NULL COMMENT ‘过期时间’,
lastModifiedAt datetime DEFAULT NULL COMMENT ‘最终修改时间’
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT=‘授权记录’;

–客户端信息
CREATE TABLE oauth_client_details (
client_id varchar(255) NOT NULL COMMENT ‘客户端ID’,
resource_ids varchar(255) DEFAULT NULL COMMENT ‘资源ID集合,多个资源时用逗号(,)分隔’,
client_secret varchar(255) DEFAULT NULL COMMENT ‘客户端密匙’,
scope varchar(255) DEFAULT NULL COMMENT ‘客户端申请的权限范围’,
authorized_grant_types varchar(255) DEFAULT NULL COMMENT ‘客户端支持的grant_type’,
web_server_redirect_uri varchar(255) DEFAULT NULL COMMENT ‘重定向URI’,
authorities varchar(255) DEFAULT NULL COMMENT ‘客户端所拥有的Spring Security的权限值，多个用逗号(,)分隔’,
access_token_validity int(11) DEFAULT NULL COMMENT ‘访问令牌有效时间值(单位:秒)’,
refresh_token_validity int(11) DEFAULT NULL COMMENT ‘更新令牌有效时间值(单位:秒)’,
additional_information varchar(255) DEFAULT NULL COMMENT ‘预留字段’,
autoapprove varchar(255) DEFAULT NULL COMMENT ‘用户是否自动Approval操作’
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT=‘客户端信息’;

–客户端token信息
CREATE TABLE oauth_client_token (
token_id varchar(255) DEFAULT NULL COMMENT ‘加密的access_token值’,
token longblob COMMENT ‘OAuth2AccessToken.java对象序列化后的二进制数据’,
authentication_id varchar(255) DEFAULT NULL COMMENT ‘加密过的username,client_id,scope’,
user_name varchar(255) DEFAULT NULL COMMENT ‘登录的用户名’,
client_id varchar(255) DEFAULT NULL COMMENT ‘客户端ID’
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT=‘客户端token信息’;

–授权码
CREATE TABLE oauth_code (
code varchar(255) DEFAULT NULL COMMENT ‘授权码(未加密)’,
authentication varbinary(255) DEFAULT NULL COMMENT ‘AuthorizationRequestHolder.java对象序列化后的二进制数据’
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT=‘授权码’;

–刷新token
CREATE TABLE oauth_refresh_token (
token_id varchar(255) DEFAULT NULL COMMENT ‘加密过的refresh_token的值’,
token longblob COMMENT 'OAuth2RefreshToken.java对象序列化后的二进制数据 ',
authentication longblob COMMENT ‘OAuth2Authentication.java对象序列化后的二进制数据’
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT=‘用户信息表’;