本文讨论一种常见的访问控制方法:
[En]
This article discusses a common way of access control:
数据授权:
角色 + 接口 + 表实体 + 字段 + 判断类别+数据列表
role1 + api1 + tbl1 + id + in + (1,2,3)
role1 + api1 + tbl1 + age + < + 35
权限控制中:数据授权可以采用切面的方式,在dao层操作时候,将数据授权(例如:xx in (1,2,3) )的sql拼接到目标业务sql上。
set foreign_key_checks = 0;
CREATE TABLE tbl_common_group
(
id
int(10) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '主键',
code
varchar(100) NOT NULL COMMENT '唯一编码',
create_time
datetime(3) NOT NULL COMMENT '创建时间',
last_update_time
datetime(3) NULL COMMENT '最后更新时间',
name
varchar(255) NOT NULL COMMENT '名称',
detail
varchar(255) NULL COMMENT '详情',
status
int(10) UNSIGNED NOT NULL DEFAULT 2 COMMENT '状态:0-无效,1-有效,2-编辑',
group_type
varchar(100) NOT NULL COMMENT '组类型',
PRIMARY KEY (id
) ,
CONSTRAINT fk_tbl_sapo_group_tbl_sapo_group_type_1
FOREIGN KEY (group_type
) REFERENCES tbl_common_group_type
(code
),
UNIQUE INDEX uni_idx_group_code
(code
ASC) USING BTREE
)
COMMENT = '组';
CREATE TABLE tbl_common_group_rel
(
id
int(10) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '主键',
create_time
datetime(3) NOT NULL COMMENT '创建时间',
last_update_time
datetime(3) NULL COMMENT '最后更新时间',
parent_code
varchar(100) NOT NULL COMMENT '父节点代码,tbl_common_group表code',
child_code
varchar(100) NOT NULL COMMENT '子节点代码,tbl_common_group表code',
status
int(10) UNSIGNED NOT NULL DEFAULT 2 COMMENT '状态:0-无效,1-有效,2-编辑',
group_rel_type
varchar(100) NOT NULL COMMENT '组关系类型代码,来自tbl_common_group_rel_type表code',
tree_code
varchar(100) NOT NULL COMMENT '树节点代码,tbl_common_tree表code',
PRIMARY KEY (id
) ,
CONSTRAINT fk_tbl_sapo_group_rel_parent_code
FOREIGN KEY (parent_code
) REFERENCES tbl_common_group
(code
),
CONSTRAINT fk_tbl_sapo_group_rel_child_code
FOREIGN KEY (child_code
) REFERENCES tbl_common_group
(code
),
CONSTRAINT fk_tbl_sapo_group_rel_tbl_sapo_group_rel_type_1
FOREIGN KEY (group_rel_type
) REFERENCES tbl_common_group_rel_type
(code
),
CONSTRAINT fk_tbl_sapo_group_rel_tbl_sapo_tree_1
FOREIGN KEY (tree_code
) REFERENCES tbl_common_tree
(code
)
)
COMMENT = '组关系';
CREATE TABLE tbl_common_group_type
(
id
int(10) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '主键',
code
varchar(100) NOT NULL COMMENT '唯一编码',
create_time
datetime(3) NOT NULL COMMENT '创建时间',
last_update_time
datetime(3) NULL COMMENT '最后更新时间',
name
varchar(255) NOT NULL COMMENT '名称',
detail
varchar(255) NULL COMMENT '详情',
status
int(10) UNSIGNED NOT NULL DEFAULT 2 COMMENT '状态:0-无效,1-有效,2-编辑',
PRIMARY KEY (id
) ,
UNIQUE INDEX uni_idx_group_code
(code
ASC) USING BTREE
)
COMMENT = '组类型';
CREATE TABLE tbl_common_group_rel_type
(
id
int(10) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '主键',
code
varchar(100) NOT NULL COMMENT '唯一编码',
create_time
datetime(3) NOT NULL COMMENT '创建时间',
last_update_time
datetime(3) NULL COMMENT '最后更新时间',
name
varchar(255) NOT NULL COMMENT '名称',
detail
varchar(255) NULL COMMENT '详情',
status
int(10) UNSIGNED NOT NULL DEFAULT 2 COMMENT '状态:0-无效,1-有效,2-编辑',
PRIMARY KEY (id
) ,
UNIQUE INDEX uni_idx_group_code
(code
ASC) USING BTREE
)
COMMENT = '组关系类型';
CREATE TABLE tbl_common_tree
(
id
int(10) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '主键',
code
varchar(100) NOT NULL COMMENT '唯一编码',
create_time
datetime(3) NOT NULL COMMENT '创建时间',
last_update_time
datetime(3) NULL COMMENT '最后更新时间',
name
varchar(255) NOT NULL COMMENT '名称',
detail
varchar(255) NULL COMMENT '详情',
status
int(10) UNSIGNED NOT NULL DEFAULT 2 COMMENT '状态:0-无效,1-有效,2-编辑',
PRIMARY KEY (id
) ,
UNIQUE INDEX uni_idx_group_code
(code
ASC) USING BTREE
)
COMMENT = '树定义';
CREATE TABLE tbl_commom_tree_group
(
id
int(10) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '主键',
create_time
datetime(3) NOT NULL COMMENT '创建时间',
last_update_time
datetime(3) NULL COMMENT '最后更新时间',
group_code
varchar(100) NOT NULL COMMENT '组代码,tbl_common_group表code',
tree_code
varchar(100) NOT NULL COMMENT '树代码,tbl_common_tree表code',
status
int(10) UNSIGNED NOT NULL DEFAULT 2 COMMENT '状态:0-无效,1-有效,2-编辑',
is_root
int(10) UNSIGNED NOT NULL DEFAULT 0 COMMENT '是否根节点:1-根节点,0-非根节点',
PRIMARY KEY (id
) ,
CONSTRAINT fk_tbl_sapo_tree_group_tbl_sapo_tree_1
FOREIGN KEY (tree_code
) REFERENCES tbl_common_tree
(code
),
CONSTRAINT fk_tbl_sapo_tree_group_tbl_sapo_group_1
FOREIGN KEY (group_code
) REFERENCES tbl_common_group
(code
)
)
COMMENT = '树包含的组';
CREATE TABLE tbl_admin_role
(
id
int(20) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '主键',
code
varchar(100) NOT NULL COMMENT '唯一编码',
create_time
datetime(3) NOT NULL COMMENT '创建时间',
last_update_time
datetime(3) NULL COMMENT '最后更新时间',
name
varchar(255) NOT NULL COMMENT '名称',
detail
varchar(1024) NULL COMMENT '详情',
status
int(10) UNSIGNED NOT NULL DEFAULT 2 COMMENT '状态:0-无效,1-有效,2-编辑',
type_code
varchar(100) NULL COMMENT '类型code,tbl_admin_role_type表code',
PRIMARY KEY (id
) ,
CONSTRAINT fk_tbl_admin_role_tbl_admin_role_type_1
FOREIGN KEY (type_code
) REFERENCES tbl_admin_role_type
(code
),
UNIQUE INDEX uni_idx_group_code
(code
ASC) USING BTREE
);
CREATE TABLE tbl_admin_resource
(
id
int(20) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '主键',
code
varchar(100) NOT NULL COMMENT '唯一编码',
create_time
datetime(3) NOT NULL COMMENT '创建时间',
last_update_time
datetime(3) NULL COMMENT '最后更新时间',
name
varchar(255) NOT NULL COMMENT '名称',
detail
varchar(1024) NULL COMMENT '详情',
status
int(10) UNSIGNED NOT NULL DEFAULT 2 COMMENT '状态:0-无效,1-有效,2-编辑',
type_code
varchar(100) NOT NULL COMMENT '资源类型code,tbl_admin_resource_type表code',
content
varchar(1024) NULL COMMENT '内容',
PRIMARY KEY (id
) ,
CONSTRAINT fk_tbl_admin_resource_tbl_admin_resource_type_1
FOREIGN KEY (type_code
) REFERENCES tbl_admin_resource_type
(code
),
UNIQUE INDEX uni_idx_group_code
(code
ASC) USING BTREE
);
CREATE TABLE tbl_admin_api
(
id
int(20) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '主键',
code
varchar(100) NOT NULL COMMENT '唯一编码',
create_time
datetime(3) NOT NULL COMMENT '创建时间',
last_update_time
datetime(3) NULL COMMENT '最后更新时间',
name
varchar(255) NOT NULL COMMENT '名称',
detail
varchar(1024) NULL COMMENT '详情',
status
int(10) UNSIGNED NOT NULL DEFAULT 2 COMMENT '状态:0-无效,1-有效,2-编辑',
api_path
varchar(1024) NULL COMMENT 'api路径',
type_code
varchar(100) NULL COMMENT '接口类型,tbl_admin_api_type的code',
PRIMARY KEY (id
) ,
CONSTRAINT fk_tbl_admin_api_tbl_admin_api_type_1
FOREIGN KEY (type_code
) REFERENCES tbl_admin_api_type
(code
),
UNIQUE INDEX uni_idx_group_code
(code
ASC) USING BTREE
);
CREATE TABLE tbl_admin_table
(
id
int(20) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '主键',
tbl_name
varchar(100) NOT NULL COMMENT '表名',
create_time
datetime(3) NOT NULL COMMENT '创建时间',
last_update_time
datetime(3) NULL COMMENT '最后更新时间',
name
varchar(255) NOT NULL COMMENT '名称',
detail
varchar(1024) NULL COMMENT '详情',
status
int(10) UNSIGNED NOT NULL DEFAULT 2 COMMENT '状态:0-无效,1-有效,2-编辑',
PRIMARY KEY (id
) ,
UNIQUE INDEX uni_idx_group_code
(tbl_name
ASC) USING BTREE
)
COMMENT = '表';
CREATE TABLE tbl_admin_data_auth
(
id
int(20) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '主键',
create_time
datetime(3) NOT NULL COMMENT '创建时间',
last_update_time
datetime(3) NULL COMMENT '最后更新时间',
name
varchar(255) NOT NULL COMMENT '名称',
detail
varchar(1024) NULL COMMENT '详情',
status
int(10) UNSIGNED NOT NULL DEFAULT 2 COMMENT '状态:0-无效,1-有效,2-编辑',
role_code
varchar(100) NULL COMMENT '角色表示,tbl_admin_role表code',
api_code
varchar(100) NULL COMMENT 'api接口表示,tbl_admin_api表code',
tbl_name
varchar(100) NULL COMMENT '表名,来自tbl_admin_table表tbl_name',
col_name
varchar(255) NULL COMMENT '字段名,来自tbl_admin_table_column表col_name',
auth_type
int(10) NULL COMMENT '授权类型:1-in ,2-大于,3-小于',
PRIMARY KEY (id
) ,
CONSTRAINT fk_tbl_admin_data_auth_tbl_admin_table_1
FOREIGN KEY (tbl_name
) REFERENCES tbl_admin_table
(tbl_name
),
CONSTRAINT fk_tbl_admin_data_auth_tbl_admin_api_1
FOREIGN KEY (api_code
) REFERENCES tbl_admin_api
(code
),
CONSTRAINT fk_tbl_admin_data_auth_tbl_admin_role_1
FOREIGN KEY (role_code
) REFERENCES tbl_admin_role
(code
),
CONSTRAINT fk_tbl_admin_data_auth_tbl_admin_table_column_1
FOREIGN KEY (col_name
) REFERENCES tbl_admin_table_column
(col_name
)
);
CREATE TABLE tbl_admin_data
(
id
int(20) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '主键',
code
varchar(100) NOT NULL COMMENT '唯一编码',
create_time
datetime(3) NOT NULL COMMENT '创建时间',
last_update_time
datetime(3) NULL COMMENT '最后更新时间',
name
varchar(255) NOT NULL COMMENT '名称',
detail
varchar(1024) NULL COMMENT '详情',
status
int(10) UNSIGNED NOT NULL DEFAULT 2 COMMENT '状态:0-无效,1-有效,2-编辑',
content
varchar(1024) NULL COMMENT '内容',
data_auth_id
int(20) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '数据授权id,tbl_admin_data_auth表id',
PRIMARY KEY (id
) ,
CONSTRAINT fk_tbl__copy_1_tbl_admin_data_auth_1
FOREIGN KEY (data_auth_id
) REFERENCES tbl_admin_data_auth
(id
),
UNIQUE INDEX uni_idx_group_code
(code
ASC) USING BTREE
);
CREATE TABLE tbl_admin_table_column
(
id
int(20) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '主键',
col_name
varchar(100) NOT NULL COMMENT '字段名',
tbl_name
varchar(100) NULL COMMENT '表名,tbl_admin_table表tbl_name',
create_time
datetime(3) NOT NULL COMMENT '创建时间',
last_update_time
datetime(3) NULL COMMENT '最后更新时间',
name
varchar(255) NOT NULL COMMENT '名称',
detail
varchar(1024) NULL COMMENT '详情',
status
int(10) UNSIGNED NOT NULL DEFAULT 2 COMMENT '状态:0-无效,1-有效,2-编辑',
PRIMARY KEY (id
) ,
CONSTRAINT fk_tbl_admin_table_column_tbl_admin_table_1
FOREIGN KEY (tbl_name
) REFERENCES tbl_admin_table
(tbl_name
),
UNIQUE INDEX uni_idx_group_code
(tbl_name
ASC, col_name
ASC) USING BTREE
)
COMMENT = '表中字段';
CREATE TABLE tbl_admin_group_api
(
id
int(20) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '主键',
create_time
datetime(3) NOT NULL COMMENT '创建时间',
last_update_time
datetime(3) NULL COMMENT '最后更新时间',
status
int(10) UNSIGNED NOT NULL DEFAULT 2 COMMENT '状态:0-无效,1-有效,2-编辑',
group_code
varchar(100) NOT NULL COMMENT '组代码,tbl_common_group表code',
api_code
varchar(100) NOT NULL COMMENT 'api代码,tbl_admin_api表code',
PRIMARY KEY (id
) ,
CONSTRAINT fk_tbl_admin_group_api_tbl_common_group_1
FOREIGN KEY (group_code
) REFERENCES tbl_common_group
(code
),
CONSTRAINT fk_tbl_admin_group_api_tbl_admin_api_1
FOREIGN KEY (api_code
) REFERENCES tbl_admin_api
(code
)
);
CREATE TABLE tbl_admin_group_resource
(
id
int(20) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '主键',
create_time
datetime(3) NOT NULL COMMENT '创建时间',
last_update_time
datetime(3) NULL COMMENT '最后更新时间',
status
int(10) UNSIGNED NOT NULL DEFAULT 2 COMMENT '状态:0-无效,1-有效,2-编辑',
group_code
varchar(100) NOT NULL COMMENT '组代码,tbl_common_group表code',
resource_code
varchar(100) NOT NULL COMMENT 'api代码,tbl_admin_resource表code',
PRIMARY KEY (id
) ,
CONSTRAINT fk_tbl_admin_group_resource_tbl_common_group_1
FOREIGN KEY (group_code
) REFERENCES tbl_common_group
(code
),
CONSTRAINT fk_tbl_admin_group_resource_tbl_admin_resource_1
FOREIGN KEY (resource_code
) REFERENCES tbl_admin_resource
(code
)
);
CREATE TABLE tbl_admin_resource_type
(
id
int(20) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '主键',
code
varchar(100) NOT NULL COMMENT '唯一编码',
create_time
datetime(3) NOT NULL COMMENT '创建时间',
last_update_time
datetime(3) NULL COMMENT '最后更新时间',
name
varchar(255) NOT NULL COMMENT '名称',
detail
varchar(1024) NULL COMMENT '详情',
status
int(10) UNSIGNED NOT NULL DEFAULT 2 COMMENT '状态:0-无效,1-有效,2-编辑',
PRIMARY KEY (id
) ,
UNIQUE INDEX uni_idx_group_code
(code
ASC) USING BTREE
);
CREATE TABLE tbl_admin_api_type
(
id
int(20) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '主键',
code
varchar(100) NOT NULL COMMENT '唯一编码',
create_time
datetime(3) NOT NULL COMMENT '创建时间',
last_update_time
datetime(3) NULL COMMENT '最后更新时间',
name
varchar(255) NOT NULL COMMENT '名称',
detail
varchar(1024) NULL COMMENT '详情',
status
int(10) UNSIGNED NOT NULL DEFAULT 2 COMMENT '状态:0-无效,1-有效,2-编辑',
PRIMARY KEY (id
) ,
UNIQUE INDEX uni_idx_group_code
(code
ASC) USING BTREE
);
CREATE TABLE tbl_admin_group_role
(
id
int(20) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '主键',
create_time
datetime(3) NOT NULL COMMENT '创建时间',
last_update_time
datetime(3) NULL COMMENT '最后更新时间',
status
int(10) UNSIGNED NOT NULL DEFAULT 2 COMMENT '状态:0-无效,1-有效,2-编辑',
group_code
varchar(100) NOT NULL COMMENT '组代码,tbl_common_group表code',
role_code
varchar(100) NOT NULL COMMENT 'api代码,tbl_admin_role表code',
PRIMARY KEY (id
) ,
CONSTRAINT fk_tbl_admin_group_role_tbl_common_group_1
FOREIGN KEY (group_code
) REFERENCES tbl_common_group
(code
),
CONSTRAINT fk_tbl_admin_group_role_tbl_admin_role_1
FOREIGN KEY (role_code
) REFERENCES tbl_admin_role
(code
)
);
CREATE TABLE tbl_admin_role_type
(
id
int(20) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '主键',
code
varchar(100) NOT NULL COMMENT '唯一编码',
create_time
datetime(3) NOT NULL COMMENT '创建时间',
last_update_time
datetime(3) NULL COMMENT '最后更新时间',
name
varchar(255) NOT NULL COMMENT '名称',
detail
varchar(1024) NULL COMMENT '详情',
status
int(10) UNSIGNED NOT NULL DEFAULT 2 COMMENT '状态:0-无效,1-有效,2-编辑',
PRIMARY KEY (id
) ,
UNIQUE INDEX uni_idx_group_code
(code
ASC) USING BTREE
);
CREATE TABLE tbl_admin_role_group_auth
(
id
int(20) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '主键',
create_time
datetime(3) NOT NULL COMMENT '创建时间',
last_update_time
datetime(3) NULL COMMENT '最后更新时间',
status
int(10) UNSIGNED NOT NULL DEFAULT 2 COMMENT '状态:0-无效,1-有效,2-编辑',
group_code
varchar(100) NOT NULL COMMENT '组代码,tbl_common_group表code',
role_code
varchar(100) NOT NULL COMMENT 'api代码,tbl_admin_role表code',
PRIMARY KEY (id
) ,
CONSTRAINT fk_tbl_admin_group_role_auth_tbl_common_group_1
FOREIGN KEY (group_code
) REFERENCES tbl_common_group
(code
),
CONSTRAINT fk_tbl_admin_group_role_auth_tbl_admin_role_1
FOREIGN KEY (role_code
) REFERENCES tbl_admin_role
(code
)
);
CREATE TABLE tbl_admin_role_api_auth
(
id
int(20) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '主键',
create_time
datetime(3) NOT NULL COMMENT '创建时间',
last_update_time
datetime(3) NULL COMMENT '最后更新时间',
status
int(10) UNSIGNED NOT NULL DEFAULT 2 COMMENT '状态:0-无效,1-有效,2-编辑',
api_code
varchar(100) NOT NULL COMMENT '组代码,tbl_admin_api表code',
role_code
varchar(100) NOT NULL COMMENT 'api代码,tbl_admin_role表code',
PRIMARY KEY (id
) ,
CONSTRAINT fk_tbl_admin_role_api_auth_tbl_admin_role_1
FOREIGN KEY (role_code
) REFERENCES tbl_admin_role
(code
),
CONSTRAINT fk_tbl_admin_role_api_auth_tbl_admin_api_1
FOREIGN KEY (api_code
) REFERENCES tbl_admin_api
(code
)
);
CREATE TABLE tbl_admin_role_resource_auth
(
id
int(20) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '主键',
create_time
datetime(3) NOT NULL COMMENT '创建时间',
last_update_time
datetime(3) NULL COMMENT '最后更新时间',
status
int(10) UNSIGNED NOT NULL DEFAULT 2 COMMENT '状态:0-无效,1-有效,2-编辑',
resource_code
varchar(100) NOT NULL COMMENT '组代码,tbl_admin_resource表code',
role_code
varchar(100) NOT NULL COMMENT 'api代码,tbl_admin_role表code',
PRIMARY KEY (id
) ,
CONSTRAINT fk_tbl_admin_role_resource_auth_tbl_admin_role_1
FOREIGN KEY (role_code
) REFERENCES tbl_admin_role
(code
),
CONSTRAINT fk_tbl_admin_role_resource_auth_tbl_admin_resource_1
FOREIGN KEY (resource_code
) REFERENCES tbl_admin_resource
(code
)
);
建表语句
人和账号的关系分两种:
方式一:账户被重用,人员可以来回更换(如离职),即修改账户的所有权。
[En]
Mode 1: the account is reused, and the person can replace it back and forth (for example, leaving), which is to modify the ownership of the account.
模式二:人员角色被授权,可以有多个账号(微信、邮箱等)。便于实施邀请登记模式。
[En]
Mode 2: the role of the person is authorized, and the person can have multiple accounts (Wechat, mailbox, etc.). It is convenient to implement the invitation registration mode.
模式二:
模式二可以方便的实现邀请注册模式。
1.邀请某用户即新建【账户】,然后向该用户邮箱发送一个注册链接。
将分配给该用户的角色和其他参数,随链接一并发送,例如:角色、有效期、账号、sign(HMAC方式,主要起验证作用)。
例如:www.test.com?roleId=[1,2,3]&expireTime=20220509&mail=wanglifeng@163.com&sign=a8e2389ae
2.用户通过点击链接,注册和维护个人信息及密码,完成注册。
3.后端收到注册请求,通过sign参数验证参数合法性,根据参数为该【账号】创建【用户】,为该【用户】添加角色。
本文来自博客园,作者:wanglifeng,转载请注明原文链接:https://www.cnblogs.com/wanglifeng717/p/16200942.html
Original: https://www.cnblogs.com/wanglifeng717/p/16200942.html
Author: 王李峰
Title: 管理控制台权限控制
原创文章受到原创版权保护。转载请注明出处:https://www.johngo689.com/505448/
转载文章受原作者版权保护。转载请注明原作者出处!