Thursday, December 20, 2012

Deleting all rows of all tables of a list of MySQL databases

Like most Java developers, I use the database technology with just enough knowledge of SQL, JPA/JPQL. In one of our application, we have a bunch of mysql databases, and recently, I had a need to clean up the contents of all the databases, all the tables, without deleting the structure. In other words, there was no choice of dropping and recreating databases. Also there was no choice of any UI tools, or not even a choice of writing something from my favorite Java. The only option was shell-script with a headless unix system.

Therefore, I wrote this simple utility in shell to clean-up a list of databases. It may be handy. Enjoy!


#!/bin/sh

cleanup_mysql(){
        read -s -p "Enter MySQL root Password: " mysqlpassword
        db_list="Database1   Database2   Database3   Database4"
        dml="SET foreign_key_checks = 0;"
        for db in $db_list
        do
                echo "Cleaning up database: $db"
                TABLES=`mysql -u root -p$mysqlpassword $db -e 'show full tables where Table_Type = "BASE TABLE"'|awk '{ print $1}'|grep -v '^Tables'`
                for table in $TABLES
                do
                        dml="$dml DELETE FROM $db.$table;"
                done
        done
        dml="$dml SET foreign_key_checks = 1;"
        mysql -u root -p$mysqlpassword --e="$dml"
}
cleanup_mysql

Monday, October 1, 2012

GlassFish3: Using encrypted password for JDBC connections

If you had configured a jdbc-connection-pool, or jms-connection on GlassFish3, you may have noticed that the passwords are stored in plain-text in config/domain.xml. That is unfortunate, and a security-guy in your organization may give you a hard-time for it.

Hopefully, a future release of GlassFish may choose encrypted-password-storage as the DEFAULT, but till then, creating password-alias could be a satisfactory alternative. There is enough documentation on the create-password-alias command, but I thought of creating a really simple example in this context.

Let's create a jdbc-connection-pool called MyConnectionPool from cmd-line. It could be done with a single command or easily through admin-console as well.

asadmin> create-jdbc-connection-pool --datasourceclassname oracle.jdbc.pool.OracleDataSource --restype javax.sql.DataSource MyConnectionPool

asadmin>
set resources.jdbc-connection-pool.MyConnectionPool.property.user=dbuser
asadmin>
set resources.jdbc-connection-pool.MyConnectionPool.property.password=${ALIAS=db_password_alias}
asadmin> set resources.jdbc-connection-pool.MyConnectionPool.property.url="jdbc:oracle:thin:@host:port:service

And if you look at the domain.xml , you would see something like this:

      <property name="password" value="${ALIAS=db_password_alias}"></property>

Now let's provide actual-password, the one we want to store in an encrypted manner:

asadmin> create-password-alias
Enter the value for the aliasname operand>
db_password_alias
Enter the alias password> *******
Enter the alias password again> ******
Command create-password-alias executed successfully.

You can take a peek in the config/domain-passwords file, which maps aliases to passwords, and confirm that your password above can not be deciphered. That's it, but don't forget to verify that a connection could be retrieved using the encrypted password.

asadmin> ping-connection-pool MyConnectionPool
Command ping-connection-pool executed successfully.

If you fat-fingered, or if you would like to change the password, you can use update-password-alias command. Refer to glassfish commands documentation for more information, and that above command works in a non-interactive mode as well, if you prefer to script it.

Good-luck getting that security-guy off your back!

Monday, July 9, 2012

Weblogic12c: SQLException Invalid column index





Intermittently occurring problems are always hard to fix. I encountered one today in a webapp running on Weblogic12c connecting to Oracle database. There were random incidences of following exception in using a PreparedStatement:

  java.sql.SQLException: Invalid column index
    at oracle.jdbc.driver.OraclePreparedStatement.setStringInternal(OraclePreparedStatement.java:5330)
    at oracle.jdbc.driver.OraclePreparedStatement.setString(OraclePreparedStatement.java:5318)
    at oracle.jdbc.driver.OraclePreparedStatementWrapper.setString(OraclePreparedStatementWrapper.java:282)
    at weblogic.jdbc.wrapper.PreparedStatement.setString(PreparedStatement.java:903)

