无为

Krishnakumar Balachandar (krishnakumarb@in.ibm.com), Software Engineer, WebSphere Application Server Community Edition Support Team, IBM

20 Sep 2006

This tutorial shows you how to set up an SQL realm to implement database security for a Web application using the no-charge Java kick-start package.

Before you start

When implementing security in a Web application, a JAAS module is required to specifically handle user authentication and authorization. WebSphere Application Server Community Edition provides a SQL realm that can connect to a database for obtaining user credentials. In this tutorial, you will see how to set up tables for creating an SQL realm in DB2 Express-C, how to set up a development environment using the Eclipse Web Tooling Platform to create a Web application, and how to deploy the Web application to WebSphere Application Server Community Edition from Eclipse.

All Web applications should employ some level of security for user authentication and authorization to verify a user’s identity and enable access to application functions that are applicable to the user's role.

Web applications can use LDAP, database, file system, Kerberos, or other such systems to provide authentication and authorization. To access security data from the selected method or system, an SQL realm is set up by creating tables in a database and configuring a Java Authentication and Authorization Service (JAAS) module to access these tables from an application server.

Figure 1 shows different components in WebSphere Application Server Community Edition (hereafter referred to as Community Edition) used to provide an SQL realm. The Web application uses an SQL realm that connects to the database using a pool or driver. The user credentials are stored in tables in the database and are fetched when calls are made using the JAAS SQL realm.

Java Authentication and Authorization Service (JAAS) is a set of APIs that enables Java applications to access authentication and access control services without being tied to the service it is using. Using JAAS, the Java application can plug into security mechanisms like LDAP, Kerberos, databases, and so on, providing a layer between our application and the security mechanism being used.

Setting up a database realm

Setting up a database realm involves performing these general steps:

• Create tables for users and groups in DB2 Express-C.
• Deploy a database pool in Community Edition, if the realm is going to use a pool to connect to the database.
• Deploy the SQL realm plan in Community Edition.
• Configure and deploy the Web application that uses the SQL realm.

The remainder of this tutorial explains these tasks in detail.

Setting up a database

Using the Control Center

Create the database

1. In DB2 Express-C, open the Control Center.
2. Right-click All Databases, then select Create Database => Standard.
3. For Database name, enter WASCE.
4. Click Finish.

You just created a new database called WASCE and should see it listed under All Databases (Figure 2).

Figure 2. New database created

Back to top

Create tables

Now, create the tables for users (USERS) and groups (GROUPS) in the new WASCE database:

1. Under WASCE in the directory listing, right-click Tables, then select Create.
2. For Table name, enter USERS, then click Next.
3. Add a column:
   1. Select Add to add a column to the new table.
   2. For Column name, enter USERNAME.
   3. For Data type, select VARCHAR.
   4. For Nullable or not, select No.
   5. Click Apply.
4. Repeat step 3 to add an EMAIL column and a PASSWORD column to the USERS table, with the values shown in Figure 3. When finished, click OK.
5. Repeat steps 1 through 4 to create a new GROUPS table with two columns: GROUPNAME and USERNAME.
6. Click Next => Next => Next to get to the Define keys on new table panel.
7. Select Add Primary. From the available columns, select which column is to be the primary key for each table:
   1. For the USERS table, select USERNAME, and for the GROUPS table, select USERNAME/GROUPNAME as the primary key.
   2. For the GROUPS table, select Add foreign => USERNAME. Click the > button to move it to foreign key. Select USERNAME as the primary key in the Parent table (Schema/Name - ADMINISTRATOR/USERS).
8. Click Finish and the table is created.

Use the steps above and the schema definitions shown in Figures 3 and 4 to create the USERS and GROUPS tables. For the USERS table, USERNAME is the primary key. For the GROUPS table, GROUPNAME and USERNAME are primary keys, and USERNAME is a foreign key (referring to USERNAME in the USERS table).

Figure 3. USERS schema


Figure 4. GROUPS schema

