How to use log4jdbc with WSO2 products to log JDBC calls

log4jdbc is a nice tool to log JDBC call details of an application. Here are the steps to use it in WSO2 products. Here I'm using WSO2 APIM.

1) Download log4jdbc driver from here, and place it in <API_HOME>/repository/components/lib/ directory.

Note: Make sure to have the JDBC driver corresponding to your database server too in the same directory.

2) In master-datasources.xml,

     i) Change JDBC url like this. (note :log4jdbc: part)

<url>jdbc:log4jdbc:mysql://localhost:3306/AM_DB</url>

     ii) Set JDBC driver class like this.

<driverClassName>net.sf.log4jdbc.DriverSpy</driverClassName>

3)  Add following to log4j.properties file.

log4j.logger.jdbc.sqlonly=INFO
log4j.logger.jdbc.sqltiming=INFO
log4j.logger.jdbc.audit=OFF
log4j.logger.jdbc.resultset=ERROR
log4j.logger.jdbc.connection=ERROR 

4) Restart the server, and you'll see logs like this.

[2016-07-28 21:25:27,719] INFO - sqltiming SELECT ID, NAME, IS_ENABLED, DISPLAY_NAME FROM IDP_AUTHENTICATOR WHERE IDP_ID = 1
{executed in 5 msec}
[2016-07-28 21:25:27,720] INFO - sqltiming SELECT PROPERTY_KEY, PROPERTY_VALUE, IS_SECRET FROM IDP_AUTHENTICATOR_PROPERTY WHERE AUTHENTICATOR_ID = 1
{executed in 0 msec}

[WSO2 IS] User Account Locking/Unlocking with Secondary Userstores

In WSO2 Identity Server, you can lock user accounts when they are created, and unlock later. For this feature to work, in each userstore, a user attribute should be mapped to "http://wso2.org/claims/identity/accountLocked"  claim. In identity server, this claim is already mapped to "accountLock" attribute in embedded LDAP userstore. So you only have to follow below steps to enable "Account locking on creation" feature.

For Primary Userstore

1) Enable Identity Management Listener in <IS_HOME>/repository/conf/identity/identity.xml

<EventListener type="org.wso2.carbon.user.core.listener.UserOperationEventListener" name="org.wso2.carbon.identity.mgt.IdentityMgtEventListener" orderId="50" enable="true"/>

2)  Do the following configurations in the <IS_HOME>/repository/conf/identity/identity­-mgt.properties file.

Authentication.Policy.Enable=true

Authentication.Policy.Account.Lock.On.Creation=true

For more information, you can read this.

3) If you want to see if an account is locked or not in user profile, you can set "http://wso2.org/claims/identity/accountLocked" claim "Supported By Default", by ticking it in Claim management UI > http://wso2.org/claims > Account Locked > Edit like this.

Then you'll see it in your profile like this.

For a Secondary Userstore

Now, let's try the same with a Secondary userstore. Say you have already added a Secondary Userstore with domain "WSO2". Now we need to map a user attribute from that userstore  to http://wso2.org/claims/identity/accountLocked  claim. 

Let's say we map above claim to an attribute named "locked" in your secondary userstore. You can map it like this. 

After doing that, user accounts in secondary userstore will also be locked once they are created.

That's all. Feel free to ask related questions below.