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:

The User 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` AS `user_name`,
        `groups`.`group_name` AS `group_name`,
        `user`.`password` AS `password`
    FROM
        ((`user`
        JOIN `groups`)
        JOIN `user_group`)
    WHERE
        ((`user`.`id` = `user_group`.`user_id`)
            AND (`groups`.`id` = `user_group`.`group_id`))

If you already have the course_management schema from earlier chapters, then run the script in the add_auth_tables.sql file (the file is in the source code folder for this chapter). If you are using MySQLWorkbench, you can run the script as follows:

1. Make sure course_management is the default schema; right-click on the schema and select the Set as Default Schema option.
2. Select the File | Open SQL Script menu, and then select the add_auth_tables.sql file. The file will open in a new tab.
3. Click on the Execute icon in the toolbar to execute this script.
4. Right-click on the course_management schema and select the Refresh All option. Make sure the new tables and the view are created in the schema.

For testing purpose, let's insert the following data in the user table:

Groups:

User_Group:

As per the preceding data, user1 is in the admin group, and user2 is not in any group.