In the steps shown above, the USERS and GROUPS tables are created under the ADMINISTRATOR schema. Had you logged in as db2admin, the tables would be created under the DB2ADMIN schema.

Populate tables with values

1. Select WASCE => Tables => USERS.
2. Right-click on the USERS table, then select Open => Add row.
3. Enter test values for each field in the database record, then select Commit.
4. Repeat steps 2 and 3 for each test record you wish to create, then Close.
5. Repeat steps 1 through 4 for the GROUPS table (Figure 5).

Figure 5. Create test data records

Using scripts

As an alternative to using the DB2 Control Center, you can create the database, tables, and test data using an SQL script. The download file included with this tutorial contains a sanmple script file, db2_realm.sql. Execute this script from DB2 command line to create the database and tables:

1. Open a Windows command window or run the DB2 batch file /<db2 install directory>bin/db2cw.bat.
2. Execute the sample script file with this command: db2 -tvfdb2-realm.sql.

If there are no errors, success messages will be generated for the SQL (Figure 6).

Figure 6. Create database, tables, and test data using script

Creating the Web application

Define a new server

To deploy an application, you need to define a server in Eclipse:

1. In Eclipse, right-click the Server tab, and select New Server to open the New Server wizard (Figure 7).
   
   Figure 7. Define new server
   
   
   
2. Select WebSphere Application Server Community v1.1 Server, then press Next.
3. Enter values for all fields, as shown in Figure 8, then Finish.
   
   Figure 8. New server parameters
   
   
   

Create a JRE

To build application for supported Java Runtime Environment (JRE), in this case the IBM JRE, you need to configure the JRE in Eclipse WTP:

1. In Eclipse, select Window => Preferences.
2. Select Java => Installed JREs.
3. Click Add. . .
4. On the Add JRE dialog, enter values for all fields, as shown in Figure 9.
   
   Figure 9. Add JRE
   
   
   
5. Be sure this JRE is selected as the default JRE.
6. Next, you need to make sure that the IBM JRE will run the new server that you created earlier. Right-click on the new server, then select Open (Figure 10).
   
   Figure 10. Open new server
   
   
   
7. Next to Runtime, select Edit.
8. For JRE, select IBM (Figure 11), then Finish.
   
   Figure 11. Select IBM JRE
   
   
   

Develop an application

The Web application will contain pages for the application (in a WAR file) and will be added to an enterprise application (in an EAR file). For the purposes of this tutorial, you can either use an application you develop from scratch, or you can use the sample application included in the download file.

Create a new application in Eclipse WTP

1. Run Eclipse WTP either by double-clicking eclipse.exe from the location where you extracted the WTP platform, or by executing eclipse.exe from a command line.
2. Select File => New => Project => Web =>Dynamic Web Project.
3. On the Dynamic Web Project dialog, enter or select the following values:
   • Project name: SQLRealmWeb
   • Target runtime: WebSphere Community Edition v1.1
   • EAR membership: SQLRealmApp
4. If you need to create a WebSphere Application Server Community Edition V1.1 runtime option (Figure 12):
   1. Click the New button next to Target Runtime.
   2. Select WebSphere Application Server Community Edition v1.1, then Next.
   3. Select IBM for JRE, and browse to the appropriate Application Server Install Directory, then click Finish.
      
      Figure 12. Create Community Edition runtime
      
      
      
5. Click Next on the next two panels.
6. For Context Root, enter /SQLRealmWeb, then Next.
7. Configure the Geronimo deployment plan by entering these values:
   • Group ID: wasce
   • Artifact ID: sqlrealm
   • Version: 1.0
   • Artifact Type: WAR
8. Click Finish (Figure13).
   
   Figure 13. Server setup complete
   
   
   

Import sample application into Eclipse WTP

If you choose to import the sample project, the download file included with this tutorial contains an EAR file you can import into the Eclipse WTP workspace:

1. In Eclipse, select File => Import => J2EE.
2. For Source File, enter SQLRealmAppSrc.ear, then click Next.
3. Enter or select the following values:
   • EAR Project: SQLRealmApp
   • Web Project: SQLRealmWeb
   • Target Runtime: WebSphere Community Edition v1.1
4. If you need to create a WebSphere Application Server Community Edition V1.1 runtime option (Figure 12):
   1. Click the New button next to Target Runtime.
   2. Select WebSphere Application Server Community Edition v1.1, then Next.
   3. Select IBM for JRE, and browse to the appropriate Application Server Install Directory, then click Finish.
5. Click Finish.

Deploying the application

Deploy the database pool

To connect to DB2, you need a database pool, which you can create by deploying a database pool plan with the wizard that is available from the Community Edition administrative console:

1. Logon to the Community Edition administrative console by starting the server. To start the server, right-click on the new server you created above, and select Start. Once the server is started, the status changes to green and shows "Started". You can now launch the console by right-clicking on the started server and selecting Launch Community Edition Console. Enter your username and password as system and manager.
2. Under Services, select Database Pools => Using Geronimo PoolsWizard.
3. For Database Pools, enter Db2Realm, then Next.
4. On the Select Driver, JAR, Parameters panel, enter or select the values shown for all fields in Figure 14. When finished, click Next.
   
   Figure 14. Enter database pool parameters
   
   
   
5. Test the connection for errors by selecting Test connection. If there are no errors, you will see the message: Test Result: Connected to DB2/NT SQL09000 in the console.
6. Once you deploy the pool, it is started and running (Figure 15).
   
   Figure 15. Database pool deployed
   
   
   

Deploy the realm plan

Community Edition connects to SQL databases using JAAS, by default, to provide an SQL realm to applications deployed in the server. You can make this connection by using a JDBC driver that connects directly to the database, or by using a database pool that gets a connection from a pool to connect to the database.

The realm plan (see Listings 1 and 2) is used to deploy an SQL realm that connects to the database. The realm plan contains Geronimo GBeans required to create an SQL realm. The GBean "sql-login" has attributes that you use to specify the realm type, loginModuleClass = org.apache.geronimo.security.realm.providers.SQLLoginModule, and options where you specify the JDBC properties required to connect to the database. You can use this plan to connect to any JDBC database. For this exercise, you can specify either the JDBC driver or the DataSource name to connect to the database.

Using the JDBC driver

Listing 1 shows an SQL realm plan that uses the JDBC driver to connect to database. The plan is contained in the download file as sqlplan.xml. In the statements that are bold in Listing 1, replace ADMINISTRATORS with the schema name in which the tables have been created.

Listing 1. SQL realm plan using JDBC driver

                    
<module xmlns="http://geronimo.apache.org/xml/ns/deployment-1.1">
  <environment>
    <moduleId>
      <groupId>db2</groupId>
      <artifactId>realm</artifactId>
      <version>1.0</version>
      <type>car</type>
    </moduleId>
    <dependencies>
      <dependency>
        <groupId>geronimo</groupId>
        <artifactId>j2ee-security</artifactId>
        <version>1.1</version>
        <type>car</type>
      </dependency>
      <dependency>
        <groupId>console.dbpool</groupId>
        <artifactId>DB2Realm</artifactId>
        <version>1.0</version>
        <type>rar</type>
      </dependency>
    </dependencies>
    <hidden-classes/>
    <non-overridable-classes/>
  </environment>
  <gbean name="sql-login" class="org.apache.geronimo.security.jaas.LoginModuleGBean">
    <attribute name="loginModuleClass">org.apache.geronimo.
        security.realm.providers.SQLLoginModule</attribute>
    <attribute name="serverSide">true</attribute>
    <attribute name="options">jdbcUser=db2admin
      jdbcPassword=db2admin
      jdbcURL=jdbc:db2:WASCE
      jdbcDriver=com.ibm.db2.jcc.DB2Driver
      userSelect=select username, password from ADMINISTRATOR.USERS
      groupSelect=select groupname, username from ADMINISTRATOR.GROUPS</attribute>
    <attribute name="loginDomainName">sql-realm</attribute>
  </gbean>
  <gbean name="sql-realm" class="org.apache.geronimo.security.realm.GenericSecurityRealm">
    <attribute name="realmName">sql-realm</attribute>
    <reference name="LoginModuleConfiguration">
      <name>sql-login</name>
    </reference>
    <reference name="ServerInfo">
      <name>ServerInfo</name>
    </reference>

    <!--Add-->
    <reference name="LoginService">
      <name>JaasLoginService</name>
    </reference>
  </gbean>
  <gbean name="sql-login" class="org.apache.geronimo.security.jaas.JaasLoginModuleUse">
    <attribute name="controlFlag">REQUIRED</attribute>
    <reference name="LoginModule">
      <name>sql-login</name>
    </reference>
  </gbean>
  <gbean name="sqltest"
