In this chapter, we will use a database to authenticate users. Other methods used to store security information include files and LDAP. We will need to update our course_management
database with tables to store information about users and groups. Let's create three tables—User
, Groups
, and User_Group
:
Figure 14.1: New tables for authentication
TheUser
table stores the username and password. The Groups
table stores the group names. We will group names directly into roles later. The User_Group
table is a joint table, joining the User
and Groups
tables. One user can be in many groups, and one group can have many users.
To simplify mapping information from the preceding tables when configuring realms in JEE servers, we will create a view, named user_group_view
, that makes information from all the preceding tables available in one view. The DDL script for the view is as follows:
CREATE VIEW `user_group_view` AS SELECT `user`.`user_name...