frevvo v11.1 is no longer supported. Please visit the Documentation Directory for our current Cloud Release and other versions.

DB Connector Security

When accessing your database from an externally hosted SaaS frevvo Server, follow these steps to ensure your data is secure. You may also wish to consider one or all of these steps even when using the frevvo On Premise version if you feel your intranet is not secure.

Using the frevvo Database Connector's security mechanism, combined with only accepting SSL connections to the database connector from the web application container, will prevent unauthorized access to your database queries. The steps below describe how to secure your data.

On this page:


Configure SSL

The database connector does not have any specific configuration to handle SSL. Since it runs inside the Servlet container, it is typically the responsibility of the container to handle this layer of security. In any case, here is what we have done for customers that needed SSL and were using Tomcat: 

SSL (Secure Socket Layer), is a technology which allows web clients and web servers to communicate over a secured connection. This means the data being sent is encrypted by one side, transmitted, and then decrypted by the other side before processing. This is a two-way process, meaning that both the server AND the client encrypt all traffic before sending out data.” The basic steps:

  • Configure Tomcat to accept only SSL connections to the frevvo Database Connector. This encrypts data sent between the hosted frevvo Server and the frevvo Database Connector installed in your local machine, thus protecting the queryKey.
  • Create an SSL self-signed certificate and install it in Tomcat’s keystore. The self-signed certificate will ensure that the data being transmitted and received by the frevvo Database Connector is private and cannot be snooped by anyone who may be eavesdropping on the connection.
  • You can find more details here about running the frevvo Database Connector under Tomcat. 

Configure SSL for Standalone Database Connector

  1. Get an SSL Certificate. We recommend you get a certificate with PKCS12 (.pfx or .p12) format and SHA2 encryption.
  2. Edit <connectorInstallationDirectory>\config\dbconnector.properties to add the SSL to the keystore. Example:

    # Customize the DbConnector here
    logging.file=./logs/database-connector.%d{yyyy-MM-dd}.log
    server.port=8443
    server.ssl.key-store=keystore.p12
    server.ssl.key-store-password=PASSWORD
    server.ssl.keyStoreType=PKCS12
    server.ssl.keyAlias=tomcat


    The property "server.ssl.key-store" must point to the location of the certificate file on your server. If you save the certificate file in the same directory as the dbconnector.properties file, you can just set this to the file name.

  3. Browse https://<host>:<port>/database/status to check the status of the connector. Note: You may see a warning about an invalid cert; however, you will get the status page if you 'ignore' and go past it.

The queryKey attribute

The queryKey attribute enables a password authentication mechanism that limits query execution to only those who know the queryKey password string. Every HTTP request that is sent to the database connector to execute that query must contain the key. The connector will deny all requests that do not contain the key.
 
For example, given the configuration below:

<query name="customers" queryKey="abc123"> 
    <retrieve>
    <!-- maps to the http GET method --> 
        <statement> 
            SELECT * FROM customers WHERE customerId='{customerId}'
        </statement> 
    </retrieve>
</query>

 A valid request would be: http://localhost:8082/database/myStore/customers?queryKey=abc123&customerId=23434

SQL Injection Protection

The frevvo Database Connector automatically protects your data from Injection Attacks. No configuration is required for this security measure.

Database Password Security

While you cannot encrypt the database password in the <frevvo-home>\tomcat\conf\dbconnector.properties file, you can provide added security using one of the following methods:

  1. Define the data source at the container (tomcat) level for some added security. Please see this documentation which explains how.
  2. Store password as an OS Environment Variable and reference that variable in the dbconnector.properties file. See Secure Passwords in Tomcat for details.


Block Public Access to Database Connector URLs

There is nothing built in the frevvo Database Connector to block public access or enforce authentication to the database connector status page or other database connector URLs.
 
If you are using frevvo On Premise and the Database Connector is installed in the same tomcat as frevvo, you can restrict access to all database connector URLs from outside, allowing connections only from localhost (i.e. frevvo) by creating an individual context.xml for your app.

  1. Stop frevvo.
  2. Navigate to <frevvohome>\tomcat\webapps\database\META-INF\context.xml. Edit this file.
  3. Add the line 

    <Valve className="org.apache.catalina.valves.RemoteAddrValve" allow="127\.\d+\.\d+\.\d+|::1|0:0:0:0:0:0:0:1"/>

    Your context.xml should now look like this:

    <Context path="/database" unpackWAR="true" copyXML="false">
    	<Parameter name="spring.main.banner-mode" value="OFF" override="false"/>
    	<Parameter name="logging.file" value="${catalina.base}/logs/database-connector.%d{yyyy-MM-dd}.log" override="false"/>
    	
    	<JarScanner scanClassPath="false"/>
    	<!-- Add the next line to block remote access to the "database" web app -->
    	<Valve className="org.apache.catalina.valves.RemoteAddrValve" allow="127\.\d+\.\d+\.\d+|::1|0:0:0:0:0:0:0:1"/>
    </Context>
    

    Please see additional options for this solution in this article.

  4. Restart frevvo.

Alternately, if you want to block public access to just the Database Connector status page, then the only option is to block it using a reverse proxy.