MySQL Set 3 right discrete

The rights module in the background of the website is very common, so it is necessary to master 3 right separation.

3 The right discrete table has the user table, the role table, the Permissions table, the corresponding relationship is as follows

Users have "reader", "author" and "Administrator" roles, roles have different permissions, such as publishing articles, reviewing articles, viewing articles, etc.

We set the user and role here to be a one-to-many relationship where a user has only one role;

The relationship between roles and permissions is a many-to-many relationship. That is, a role has multiple permissions, and similarly, a permission can be assigned to different roles.

The corresponding Database build table statements are as follows:

User table:

Create tablecreate TABLE ' orm_user ' (  ' i_id ' int (one) not NULL auto_increment,  ' user_name ' varchar "not null,
    ' user_pass ' varchar (not NULL,  PRIMARY KEY (' i_id ')) Engine=innodb auto_increment=7 DEFAULT Charset=utf8

　　

Role table

Create tablecreate table ' orm_role ' (  ' i_id ' int (one) not NULL auto_increment COMMENT ' permission table ',  ' s_name ' varchar () N OT null,  ' s_description ' varchar (default NULL,  PRIMARY KEY (' i_id ')) Engine=innodb auto_increment=4 Default Charset=utf8

　　

Permission table

Create tablecreate TABLE ' orm_permission ' (  ' i_id ' int (one) not null auto_increment,  ' s_name ' varchar (a) NOT NULL ,  ' s_description ' varchar default NULL,  PRIMARY KEY (' i_id ') engine=innodb auto_increment=13 default Charset=utf8

　　

User Role Table

Create tablecreate TABLE ' orm_user_role ' (  ' i_id ' int (one) not NULL auto_increment,  ' i_user_id ' int (one) not null,< c10/> ' i_role_id ' int (one) not NULL,  PRIMARY KEY (' i_id ')) Engine=innodb auto_increment=7 DEFAULT Charset=utf8

　　

Role Permission Table

Create tablecreate TABLE ' orm_role_permission ' (  ' i_id ' int (one) not NULL auto_increment,  ' i_role_id ' int (one) not NULL,  ' i_permission_id ' int (one) not NULL,  PRIMARY KEY (' i_id ')) Engine=innodb auto_increment=26 DEFAULT CHARSET =utf8

　　

Inserting related data

Then use the PHP operation database to query the relevant data

MySQL Set 3 right discrete