Classic permission system design (five tables)

Five tables are used: userinfo, roleinfo, menuinfo, userrole, and rolemenu ).

The structure of each table is as follows:

1. User table (userinfo): ID, username, userpwd

2. Role info: ID and rolename

3. Menu table (menuinfo): ID and menuname

4. User Role table (userrole): ID, userid, roleid

5. Role menu: ID, roleid, and menuid

 

The most important thing is how to find the menu permissions of a user during logon? In fact, one statement can be done:

If the user name is zhangsan, his menu permission query is as follows:

Select M. ID, M. menuname from menuinfo M, userinfo U userrole ur, rolemenu RM where M. id = RM. menuid and Ur. roleid = RM. roleid and Ur. userid = u. ID and U. username = 'hangsan'