RSS

Category Archives: mysql

mysql related post

Pagination with Java Hibernate Criteria


Hibernate is most popular technology in Java development. Most of the real world application they used kind of hibernate technology with fine tuned mechanisms. If you are in java development I hope the following code snippet will be helpful you to make your application faster.

What I’m going to explain in this post is how to load the chunk of data with efficient manner.
Hibernate Criteria helps to do this as we wish. Think about if you have millions of data in your data table and you are going to display the data in your application. Check the following code snippet

You can see in the above example I have used two numbers to query the data from back end. The pageNumber means which page that your are in and the recordesPerPage will use to filter the max record count going to be query. Then the each time fetch the data from database by given range.

This will really make the performance gain rather than loading whole chunk of data.

 
5 Comments

Posted by on March 13, 2014 in java, mysql

 

Tags: , , ,

Install LAMP(Apache, Mysql, php) and PhpMyAdmin on Ubuntu 12.04


Most people doing software development on linux and they need to install the LAMP on there machines so these are the steps to achieve above task.

1. First you have to install the “tasksel” on Ubuntu. so go to terminal and follow the command.

sudo apt-get install tasksel

2. Then run the tasksel.

sudo tasksel

3. Select the LAMP server and Install.
lamp
4. Now you need to verify the php whether its working properly. so you can open the following file on terminal

sudo vi /var/www/info.php

Then past the following command on it and save

<?php
 phpinfo();
?>

5. Now restart the server

 sudo /etc/init.d/apache2 restart

6. Now go to browser and visit the http://localhost/info.php page
info.php

7. Now you have completely setup the LAMP server but you don’t have nice management console to access the MySql Database. So that is why we need to install phpMyAdmin console.

8. Follow the steps to install phpMyAdmin

 sudo apt-get install phpmyadmin

apache

passmyadmin

9. Once the setup is completed you can go to the management console

mysqlAdmin

10. Don’t worry if you got Page Not found so then you have to do the extra step to set up phpmyadmin under Apache manually.

Open the file

 sudo gedit /etc/apache2/apache2.conf

Copy past the following line in the bottom of the apache2.conf file

 Include /etc/phpmyadmin/apache.conf

Now restart the server as follows and go to management console

sudo /etc/init.d/apache2 restart
 
3 Comments

Posted by on October 7, 2013 in mysql, php

 

Tags: , , ,

Custom JDBC user store manager with Custom properties as Claims in WSO2IS 4.1.1 alpha


In my early blog post I have describe how to write the custom user store manager and plug in to the WSO2 Identity Server. Now I’m going to explain how we can plug the user properties as claims and how to do some authorization with that properties. First of all you need to read my early blog post (How to write Custom JDBC user store manager with WSO2IS 4.1.1 alpha)

I have modify the DB Schema as follows

CREATE TABLE CUSTOMER_DATA (
             CUSTOMER_ID INTEGER NOT NULL AUTO_INCREMENT,
             CUSTOMER_NAME VARCHAR(255) NOT NULL,
             PASSWORD VARCHAR(255) NOT NULL,
             EMAIL VARCHAR(255) NOT NULL,
             AGE VARCHAR(255) NOT NULL,
             STATUS VARCHAR(255) NOT NULL,
             PRIMARY KEY (CUSTOMER_ID),
             UNIQUE(CUSTOMER_NAME)
);

INSERT INTO CUSTOMER_DATA (CUSTOMER_NAME, PASSWORD,EMAIL,AGE,STATUS) VALUES("dinuka" ,"dinuka","dinukam@wso2.com","25","ACTIVE");
INSERT INTO CUSTOMER_DATA (CUSTOMER_NAME, PASSWORD,EMAIL,AGE,STATUS) VALUES("malinda" ,"malinda","malinda@gmail.com","25","INACTIVE");

As I mention in the early post we need to create the data sources in master-datasources.xml and plug the newly created JDBCUserStoreManager through the user-mgt.xml

1. Lets look at the modification of JDBCUserStoreManager.

