Live Forms v6.2 is no longer supported. Click here for information about upgrading to our latest GA Release.

Database Container Security Manager

If your configuration has a requirement for a container to handle authentication, but users/roles reside in an external database,  tenants using DB Container Security Manager 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.

On this Page:

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. 

The DB Container Security Manager should only query a JDBC security realm and never update it.

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: 

TaskDescriptionContext ParameterAvailable Parameters:Resultset Columns:Example query:
Find Subject InfoReturns a single row containing metadata about a given subject.frevvo.security.container.findSubjectinfo
  • tenantId
  • userId
  • firstName
  • lastName
  • email
  • maxAttachmentSize
  • taskNotification
  • reportsTo
  • signature
  • signatureType
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
Query All RolesReturns all available roles in a tenant based on a given filter.frevvo.security.container.queryAllroles
  • tenantId
  • filter 
  • role
SELECT role_name as role
  FROM roles
  WHERE UPPER(role_name) LIKE :filter
  ORDER BY role_name
Query All User IdsReturns all available user id's in a tenant based on a given filter.frevvo.security.container.queryAllUserIds
  • tenantId
  • filter
  • userId
SELECT user_name as userId
  FROM users
  WHERE UPPER(user_name) LIKE :filter
  ORDER BY user_name
Query All User Ids for a RoleReturns all available user id's in a tenant for a given role. This is used to retrieve all tenant administrators for a tenant. frevvo.security.container.queryUserIdsForRole
  • tenantId
  • role
  • filter
  • userId
SELECT user_name as userId
  FROM user_roles
  WHERE role_name = :role and UPPER(user_name) LIKE :filter
  ORDER BY user_name
Query All Roles for a userReturns all roles for a given user. frevvo.security.container.queryRolesForUser
  • tenantId
  • userId
  • role
SELECT role_name as role
  FROM user_roles
  WHERE user_name = :userId
  ORDER BY role_name
Query All Subject Info for a list of Rolesreturns all SubjectInfo for a given list of roles and it is used to send notifications to flow participants.frevvo.security.container.querySubjectInfoForRoles
  • tenantId
  • roles :: 
  • comma separated list of roles ( e.g. ('manager','supervisor') )
  • role
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 changes to 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.

  1. Restart .
  2. Browse to the following url:  http://<hostname>:<port>/frevvo/web/login
  3. Login into your  as  the superuser:
    • Username : admin@d 
    • Password : admin 
    4. You should be logged in successfully as the Frevvo Admin user. 

  • 5. Once you are logged in as administrator click on the “Manage Tenants” link. 



    6. Click  icon to add a new Tenant .
     
  • 7. Select “DB Container Security Manager” as the Security Manager Class. 

    8. Add a Tenant ID, Tenant Name and Tenant Description. Use the user ID from the Security DB that has the role “frevvo.TenantAdmin” as the Admin Id


  • 9. Click Submit You should see a message “Tenant <TenantName> added successfully” 

If the tenant admin user does not exist or it does not have the required roles, this error will display with the name of the tenant you are trying to add:

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:

Existing 5.0.x, 5.1.x, and 5.2.x installations using the old Container Security Manager (package com.frevvo.security) will still work as before when upgraded since the new DB Container Security Manager is a separate implementation. This method will be merged into the new in a future release.

Here is how you can create a new tenant using the old Container Security Manager:

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