Database (SQL) Realm

In this section we will focus on the use a database for verifying and retrieving user names and passwords.

For this example we created a new database called SecurityDatabase using the built-in Derby database. The following steps summarize the procedure performed to create the database and tables, load some sample data and create the connection pool. Detailed instructions on how to define database connection pools are described in the Configuring database pools section.

Create database and load sample data

In the Console Navigation menu on the left click on Database Manager.
Enter SecurityDatabase in the Create DB: field and click Create.
Select the SecurityDatabase database from the Use DB: pull-down menu, enter the following commands and click Run SQL.
create table users
(username varchar(15),
password varchar(15));
create table groups
(username varchar(15),
groupname varchar(15));
insert into users values('userone','p1');
insert into users values('usertwo','p2');
insert into users values('userthree','p3');
insert into groups values('userone','admin');
insert into groups values('usertwo','admin');
insert into groups values('userthree','user');

Create connection pool

In the Console Navigation menu on the left click on Database Pools.
Click on Using the Geronimo database pool wizard.
Enter SecurityDatabase as the database pool name.
Select Derby embedded from the database pool type pull-down menu and click Next.
Verify the JDBC driver class is org.apache.derby.jdbc.EmbeddedDriver.
From the Driver Jar pull-down menu select org.apache.derby/derby/10.1.3.1/jar.
Leave blank the connection user name and password.
Enter SecurityDatabase as the database name and click Next.
Click Test Connection.
Click Deploy.

Add a new security realm

To create a new security realm click on Add new security realm from the Security Realms portlet.

Enter derby_security_realm in the Name of Security Realm: field and select Database (SQL) Realm from the Realm type: pull-down menu and click Next.

The following screen configures the login module. The first two field you need to fill may vary from one database type to another. In this case we are using the embedded Derby database so the User and Group select SQL should read as follows:

User SELECT SQL: select username, password from APP.users where username=?
Group SELECT SQL: select username, groupname from APP.groups where username=?

Note that APP is the default schema for the embedded Derby database and it needs to precede the table in the SQL statement. These statements are likely to be different from one database to another, for instance this procedure was also tested with DB2, the SQL statements used were:

User SELECT SQL: select username, password from users where username=?
Group SELECT SQL: select username, groupname from groups where username=?

Once you entered the SQL statements for retrieving users and groups you need to select from the Database Pool pull-down menu the database connection pool you created in the previous step. Add the required values as shown below and click Next.

Database Pool: SecurityDatabase

JDBC Driver Class: org.apache.derby.jdbc.EmbeddedDriver

Driver JAR: org.apache.derby/derby/10.1.1.0/jar

JDBC URL: jdbc:derby:SecurityDatabase

The following step will allow you to enable auditing for monitoring the login attempts via this realm. In this step you can also configure the account lockout based on the number of failed loging attempts withing a specified timeframe. If you enable Store Password, then it will allow the realm to store the user's password in a private credential in the "Subject". If you enable Naming Credential, in addition to the user's password, this option will use private credentials to store user names too.

At this point you have configured this new security realm, the next step i to test it and then deploy it. Click on Test a Login.

Enter a valid user name and password to be retrieved from the database and click Next.

You should receive a confirmation message that the login succeded, click on Deploy Realm to load this configuration to the server.

Now you have a new, fully configured, security realm that retrieves user names and passwords from the build in Derby database.

If you get an error the first time you try to validate this realm, you will very likely see the SQL Exception: Failed to start database ... error in the terminal and logs. This is a know issue with Derby, you will need to restart Geronimio so the new database can communicate properly.

The followig example shows the deployment plan for this security realm. As an alternative to the Geronimo Administration Console, you can save this example to a file (i.e. derby_security_realm.xml) and deploy it with the Deployer tool by running the following command:

<geronimo_home>\bin\deploy --user system --password manager deploy <realm_path>\derby_security_realm.xml

derby_security_realm

<module xmlns="http://geronimo.apache.org/xml/ns/deployment-1.2">
    <environment>
        <moduleId>
            <groupId>console.realm</groupId>
            <artifactId>derby_security_realm</artifactId>
            <version>1.0</version>
            <type>car</type>
        </moduleId>
        <dependencies>
            <dependency>
                <groupId>org.apache.geronimo.configs</groupId>
                <artifactId>j2ee-security</artifactId>
                <type>car</type>
            </dependency>
            <dependency>
                <groupId>org.apache.derby</groupId>
                <artifactId>derby</artifactId>
                <version>10.1.3.1</version>
                <type>jar</type>
            </dependency>
            <dependency>
                <groupId>console.dbpool</groupId>
                <artifactId>SecurityDatabase</artifactId>
                <version>1.0</version>
                <type>rar</type>
            </dependency>
        </dependencies>
    </environment>
    <service name="derby_security_realm" class="org.apache.geronimo.security.realm.GenericSecurityRealm" xsi:type="dep:gbeanType" 
                xmlns:dep="http://geronimo.apache.org/xml/ns/deployment-1.2" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
        <attribute name="realmName">derby_security_realm</attribute>
        <reference name="ServerInfo">
            <name>ServerInfo</name>
        </reference>
        <reference name="LoginService">
            <name>JaasLoginService</name>
        </reference>
        <xml-reference name="LoginModuleConfiguration">
            <log:login-config xmlns:log="http://geronimo.apache.org/xml/ns/loginconfig-1.2">
                <log:login-module control-flag="REQUIRED" server-side="true" wrap-principals="false">
                    <log:login-domain-name>derby_security_realm</log:login-domain-name>
                    <log:login-module-class>org.apache.geronimo.security.realm.providers.SQLLoginModule</log:login-module-class>
                    <log:option name="userSelect">select username, password from APP.users where username=?</log:option>
                    <log:option name="jdbcDriver">org.apache.derby.jdbc.EmbeddedDriver</log:option>
                    <log:option name="dataSourceApplication">null</log:option>
                    <log:option name="groupSelect">select username, groupname from APP.groups where username=?</log:option>
                    <log:option name="dataSourceName">SecurityDatabase</log:option>
                    <log:option name="jdbcURL">jdbc:derby:SecurityDatabase</log:option>
                </log:login-module>
                <log:login-module control-flag="OPTIONAL" server-side="true" wrap-principals="false">
                    <log:login-domain-name>derby_security_realm-Audit</log:login-domain-name>
                    <log:login-module-class>org.apache.geronimo.security.realm.providers.FileAuditLoginModule</log:login-module-class>
                    <log:option name="file">var/log/derby_security_realm.log</log:option>
                </log:login-module>
                <log:login-module control-flag="REQUISITE" server-side="true" wrap-principals="false">
                    <log:login-domain-name>derby_security_realm-Lockout</log:login-domain-name>
                    <log:login-module-class>org.apache.geronimo.security.realm.providers.RepeatedFailureLockoutLoginModule</log:login-module-class>
                    <log:option name="lockoutDurationSecs">60</log:option>
                    <log:option name="failurePeriodSecs">10</log:option>
                    <log:option name="failureCount">3</log:option>
                </log:login-module>
            </log:login-config>
        </xml-reference>
    </service>
</module>