If your configuration has a requirement for a container (Tomcat, JBoss, Websphere) to handle authentication, but users/roles reside in an external database,  tenants using DB-CSM security can provide a database interface to fetch those user/roles. Some configuration is necessary and details may vary depending on the container you are using. 

When using the Container Security Manager, anonymous access to public forms/flows,  requires a public url.

Prerequisites

1. There must be a security database that with available users and roles.
2. There must be a frevvo.User role in the database.
3. All the users in your DB-CSM tenant must have this role.
4. The tenant admin must have the roles of frevvo.TenantAdmin, frevvo.Designer and frevvo.User.

Configuration Steps

Setting up the Security Database

1. Modify your container configuration files to point to your security database. For example, setting up datasources are deployed in an xml file using the -ds.xml naming convention, such as jaas-ds.xml.  In this example, the jaas-ds.xml. file for JBOSS is modified to point to an Oracle security database so  can use it.
   1. Supply the SECURITY_DB_NAME and DB_PORT shown in the example for your database.  

<!--
       ORACLE Resource
       -->

       <datasource jndi-name="java:jboss/datasources/securityDS" pool-name="userDBPool"
       enabled="true" use-java-context="true">
              <connection-url>jdbc:oracle:thin:@<SECURITY_DB_NAME>:<DB_PORT>:ORCL</connection-url>
              <driver>com.oracle</driver>
              <pool>
              <max-pool-size>30</max-pool-size>
              </pool>
              <security>
              <user-name>root</user-name>
              <password>root</password>
              </security>
       </datasource>

Configure the Container Security Queries

You will not be able to use the DB Container Security Manager,until you configure  the required queries.These queries are global to the server installation and all tenants using the DB Container Security Manager will use the same queries.  This limitation will be lifted in a future release. 

Container queries to your security database are used to extract users' metadata. The results from these queries are used by  to generate users list, user information, user roles etc. Note that, if this file is updated when the frevvo server is up, the server requires a restart to use the updated queries. If you are using a container other than tomcat, you may have created a frevvo-config.properties. Create this file if you do not already have one and add your queries to it. Of course, the queries must conform to the structure and syntax of your database. Here are some examples: 

SELECT user_name as firstName, user_name as lastName, user_email as email, null as maxAttachmentSize, null as taskNotification, null as reportsTo, null as signature, null as signatureType
  FROM users
  WHERE user_name = :userId

SELECT role_name as role
  FROM roles
  WHERE UPPER(role_name) LIKE :filter
  ORDER BY role_name

SELECT user_name as userId
  FROM users
  WHERE UPPER(user_name) LIKE :filter
  ORDER BY user_name

SELECT user_name as userId
  FROM user_roles
  WHERE role_name = :role and UPPER(user_name) LIKE :filter
  ORDER BY user_name

SELECT role_name as role
  FROM user_roles
  WHERE user_name = :userId
  ORDER BY role_name

SELECT user_name as userId, user_name as firstName, user_name as lastName, user_email as email
  FROM users
  WHERE user_name IN (SELECT user_name FROM user_roles WHERE role_name IN (:roles))

Here's how the queries would look in the frevvo-config.properties file:

frevvo.security.container.datasource                            = java:comp/env/jdbc/tomcatDS

frevvo.security.container.findSubjectinfo                       = \
        SELECT user_name as firstName, user_name as lastName, user_email as email \
        FROM users \
        WHERE user_name = :userId

frevvo.security.container.queryAllroles                         = \
        SELECT role_name as role \
        FROM roles \
        WHERE UPPER(role_name) LIKE :filter \
        ORDER BY role_name

frevvo.security.container.queryAllUserIds                       = \
        SELECT user_name as userId \
        FROM users \
        WHERE UPPER(user_name) LIKE :filter \
        ORDER BY user_name

frevvo.security.container.queryUserIdsForRole                   = \
        SELECT user_name as userId \
        FROM user_roles \
        WHERE role_name = :role and UPPER(user_name) LIKE :filter \
        ORDER BY user_name

frevvo.security.container.queryRolesForUser                     = \
        SELECT role_name as role \
        FROM user_roles \
        WHERE user_name = :userId \
        ORDER BY role_name

frevvo.security.container.querySubjectInfoForRoles              = \
        SELECT user_name as firstName, user_name as lastName, user_email as email \
        FROM users \
        WHERE user_name IN (SELECT user_name FROM user_roles WHERE role IN (:roles))

JAAS Authentication Configuration

The Java Authentication and Authorization Service must be configured when using DBCSM.  Add the security configuration under the <security-domains> tag and modify the default-security-domain  to point to this configuration. Here's an example of the entry in the standalone.xml file in a JBOSS configuration:

<default-security-domain value="newly-defined-security-domain"/>
...
<subsystem xmlns="urn:jboss:domain:security:1.2">
  <security-domains>
     <security-domain name="newly-defined-security-domain" cache-type="default">
                ...
     </security-domain>
  </security-domains>
</subsystem> 

Modify the principalsQuery and rolesQuery 

Here's an example of the changes that need to be made in the standalone.xml file:

security-domain name="other" cache-type="default">
<authentication>
<login-module code="Database" flag="required">
<module-option name="dsJndiName"
value="java:jboss/datasources/securityDS"/>
<module-option name="principalsQuery" value="select USER_ID from FREVVO_USER_T where USER_ID = ?"/>
<module-option name="rolesQuery" value="SELECT CASE INSTR(gt.role_name, 'frevvo.') WHEN 1 THEN gt.role_name ELSE pt.customer_id || '_'|| gt.role_name END as role, 'Roles' FROM frevvo_user_roles_t ut, frevvo_group_rights_t gt, frevvo_person_t pt WHERE gt.group_name = ut.group_name AND pt.person_id = ut.user_id AND ut.user_id = ?"/>
</login-module>
</authentication>
</security-domain>

Set up frevvo.war for JAAS Authentication 

 To  identify any user with the role frevvo.User as a valid frevvo user, add this section to the web.xml file  in the frevvo.war. The web.xml file is included in the <frevvo-home>\tomcat\webapps\frevvo.war. The frevvo.war must be unzipped/rezipped after modifications have been made as outlined in the steps below:  

1. Stop  if it is running. 
2. Unpack the frevvo.war file to a temporary location of your choice: e.g. c:\tmp\frevvo-war. Change the file extension from .war to .zip if necessary.

3. Edit c:\tmp\frevvo-war\WEB-INF\web.xml. Add the section:
   
   

   <!-- 
   Security constraint BASIC AUTH
   -->    
   <security-constraint>
        <web-resource-collection>
               <web-resource-name>Secure frevvo</web-resource-name>
               <url-pattern>/*</url-pattern>
                  </web-resource-collection>
        <auth-constraint>
          <role-name>frevvo.User</role-name>
        </auth-constraint>
   </security-constraint>
   <security-constraint>
        <web-resource-collection>
               <web-resource-name>UnsecureHeartbeat</web-resource-name>
               <url-pattern>/heartbeat/*</url-pattern>
        </web-resource-collection>
        <user-data-constraint>
               <transport-guarantee>NONE</transport-guarantee>
        </user-data-constraint>
   </security-constraint>
   <login-config>
        <auth-method>BASIC</auth-method>
   </login-config>
   <security-role>
                  <role-name>frevvo.User</role-name>       
   </security-role>

   
   
   

4. Save the changes to the web.xml file. 

5. Rezip all the files in the c:\tmp\frevvo-war directory, even the ones you did not edit — if you change directories or zip them differently, Live Forms may not load correctly:

   This is the correct structure for the frevvo.war zip-file.

   
   
   
   

6. Make sure you create the zip-file with the directory structure as shown in the image above. It is an easy mistake to include the containing directory in the zip-file.

7. Zip will often give your zip-file a .zip extension. Make sure you change this to a .war extension.

Tomcat Configuration

For Tomcat specific information, please see JDBC Realm. This Tomcat specific setup is being added here as an additional reference.

Add the JDBC Realm to Tomcat's server.xml

<Realm className="org.apache.catalina.realm.JDBCRealm"
  driverName="org.gjt.mm.mysql.Driver"
  connectionURL="jdbc:mysql://localhost/tomcat?user=root&amp;password="
  userTable="users" userNameCol="user_name" userCredCol="user_pass"
  userRoleTable="user_roles" roleNameCol="role_name" />

Add the Tomcat JDBC Realm Data Source

Add the following to the frevvo.xml (adjust user and password accordingly):

<Resource auth="Container" type="javax.sql.DataSource" name="jdbc/tomcatDS"
  driverClassName="org.gjt.mm.mysql.Driver" username="root" password="" url="jdbc:mysql://localhost/tomcat?user=root&amp;password="
  maxActive="200" maxIdle="20" maxWait="10000" 
  testOnBorrow="true" testOnIdle="true" timeBetweenEvictionRunsMillis="10000"
  removeAbandoned="true" logAbandoned="true" />

Create Tenant with DB Container Security

In order to successfully create a DB ContainerSecurityManager tenant, requires you must have an admin user that: already exists and has the following roles: frevvo.TenantAdmin, frevvo.Designer.

Managing DB Container Security Manager users & roles works the same as the existing LDAP Security Manager, You can see the list of the existing users, go to their corresponding home pages, but you will not be able to manage them.

Here is a screenshot  of the expected functionality when managing users:

And here when managing roles:

Once your tenant is created, browse to this url to login as different users: http://<hostname>:<port>/frevvo/web/<tn>/<tenantname>/login.