If we need to get some user properties as claims then you need to override the following methods in JDBCUserStoreManager (Download the mvn project here).

    @Override
    protected String getProperty(Connection dbConnection, String userName, String propertyName,
                                 String profileName) throws UserStoreException {
        String sqlStmt = realmConfig.getUserStoreProperty(JDBCRealmConstants.GET_PROPS_FOR_PROFILE);
        if (sqlStmt == null) {
            throw new UserStoreException("The sql statement for add user property sql is null");
        }
        PreparedStatement prepStmt = null;
        ResultSet rs = null;
        String value = null;
        try {
            prepStmt = dbConnection.prepareStatement(sqlStmt);
            prepStmt.setString(1, userName);

            rs = prepStmt.executeQuery();
            while (rs.next()) {
                if(propertyName.equals("EMAIL")){
                    value = rs.getString(1);
                }else if(propertyName.equals("AGE")){
                    value = rs.getString(2);
                }else if(propertyName.equals("STATUS")){
                    value = rs.getString(3);
                }
            }
            return value;
        } catch (SQLException e) {
            log.error("Using sql : " + sqlStmt);
            throw new UserStoreException(e.getMessage(), e);
        } finally {
            DatabaseUtil.closeAllConnections(null, rs, prepStmt);
        }
    }

    @Override
    public Map<String, String> getUserPropertyValues(String userName, String[] propertyNames,
                                                     String profileName) throws UserStoreException {
        if (profileName == null) {
            profileName = UserCoreConstants.DEFAULT_PROFILE;
        }
        Connection dbConnection = null;
        String sqlStmt = null;
        PreparedStatement prepStmt = null;
        ResultSet rs = null;
        String[] propertyNamesSorted = propertyNames.clone();
        Arrays.sort(propertyNamesSorted);
        Map<String, String> map = new HashMap<String, String>();
        try {
            dbConnection = getDBConnection();
            sqlStmt = realmConfig.getUserStoreProperty(JDBCRealmConstants.GET_PROPS_FOR_PROFILE);
            prepStmt = dbConnection.prepareStatement(sqlStmt);
            prepStmt.setString(1, userName);

            rs = prepStmt.executeQuery();
            while (rs.next()) {
                String email = rs.getString(1);
                String age = rs.getString(2);
                String status = rs.getString(3);
                if (Arrays.binarySearch(propertyNamesSorted, "EMAIL") >= 0) {
                    map.put("EMAIL", email);
                }

                if(Arrays.binarySearch(propertyNamesSorted, "AGE") >= 0){
                    map.put("AGE", age);
                }

                if(Arrays.binarySearch(propertyNamesSorted, "STATUS") >= 0){
                    map.put("STATUS", status);
                }

            }

            return map;
        } catch (SQLException e) {
            throw new UserStoreException(e.getMessage(), e);
        } finally {
            DatabaseUtil.closeAllConnections(dbConnection, rs, prepStmt);
        }
    }

