title: JEE use JNDI with JDBC and auth Application level
body: <p>For simplicity we have Tomcat 8.0.27. Under the IDE create a web project.</p>
  
  <p>In web.xml</p>
  
  <pre><code>&lt;resource-ref&gt;
      &lt;description&gt;DB Connection&lt;/description&gt;
      &lt;res-ref-name&gt;jdbc/tablemanager&lt;/res-ref-name&gt;
      &lt;res-type&gt;javax.sql.DataSource&lt;/res-type&gt;
      &lt;res-auth&gt;Container&lt;/res-auth&gt;
  &lt;/resource-ref&gt;
  </code></pre>
  
  <p>In context.xml(This under tomcat)</p>
  
  <p>I prefer this way instead the double reference  <a href="https://www.journaldev.com/2513/tomcat-datasource-jndi-example-java" rel="nofollow noreferrer">https://www.journaldev.com/2513/tomcat-datasource-jndi-example-java</a></p>
  
  <pre><code>&lt;Resource
        name="jdbc/tablemanager"
        auth="Container"
        type="javax.sql.DataSource"
        factory="org.apache.tomcat.jdbc.pool.DataSourceFactory"
        initialSize="34"
        maxActive="377"
        maxIdle="233"
        minIdle="89"
        timeBetweenEvictionRunsMillis="34000"
        minEvictableIdleTimeMillis="55000"
        validationQuery="SELECT 1"
        validationInterval="34000"
        testOnBorrow="true"
        removeAbandoned="true"
        removeAbandonedTimeout="55"
        username="tablemanager"
        password="tablemanager"
        driverClassName="org.mariadb.jdbc.Driver"
        url="jdbc:mysql://172.17.0.3:3306/tablemanager?allowMultiQueries=true"
   /&gt;
  </code></pre>
  
  <p>In the page </p>
  
  <pre><code>Context initContext = new InitialContext();
  Context envContext = (Context) initContext.lookup("java:/comp/env");
  DataSource ds = (DataSource) envContext.lookup("jdbc/tablemanager");
  
  try (Connection conn = ds.getConnection()) {
  </code></pre>
  
  <p>This work very well if the user that access to the DB is located at application level.</p>
  
  <p>Is there a strict DB user management where each user has it row level grant? That is, instead of using a Container auth can the app:</p>
  
  <ul>
  <li><p>Use application level authorization. Like:</p>
  
  <p>Connection conn = ds.getConnection("tablemanager", "tablemanager");</p></li>
  <li><p>Can the connection retrieved from a pool (I suspect that this is not possible because the username and password are used in Tomcat jdbc pool to test the query)</p></li>
  <li><p>If pool is not suitable can I define a resource and use it for the connection.</p></li>
  </ul>
  
  <p>I have done some tests </p>
  
  <pre><code>&lt;resource-ref&gt;
      &lt;description&gt;DB2 Connection&lt;/description&gt;
      &lt;res-ref-name&gt;jdbc/db2&lt;/res-ref-name&gt;
      &lt;res-type&gt;javax.sql.DataSource&lt;/res-type&gt;
      &lt;res-auth&gt;Application&lt;/res-auth&gt;
      &lt;res-sharing-scope&gt;Unshareable&lt;/res-sharing-scope&gt;
  &lt;/resource-ref&gt;
  
  &lt;Resource
      name="jdbc/db2"
      auth="Application"
      type="javax.sql.DataSource"
      driverClassName="org.mariadb.jdbc.Driver"
      url="jdbc:mysql://172.17.0.3:3306/tablemanager?allowMultiQueries=true"
   /&gt;
  </code></pre>
  
  <p>With this config when I do</p>
  
  <pre><code>Context initContext = new InitialContext();
  Context envContext = (Context) initContext.lookup("java:/comp/env");
  DataSource ds = (DataSource) envContext.lookup("jdbc/db2");
  Connection conn = ds.getConnection("tablemanager", "tablemanager");
  </code></pre>
  
  <p>I get </p>
  
  <blockquote>
    <p>Not supported by BasicDataSource</p>
  </blockquote>