Support menu dynamic generation RBAC Permission System Database Structure Design Scheme

The simplest Database Structure Design Based on RBAC permission system includes the following table 1. user table -- table "t_user" ddlcreate table 't_ user' ('id' int (11) not null auto_increment, 'username' varchar (100) default null, 'Password' varchar (100) default null, 'name' varchar (100) default null, 'status' int (11) default '0 ', 'department _ id' int (11) default null, 'desc' varchar (255) default null, primary key ('id') engine = InnoDB default charset = utf8; 2. permission table -- table "t_permission" ddlcreate table 't_ permission' ('id' int (11) not null auto_increment, 'name' varchar (100) default null, 'desc' varchar (255) default null, 'status' int (11) default null, 'is _ menu_display' int (11) default null, 'menu _ url' varchar (255) default null, 'main _ menu_id 'int (11) default null, primary key ('id') engine = InnoDB default charset = utf8; 3. role table -- table "t_role" ddlcreate table 't_ role' ('id' int (11) not null auto_increment, 'name' varchar (100) default null, 'desc' varchar (200) default null, primary key ('id') engine = InnoDB default charset = utf8; 4. user Role table -- table "t_user_role" ddlcreate table 't_ user_role '('user _ id' int (11) not null default '0 ', 'Role _ id' int (11) not null default '0', primary key ('user _ id', 'Role _ id') engine = InnoDB default charset = utf8; 5. role permission table -- table "t_role_permission" ddlcreate table 't_ role_permission '('Role _ id' int (11) not null default '0 ', 'permission _ id' int (11) not null default '0', primary key ('Role _ id', 'permission _ id') engine = InnoDB default charset = utf8; 6. menu table -- table "t_menu" ddlcreate table 't_ menu '('id' int (11) not null auto_increment, 'name' varchar (100) default null, 'desc' varchar (255) default null, primary key ('id') engine = InnoDB default charset = utf8; 7. department table -- table "t_department" ddlcreate table't _ Department '('id' int (11) not null auto_increment, 'name' varchar (100) default null, 'desc' varchar (255) default null, primary key ('id') engine = InnoDB default charset = utf8; main SQL involved: 1. obtain the permission list of a user: Select * From t_permission where ID in (select distinct permission_id from t_role_permission where role_id in (select role_id from t_user_role where user_id = 1) 2. main Menu list: (menu) Select * From t_menu where ID in (select distinct main_menu_id from t_permission where ID in (1, 2, 3, 4) 3. sub-Menu list under a main menu: Select * From t_permission where ID in (,) and main_menu_id = 1 select * From t_permission where ID in (,) and main_menu_id = 2

 

 

Test data:

 

-- ---------------------------- Insert into't _ department 'values ('1', 'technical department ', 'technical department'); insert into 't_department 'values ('2 ', 'Finance department ', 'Finance department'); insert into't _ department 'values ('3', 'Design department ', 'Design department '); insert into't _ menu 'values ('1', 'System management', 'System management'); insert into't _ menu 'values ('2 ', 'financial management', 'financial management'); insert into 't_menu 'values ('3', 'department management', 'department management '); insert into't _ menu 'values ('4', 'Log management', 'Log management'); insert into't _ menu 'values ('5 ', 'Role management', 'Role management'); insert into 't_ permission' VALUES ('1', 'user list', 'user list', '0 ', '1', 'userlist. do ', '1'); insert into't _ permission 'values ('2', 'user add', 'user add', '0', '1 ', 'useradd. do ', '1'); insert into't _ permission 'values ('3', 'user modified', 'user modified', '0', '1 ', 'usermodify. do ', '1'); insert into't _ permission 'values ('4', 'user delete', 'user delete', '0', '1 ', 'userdel. do ', '2'); insert into't _ permission 'values ('5', 'user query', 'user query', '0', '1 ', 'usersearch. do ', '3'); insert into't _ role' VALUES ('1', 'System admin', 'System admin '); insert into 't_ role' VALUES ('2', 'test role 1', 'test role 1'); insert into't _ role' VALUES ('3 ', 'test role 2', 'test role 2'); insert into't _ role_permission 'values ('1', '1 '); insert into't _ role_permission 'values ('1', '2'); insert into't _ role_permission 'values ('1', '3 '); insert into't _ role_permission 'values ('2', '2'); insert into't _ role_permission 'values ('3', '4 '); insert into 't_ user' VALUES ('1', 'admin', '0', '1', 'admin '); insert into't _ user_role 'values ('1', '1'); insert into't _ user_role 'values ('1', '2 '); insert into't _ user_role 'values ('1', '3 ');

 

 

Article address:

Http://blog.csdn.net/5iasp/article/details/9750093

Author: javaboy2012
Email: yanek@163.com
Qq: 1046011462