2. In this code you can see we are using some external sql query as GET_PROPS_FOR_PROFILE (realmConfig.getUserStoreProperty(JDBCRealmConstants.GET_PROPS_FOR_PROFILE);) so we need to pass it from user-mgt.xml as follows.

       
<UserManager>
    <Realm>
        <Configuration>
		<AddAdmin>true</AddAdmin>
                <AdminRole>admin</AdminRole>
                <AdminUser>
                     <UserName>admin</UserName>
                     <Password>admin</Password>
                </AdminUser>
            <EveryOneRoleName>everyone</EveryOneRoleName> <!-- By default users in this role sees the registry root -->
            <Property name="dataSource">jdbc/WSO2CarbonDB</Property>
            <Property name="MultiTenantRealmConfigBuilder">org.wso2.carbon.user.core.config.multitenancy.SimpleRealmConfigBuilder</Property>
	    <!-- Use the following MultiTenantRealmConfigBuilder with LDAP based UserStoreManagers-->
	    <!--Property name="MultiTenantRealmConfigBuilder">org.wso2.carbon.user.core.config.multitenancy.CommonLDAPRealmConfigBuilder</Property-->
        </Configuration>
	
	<UserStoreManager class="org.wso2.carbon.user.core.jdbc.JDBCUserStoreManager">
	    <Property name="ReadOnly">false</Property>
	    <Property name="dataSource">jdbc/WSO2CarbonDB</Property>
            <Property name="MaxUserNameListLength">100</Property>
            <Property name="Disabled">false</Property>                       
            <Property name="IsEmailUserName">false</Property>
            <Property name="DomainCalculation">default</Property>
            <Property name="PasswordDigest">SHA-256</Property>
            <Property name="StoreSaltedPassword">true</Property>
            <Property name="ReadGroups">true</Property>
	    <Property name="WriteGroups">true</Property>
            <Property name="UserNameUniqueAcrossTenants">false</Property>
            <Property name="PasswordJavaRegEx">^[\S]{5,30}$</Property>
            <Property name="PasswordJavaScriptRegEx">^[\S]{5,30}$</Property>
	    <Property name="UsernameJavaRegEx">^[^~!#$;%^*+={}\\|\\\\&lt;&gt;,\'\"]{3,30}$</Property>
	    <Property name="UsernameJavaScriptRegEx">^[\S]{3,30}$</Property>
	    <Property name="RolenameJavaRegEx">^[^~!#$;%^*+={}\\|\\\\&lt;&gt;,\'\"]{3,30}$</Property>
	    <Property name="RolenameJavaScriptRegEx">^[\S]{3,30}$</Property>
            <Property name="UserRolesCacheEnabled">true</Property>
            <Property name="MaxRoleNameListLength">100</Property>
            <Property name="MaxUserNameListLength">100</Property>            
        </UserStoreManager>

	<UserStoreManager class="org.wso2.carbon.jdbc.sample.SampleJDBCUserStoreManager">
	    <Property name="ReadOnly">false</Property>
	    <Property name="dataSource">jdbc/SampleUserStore</Property>
            <Property name="MaxUserNameListLength">100</Property>
            <Property name="Disabled">false</Property>                       
            <Property name="IsEmailUserName">false</Property>
            <Property name="DomainCalculation">default</Property>
            <Property name="PasswordDigest">SHA-256</Property>
            <Property name="StoreSaltedPassword">true</Property>
            <Property name="ReadGroups">true</Property>
	    <Property name="WriteGroups">true</Property>
            <Property name="UserNameUniqueAcrossTenants">false</Property>
            <Property name="PasswordJavaRegEx">^[\S]{5,30}$</Property>
            <Property name="PasswordJavaScriptRegEx">^[\S]{5,30}$</Property>
	    <Property name="UsernameJavaRegEx">^[^~!#$;%^*+={}\\|\\\\&lt;&gt;,\'\"]{3,30}$</Property>
	    <Property name="UsernameJavaScriptRegEx">^[\S]{3,30}$</Property>
	    <Property name="RolenameJavaRegEx">^[^~!#$;%^*+={}\\|\\\\&lt;&gt;,\'\"]{3,30}$</Property>
	    <Property name="RolenameJavaScriptRegEx">^[\S]{3,30}$</Property>
            <Property name="UserRolesCacheEnabled">true</Property>
            <Property name="MaxRoleNameListLength">100</Property>
            <Property name="MaxUserNameListLength">100</Property>
	    <Property name="DomainName">sample.com</Property>

            <Property name="SelectUserSQL">SELECT * FROM CUSTOMER_DATA WHERE CUSTOMER_NAME=?</Property>
            <Property name="UserFilterSQL">SELECT CUSTOMER_NAME FROM CUSTOMER_DATA WHERE CUSTOMER_NAME LIKE ?  ORDER BY CUSTOMER_ID</Property>
	    <Property name="GetUserPropertiesForProfileSQL">SELECT EMAIL,AGE,STATUS FROM CUSTOMER_DATA WHERE CUSTOMER_NAME=?</Property>
        </UserStoreManager>
	
	<AuthorizationManager
            class="org.wso2.carbon.user.core.authorization.JDBCAuthorizationManager">
            <Property name="AdminRoleManagementPermissions">/permission</Property>
	    <Property name="AuthorizationCacheEnabled">true</Property>
        </AuthorizationManager>
    </Realm>
</UserManager>

3. Same as you did in early post you have to place the mysql-connector-java-5.1.7-bin.jar and Sample_user_store-1.0.jar in to $IS_HOME/repository/component/libs

4. Start the Identity server and Sign in as admin.

5. Go to claim management.
is
Now you can see some Available Claim Dialects here. In the user store manager is using the http://wso2.org/claims so we need to add the new claim mapping on this.

5. Go to http://wso2.org/claims Dialect.
is

6. Add new Claim EMAIL
is1
Here you need to specify the Mapped Attribute (s) as EMAIL because in our custom user store manager check the property name as “EMAIL”,”AGE”,”STATUS”

7. Add new Claim AGE
is

8. Add new Claim STATUS
is1

9. Lets look at the users
is

And go to User profile
is

Now you can see the properties in the database is coming under user profile.

10. Now we need to use those properties and do some authorization. So we can easily move to XACML engine coming with the Identity server to do the needful. Here I’m not going to explain the behavior of the XACML Engine in Identity server but you can follow my early blog posts such as
Authentication and Authorization with WSO2ESB and WSO2IS
XACML Authorization

11. Now I’m going to create the XACML policy buy using the claims that we newly added.
is1

You can see the claim names that we mapped early so you can select those from the UI.
is

Click on the policy name and go to source view of XACML policy.
is

is1

and replace the Deny with Permit and update the policy.
is2

12. Now enable the created policy as follows.

is

13. Go to Tryit and test your policy.

is1

is2

This is very basic example but when its coming to the real world example you can do so many things with this user store extensible facility. as well you can do all the above stuff with the use of web services which are exposed by WSO2 Identity Server.

 
3 Comments

Posted by on April 4, 2013 in Identity Server, java, mysql, wso2

 

Tags: , , ,

How to write Custom JDBC user store manager with WSO2IS 4.1.1 alpha


Download id 4.1.1- alpha here
Lets think about real world business scenario, Simple company has their own database with Customer name and the password. now they need to do some security implementation with WSO2 Identity Server and they cannot duplicate those data as well as cannot change their db schema.

That means we cannot take those users and insert into the WSO2 IS database as well as they cannot change their DB schema to our WSO2 IS database schema. Don’t worry WSO2 Identity server has capability to solve this issue. lets look at the solution with mysql DB.

Steps:
1. Plug the external db as secondary user store.
2. Create some roles in Identity Server and assign the users (coming from external DB).
3. Give the permission to the role.
4. Access the Identity server.

Custom DB Schema

CREATE TABLE CUSTOMER_DATA (
             CUSTOMER_ID INTEGER NOT NULL AUTO_INCREMENT,
             CUSTOMER_NAME VARCHAR(255) NOT NULL,
             PASSWORD VARCHAR(255) NOT NULL,
             PRIMARY KEY (CUSTOMER_ID),
             UNIQUE(CUSTOMER_NAME)
);

1. First we have to write down the Custom JDBCUserStoreManager to compatible with the above schema. So you need to create a class with any name that you preferred and extends JDBCUserStoreManager. I have created class call SampleJDBCUserStoreManager.java (download mvn project here).

In my custom class I’m not going to override all methods in JDBCUserStoreManager because this user store is going to plug as read only mode.

2. Let’s look at the master-datasource.xml
I have created two data source because I’m going to connect to the two user stores as primary and secondary. you bettor read my early blog post call (Multiple User Stores configuration in WSO2 Identity Server) but there is lot of api changes in 4.1.1 alpha.

<datasources-configuration xmlns:svns="http://org.wso2.securevault/configuration">
  
    <providers>
        <provider>org.wso2.carbon.ndatasource.rdbms.RDBMSDataSourceReader</provider>
    </providers>
  
    <datasources>
      
        <datasource>
            <name>WSO2_CARBON_DB</name>
            <description>The datasource used for registry</description>
            <jndiConfig>
                <name>jdbc/WSO2CarbonDB</name>
            </jndiConfig>
	    <definition type="RDBMS">
                <configuration>
                    <url>jdbc:mysql://localhost:3306/WSO2_CARBON</url>
                    <username>root</username>
                    <password>root</password>
                    <driverClassName>com.mysql.jdbc.Driver</driverClassName>
                    <maxActive>50</maxActive>
                    <maxWait>60000</maxWait>
                    <testOnBorrow>true</testOnBorrow>
                    <validationQuery>SELECT 1</validationQuery>
                    <validationInterval>30000</validationInterval>
                </configuration>
            </definition>
           
        </datasource>

	<datasource>
            <name>Sample_DB</name>
            <description>The datasource used for user manager</description>
            <jndiConfig>
                <name>jdbc/SampleUserStore</name>
            </jndiConfig>
	    <definition type="RDBMS">
                <configuration>
                    <url>jdbc:mysql://localhost:3306/custom_users</url>
                    <username>root</username>
                    <password>root</password>
                    <driverClassName>com.mysql.jdbc.Driver</driverClassName>
                    <maxActive>50</maxActive>
                    <maxWait>60000</maxWait>
                    <testOnBorrow>true</testOnBorrow>
                    <validationQuery>SELECT 1</validationQuery>
                    <validationInterval>30000</validationInterval>
                </configuration>
            </definition>
	</datasource>
        
    </datasources>

</datasources-configuration>

3. Go to user-mgt.xml
Here I have define two user store manages and the first definition pic as primary user store manager form the Identity server.
The secondary one is the custom user store manager that i have written early.

 
<UserManager>
    <Realm>
        <Configuration>
		<AddAdmin>true</AddAdmin>
                <AdminRole>admin</AdminRole>
                <AdminUser>
                     <UserName>admin</UserName>
                     <Password>admin</Password>
                </AdminUser>
            <EveryOneRoleName>everyone</EveryOneRoleName> <!-- By default users in this role sees the registry root -->
            <Property name="dataSource">jdbc/WSO2CarbonDB</Property>
            <Property name="MultiTenantRealmConfigBuilder">org.wso2.carbon.user.core.config.multitenancy.SimpleRealmConfigBuilder</Property>
	    <!-- Use the following MultiTenantRealmConfigBuilder with LDAP based UserStoreManagers-->
	    <!--Property name="MultiTenantRealmConfigBuilder">org.wso2.carbon.user.core.config.multitenancy.CommonLDAPRealmConfigBuilder</Property-->
        </Configuration>
	
	<UserStoreManager class="org.wso2.carbon.user.core.jdbc.JDBCUserStoreManager">
	    <Property name="ReadOnly">false</Property>
	    <Property name="dataSource">jdbc/WSO2CarbonDB</Property>
            <Property name="MaxUserNameListLength">100</Property>
            <Property name="Disabled">false</Property>                       
            <Property name="IsEmailUserName">false</Property>
            <Property name="DomainCalculation">default</Property>
            <Property name="PasswordDigest">SHA-256</Property>
            <Property name="StoreSaltedPassword">true</Property>
            <Property name="ReadGroups">true</Property>
	    <Property name="WriteGroups">true</Property>
            <Property name="UserNameUniqueAcrossTenants">false</Property>
            <Property name="PasswordJavaRegEx">^[\S]{5,30}$</Property>
            <Property name="PasswordJavaScriptRegEx">^[\S]{5,30}$</Property>
	    <Property name="UsernameJavaRegEx">^[^~!#$;%^*+={}\\|\\\\&lt;&gt;,\'\"]{3,30}$</Property>
	    <Property name="UsernameJavaScriptRegEx">^[\S]{3,30}$</Property>
	    <Property name="RolenameJavaRegEx">^[^~!#$;%^*+={}\\|\\\\&lt;&gt;,\'\"]{3,30}$</Property>
	    <Property name="RolenameJavaScriptRegEx">^[\S]{3,30}$</Property>
            <Property name="UserRolesCacheEnabled">true</Property>
            <Property name="MaxRoleNameListLength">100</Property>
            <Property name="MaxUserNameListLength">100</Property>            
        </UserStoreManager>

	<UserStoreManager class="org.wso2.carbon.jdbc.sample.SampleJDBCUserStoreManager">
	    <Property name="ReadOnly">false</Property>
	    <Property name="dataSource">jdbc/SampleUserStore</Property>
            <Property name="MaxUserNameListLength">100</Property>
            <Property name="Disabled">false</Property>                       
            <Property name="IsEmailUserName">false</Property>
            <Property name="DomainCalculation">default</Property>
            <Property name="PasswordDigest">SHA-256</Property>
            <Property name="StoreSaltedPassword">true</Property>
            <Property name="ReadGroups">true</Property>
	    <Property name="WriteGroups">true</Property>
            <Property name="UserNameUniqueAcrossTenants">false</Property>
            <Property name="PasswordJavaRegEx">^[\S]{5,30}$</Property>
            <Property name="PasswordJavaScriptRegEx">^[\S]{5,30}$</Property>
	    <Property name="UsernameJavaRegEx">^[^~!#$;%^*+={}\\|\\\\&lt;&gt;,\'\"]{3,30}$</Property>
	    <Property name="UsernameJavaScriptRegEx">^[\S]{3,30}$</Property>
	    <Property name="RolenameJavaRegEx">^[^~!#$;%^*+={}\\|\\\\&lt;&gt;,\'\"]{3,30}$</Property>
	    <Property name="RolenameJavaScriptRegEx">^[\S]{3,30}$</Property>
            <Property name="UserRolesCacheEnabled">true</Property>
            <Property name="MaxRoleNameListLength">100</Property>
            <Property name="MaxUserNameListLength">100</Property>
	    <Property name="DomainName">sample.com</Property>

            <Property name="SelectUserSQL">SELECT * FROM CUSTOMER_DATA WHERE CUSTOMER_NAME=?</Property>
            <Property name="UserFilterSQL">SELECT CUSTOMER_NAME FROM CUSTOMER_DATA WHERE CUSTOMER_NAME LIKE ?  ORDER BY CUSTOMER_ID</Property>
        </UserStoreManager>
	
	<AuthorizationManager
            class="org.wso2.carbon.user.core.authorization.JDBCAuthorizationManager">
            <Property name="AdminRoleManagementPermissions">/permission</Property>
	    <Property name="AuthorizationCacheEnabled">true</Property>
        </AuthorizationManager>
    </Realm>
</UserManager>

You can see I’m passing the custom SQLs as “SelectUserSQL” and “UserFilterSQL”

4. You need to put following jars in to the $IS_HOME/repository/component/libs
mysql-connector-java-5.1.7-bin.jar and Sample_user_store-1.0.jar

5. Now I’m going to start the Identity server and sign in as admin

6. Go to users then you can see the users coming from secondary user store as read only
is

7. Now we need to create the internal role because we cannot create the roles inside of the secondary user store manager due to read only mode.So you can do it as follows.

is1

you have to give the permission to the created role

is2

Now you can assign the users from secondary user store.

is

8. Login to the IS from the secondary users.

is

 
2 Comments

Posted by on April 3, 2013 in Identity Server, java, mysql, wso2

 

Tags: , , ,

Multiple User Stores configuration in WSO2 Identity Server


You know wso2 products are very smart and customizable products. Here I’m going to explain one of another smart features came up with wso2 products. As a example will take WSO2 Identity server as a product.

Lets look at one of simple business scenarios.

1. I need connect multiple user stores.
ex/ different domain users in different databases to one single product.
2. All users can access same wso2 product (IS).

Multiple User Stores

First of all we need to do some configuration in Identity Server. Refer this post for additional information about data base configuration with wso2 products.

You have to create two databases in Mysql as FOO and BAR with database table structure. You can simply do this as follows.
Go to mysql command line and create two databases.

mysql > create database FOO;
mysql > create database BAR;

Go to command line and run the following commands to create the table structure.

 > mysql -u username -p FOO < $IS_HOME/dbscripts/mysql.sql
 > mysql -u username -p BAR < $IS_HOME/dbscripts/mysql.sql

Now we created databases properly.

1. master-datasource.xml – we have to configure three data sources for H2, FOO, BAR databases.

<datasources>      
        <datasource>
            <name>WSO2_CARBON_DB</name>
            <description>The datasource used for registry and user manager</description>
            <jndiConfig>
                <name>jdbc/WSO2CarbonDB</name>
            </jndiConfig>
            <definition type="RDBMS">
                <configuration>
                    <url>jdbc:h2:repository/database/WSO2CARBON_DB;DB_CLOSE_ON_EXIT=FALSE;LOCK_TIMEOUT=60000</url>
                    <username>wso2carbon</username>
                    <password>wso2carbon</password>
                    <driverClassName>org.h2.Driver</driverClassName>
                    <maxActive>50</maxActive>
                    <maxWait>60000</maxWait>
                    <testOnBorrow>true</testOnBorrow>
                    <validationQuery>SELECT 1</validationQuery>
                    <validationInterval>30000</validationInterval>
                </configuration>
            </definition>
        </datasource>

       <datasource>
            <name>WSO2_MySQL_FOO_DB</name>
            <description>The datasource used for user manager bar.com</description>
            <jndiConfig>
                <name>jdbc/WSO2MySqlFooDB</name>
            </jndiConfig>
            <definition type="RDBMS">
                <configuration>
                    <url>jdbc:mysql://localhost:3306/FOO</url>
                    <username>root</username>
                    <password>root</password>
                    <driverClassName>com.mysql.jdbc.Driver</driverClassName>
                    <maxActive>50</maxActive>
                    <maxWait>60000</maxWait>
                    <testOnBorrow>true</testOnBorrow>
                    <validationQuery>SELECT 1</validationQuery>
                    <validationInterval>30000</validationInterval>
                </configuration>
            </definition>
        </datasource>

       <datasource>
            <name>WSO2_MySQL_BAR_DB</name>
            <description>The datasource used for user manager bar.com</description>
            <jndiConfig>
                <name>jdbc/WSO2MySqlBarDB</name>
            </jndiConfig>
            <definition type="RDBMS">
                <configuration>
                    <url>jdbc:mysql://localhost:3306/BAR</url>
                    <username>root</username>
                    <password>root</password>
                    <driverClassName>com.mysql.jdbc.Driver</driverClassName>
                    <maxActive>50</maxActive>
                    <maxWait>60000</maxWait>
                    <testOnBorrow>true</testOnBorrow>
                    <validationQuery>SELECT 1</validationQuery>
                    <validationInterval>30000</validationInterval>
                </configuration>
            </definition>
        </datasource>

2. user-mgt.xml – Have to refer created data sources for specific domains.
The first user store configuration take as primary user store and others are secondary.
In the primary user store we are not going to specify the domain name.

        <UserStoreManager class="org.wso2.carbon.user.core.ldap.ReadWriteLDAPUserStoreManager">
            <Property name="defaultRealmName">WSO2.ORG</Property>
            <Property name="kdcEnabled">false</Property>
            <Property name="ConnectionURL">ldap://localhost:${Ports.EmbeddedLDAP.LDAPServerPort}</Property>
            <Property name="ConnectionName">uid=admin,ou=system</Property>
            <Property name="ConnectionPassword">admin</Property>
            <Property name="passwordHashMethod">SHA</Property>
            <Property name="UserNameListFilter">(objectClass=person)</Property>
            <Property name="UserEntryObjectClass">scimPerson</Property>
            <Property name="UserSearchBase">ou=Users,dc=wso2,dc=org</Property>
            <Property name="UserNameSearchFilter">(&amp;(objectClass=person)(uid=?))</Property>
            <Property name="UserNameAttribute">uid</Property>
            <Property name="PasswordJavaScriptRegEx">^[\\S]{5,30}$</Property>
	    <Property name="ServicePasswordJavaRegEx">^[\\S]{5,30}$</Property>
	    <Property name="ServiceNameJavaRegEx">^[\\S]{2,30}/[\\S]{2,30}$</Property>
            <Property name="UsernameJavaScriptRegEx">^[\\S]{3,30}$</Property>
            <Property name="UsernameJavaRegEx">[a-zA-Z0-9._-|//]{3,30}$</Property>
            <Property name="RolenameJavaScriptRegEx">^[\\S]{3,30}$</Property>
            <Property name="RolenameJavaRegEx">[a-zA-Z0-9._-|//]{3,30}$</Property>
	    <Property name="ReadLDAPGroups">true</Property>
	    <Property name="WriteLDAPGroups">true</Property>
	    <Property name="EmptyRolesAllowed">true</Property>
            <Property name="GroupSearchBase">ou=Groups,dc=wso2,dc=org</Property>
            <Property name="GroupNameListFilter">(objectClass=groupOfNames)</Property>
	    <Property name="GroupEntryObjectClass">groupOfNames</Property>
            <Property name="GroupNameSearchFilter">(&amp;(objectClass=groupOfNames)(cn=?))</Property>
            <Property name="GroupNameAttribute">cn</Property>
            <Property name="MembershipAttribute">member</Property>
            <Property name="UserRolesCacheEnabled">true</Property>
	    <Property name="UserDNPattern">uid={0},ou=Users,dc=wso2,dc=org</Property>
	    <Property name="SCIMEnabled">true</Property>
	    <Property name="maxFailedLoginAttempt">0</Property>
        </UserStoreManager>

        <UserStoreManager class="org.wso2.carbon.user.core.jdbc.JDBCUserStoreManager">
	    <Property name="ReadOnly">false</Property>
            <Property name="MaxUserNameListLength">100</Property>
            <Property name="IsEmailUserName">false</Property>
            <Property name="DomainCalculation">default</Property>
            <Property name="PasswordDigest">SHA-256</Property>
            <Property name="StoreSaltedPassword">true</Property>
            <Property name="UserNameUniqueAcrossTenants">false</Property>
            <Property name="PasswordJavaRegEx">^[\S]{5,30}$</Property>
            <Property name="PasswordJavaScriptRegEx">^[\\S]{5,30}$</Property>
	    <Property name="UsernameJavaRegEx">[a-zA-Z0-9._-|//]{3,30}$</Property>
	    <Property name="UsernameJavaScriptRegEx">^[\\S]{3,30}$</Property>
	    <Property name="RolenameJavaRegEx">[a-zA-Z0-9._-|//]{3,30}$</Property>
	    <Property name="RolenameJavaScriptRegEx">^[\\S]{3,30}$</Property>
            <Property name="UserRolesCacheEnabled">true</Property>
	    <Property name="maxFailedLoginAttempt">0</Property>
            <Property name="dataSource">jdbc/WSO2MySqlFooDB</Property>
            <Property name="DomainName">foo.com</Property>	
        </UserStoreManager>
        
        <UserStoreManager class="org.wso2.carbon.user.core.jdbc.JDBCUserStoreManager">
	    <Property name="ReadOnly">false</Property>
            <Property name="MaxUserNameListLength">100</Property>
            <Property name="IsEmailUserName">false</Property>
            <Property name="DomainCalculation">default</Property>
            <Property name="PasswordDigest">SHA-256</Property>
            <Property name="StoreSaltedPassword">true</Property>
            <Property name="UserNameUniqueAcrossTenants">false</Property>
            <Property name="PasswordJavaRegEx">^[\S]{5,30}$</Property>
            <Property name="PasswordJavaScriptRegEx">^[\\S]{5,30}$</Property>
	    <Property name="UsernameJavaRegEx">[a-zA-Z0-9._-|//]{3,30}$</Property>
	    <Property name="UsernameJavaScriptRegEx">^[\\S]{3,30}$</Property>
	    <Property name="RolenameJavaRegEx">[a-zA-Z0-9._-|//]{3,30}$</Property>
	    <Property name="RolenameJavaScriptRegEx">^[\\S]{3,30}$</Property>
            <Property name="UserRolesCacheEnabled">true</Property>
	    <Property name="maxFailedLoginAttempt">0</Property>
            <Property name="dataSource">jdbc/WSO2MySqlBarDB</Property>
            <Property name="DomainName">bar.com</Property>	
        </UserStoreManager>

Once you done above configurations you can start the Identity server.

3. Log in to the IS (username :admin, password : admin)
Configuration ->Users and Roles
is

Select Users->Add new Users
is

Now you can create the user in different domains.

[username :test password :test123] – it will store at primary user store.
as well as you can create User with specifying the domain. then it will store at specified domain user store.
[username :foo.com/dinuka password:dinuka ]
[username :bar.com/malinda password:malinda]

is

is

once you done this you have to go to Roles and give the login permission to everyone role.
is

Now you can log in to the IS with any user in primary or secondary user store either specifying the domain name or without domain name.

[username :foo.com/dinuka password : dinuka] or
[username :dinuka password : dinuka]
Once you try to log in to the system with specifying the domain, IS will look at the specified domain user store to authenticate the user.
other wise it will go through all the define user stores starting from primary user store.

is

is

 
4 Comments

Posted by on January 11, 2013 in Identity Server, java, mysql, Other, wso2

 

Tags: ,

Database Configuration in WSO2 Carbon Products


Wso2 Carbon is an open source enterprise SOA midldeware platform. There is a facility to engage with different databases such as H2,Apache Derby,MS SQL Server,MySQL,Oracle,OpenEdge,PostgreSQL,IBM DB2 to hold the User Management data,Registry..etc.

Following diagram will help you to identify how we can configure the database in different way
Here I’m using WSO2 Identity Server as Carbon product and explaining two simple samples.
DB Configuration

Before going to discuss about samples we have to understand which configuration files that we need use.

First You have to go to $IS_HOME/repository/conf 
user-mgt.xml – all user store and user permission configurations.
registry.xml – all registry configurations.

Lets go to $IS_HOME/repository/conf/datasources 
master-datasources.xml – this is the common file that we used to define data sources configurations.

Move to above sample 1 there is a single mysql database to store User permission, Registry data and embedded LDAP for User Store (ex/ username, password,..etc ).

1.master-datasources.xml – first you have to define the mysql datasource
Go to mysql and create database call WSO2_MYSQL_CARBON_DB

create database WSO2_MYSQL_CARBON_DB

1.Then place the database name under name tag
2.Change the Jndi Config name jdbc/WSO2MysqlCarbonDB
3.Update url as jdbc:mysql://localhost:3306/WSO2_MYSQL_CARBON_DB
4.Set username and password rootroot123 5.Driver class name com.mysql.jdbc.Driver
5.Define driver class name as com.mysql.jdbc.Driver
6.Now you have to copy the driver into $IS_HOME/repository/components/lib

<datasource>
   <name>WSO2_CARBON_DB</name>
   <description>The datasource used for registry and user manager</description>
   <jndiConfig>
      <name>jdbc/WSO2CarbonDB</name>
   </jndiConfig>
   <definition type="RDBMS">
    <configuration>
      <url>jdbc:h2:repository/database/WSO2CARBON_DB;DB_CLOSE_ON_EXIT=FALSE;LOCK_TIMEOUT=60000</url>
      <username>wso2carbon</username>
      <password>wso2carbon</password>
      <driverClassName>org.h2.Driver</driverClassName>
      <maxActive>50</maxActive>
      <maxWait>60000</maxWait>
      <testOnBorrow>true</testOnBorrow>
      <validationQuery>SELECT 1</validationQuery>
      <validationInterval>30000</validationInterval>
    </configuration>
   </definition>
</datasource>

2.registry.xml
Now you have to refer the created datasource in registry xml.
change datasource name as jdbc/WSO2MysqlCarbonDB

      <dataSource>jdbc/WSO2MysqlCarbonDB</dataSource>  

3.user-mgt.xml
Refer the created data source in here as well. This is for User permission data.

<Configuration>
     <AdminRole>admin</AdminRole>
     <AdminUser>
         <UserName>admin</UserName>
         <Password>admin</Password>
     </AdminUser>
     <EveryOneRoleName>everyone</EveryOneRoleName> <!-- By default users in this role sees the registry root -->
     <Property name="dataSource">jdbc/WSO2MysqlCarbonDB</Property>
     <Property name="MultiTenantRealmConfigBuilder">org.wso2.carbon.user.core.config.multitenancy.CommonLDAPRealmConfigBuilder</Property>
</Configuration>

Now if we look at User Store configuration we don’t need Jdbc connection because this is going to connect with LDAP so its different protocol. to understand follow this configuration.

<UserStoreManager class="org.wso2.carbon.user.core.ldap.ReadWriteLDAPUserStoreManager">
    <Property name="defaultRealmName">WSO2.ORG</Property>
    <Property name="kdcEnabled">false</Property>
    <Property name="ConnectionURL">ldap://localhost:${Ports.EmbeddedLDAP.LDAPServerPort}</Property>
    <Property name="ConnectionName">uid=admin,ou=system</Property>
    <Property name="ConnectionPassword">admin</Property>
    <Property name="passwordHashMethod">SHA</Property>
    <Property name="UserNameListFilter">(objectClass=person)</Property>
    <Property name="UserEntryObjectClass">scimPerson</Property>
    <Property name="UserSearchBase">ou=Users,dc=wso2,dc=org</Property>
    <Property name="UserNameSearchFilter">(&amp;(objectClass=person)(uid=?))</Property>
    <Property name="UserNameAttribute">uid</Property>
    <Property name="PasswordJavaScriptRegEx">^[\\S]{5,30}$</Property>
    <Property name="ServicePasswordJavaRegEx">^[\\S]{5,30}$</Property>
    <Property name="ServiceNameJavaRegEx">^[\\S]{2,30}/[\\S]{2,30}$</Property>
    <Property name="UsernameJavaScriptRegEx">^[\\S]{3,30}$</Property>
    <Property name="UsernameJavaRegEx">[a-zA-Z0-9._-|//]{3,30}$</Property>
    <Property name="RolenameJavaScriptRegEx">^[\\S]{3,30}$</Property>
    <Property name="RolenameJavaRegEx">[a-zA-Z0-9._-|//]{3,30}$</Property>
    <Property name="ReadLDAPGroups">true</Property>
    <Property name="WriteLDAPGroups">true</Property>
    <Property name="EmptyRolesAllowed">true</Property>
    <Property name="GroupSearchBase">ou=Groups,dc=wso2,dc=org</Property>
    <Property name="GroupNameListFilter">(objectClass=groupOfNames)</Property>
    <Property name="GroupEntryObjectClass">groupOfNames</Property>
    <Property name="GroupNameSearchFilter">(&amp;(objectClass=groupOfNames)(cn=?))</Property>
    <Property name="GroupNameAttribute">cn</Property>
    <Property name="MembershipAttribute">member</Property>
    <Property name="UserRolesCacheEnabled">true</Property>
    <Property name="UserDNPattern">uid={0},ou=Users,dc=wso2,dc=org</Property>
    <Property name="SCIMEnabled">true</Property>
    <Property name="maxFailedLoginAttempt">0</Property>
    <Property name="DomainName">domain.com</Property>
</UserStoreManager>

Lets move to Sample 2 you can see there are two databases connect with IS, one for user management and other for registry.
1.Create two data sources in master-datasources.xml as follows

       <datasource>
            <name>WSO2_CARBON_DB</name>
            <description>The datasource used for user permission data</description>
            <jndiConfig>
                <name>jdbc/WSO2CarbonDB</name>
            </jndiConfig>
            <definition type="RDBMS">
                <configuration>
                    <url>jdbc:mysql://localhost:3306/WSO2_MYSQL_CARBON_DB</url>
                    <username>root</username>
                    <password>root123</password>
                    <driverClassName>com.mysql.jdbc.Driver</driverClassName>
                    <maxActive>50</maxActive>
                    <maxWait>60000</maxWait>
                    <testOnBorrow>true</testOnBorrow>
                    <validationQuery>SELECT 1</validationQuery>
                    <validationInterval>30000</validationInterval>
                </configuration>
            </definition>
        </datasource>

        <datasource>
            <name>WSO2_REGISTRY_DB</name>
            <description>The datasource used for registry</description>
            <jndiConfig>
                <name>jdbc/WSO2RegistryDB</name>
            </jndiConfig>
            <definition type="RDBMS">
                <configuration>
                    <url>jdbc:h2:repository/database/WSO2CARBON_DB;DB_CLOSE_ON_EXIT=FALSE;LOCK_TIMEOUT=60000</url>
                    <username>wso2carbon</username>
                    <password>wso2carbon</password>
                    <driverClassName>org.h2.Driver</driverClassName>
                    <maxActive>50</maxActive>
                    <maxWait>60000</maxWait>
                    <testOnBorrow>true</testOnBorrow>
                    <validationQuery>SELECT 1</validationQuery>
                    <validationInterval>30000</validationInterval>
                </configuration>
            </definition>
        </datasource>      

2.registry.xml – refer H2 datasource

    <dataSource>jdbc/WSO2RegistryDB</dataSource>     

3.user-mgt.xml – refer mysql datasource

<Configuration>
    <AdminRole>admin</AdminRole>
    <AdminUser>
      <UserName>admin</UserName>
      <Password>admin</Password>
    </AdminUser>
    <EveryOneRoleName>everyone</EveryOneRoleName> <!-- By default users in this role sees the registry root -->
    <Property name="dataSource">jdbc/WSO2CarbonDB</Property>
    <Property name="MultiTenantRealmConfigBuilder">org.wso2.carbon.user.core.config.multitenancy.CommonLDAPRealmConfigBuilder</Property>
</Configuration>  

This is the way that we can simply configure the different databases with Carbon Products.

 
1 Comment

Posted by on January 8, 2013 in Identity Server, java, mysql, wso2

 

Tags: , , , ,

Restore MySql Backup file by using JAVA


First read my early blog post “Take the MySql DB backup in JAVA” and you can download the source from there


public boolean restoreDatabase(String dbUserName, String dbPassword, String source) {

        String[] executeCmd = new String[]{"mysql", "--user=" + dbUserName, "--password=" + dbPassword, "-e", "source " + source};

        Process runtimeProcess;
        try {
            runtimeProcess = Runtime.getRuntime().exec(executeCmd);
            int processComplete = runtimeProcess.waitFor();

            if (processComplete == 0) {
                log.info("Backup restored successfully with " + source);
                return true;
            } else {
                log.info("Could not restore the backup " + source);
            }
        } catch (Exception ex) {
            log.error(ex, ex.getCause());
        }

        return false;

    }

 
2 Comments

Posted by on September 28, 2012 in java, mysql

 

Tags: , ,

 
%d bloggers like this: