Supplying a JDBC password at run-time.

classic Classic list List threaded Threaded
12 messages Options
Reply | Threaded
Open this post in threaded view
|

Supplying a JDBC password at run-time.

randygalbraith-2
I am looking for advice on the best way to supply an Oracle password at
run-time. When our application connects to Oracle it uses a password that is
provided from a password store. The password itself is updated every 60
days.

My current WEB-INF/resources.xml looks like this:

<?xml version="1.0" encoding="UTF-8"?>
<resources>
  <Resource id="ID" type="javax.sql.DataSource">
      JdbcDriver = oracle.jdbc.OracleDriver
      JdbcUrl = jdbc:oracle:thin:@HOST:PORT:DBNAME
      JtaManaged = true
      UserName = TheUserId
      Password = PlainTextPassword
  </Resource>
</resources>


My test.jsp test harness within my .war file looks like this:

<%@ taglib uri="http://java.sun.com/jsp/jstl/sql" prefix="sql" %>
<%@ taglib uri="http://java.sun.com/jsp/jstl/core" prefix="c" %>

<sql:query var="rs" dataSource="ID">
select col1, col2 from schema.table where id > 3081915
</sql:query>

<html>
  <head>
    <title>DB Test</title>
  </head>
  <body>

<c:forEach var="row" items="${rs.rows}">
    row.col1=${row.col1}<br/>
    row.col2=${row.col2}<br/>
</c:forEach>

  </body>
</html>

This all works. What I now need to do is supply the value of
PlainTextPassword dynamically at run-time. I have added code to test.jsp
that demonstrates my ability to retrieve the password from our custom
password store. Alas, I am unsure how to apply it to the dataSource.

Reading through the TomEE documentation suggests I need to understand how to
use JNDI to access the dataSource resource. Then I should be able to
dynamically provide the password. I will begin to push in that direction.

Thanks in advance to any who respond. If my research lands on a solution I
will drop by and document what I find.

Version info (from catalina.out):

Server version: Apache Tomcat (TomEE)/9.0.12 (8.0.0-M2)
Server built:   Sep 4 2018 22:13:41 UTC
Server number:  9.0.12.0
OS Name:        Linux
OS Version:     3.10.0-1062.1.2.el7.x86_64
Architecture:   amd64
Java Home:      /path-on-host/jdk-11.0.2
JVM Version:    11.0.2+7

Kind regards,
-Randy Galbraith




--
Sent from: http://tomee-openejb.979440.n4.nabble.com/TomEE-Users-f979441.html
Reply | Threaded
Open this post in threaded view
|

RE: Supplying a JDBC password at run-time.

dimas
Hi Randy,

I'm using TomEE substitution in tomee.xml for this, here is some example:

<Resource id="someDS" type="javax.sql.DataSource">
        JdbcDriver   = org.postgresql.Driver
        JdbcUrl      = ${SOME_DS_URL}
        UserName     = ${SOME_DS_USER}
        Password     = ${SOME_DS_PASSWORD}
        //all other data source config properties
    </Resource>

Where SOME_DS_URL, SOME_DS_USER and SOME_DS_PASSWORD are env variables available for the TomEE process.

Cheers,
Dmitry

-----Original Message-----
From: randygalbraith [mailto:[hidden email]]
Sent: Monday, December 2, 2019 12:59 PM
To: [hidden email]
Subject: Supplying a JDBC password at run-time.

I am looking for advice on the best way to supply an Oracle password at run-time. When our application connects to Oracle it uses a password that is provided from a password store. The password itself is updated every 60 days.

My current WEB-INF/resources.xml looks like this:

<?xml version="1.0" encoding="UTF-8"?>
<resources>
  <Resource id="ID" type="javax.sql.DataSource">
      JdbcDriver = oracle.jdbc.OracleDriver
      JdbcUrl = jdbc:oracle:thin:@HOST:PORT:DBNAME
      JtaManaged = true
      UserName = TheUserId
      Password = PlainTextPassword
  </Resource>
</resources>


My test.jsp test harness within my .war file looks like this:

<%@ taglib uri="https://urldefense.com/v3/__http://java.sun.com/jsp/jstl/sql__;!LLC3CZw_jFA!M-74VSVl1d3oPky2Z3L3OvGfe57WrO0A_yKFRaotilRPddsn-tUCzLQLntpKoX8kbnHS$ " prefix="sql" %> <%@ taglib uri="https://urldefense.com/v3/__http://java.sun.com/jsp/jstl/core__;!LLC3CZw_jFA!M-74VSVl1d3oPky2Z3L3OvGfe57WrO0A_yKFRaotilRPddsn-tUCzLQLntpKoQCuEW1q$ " prefix="c" %>

<sql:query var="rs" dataSource="ID">
select col1, col2 from schema.table where id > 3081915 </sql:query>

<html>
  <head>
    <title>DB Test</title>
  </head>
  <body>

<c:forEach var="row" items="${rs.rows}">
    row.col1=${row.col1}<br/>
    row.col2=${row.col2}<br/>
</c:forEach>

  </body>
</html>

This all works. What I now need to do is supply the value of PlainTextPassword dynamically at run-time. I have added code to test.jsp that demonstrates my ability to retrieve the password from our custom password store. Alas, I am unsure how to apply it to the dataSource.

Reading through the TomEE documentation suggests I need to understand how to use JNDI to access the dataSource resource. Then I should be able to dynamically provide the password. I will begin to push in that direction.

Thanks in advance to any who respond. If my research lands on a solution I will drop by and document what I find.

Version info (from catalina.out):

Server version: Apache Tomcat (TomEE)/9.0.12 (8.0.0-M2)
Server built:   Sep 4 2018 22:13:41 UTC
Server number:  9.0.12.0
OS Name:        Linux
OS Version:     3.10.0-1062.1.2.el7.x86_64
Architecture:   amd64
Java Home:      /path-on-host/jdk-11.0.2
JVM Version:    11.0.2+7

Kind regards,
-Randy Galbraith




--
Sent from: https://urldefense.com/v3/__http://tomee-openejb.979440.n4.nabble.com/TomEE-Users-f979441.html__;!LLC3CZw_jFA!M-74VSVl1d3oPky2Z3L3OvGfe57WrO0A_yKFRaotilRPddsn-tUCzLQLntpKoQic8ZAp$ 
Reply | Threaded
Open this post in threaded view
|

RE: Supplying a JDBC password at run-time.

randygalbraith-2
Hi Dmitry,

Interesting. I did not know ${} syntax within resources.xml would pull in
values from the environment. Alas, that would not work in my case, since our
Oracle password is not and cannot be an environment variable.

Cheers,
-Randy



--
Sent from: http://tomee-openejb.979440.n4.nabble.com/TomEE-Users-f979441.html
Reply | Threaded
Open this post in threaded view
|

RE: Supplying a JDBC password at run-time.

dimas
Custom DataSourceFactory also worked for me. The trick was to create one big config string and use it in the org.apache.openejb.resource.jdbc.DataSourceFactory.create().
Something like this:

<resources>

    <Resource id="myDS" type="javax.sql.DataSource" class-name="com.bla.bla.DataSourceFactory" factory-name="create">
        JdbcDriver   = org.postgresql.Driver
    </Resource>
</resources>

Public class DataSourceFactory {

public Object create() {

        String definition = "JtaManaged=true\n" +
                "JdbcDriver=org.postgresql.Driver\n" +
                "JdbcUrl=yourUrl\n" +
                "UserName=yourUser\n" +
                "Password=yourPassword\n" +
                "PasswordCipher=PlainText\n" +
                "OpenEJBResourceClasspath=false\n" + ...;

        return org.apache.openejb.resource.jdbc.DataSourceFactory.create("someDS", true, org.postgresql.Driver.class,
                definition, null, null, null, false);
   }
}

Cheers,
Dmitry


-----Original Message-----
From: randygalbraith [mailto:[hidden email]]
Sent: Monday, December 2, 2019 2:32 PM
To: [hidden email]
Subject: RE: Supplying a JDBC password at run-time.

Hi Dmitry,

Interesting. I did not know ${} syntax within resources.xml would pull in values from the environment. Alas, that would not work in my case, since our Oracle password is not and cannot be an environment variable.

Cheers,
-Randy



--
Sent from: https://urldefense.com/v3/__http://tomee-openejb.979440.n4.nabble.com/TomEE-Users-f979441.html__;!LLC3CZw_jFA!OtvzO-Y8s49lf9J1CSZUZwMO_WknI9cxY6N-BAQkYZkEk5Z-Dy_qWfm87gHUZKSIjqmN$ 
Reply | Threaded
Open this post in threaded view
|

RE: Supplying a JDBC password at run-time.

randygalbraith-2
Hi Dimtry,

I hope to work on this again tomorrow. The idea of creating a custom
DataSourceFactory seems like the way to go. This approach does raise the
question about where to store the elements other than the password. For
example the UserName= will need to be variable on a per-environment basis.
Could the create() reference data within <Resource>...</Resource>? Or
perhaps placing such data in a .properties file would make more sense?

I saw your response earlier in the day. However, I was deep into making
calls to retrieve data via properties and/or otherwise trying to understand
JNDI within Java. Coming from C, I'm still working on building my Java-tech
muscles.

Cheers, -Randy



--
Sent from: http://tomee-openejb.979440.n4.nabble.com/TomEE-Users-f979441.html
Reply | Threaded
Open this post in threaded view
|

Re: Supplying a JDBC password at run-time.

Richard Monson-Haefel
Hi Randy,

I think you'll find this documentation very helpful is setting up a factory
for your resource http://tomee.apache.org/application-resources.html

Also, system properties override resource.xml and tomee.xml. There is a
system.properties files under the conf directory I believe that also
overrides the resource.xml and tomee.xml files.

Richard

On Mon, Dec 2, 2019 at 9:56 PM randygalbraith <[hidden email]>
wrote:

> Hi Dimtry,
>
> I hope to work on this again tomorrow. The idea of creating a custom
> DataSourceFactory seems like the way to go. This approach does raise the
> question about where to store the elements other than the password. For
> example the UserName= will need to be variable on a per-environment basis.
> Could the create() reference data within <Resource>...</Resource>? Or
> perhaps placing such data in a .properties file would make more sense?
>
> I saw your response earlier in the day. However, I was deep into making
> calls to retrieve data via properties and/or otherwise trying to understand
> JNDI within Java. Coming from C, I'm still working on building my Java-tech
> muscles.
>
> Cheers, -Randy
>
>
>
> --
> Sent from:
> http://tomee-openejb.979440.n4.nabble.com/TomEE-Users-f979441.html
>


--
Richard Monson-Haefel
https://twitter.com/rmonson
https://www.linkedin.com/in/monsonhaefel/
Reply | Threaded
Open this post in threaded view
|

Re: Supplying a JDBC password at run-time.

randygalbraith-2
Hi Richard,

I was reading that document (among others). In the "Defining Resources"
section it shows this code:

p.setProperty("DB.Password", "password");

This is close to what I need to work. Alas, the example does not show how
the variable "p" is created. Thus I was on the hunt to understand properties
within Java and then TomEE containers in particular. Since I was at that
part of the document I didn't get down to the section "factory-name method."

I'm happy you mentioned that link, because it gives me confidence I was
looking in the right spot. There are some other aspects of my project I
haven't mentioned that will impact the ultimate solution. All our
configuration starts out in a gnumake file as gnumake variables. These are
then expressed as m4 macros we then place in a template (i.e.
foo.properties.m4 becomes foo.properties).

Second is that while TomEE is being used for development the module that I'm
working on may need to land in Weblogic (the wheels of new technology review
and approval often moves slowly in large organization). Thus whatever the
solution winds up being I most likely will need to work in TomEE and
Weblogic.

Cheers, -Randy



--
Sent from: http://tomee-openejb.979440.n4.nabble.com/TomEE-Users-f979441.html
Reply | Threaded
Open this post in threaded view
|

Re: Supplying a JDBC password at run-time.

Richard Monson-Haefel
Hi,

I probably was not clear. There is a text file under tomee/conf called
system.properties.  You can simply write the property as follows

DB.Password = password

It just takes keys and values and then adds them to the system properties
on start up.  It contains a lot of commented out examples right in the
file.  Do you see it?

Richard

On Tue, Dec 3, 2019 at 7:40 AM randygalbraith <[hidden email]>
wrote:

> Hi Richard,
>
> I was reading that document (among others). In the "Defining Resources"
> section it shows this code:
>
> p.setProperty("DB.Password", "password");
>
> This is close to what I need to work. Alas, the example does not show how
> the variable "p" is created. Thus I was on the hunt to understand
> properties
> within Java and then TomEE containers in particular. Since I was at that
> part of the document I didn't get down to the section "factory-name
> method."
>
> I'm happy you mentioned that link, because it gives me confidence I was
> looking in the right spot. There are some other aspects of my project I
> haven't mentioned that will impact the ultimate solution. All our
> configuration starts out in a gnumake file as gnumake variables. These are
> then expressed as m4 macros we then place in a template (i.e.
> foo.properties.m4 becomes foo.properties).
>
> Second is that while TomEE is being used for development the module that
> I'm
> working on may need to land in Weblogic (the wheels of new technology
> review
> and approval often moves slowly in large organization). Thus whatever the
> solution winds up being I most likely will need to work in TomEE and
> Weblogic.
>
> Cheers, -Randy
>
>
>
> --
> Sent from:
> http://tomee-openejb.979440.n4.nabble.com/TomEE-Users-f979441.html
>


--
Richard Monson-Haefel
https://twitter.com/rmonson
https://www.linkedin.com/in/monsonhaefel/
Reply | Threaded
Open this post in threaded view
|

Re: Supplying a JDBC password at run-time.

randygalbraith-2
Hi Dmitry & Richard,

Thank you for all your help! Here is my anonymized source for what worked
:-)

DataSourceFactory.java:

package path1.path2;

import java.io.IOException;
import path3.path4.FooStore;


public class DataSourceFactory {

    public Object create() {

        String password = null;

        try {
            password = FooStore.getPassword("user", "db");
        } catch (Exception e) {
            System.err.println(e.toString());
            return null;
           
        }
        String definition = "JdbcDriver=oracle.jdbc.OracleDriver\n" +
            "JdbcUrl=jdbc:oracle:thin:@host:port:db\n" +
            "JtaManaged=true\n" +
            "UserName=user\n" +
            "Password=" + password + "\n";
        System.err.println("definition=["+definition+"]");
        try {
            return org.apache.openejb.resource.jdbc.DataSourceFactory.
                create("someDS", true, oracle.jdbc.OracleDriver.class,
                       definition, null, null, null, false);
        } catch (IllegalAccessException iae) {
            System.err.println(iae.toString());
            return null;
        } catch (InstantiationException ie) {
            System.err.println(ie.toString());
            return null;
        } catch (IOException ioe) {
            System.err.println(ioe.toString());
            return null;
        }
   }
}

resources.xml:

<?xml version="1.0" encoding="UTF-8"?>
<resources>
    <Resource id="myDS"
              type="javax.sql.DataSource"
              class-name="path1.path2.DataSourceFactory"
              factory-name="create">
      JdbcDriver = oracle.jdbc.OracleDriver
    </Resource>
</resources>

build.xml updates:
+        <pathelement location = "${libcat}/openejb-core-8.0.0-M2.jar"/>
+        <pathelement location = "${libora}/ojdbc8.jar"/>

Cheers, -Randy



--
Sent from: http://tomee-openejb.979440.n4.nabble.com/TomEE-Users-f979441.html
Reply | Threaded
Open this post in threaded view
|

Re: Supplying a JDBC password at run-time.

jgallimore
Glad you have it working. The other approach you could take is to implement
a custom password cipher:
https://tomee.apache.org/latest/examples/datasource-ciphered-password.html or
a properties provider:
https://tomee.apache.org/latest/docs/admin/configuration/resources.html which
you might be able to hook up to your password store. Here's a sample
properties provider in a unit test, if that helps.
https://github.com/apache/tomee/blob/master/container/openejb-core/src/test/java/org/apache/openejb/resource/PropertiesProviderTest.java#L90-L103

The DataSourceFactory is a little complex, but in general the properties
part of the system is quite flexible. The defaults for different resources
come from service-jar.xml in openejb-core, and are overridden by tomee.xml
or WEB-INF/resources.xml, and in turn, overridden by system properties.
Then you have ciphering, properties providers and class factories in the
mix as well, so there's a bunch of of different ways you can do it.

Couple of points to be wary of - specifying JVM args would potentially mean
the password is exposed on the command line, and visible to someone doing a
`ps`. Your System.err.println() may write the plain text password to a log,
depending on where stderr is routed to. Also, no matter how you get the
password to TomEE, if the server is compromised and an attacker is able to
get a heap dump, they'll be able to get your database password, so nothing
is perfect.