class="org.apache.geronimo.security.jaas.ServerRealmConfigurationEntry">
    <attribute name="applicationConfigName">sqltest</attribute>
    <attribute name="realmName">sql-realm</attribute>
    <reference name="LoginService">
      <name>JaasLoginService</name>
    </reference>
  </gbean>
</module>

Using database pool

Listing 2 shows an SQL realm plan that uses a database pool (DB2Realm) that was deployed using the console wizard. The plan is contained in the download file as sqlpoolplan.xml.

Listing 2. SQL realm plan using database pool

                    
<module xmlns="http://geronimo.apache.org/xml/ns/deployment-1.1">
  <environment>
    <moduleId>
      <groupId>db2</groupId>
      <artifactId>pool-realm</artifactId>
      <version>1.0</version>
      <type>car</type>
    </moduleId>
    <dependencies>
      <dependency>
        <groupId>geronimo</groupId>
        <artifactId>j2ee-security</artifactId>
        <version>1.1</version>
        <type>car</type>
      </dependency>
      <dependency>
        <groupId>console.dbpool</groupId>
        <artifactId>DB2Realm</artifactId>
        <version>1.0</version>
        <type>rar</type>
      </dependency>
    </dependencies>
    <hidden-classes/>
    <non-overridable-classes/>
  </environment>
  <gbean name="sqlpool-login"
class="org.apache.geronimo.security.jaas.LoginModuleGBean">
    <attribute name="loginModuleClass">org.apache.geronimo.security.realm.
        providers.SQLLoginModule</attribute>
    <attribute name="serverSide">true</attribute>
    <attribute name="options">
      userSelect=select username, password from ADMINISTRATOR.USERS
      groupSelect=select groupname, username from ADMINISTRATOR.GROUPS
      dataSourceName=DB2Realm</attribute>
    <attribute name="loginDomainName">sqlpool-realm</attribute>
  </gbean>
  <gbean name="sqlpool-realm"
class="org.apache.geronimo.security.realm.GenericSecurityRealm">
    <attribute name="realmName">sqlpool-realm</attribute>
    <reference name="LoginModuleConfiguration">
      <name>sqlpool-login</name>
    </reference>
    <reference name="ServerInfo">
      <name>ServerInfo</name>
    </reference>
    <!--Add-->
    <reference name="LoginService">
      <name>JaasLoginService</name>
    </reference>
  </gbean>
  <gbean name="sqlpool-login"
class="org.apache.geronimo.security.jaas.JaasLoginModuleUse">
    <attribute name="controlFlag">REQUIRED</attribute>
    <reference name="LoginModule">
      <name>sqlpool-login</name>
    </reference>
  </gbean>
  <gbean name="sqlpooltest"
class="org.apache.geronimo.security.jaas.ServerRealmConfigurationEntry">
    <attribute name="applicationConfigName">sqlpooltest</attribute>

Deploy the realm

You can deploy the realms to the server using the administrative console:

1. Start the Community Edition server from Eclipse WTP. In Eclipse, select the server you created earlier by right-clicking on the Server tab, then select Start (Figure 16).
   
   Figure 16. Start server
   
   
   
2. Login to the Community Edition administrative console.
3. Deploy the SQL realm plan that uses JDBC Driver (sqlplan.xml shown in Listing 1) by selecting Deploy New on the console (Figure 17).
   
   Figure 17. Deploy database realm
   
   
   
4. Deploy the SQL database realm plan that uses the database pool (sqlpoolplan.xml shown in Listing 2).
5. Check to see that these two plans are deployed successfully by selecting Systems Modules under Applications in the console (Figure 18).
   
   Figure 18. Check successful real deployment
   
   
   

Deploy the application

With the application and server configuration defined in Eclipse, and the database realm deployed, you can now deploy the application to the server.

1. In Community Edition, select Server tab, right-click on the server you created earlier, and then select Add and Remove Projects.
2. Select the SQLRealmApp from the Available projects list and click Add > to move it over to the Configured projects list (Figure 19).
3. Click Finish.
   
   Figure 19. Add to configured projects
   
   
   
4. Start the server. From the Server tab, right-click again on your server and select Start. The application will run on the server you selected.
5. To launch the console for the server, right-click on the Community Edition server, then select Launch Community Edition Console. Enter the login ID and password: system and manager, respectively (Figure 20).
   
   Figure 20. Launch administrative consoles
    
   

   Running the application

   In the prior sections, you created and deployed the Web application to the server from Eclipse WTP. Next, you can use the application, following the instructions in this section to test authentication, authorization, and access to the database pool. The application referred to in this section is the sample application provided in the download file.

   Access the application

   To access the Web application:

   1. From the Eclipse Navigator, right-click the Web application SQLRealmWeb, then select Run As => Run on Server.
   2. Select WebSphere Application Server Community Edition 1.1, which is the server you defined earlier.
   3. The project should already be added. Select Next and Finish.
   4. The application should now be available at http://localhost:8080/SQLRealmWeb/ (Figure 21).
      
      Figure 21. Test the application
      
      
      

   Test the authentication

   Included with this sample application are several valid user ID and password combinations (for testing authentication) with different roles (for testing authorization). Test the authentication by logging in. Using a correct user ID and password will log you in. An incorrect user ID and password will display the error page shown in Figure 22.

   Valid user ID/password values are as follows:

   system/manager
admin/admin123
user1/user1123
user2/user2123
guest1/guest1123
guest2/guest2123

   
   Figure 22. Application error page
   
   

   Test authorization

   Once authenticated and inside the application, the user is allowed access to areas of Web application based on the user's business role, as defined in the deployment descriptor. The sample application is defined with three roles: admin, user, and guest. A user with an admin role has access to all accounts in the sample application, but a user with a guest role will only be able to access pages defined for the guest. Test this by entering the application with different user IDs and passwords with admin, user, and guest roles. Figure 23 shows a user logged in as admin, but who is able to view the guest pages.

   
   Figure 23. Admin user viewing Guest page
   
   

   In the case of unauthorized access (for example, if a guest user attempts to access a page authorized for admin users only), an HTTP 403 status error occurs (Figure 24) and prevents access to the page.

   
   Figure 24. Authorization error
   
   

   Using the database pool

   The default Web application is configured to use SQL realm that uses the JDBC driver. You can change this so that it uses the database pool by changing the realm name in the geronimo-web.xml file as <security-realm-name>sqlpool-realm</security-realm-name>, and republishing the server. Verify if this works by stopping the db2/realm/1.0/car configuration in the server.

   Contents of the download file

   The download file included with this tutorial, sample-db2-realm.zip, contains the following files:

   • db2-realm.sql - SQL script file to create database and tables
   • db2-plan.xml - Plan to deploy a DB2 database pool.
   • sqlplan.xml - SQL realm plan that uses JDBC driver.
   • sqlpoolplan.xml - SQL realm plan that uses a database pool.
   • SQLRealmAppSrc.ear - Source for sample Web application.