Wednesday, August 24, 2016

WSO2 DSS, Secure Data Service using Basic Auth

Hope you have read this article: Using WSO2 DSS retrieve data from multiple databases in a single Query. Lets use the same data service and secure it using Basic Auth. Then lets try to invoke the secured data service with SOAP UI or from a WSO2 ESB.

Securing the data service
1. If not already created, add a new collection to /_system/config as security, i.e., /_system/config/security
2. Then add this policy file[1] (if you want you can rename the policy file, say policy1.xml) to /_system/config/security/ using Add Resource | Upload content from file | choose file




3. Next you have to add this policy to your data service. Go to data service xml editor view.
Add following to Data Service configuration at the end just before </data> closing tag.

   <policy key="conf:security/policy1.xml"/>
   <enableSec/>


Wait for few seconds and go to Deployed Services page and view the available data services. Now you can see the updated data service is as secured.


When you are testing the data service from the SOAP UI, you need to send the Authorization header.
Go to request level of your SOAP UI project created in the previous article and add Header as follows.

Header Name : Authorization
Value : Basic YWRtaW46YWRtaW4=

We are using admin:admin default credentials here.
YWRtaW46YWRtaW4= is the base64 encoded value of admin:admin
You can calculate the base64 encoded value online [2].

If a WSO2 ESB is invoking the service simply add following configuration in the synapse config
before sending message to the endpoint.

<property xmlns:ns="http://org.apache.synapse/xsd"
 name="Authorization"
 expression="fn:concat('Basic ', base64Encode('username:password'))"
 scope="transport"/>

If you are using jaggery to call DSS endpoints you can send the headers with the request as follows.

var POST_HEADERS = { "Content-Type": "application/json", "Authorization": "Basic YWRtaW46YWRtaW4="};

var resp = put(dssLDAPUserDSURL + "/employee/status", stringify(dataPost), POST_HEADERS);

You can find the sample data service, policy file, SOAP UI project and the relevant MySQL database scripts here [3].

Reference:
[1] https://svn.wso2.org/repos/wso2/people/suhan/BasicAuthSecuredBackendService/UT_policy.xml
[2] https://www.base64encode.org/
[3] https://svn.wso2.org/repos/wso2/people/suhan/BasicAuthSecuredBackendService/


Tuesday, August 23, 2016

Using WSO2 DSS retrieve data from multiple databases in a single Query

I came across an issue of having same table in multiple databases and having to write data multiple times when syncing these data with external systems. Therefore I thought of transferring these duplicated tables into one single common database. Then this common database will to be used by several of our internal systems. However I recently came across an issue that I needed to retrieve data from multiple databases using a single query. Since I'm already using WSO2 DSS, I found a solution to tackle this problem.

Here's how to do it. For the ease of understanding I have divided the steps into two parts.

Part 1 - Configure MySQL Database

Login to your local MySQL db and create two databases.
Then create two tables and insert some test data as follows.

mysql> create database db1;
mysql> create database db2;

mysql> use db1; 
mysql> CREATE TABLE employees ( EmployeeID int(11) NOT NULL AUTO_INCREMENT, FirstName varchar(255) DEFAULT NULL, LastName varchar(255) DEFAULT NULL, Team varchar(255) DEFAULT NULL, PRIMARY KEY (EmployeeID));
mysql> insert into employees (FirstName, LastName, Team) values('Suhan', 'Dharmasuriya', 'InternalIT');
mysql> insert into employees (FirstName, LastName, Team) values('Thilina', 'Perera', 'Finance');
mysql> select * from employees;
+------------+-----------+--------------+------------+
| EmployeeID | FirstName | LastName     | Team       |
+------------+-----------+--------------+------------+
|          1 | Suhan     | Dharmasuriya | InternalIT |
|          2 | Thilina   | Perera       | Finance    |
+------------+-----------+--------------+------------+
2 rows in set (0.00 sec)

mysql> use db2;
mysql> CREATE TABLE engagements ( EngagementID int(11) NOT NULL AUTO_INCREMENT, EmployeeID int(11), PRIMARY KEY (EngagementID));
mysql> insert into engagements (EmployeeID) values(1);
mysql> select * from engagements;
+--------------+------------+
| EngagementID | EmployeeID |
+--------------+------------+
|            1 |          1 |
+--------------+------------+
1 row in set (0.00 sec)

Now lets test the following MySQL query and get results. Here I have used a LEFT OUTER JOIN.
Result will be obtained from the two databases. We will save this query in WSO2 DSS. 

mysql> SELECT A.EmployeeID, A.FirstName, A.LastName, A.Team, B.EngagementID from db1.employees AS A LEFT OUTER JOIN db2.engagements AS B ON A.EmployeeID=B.EmployeeID;
+------------+-----------+--------------+------------+--------------+
| EmployeeID | FirstName | LastName     | Team       | EngagementID |
+------------+-----------+--------------+------------+--------------+
|          1 | Suhan     | Dharmasuriya | InternalIT |            1 |
|          2 | Thilina   | Perera       | Finance    |         NULL |
+------------+-----------+--------------+------------+--------------+
2 rows in set (0.00 sec)

Part 2 - Configure WSO2 DSS

  1. Download WSO2 DSS 3.5.0 from here (http://wso2.com/products/data-services-server/). If you already have the product zip file (wso2dss-3.5.0.zip) continue with the next step.
  2. Unzip the product to a path containing no spaces in the path name. This is your <DSS_HOME>
  3. Download the MySQL connector jar here (http://dev.mysql.com/downloads/connector/j/) and copy it to <DSS_HOME>/repository/components/lib/
  4. Start the WSO2 DSS server.
To start the server, your have to run the script wso2server.bat (on Windows) or
wso2server.sh (on Linux/Solaris) from the <DSS_HOME>/bin folder.
  1. Log in to DSS by using the default credentials (username: admin/ password: admin).

Creating the data service

  1. Create a new data service 'sampleDS'
  2. Home -> Manage -> Services -> Add -> Data Service -> Create
  3. Create a datasource as follows by referring to the above local MySQL database.
  4. Add new Query; query ID: 'getEngagementsPerEmployee', query: above tested query, and press generate response link
  5. No operation is defined in this example, therefore press next
  6. Add new Resource; Resource Path: '/engagements' by selecting Resource Method: 'GET' and selecting Query ID: 'getEngagementsPerEmployee'
  7. Press Finish to create the data service

Refer following screenshots for more information.










Now you can test the datasource as follows.

Open a new tab in your browser and type the following URL: http://localhost:9763/services/sampleDS/engagements

You will get the following response:
<Entries xmlns="http://ws.wso2.org/dataservice">
<Entry>
<EmployeeID>1</EmployeeID>
<FirstName>Suhan</FirstName>
<LastName>Dharmasuriya</LastName>
<Team>InternalIT</Team>
<EngagementID>1</EngagementID>
</Entry>
<Entry>
<EmployeeID>2</EmployeeID>
<FirstName>Thilina</FirstName>
<LastName>Perera</LastName>
<Team>Finance</Team>
<EngagementID xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:nil="true"/>
</Entry>
</Entries>

Refer following Data Service synapse configuration for more information.
<data name="sampleDS" transports="http https local">
   <config enableOData="false" id="datasource">
      <property name="driverClassName">com.mysql.jdbc.Driver</property>
      <property name="url">jdbc:mysql://localhost:3306</property>
      <property name="username">root</property>
      <property name="password">root</property>
   </config>
   <query id="getEngagementsPerEmployee" useConfig="datasource">
      <sql>SELECT A.EmployeeID, A.FirstName, A.LastName, A.Team, B.EngagementID from db1.employees AS A LEFT OUTER JOIN db2.engagements AS B ON A.EmployeeID=B.EmployeeID;</sql>
      <result element="Entries" rowName="Entry">
         <element column="EmployeeID" name="EmployeeID" xsdType="string"/>
         <element column="FirstName" name="FirstName" xsdType="string"/>
         <element column="LastName" name="LastName" xsdType="string"/>
         <element column="Team" name="Team" xsdType="string"/>
         <element column="EngagementID" name="EngagementID" xsdType="string"/>
      </result>
   </query>
   <resource method="GET" path="/engagements">
      <call-query href="getEngagementsPerEmployee"/>
   </resource>
</data>