Anyway, glad you got something working, and thanks for following up. If you
have any questions around config, please let us know.

Jon

On Tue, Dec 3, 2019 at 8:06 PM randygalbraith <[hidden email]>
wrote:

> Hi Dmitry & Richard,
>
> Thank you for all your help! Here is my anonymized source for what worked
> :-)
>
> DataSourceFactory.java:
>
> package path1.path2;
>
> import java.io.IOException;
> import path3.path4.FooStore;
>
>
> public class DataSourceFactory {
>
>     public Object create() {
>
>         String password = null;
>
>         try {
>             password = FooStore.getPassword("user", "db");
>         } catch (Exception e) {
>             System.err.println(e.toString());
>             return null;
>
>         }
>         String definition = "JdbcDriver=oracle.jdbc.OracleDriver\n" +
>             "JdbcUrl=jdbc:oracle:thin:@host:port:db\n" +
>             "JtaManaged=true\n" +
>             "UserName=user\n" +
>             "Password=" + password + "\n";
>         System.err.println("definition=["+definition+"]");
>         try {
>             return org.apache.openejb.resource.jdbc.DataSourceFactory.
>                 create("someDS", true, oracle.jdbc.OracleDriver.class,
>                        definition, null, null, null, false);
>         } catch (IllegalAccessException iae) {
>             System.err.println(iae.toString());
>             return null;
>         } catch (InstantiationException ie) {
>             System.err.println(ie.toString());
>             return null;
>         } catch (IOException ioe) {
>             System.err.println(ioe.toString());
>             return null;
>         }
>    }
> }
>
> resources.xml:
>
> <?xml version="1.0" encoding="UTF-8"?>
> <resources>
>     <Resource id="myDS"
>               type="javax.sql.DataSource"
>               class-name="path1.path2.DataSourceFactory"
>               factory-name="create">
>       JdbcDriver = oracle.jdbc.OracleDriver
>     </Resource>
> </resources>
>
> build.xml updates:
> +        <pathelement location = "${libcat}/openejb-core-8.0.0-M2.jar"/>
> +        <pathelement location = "${libora}/ojdbc8.jar"/>
>
> Cheers, -Randy
>
>
>
> --
> Sent from:
> http://tomee-openejb.979440.n4.nabble.com/TomEE-Users-f979441.html
>
Reply | Threaded
Open this post in threaded view
|

Re: Supplying a JDBC password at run-time.

randygalbraith-2
Hi Jon,

You make mention of this, but probably good to emphasize this point... The
code I posted is proof-of-concept work and it contains a rather glaring
security issue. That is:

System.err.println("definition=["+definition+"]");

dumps the plaintext password to stderr, which in my case is
logs/catalina.out. The real production code will look different and of
course I'll need to determine what might wind up as exception toString()
output, so as to avoid leaking a password in that way.

I'm fairly confident in our password store. However, as you mentioned,
visibility into the process space of the JVM could reveal the plaintext of
the password.

Cheers, -Randy




--
Sent from: http://tomee-openejb.979440.n4.nabble.com/TomEE-Users-f979441.html
Reply | Threaded
Open this post in threaded view
|

Re: Supplying a JDBC password at run-time.

Richard Monson-Haefel
Great work, Jon!  Glad you found a solution that works for you.

On Tue, Dec 3, 2019 at 3:43 PM randygalbraith <[hidden email]>
wrote:

> Hi Jon,
>
> You make mention of this, but probably good to emphasize this point... The
> code I posted is proof-of-concept work and it contains a rather glaring
> security issue. That is:
>
> System.err.println("definition=["+definition+"]");
>
> dumps the plaintext password to stderr, which in my case is
> logs/catalina.out. The real production code will look different and of
> course I'll need to determine what might wind up as exception toString()
> output, so as to avoid leaking a password in that way.
>
> I'm fairly confident in our password store. However, as you mentioned,
> visibility into the process space of the JVM could reveal the plaintext of
> the password.
>
> Cheers, -Randy
>
>
>
>
> --
> Sent from:
> http://tomee-openejb.979440.n4.nabble.com/TomEE-Users-f979441.html
>


--
Richard Monson-Haefel
https://twitter.com/rmonson
https://www.linkedin.com/in/monsonhaefel/