Modifying a database to save authentication information

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

TheUsertable stores the username and password. The Groupstable 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...