Design of RBAC database for employee Management system __ Database

RBAC (role-based access controls, role-based access control) means that users are associated with permissions through roles. Simply put, a user has several roles, and each role has several permissions. This creates a "user-role-permission" authorization model. In this model, between the user and the role, the role and the permissions, the general is many-to-many relationship.

So we generally from the user-role-privilege model, according to the corresponding relationship to build relational table to establish the relationship between table and table.

In this employee management system, there are 10 tables:

Basic table: User table, Role table, permission table, Resource table, Proxy table (you can temporarily authorize your permission to the agent of your person), Department table.

and the mapping table created according to the association relation: User Role table, role permission table, Department permission table, user Proxy table.

A user can use multiple roles or multiple permissions, all of which are many-to-many relationships, and permissions can be viewed as operational rights to resources.

In this system we can use the primary key and the foreign key to authorize the user and the whole department through the Association table.

All of us use the mapping table to maintain the relationship between them.

Here is the EER diagram of the system:

We can see that the most fundamental is the User,role,permission table and then through the Foreign Key Association of the mapping table to establish the relationship, which is also the most basic. We can also extend the table relationships and establish connections based on the requirements of the system, such as department and delegation.

SQL scripts

--MySQL dump 10.13 distrib 5.7.12, for Win64 (x86_64)----host:localhost database:user_management-------------- --------------------------------------------Server version 5.7.14-log/*!40101 SET @OLD_CHARACTER_SET_CLIENT =@@
character_set_client * *;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS =@ @CHARACTER_SET_RESULTS * *;
/*!40101 SET @OLD_COLLATION_CONNECTION =@ @COLLATION_CONNECTION * *;
/*!40101 SET NAMES UTF8 * *;
/*!40103 SET @OLD_TIME_ZONE =@ @TIME_ZONE * *;
/*!40103 SET time_zone= ' +00:00 ' * *;
/*!40014 SET @OLD_UNIQUE_CHECKS =@ @UNIQUE_CHECKS, unique_checks=0 * *;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS =@ @FOREIGN_KEY_CHECKS, foreign_key_checks=0 * *;
/*!40101 SET @OLD_SQL_MODE =@ @SQL_MODE, sql_mode= ' no_auto_value_on_zero ' * *;

/*!40111 SET @OLD_SQL_NOTES =@ @SQL_NOTES, sql_notes=0 * *;
----Table structure for table ' delegation '--DROP Table IF EXISTS ' delegation ';
/*!40101 SET @saved_cs_client = @ @character_set_client * *;
/*!40101 SET character_set_client = UTF8 * *; CREATE TABLE ' deleGation ' (' delegation_id ' int (one) not null, ' delegation_user_id ' int (one) default null, ' startdate ' date default null , ' enddate ' date DEFAULT NULL, PRIMARY key (' delegation_id '), key ' delegation_user_id ' (' delegation_user_id '), CON STRAINT ' Delegation_ibfk_1 ' FOREIGN KEY (' delegation_user_id ') REFERENCES ' user ' (' user_id ')) Engine=innodb DEFAULT CHAR
Set=utf8;

/*!40101 SET character_set_client = @saved_cs_client * *;
----dumping data for table ' delegation '--LOCK TABLES ' delegation ' WRITE;
/*!40000 ALTER TABLE ' delegation ' DISABLE KEYS * * *;
/*!40000 ALTER TABLE ' delegation ' ENABLE KEYS * * *;

UNLOCK TABLES;
----Table structure for table ' Department '--DROP Table IF EXISTS ' Department ';
/*!40101 SET @saved_cs_client = @ @character_set_client * *;
/*!40101 SET character_set_client = UTF8 * *; CREATE TABLE ' Department ' (' department_id ' int () not null, ' department_name ' varchar () DEFAULT null, PRIMARY KE Y (' department_id '), UNIQUE KEY ' Department_id_unique ' (' department_id ') Engine=innodb DEFAULT Charset=utf8;

/*!40101 SET character_set_client = @saved_cs_client * *;
----dumping data for table ' Department '--LOCK TABLES ' Department ' WRITE;
/*!40000 ALTER TABLE ' Department ' DISABLE KEYS * * *;
INSERT into ' Department ' VALUES (1, ' Research and Development Department ');
/*!40000 ALTER TABLE ' Department ' ENABLE KEYS * * *;

UNLOCK TABLES;
----Table structure for table ' department_permission '--DROP Table IF EXISTS ' department_permission ';
/*!40101 SET @saved_cs_client = @ @character_set_client * *;
/*!40101 SET character_set_client = UTF8 * *; CREATE TABLE ' department_permission ' (' department_id ' int () default null, ' permission_id ' int (one) default NULL, K EY ' department_id ' (' department_id '), KEY ' permission_id ' (' permission_id '), CONSTRAINT ' Department_permission_ibfk_1 ' FOREIGN KEY (' department_id ') REFERENCES ' Department ' (' department_id '), CONSTRAINT ' Department_permission_ibfk_2 ' FO Reign KEY (' permission_id ') REFERENCES ' permission ' (' permission_id ') Engine=innodb DEFAULT Charset=utf8;

/*!40101 SET character_set_client = @saved_cs_client * *;
----dumping data for table ' department_permission '--LOCK TABLES ' department_permission ' WRITE;
/*!40000 ALTER TABLE ' department_permission ' DISABLE KEYS * * *;
/*!40000 ALTER TABLE ' department_permission ' ENABLE KEYS * * *;

UNLOCK TABLES;
----Table structure for table ' permission '--DROP Table IF EXISTS ' permission ';
/*!40101 SET @saved_cs_client = @ @character_set_client * *;
/*!40101 SET character_set_client = UTF8 * *; CREATE TABLE ' permission ' (' permission_id ' int (one) not null, ' resource_id ' int (one) DEFAULT null, PRIMARY KEY (' Perm ission_id '), key ' resource_id ' (' resource_id '), CONSTRAINT ' Permission_ibfk_1 ' FOREIGN KEY (' resource_id ') REFERENCES
' Resource ' (' resource_id ') Engine=innodb DEFAULT Charset=utf8;

/*!40101 SET character_set_client = @saved_cs_client * *;
----dumping data for table ' permission '--LOCK TABLES ' permission ' WRITE; /*!40000 ALTER TABLE ' PermissIon ' DISABLE KEYS * *;
/*!40000 ALTER TABLE ' permission ' ENABLE KEYS * * *;

UNLOCK TABLES;
----Table structure for table ' resource '--DROP Table IF EXISTS ' resource ';
/*!40101 SET @saved_cs_client = @ @character_set_client * *;
/*!40101 SET character_set_client = UTF8 * *; CREATE TABLE ' resource ' (' resource_id ' int () not null, ' resource_name ' varchar ' DEFAULT null, ' Resource_crud '
int (one) default NULL, PRIMARY KEY (' resource_id ')) engine=innodb default Charset=utf8;

/*!40101 SET character_set_client = @saved_cs_client * *;
----dumping data for table ' resource '--LOCK TABLES ' resource ' WRITE;
/*!40000 ALTER TABLE ' resource ' DISABLE KEYS * * *;
/*!40000 ALTER TABLE ' resource ' ENABLE KEYS * * *;

UNLOCK TABLES;
----Table structure for table ' role '--DROP Table IF EXISTS ' role ';
/*!40101 SET @saved_cs_client = @ @character_set_client * *;
/*!40101 SET character_set_client = UTF8 * *; CREATE TABLE ' role ' (' role_id ' int (one) not NULL, ' role_name ' varchar () DEFAULT Null, PRIMARY key (' role_id '), UNIQUE key ' Role_id_unique ' (' role_id ') Engine=innodb DEFAULT Charset=utf8;

/*!40101 SET character_set_client = @saved_cs_client * *;
----dumping data for table "role"--LOCK TABLES ' role ' WRITE;
/*!40000 ALTER TABLE ' role ' DISABLE KEYS * * *;
INSERT into ' role ' VALUES (1, ' general manager ');
/*!40000 ALTER TABLE ' role ' ENABLE KEYS * * *;

UNLOCK TABLES;
----Table structure for table ' role_permission '--DROP Table IF EXISTS ' role_permission ';
/*!40101 SET @saved_cs_client = @ @character_set_client * *;
/*!40101 SET character_set_client = UTF8 * *;  CREATE TABLE ' role_permission ' (' role_id ' int () default null, ' permission_id ' int (one) default null, KEY ' role_id ' (' role_id '), key ' permission_id ' (' permission_id '), CONSTRAINT ' Role_permission_ibfk_1 ' FOREIGN KEY (' role_id ') refer Ences ' role ' (' role_id '), CONSTRAINT ' role_permission_ibfk_2 ' FOREIGN KEY (' permission_id ') REFERENCES ' permission ' (' PE
rmission_id ')) Engine=innodb DEFAULT Charset=utf8; /*!40101 SET character_set_client = @saved_cs_client * *;
----dumping data for table ' role_permission '--LOCK TABLES ' role_permission ' WRITE;
/*!40000 ALTER TABLE ' role_permission ' DISABLE KEYS * * *;
/*!40000 ALTER TABLE ' role_permission ' ENABLE KEYS * * *;

UNLOCK TABLES;
----Table structure for table ' user '--DROP Table IF EXISTS ' user ';
/*!40101 SET @saved_cs_client = @ @character_set_client * *;
/*!40101 SET character_set_client = UTF8 * *; CREATE TABLE ' user ' (' user_id ' int () NOT NULL, ' user_name ' varchar () DEFAULT null, ' user_gender ' varchar (8) DEF Ault null, ' user_email ' varchar default NULL, ' User_phone ' varchar (#) default null, ' user_address ' varchar (45) Default NULL, ' user_birthday ' date default null, ' department_id ' int (one) DEFAULT null, PRIMARY KEY (' user_id '), UN IQUE key ' Use_idr_unique ' (' user_id '), key ' department_id ' (' department_id '), CONSTRAINT ' User_ibfk_1 ' FOREIGN KEY (' d epartment_id ') REFERENCES ' Department ' (' department_id ') ENGine=innodb DEFAULT Charset=utf8;

/*!40101 SET character_set_client = @saved_cs_client * *;
----dumping data for table ' user '--LOCK TABLES ' user ' WRITE;
/*!40000 ALTER TABLE ' user ' DISABLE KEYS * * *; INSERT into ' user ' VALUES (1, ' Mike ', ' Male ', ' axxxx@163.com ', ' 183xxxxxxxx ', ' Chengdu ', ' 2017-01-19 '), (102, ' Mao ', ' Female ', ' xxxx@163.com ', ' 183xxxxxx ', ' Chengdu ', ' 2017-01-20 ', 1, (A, ' Huang ', ' Male ', ' axxxx@163.com ', ' 183xxxxxxxx '
, ' Chengdu ', ' 2017-01-24 ', NULL);
/*!40000 ALTER TABLE ' user ' to ENABLE the KEYS * *;

UNLOCK TABLES;
----Table structure for table ' user_delegation '--DROP Table IF EXISTS ' user_delegation ';
/*!40101 SET @saved_cs_client = @ @character_set_client * *;
/*!40101 SET character_set_client = UTF8 * *;  CREATE TABLE ' user_delegation ' (' user_id ' int () default null, ' delegation_id ' int (one) default null, KEY ' user_id ' (' user_id '), key ' delegation_id ' (' delegation_id '), CONSTRAINT ' User_delegation_ibfk_1 ' FOREIGN KEY (' user_id ') refer Ences ' user ' (' user_id '), constrainT ' user_delegation_ibfk_2 ' FOREIGN KEY (' delegation_id ') REFERENCES ' delegation ' (' delegation_id ') engine=innodb
DEFAULT Charset=utf8;

/*!40101 SET character_set_client = @saved_cs_client * *;
----dumping data for table ' user_delegation '--LOCK TABLES ' user_delegation ' WRITE;
/*!40000 ALTER TABLE ' user_delegation ' DISABLE KEYS * * *;
/*!40000 ALTER TABLE ' user_delegation ' ENABLE KEYS * * *;

UNLOCK TABLES;
----Table structure for table ' user_department '--DROP Table IF EXISTS ' user_department ';
/*!40101 SET @saved_cs_client = @ @character_set_client * *;
/*!40101 SET character_set_client = UTF8 * *;  CREATE TABLE ' user_department ' (' user_id ' int () default null, ' department_id ' int (one) default null, KEY ' user_id ' (' user_id '), key ' department_id ' (' department_id '), CONSTRAINT ' User_department_ibfk_1 ' FOREIGN KEY (' user_id ') refer Ences ' user ' (' user_id '), CONSTRAINT ' user_department_ibfk_2 ' FOREIGN KEY (' department_id ') REFERENCES ' Department ' (' De partment_id ') ENGINE=innodb DEFAULT Charset=utf8;

/*!40101 SET character_set_client = @saved_cs_client * *;
----dumping data for table ' user_department '--LOCK TABLES ' user_department ' WRITE;
/*!40000 ALTER TABLE ' user_department ' DISABLE KEYS * * *;
/*!40000 ALTER TABLE ' user_department ' ENABLE KEYS * * *;

UNLOCK TABLES;
----Table structure for table ' User_role '--DROP Table IF EXISTS ' user_role ';
/*!40101 SET @saved_cs_client = @ @character_set_client * *;
/*!40101 SET character_set_client = UTF8 * *; CREATE TABLE ' user_role ' (' user_id ' int () default null, ' role_id ' int (one) default null, KEY ' user_id ' (' user_id ') , Key ' role_id ' (' role_id '), CONSTRAINT ' User_role_ibfk_1 ' FOREIGN KEY (' user_id ') REFERENCES ' user ' (' user_id '), CO
Nstraint ' user_role_ibfk_2 ' FOREIGN KEY (' role_id ') REFERENCES ' role ' (' role_id ')) Engine=innodb DEFAULT Charset=utf8;

/*!40101 SET character_set_client = @saved_cs_client * *;
----dumping data for table ' User_role '--LOCK TABLES ' user_role ' WRITE; /*!40000ALTER TABLE ' user_role ' DISABLE KEYS * * *;
/*!40000 ALTER TABLE ' user_role ' ENABLE KEYS * * *;
UNLOCK TABLES;

/*!40103 SET time_zone= @OLD_TIME_ZONE * *;
/*!40101 SET sql_mode= @OLD_SQL_MODE * *;
/*!40014 SET foreign_key_checks= @OLD_FOREIGN_KEY_CHECKS * *;
/*!40014 SET unique_checks= @OLD_UNIQUE_CHECKS * *;
/*!40101 SET character_set_client= @OLD_CHARACTER_SET_CLIENT * *;
/*!40101 SET character_set_results= @OLD_CHARACTER_SET_RESULTS * *;
/*!40101 SET collation_connection= @OLD_COLLATION_CONNECTION * *;

/*!40111 SET sql_notes= @OLD_SQL_NOTES * *;
 --Dump completed on 2017-02-15 16:44:55