The same code would work just fine in normal operations and had no clue as to why above would occur every now and then, even with same set of data. I have overly simplified it but the code line from which this sporadic exceptions was thrown seemed something like this:

   pstmt.setString(1,somevalue);

Typically, one may think of binding("?") mismatch in SQL and setting them from java-code, but that could be ruled out as the app has been working just fine, barring rare occassions of above exception. Moreover, the SQL used to construct the PreparedStatement is defined in a static-final-variable, and no question of changing it dynamically. So what could cause this SQL Exception for some http-requests and not for the other?

In doing some extensive load-testing, we observed an increased frequency of above exceptions. And then in looking for patterns, it turned out if two shortly-spaced http-requests exercised the same PreparedStatement, above exception may occur. Try increasing the delay between those two requests and there won't be a problem. Or do something else in-between to exercise other code, other PreparedStatements/Statements etc, and you won't reproduce it.

With that in mind, I created a simple HtmlUnit based traversing of the app-pages to quickly exercise the same PreparedStatement, and managed to reproduce the intermittent problem more frequently. I deliberately chose to use the same set of data for requests and snooped the http-traffic. The requests appeared the same on the wire, and with same set of key-values, the first request used to work and the subsequent one in shorter time-span would fail.

The SQL wasn't changing, the data wasn't changing, therefore, I ruled out code-related errors and turned my attention to Weblogic DataSource configuration. After fiddling with some related Connection-Pool parameters, I stepped on Statement-Cache-Sizing parameter, and here is the description from Weblogic12c Admin Console:

Statement Cache Sizing:

The number of prepared and callable statements stored in the cache. (This may increase server performance.) WebLogic Server can reuse statements in the cache without reloading the statements, which can increase server performance. Each connection in the connection pool has its own cache of statements. Setting the size of the statement cache to 0 turns off statement caching.

In optimizing things, I guess Weblogic12c may have a bug with Statement-Cache-Sizing that surfaces in certain situations, and disabling this caching took care of the "SQLException: Invalid column index" problem for me. It is not ideal, but nevertheless a workaround.

Note the performance implications, before using it in production-environment.

How to disable Statement-Cache-Sizing?

Admin-Console-->Services--->Data Sources-->(select-your-DS)-->Connection Pool

and set Statement-Cache-Size = 0

Post alternatives if you come across any, otherwise, happy-surfing!





Monday, January 16, 2012

Seam 3 on Weblogic 12c

The JSR299/CDI(context-dependency-injection) is now part of any JavaEE6 application server, and I have had been exploring it on one such app-server Weblogic 12c, which bundles Weld 1.1.3.SP1 as the CDI container.

In JSF world, they say, it is a good practice to use appropriate scope on JSF managed-beans to make efficient use of the server-memory, and that the same applies to CDI managed-beans. For CDI beans, the supported scopes are @RequestScoped, @SessionScoped, @ApplicationScoped, and @ConversationScoped. However, there is no support in CDI for a JSF equivalent @ViewScoped bean, which happens to be the best choice in certain use cases.

So what do we do if we want @ViewScoped added to CDI managed-beans? The answer in my search so far is: cdi-extensions. The seam-faces module from JBoss is one such CDI extension that adds the @ViewScoped support. One simply needs to add the seam-faces module jars to the web or enterprise archive and the @ViewScoped is added automagically.

The auto magic part is that the seam3 modules are BDAs(bean-deployment-archives), jars that contain META-INF/beans.xml file, and therefore they are scanned for beans in the CDI bootstrap. The seam-faces module has dependencies on other seam-modules such as seam-international and those jars are also needed. A recommendation if you are using maven is to add a dependency management to seam-BOM(bill-of-material) and you would get all the needed dependencies, tested version jars etc. That's it per the JBoss documentation on getting the seam-faces working.

--snip--
    <properties>
        <project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
        <seam.version>3.0.0.Final</seam.version>
    </properties>
    <dependencyManagement>
        <dependencies>
            <dependency>
                <groupId>org.jboss.seam</groupId>
                <artifactId>seam-bom</artifactId>
                <version>${seam.version}</version>
                <type>pom</type>
                <scope>import</scope>
            </dependency>
        </dependencies>
    </dependencyManagement>

    <dependencies> 
        <dependency>
            <groupId>org.jboss.seam.faces</groupId>
            <artifactId>seam-faces</artifactId>
        </dependency>
       ........
    </dependencies>
--snip--

In reality however, getting seam-faces to work on Weblogic 12c wasn't an easy experience. It failed with a number of ClassNotFound Exceptions despite using the BOM dependency. It seemed to presume the app-server to contain some seam-classes. That wasn't the case for weblogic, and adding explicit dependencies to the pom helped get past this issue.

Next it failed due to unsatisfied CDI dependencies with exception as follows:

--snip--
Caused By: org.jboss.weld.exceptions.DeploymentException: WELD-001408 Unsatisfied dependencies for type [Messages] with qualifiers [@Default] at injection point [[parameter 2] of [method] org.jboss.seam.faces.status.MessagesAdapter.convert(PhaseEvent, Messages)]
at org.jboss.weld.bootstrap.Validator.validateInjectionPoint(Validator.java:258)
at org.jboss.weld.bootstrap.Validator.validateObserverMethods(Validator.java:484)
at org.jboss.weld.bootstrap.Validator.validateDeployment(Validator.java:314)
at org.jboss.weld.bootstrap.WeldBootstrap.validateBeans(WeldBootstrap.java:361)
at com.oracle.injection.provider.weld.WeldInjectionContainer.start(WeldInjectionContainer.java:105)
--snip--

Above exception indicates a problem satisfying an injection-point inside MessagesAdapter bean inside seam-faces. It seems to inject "Messages" bean into MessagesAdapter and fails to find the "Messages" bean. Interestingly, the "Messages" bean is defined inside seam-international module and that was part of the deployment-archive, but still it didn't find the bean. So after some deliberation and a separate test, I concluded that there is a problem/bug in Weblogic 12c that cyclic dependencies across BDAs are not satisfied. 

In other words, if you have two BDAs as part of a  test.war: a.jar and b.jar, added in WEB-INF/lib, and then trying to inject a bean from a.jar into a class in b.jar doesn't work. This is needed per the JSR 299 specification. However, there is no problem in resolving beans inside the same BDA. So what's a workaround? Merge the two jars: a.jar and b.jar into a single jar and add that one to test.war.

Here is a way to create an uber jar of seam-faces and seam-international using maven:

--snip--
<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
        xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/maven-v4_0_0.xsd">
        <modelVersion>4.0.0</modelVersion>
        <groupId>com.suhas.commonjar</groupId>
        <artifactId>seamfaces-n-seami18n</artifactId>
        <packaging>jar</packaging>
        <version>1.0-SNAPSHOT</version>
        <name>seamfaces-n-seami18n</name>
        <url>http://maven.apache.org</url>
        <dependencies>
            <dependency>
                <groupId>org.jboss.seam.faces</groupId>
                <artifactId>seam-faces</artifactId>
                <version>3.0.0.Final</version>
            </dependency>
            <dependency>
                <groupId>org.jboss.seam.international</groupId>
                <artifactId>seam-international</artifactId>
                <version>3.0.0.Final</version>
            </dependency>
            <dependency>
                <groupId>org.jboss.seam.solder</groupId>
                <artifactId>seam-solder</artifactId>
                <version>3.0.0.Final</version>
                <scope>provided</scope>
            </dependency>
        </dependencies>
        <build>
                <plugins>
                        <plugin>
                                <groupId>org.apache.maven.plugins</groupId>
                                <artifactId>maven-shade-plugin</artifactId>
                                <executions>
                                        <execution>
                                                <phase>package</phase>
                                                <goals><goal>shade</goal></goals>
                                        </execution>
                                </executions>
                                <configuration>
                                        <finalName>uber-${artifactId}-${version}</finalName>
                                </configuration>
                        </plugin>
                </plugins>
        </build>
</project>
--snip--

With above uber jar in the deployment-archive instead of seam-faces and seam-international helped resolve the CDI unsatisfied-dependencies problem, and now we have seam-faces working on 12c.

In summary, if you run into unsatisfied-dependencies problem on Weblogic 12c across two library BDAs, then see if creating an uber jar workaround may be be acceptable.

Thursday, December 29, 2011

ADF Faces on Weblogic 12c

Weblogic 12c is released a few weeks ago and now we have a robust, scalable Java EE6 implementation. The ADF seems to be slightly behind because a certified version of ADF Runtime is not yet available for weblogic 12c. You could try to install an older ADF runtime on 12c and I for sure ran into problems.

All I needed out of the ADF Runtime was the ADF Faces technology, so I thought to create a deployment-library of only needed jars that won't break ADF in general and then reference them from my webapp. That way, I could explore CDI and other cool features of Java EE6 bundled in Weblogic 12c, while continuing to use the ADF(faces). Now whenever the ADF Runtime is available, I can simply remove this custom ADF deployment-library from weblogic and update the needed library references in my webapp.

Here is what I did to create this temporal ADF deployment-library:

1. Created a maven web-archive project
$ mvn archetype:generate -DgroupId=com.test.adflib  -DartifactId=My-ADFLib -DarchetypeArtifactId=maven-archetype-webapp

2. Added <proj>/src/main/resources/META-INF/MANIFEST.MF to specify deployment-library name, version etc.
Specification-Title: TEMP-ADF Faces Library
Specification-Version: 1.0
Implementation-Title: TEMP-ADF Faces Library
Implementation-Version: 1.0
Implementation-Vendor: Self
Extension-Name: adf

2. Added following dependencies in the pom.xml and in added needed jars in my local repository.
    <dependency>
      <groupId>oracle.jdeveloper.library</groupId>
      <artifactId>ADF-Faces-Runtime-11</artifactId>
      <version>11.1.2.1.0</version>
      <type>pom</type>
      <scope>compile</scope>
    </dependency>
    <dependency>
      <groupId>oracle.jdeveloper.library</groupId>
      <artifactId>ADF-Common-Runtime</artifactId>
      <version>11.1.2.1.0</version>
      <type>pom</type>
      <scope>compile</scope>
    </dependency>
    <dependency>
      <groupId>oracle.jdeveloper.library</groupId>
      <artifactId>ADF-DVT-Faces-Runtime</artifactId>
      <version>11.1.2.1.0</version>
      <type>pom</type>
      <scope>compile</scope>
    </dependency>
    <dependency>
      <groupId>oracle.jdeveloper.library</groupId>
      <artifactId>ADF-DVT-Faces-Databinding-Runtime</artifactId>
      <version>11.1.2.1.0</version>
      <type>pom</type>
      <scope>compile</scope>
    </dependency>
    <dependency>
      <groupId>oracle.jdeveloper.library</groupId>
      <artifactId>ADF-DVT-Faces-Databinding-MDS-Runtime</artifactId>
      <version>11.1.2.1.0</version>
      <type>pom</type>
      <scope>compile</scope>
    </dependency>
    <dependency>
      <groupId>oracle.jdeveloper.library</groupId>
      <artifactId>Oracle-JEWT</artifactId>
      <version>11.1.2.1.0</version>
      <type>pom</type>
      <scope>compile</scope>
    </dependency>


4. Updated the pom.xml create-war plugin to include the custom MANIFEST.MF

  <build>
     <plugins>
      <plugin>
        <groupId>org.apache.maven.plugins</groupId>
        <artifactId>maven-war-plugin</artifactId>
        <configuration>
          <archive>
            <manifestFile>src/main/resources/META-INF/MANIFEST.MF</manifestFile>
          </archive>
        </configuration>
      </plugin>
    </plugins>
  </build>


5. Build(mvn clean install) and deploy the artifact as a deployment-library.

6. References this custom adf library by adding following to the weblogic.xml

    <library-ref>
        <library-name>adf</library-name>
        <specification-version>1.0</specification-version>
    </library-ref>

7. The web.xml in the webapp should register all the needed servlets(FacesServlet, ResourceServlet, GraphServlet,MapProxyServlet) as needed for ADF.



Thursday, September 29, 2011

ThreadLocal

"You need to change method-signature and pass a variable from method-A to method-B to method-C and then print it. Who wants to change those many classes on a short-notice? And still there is a possibility of intermingled output with multiple-threads," a friend uttered in exasperation. We thought to explore a way of implementing what he needed(pass a new parameter) without actually changing the method-signatures, thereby not affecting other code. And also, see if we could somehow make that passed value thread-safe.

ThreadLocal came to mind. The concept of ThreadLocal is a bit elusive so I decided to put-together this blog-post with a simplest and shortest possible example. In simplest terms, ThreadLocals could be used to associate a state to a thread-of-execution, where the normal variables can not work. And we could use that thread-state to attach the parameter we want to pass, and down the thread-of-execution it could be retrieved by other code. Problem solved:  no method-signature changes, and thread-safe.

Here from javadoc about the class java.lang.ThreadLocal<T>:

This class provides thread-local variables. These variables differ from their normal counterparts in that each thread that accesses one (via its get or set method) has its own, independently initialized copy of the variable. ThreadLocal instances are typically private static fields in classes that wish to associate state with a thread (e.g., a user ID or Transaction ID).


To keep the example simple, we will start only two named threads, and will attempt to retain thread-name in a variable as a state during a thread-of-execution. And to compare/contrast our attempts, we will store the thread-name in a ThreadLocal variable, and also in a normal variable.

Here is the execution output of the above code:

Fred#State1=>Fred#State2=>Fred
Lucy#State1=>Lucy#State2=>Lucy
Fred#State1=>Lucy#State2=>Fred
Lucy#State1=>Lucy#State2=>Lucy
Fred#State1=>Lucy#State2=>Fred
Lucy#State1=>Lucy#State2=>Lucy
Fred#State1=>Lucy#State2=>Fred

As you could see, the State1, retained in a normal variable was overwritten by the last thread as it entered the Running state. It could even have been "Fred", if that thread was to enter the Running state towards the end. So clearly, using a normal variable as above to retain state in a thread-of-execution doesn't work. Now contrast that too State2 and see how each thread-of-execution correctly prints the assigned state.

So next time when you are debugging a piece of code with multiple-threads, try using it. An example use-case would be in servlets as  each request creates a new thread-of-execution, and the servlet in-turn calls business-methods. You may be able to add request-specific, thread-specific data in the servlet and be able to access it deep-down in other methods where access to HttpSession or other shared-objects may not be possible.


Tuesday, May 17, 2011

Adding Weblogic security-policies using WLST

I struggled today to add a simple url-pattern security-policy on Weblogic using WLST(Web-Logic-Scripting-Tool). A google-search not revealing much is a rarity these days, so I decided to research and blog my findings here. I hope it help others, and it sure will help me as I am going to forget about it in a couple months, especially with a long vacation around the corner.

The problem I was trying to solve is simple: I wanted to enforce HTTP Basic authentication on an URI, using a url-pattern security-policy, and be able to configure this security using WLST. Ultimately, an example URL like http://host:port/myapp/someresource/* will have a restricted access.

This is fairly easy using the Weblogic admin-console, and below are typical high-level steps. We will simplify this further by excluding roles, groups and grant access only to a single-user.

1. Deploy application with CustomRolesAndSecurity as a security-model.
2. Add a user in default security-realm
3. Add url-pattern security-policy, and then add policy-condition to allow access to only #2 user.

Simple. But now how do we do the same using WLST? The #1 and #2 steps above using WLST are well-documented elsewhere, and the jython snippet in-brief here:

Application Deployment: myapp.war
deploy('myapp','/tmp/myapp.war',securityModel='CustomRolesAndPolicies')

Adding user: UserId=>testuser, Password=>testpass12
securityRealm=cmo.getSecurityConfiguration().getDefaultRealm()
authenticationProvider=securityRealm.lookupAuthenticationProvider('DefaultAuthenticator')
authenticationProvider.createUser('testuser','testpass12','comment about user')

Now the real topic of the blog-post - how to add the security-policy using WLST? There is a weblogic MBean called XACMLAuthorizerMBeanImpl and it has two methods to create/add a security-policy for a resource.

1. createPolicy(String resourceId, String expression)
A challenge in using above method is to compose the resourceId and the expression. There are different types of resources which could be protected such as uri, ejb, jms,webservice etc, and policies for all can be created using the same method. The resourceId however has to be resource-type specific, with patterns and stuff encoded, so care need to be taken to generate an appropriate string. To that, Resource classes in weblogic.security.service package could be used.

import weblogic.security.service.URLResource
#r=weblogic.security.service.URLResource(application,context_path,pattern,httpMethod,transport)
r=weblogic.security.service.URLResource('myapp','/myapp','/somepath/*',None,None)
resourceID=r.toString()

The resourceID value we get from above is: 
type=<url>, application=myapp, contextPath=/myapp, uri=/somepath/*.

Next, we need the expression and that is bit of a challenge too, but for a simple case of only one user it is Usr(testuser) without any logical-operators or so.

expression='Usr('+'testuser'+')'

With resourceID and expression available, now simply get the authorizer MBean and call createPolicy() operation, as follows:

authorizer=securityRealm.lookupAuthorizer('XACMLAuthorizer')
authorizer.createPolicy(resourceID,expression)

2. addPolicy(String policy)
This second method of creating a policy needs access policy defined in a XACML document. Here is a jython snippet that uses a sample XACML to create a policy using addPolicy() method. I chose TQS of jython to compose the long XACML for the above URL pattern.

policy="""
    <Policy PolicyId="urn:bea:xacml:2.0:entitlement:resource:type@E@Furl@G@M@Oapplication@Emyapp@M@OcontextPath@E@Umyapp@M@Ouri@E@UTest2@U@K" RuleCombiningAlgId="urn:oasis:names:tc:xacml:1.0:rule-combining-algorithm:first-applicable">
        <Description>Usr(testuser)</Description>
        <Target>
            <Resources>
                <Resource>
                    <ResourceMatch MatchId="urn:oasis:names:tc:xacml:1.0:function:string-equal">
                        <AttributeValue DataType="http://www.w3.org/2001/XMLSchema#string">type=&lt;url&gt;, application=myapp, contextPath=/myapp, uri=/somepath/*</AttributeValue>
                        <ResourceAttributeDesignator AttributeId="urn:oasis:names:tc:xacml:2.0:resource:resource-ancestor-or-self" DataType="http://www.w3.org/2001/XMLSchema#string" MustBePresent="true"/>
                    </ResourceMatch>
                </Resource>
            </Resources>
        </Target>
        <Rule RuleId="primary-rule" Effect="Permit">
            <Condition>
                <Apply FunctionId="urn:oasis:names:tc:xacml:1.0:function:string-is-in">
                    <AttributeValue DataType="http://www.w3.org/2001/XMLSchema#string">testuser</AttributeValue>
                    <SubjectAttributeDesignator AttributeId="urn:oasis:names:tc:xacml:1.0:subject:subject-id" DataType="http://www.w3.org/2001/XMLSchema#string"/>
                </Apply>
            </Condition>
        </Rule>
        <Rule RuleId="deny-rule" Effect="Deny"></Rule>
    </Policy>
"""
authorizer=securityRealm.lookupAuthorizer('XACMLAuthorizer')
authorizer.addPolicy(policy)

Now some questions:
1. How do I get XACML for a possibly complex policy?
One idea would be create the complex security-policy through admin-console, and then export the security-realm(security-realm-->migration-->export). It creates a bunch of files in a specified directory, and one file in there of interest is the XACMLAuthorizer.dat.

The XACMLAuthorizer.dat, despite the extension, is a valid XML document and contains the whole set of policies.  Now search for the policy that you created through the admin-console and grab that complete "Policy" element, and that is the XACML document you need using WLST/addPolicy() method.

2. With regard to createPolicy() method, I can create a resourceID using appropriate class for a resource from the weblogic.security.service package. However, how do I create a complex expression if there are multiple policy-conditions?

One approach would be to add policy-conditions through the admin-console. And then simply query the Expression attribute as follows for use in WLST. 

import weblogic.security.service.URLResource
r=weblogic.security.service.URLResource('myapp','/myapp','/someresource/*',None,None)
resourceID=r.toString()
securityRealm=cmo.getSecurityConfiguration().getDefaultRealm()
authorizer=securityRealm.lookupAuthorizer('XACMLAuthorizer')
import java.util.Properties
p=authorizer.getPolicy(resourceID)
Expression=p.get('Expression')
print Expression

Resources:
1.  Weblogic Mbean reference