OAuth

If you use social media (e.g. facebook, twitter) it's likely that you've used OAuth. OAuth works also for devices, e.g. my TV uses OAuth to let me access my Youtube account.

Usages:

• Delegated authorization e.g. a (web) application (the client / resource consumer) want to access a user data  in a resource provider (e.g. google calendar or facebook photos/friends), the application rely on user permission (authorisation) using an authorization server (e.g. facebook, google)  to get access to user's data.
• OpenID, which is build on top of OAuth protocol, provides single sign on / (federated) delegated authentication.

Advantages:

• To avoid too many password accounts for different applications that will increase the phishing risk, ear drop risk and revocation problem (you need to cancel/change all the password accounts in all applications)
• To avoid sharing password to resource providers, only the security provider (e.g. google) registers your password
• To standardize different (delegated) authorization methods from different vendors. At this moment in practice there are still minor differences between different vendor implementations, but the distances between them are getting smaller and generic APIs (e.g. Scribe) that adapt to different vendors are available.

Best practice

• the auth token is sent with http header instead of URL GET parameters: less obvious, to avoid the token being cached/logged as url entries
• use SSL to avoid the attackers steal the token
• limit the token lifespan (if you revoke the token it will not last too long)
• use random salt to prevent CSRF attack.
• manage user experience: tell user what happens "e.g. This application needs your approval to use this resource. We will redirect you to an authorisation server (e.g. google) so that you can give permission."

Protocol:  the web server application flow (a.k.a. the 3 legged flow)

This is the strongest OAuth protocol, to avoid the client browser (e.g. malicious javascript) to catch the access token by using intermediately autho code (which later is being exchanged for an access token via server to server call that safer than browser to server call).

First the client application has to register to the autho server / oauth security provider (e.g. google) to establish clientID & password.

The flow:

1. The application makes oauth request to the the authorisation server with parameters: clientid (from the client application registration), resorce scope (e.g. google calendar), redirect url (where the user will be returned after they approve the access) and other parameters such as state (random salt against CSRF) and access_type ('offline' for the 3 legged flow). The user has to login and then gives permission/authorisation to the application via authorisation server web interface. The authorisation server then return the user (& send the intermediate code) to the redirect url.

2. The client application then can exchange the intermediate code for an access token.

3. The application then can access the user data in the resource provider using access token. The resource provider will validate the access token. The validation process is not defined in the standard, but for example some OAuth implementation provide API in the OAuth server where the resource provider can exchange the token with user's id & scope information and then compare this result with the resource provider's own ACL.

4. Optional: in some scenarios the access token will be expired, the application need to obtain a new access token using refresh token.

Other protocols:         

• client flow (a.k.a. the 2 legged flow) : the client received the access token directly (instead of have to exchange the intermediate code.) This approach is less secure (since the access token is exposed to  the client browser, so for example a malicious javascript can steal it) but good enough for many non critical applications (e.g. invitations for photos viewer).
• password flow: use password to be exchanged with an token. This approach is less secure (the application/resource provider will know your password) but can be used for login to trusted applications inside a company (e.g. webmail, CRM).
•  client credential flow: use the client credential from the client registration. This typically is used for internal applications or trusted cloud integration e.g. salesforce CRM to your company.
•  devices flow: for mobile devices, smartphones, TV.

Links to tools/libraries:

• Google playground to try/understand OAuth protocol and its http messages: https://developers.google.com/oauthplayground/
• Google API lib:  https://developers.google.com/accounts/docs/OAuth2
• Scribe (a single oauth client framework for different APIs such as google, twitter, linkedin, facebook)   https://github.com/fernandezpablo85/scribe-java
• OpenConext: a complete OAuth API (authorization server, resource provider, consumer client) https://github.com/OpenConextApps/apis#apis-
• Oauth provider & consumer using Spring http://spring-security-oauth.codehaus.org/tutorial.html
• List of OAuth libs: http://oauth.net/code/
• OAuth consumer example to connect to the Dutch Surfnet https://wiki.surfnetlabs.nl/display/surfconextdev/OAuth+example+in+Java+%281%29
• Demo OAuth for devices (e.g. TV) http://oauth-device-demo.appspot.com/

OSB

Oracle OSB has functionality to access http header, so it's possible to use OAuth/OpenID with OSB web services for delegated authorization/SSO.

Recommended reading:

How to Secure Your OAuth Implementation
http://software-security.sans.org/blog/2011/03/07/oauth-authorization-attacks-secure-implementation

Source: Steve's blogs http://soa-java.blogspot.com/

Any comments are welcome :)

---

References:

Getting Started with OAuth 2.0

Programming Social Applications:

REST web service

Why REST (Representational state transfer) web service?

• simpler to implement & maintain than WSDL contract-based SOAP web services (e.g. less namespace headache)
• use http method appropriately for CRUD operations (e.g. GET method for retrieving data) thus better semantic compared with SOAP (that use POST method for everything) and match better with web cache infrastructure (thus improving performance & scalability)
• performance reason: REST webservice is more lightweight than SOAP web services, so it runs faster
• stateless thus easier to implement (e.g. avoid problems with distributed cache), less performance/scalability risk
• the directory structure of URI path is intuitive & self documented
• its payload is not limited to SOAP XML but can vary from XML, JSON, XHTML.
• easy interface, easy to use by service consumers so REST gets more popular / fast penetration among internet users (e.g. for Open Data movement).

However REST has also several disadvantages compared with SOAP such as:

• missing self documentation feature of WSDL
• missing (security) standards such as in the SOAP web service community (WS-Security and other WS-* standards.
• missing input/output validation using WSDL, an important  security feature 

Nevertheless for many applications, its advantages are outweigh the disadvantages, because of this many companies expose their services using REST, such as Google, Facebook, Amazon.

Request examples

Retrieving a resource:

Http request:

GET /products/10 HTTP/1.1

Response:

HTTP/1.1 200 OK

<products>

 <product id="10">

  <name>sock</name>

 </product>

...

</products>

Another request example with query parameters

GET /orders?minprice=10&minsize=5 HTTP/1.1

Creating a resource:

POST /orders

<order>

<company>asockbuyer</company>

...

</order>

Modifying a resource:

PUT /HRdept/employees/77

<employee>

<name>Allamaraju</name>

<note>favourite author</note>

...

</employee>

Deleting a resource:

DELETE /orders/13

How to implement a REST provider in Java

Basically a REST webservice is just a servlet. First you detect which web method (e.g. GET, POST) sent by the client to decide with operation your webservice need to perform (e.g. a GET method means a query/read operation). Then you extract (& tokenize) the query parameters from the URL (e.g. employeeid) and feed them to the backend (e.g. request to a SOAP web service, or SQL query to a database). Then transform the response from the backend (e.g. to XML if your REST response is XML) and pass it to the output stream of your servlet.

Using JAX-RS API the implementation is even easier using annotations, for example:

@GET

@Path("products/{id}")

@Produces("application/xml")

public StreamingOutput getProduct(@PathParam("id") int id) {

  final Product  aproduct = AtomicProductDBService.get(id);

  return new StreamingOutput() {

      public void write(OutputStream outputStream)  throws IOException, WebApplicationException {

         produceOutputStream(outputStream, aproduct);
     }
  }

}

Here you specify the REST operation using @GET, specify the output format (XML) using @Produces, specify the URI path using @Path and query parameter (id) using @PathParam.

Implementation references:

• Java Servlet in WebLogic http://docs.oracle.com/cd/E17904_01/web.1111/e13734/rest.htm#WSADV572
• JAX-RS, read RESTful Java with Jax-RS by Burke.
• Java CXF framework, read Developing Web Services with Apache CXF and Axis2 by Tong
• Oracle OSB (using http transport to extract the query parameters and map these parameters to the service call out to the backend web services), read Oracle Service Bus 11g Development Cookbook by Schmutz et.al.

REST web service Security

In principle to protect REST web services you use the same security methods as other web applications:

TLS Basic authentication

GET /employee HTTP/1.1

Host: www.mycompany.org

Authorization: Basic theBase64EncodingOfLogin:Password

TLS digest authentication

GET /employee HTTP/1.1

Host: www.mycompany.org

Authorization: Digest username="employeeapp.001", realm="Employee app", nonce="anonce",

uri="/salarymanag", response="computedresponse",

cnonce="anothernonce", nc=00000001, qop="auth"

The basic idea is to hide the authentication id & password from the attackers. The response is computed using hash of the combination of userid, realm, password, request method, URI and the nonce. The nonces are used to prevent replay attack.

Protect the query parameters with signature

GET /salarymanagement?employeeid=...&signature=... HTTP/1.1

Host: www.mycompany.org

Protect the query parameters with encryption

GET /salarymanagement?encryptedqueryparams HTTP/1.1

Host: www.mycompany.org

Others web application security mechanism applicable for REST for example:

• Client certificate (2-way) authentication (https) & SSL Encryption: see the guidelines of your specific web server.
• OAuth
• SSO (e.g. Shibolleth, Kerberos)

Best practices & guidelines

• http://techblog.appnexus.com/2012/on-restful-api-standards-just-be-cool-11-rules-for-practical-api-development-part-1-of-2/
• http://www.xml.com/pub/a/2004/08/11/rest.html
• http://rest.elkstein.org/2008/02/rest-design-guidelines.html

Source: Steve's blogs http://soa-java.blogspot.com/

Any comments are welcome :)

---

References:

Developing Web Services with Apache CXF and Axis2 by Tong

Oracle Service Bus 11g Development Cookbook by Schmutz et.al.

RESTful Web Services Cookbook by Allamaraju

RESTful Java with Jax-RS by Burke

http://www.ibm.com/developerworks/webservices/library/ws-restful/

SQL Profiling/Optimization in Oracle

To tune your SQL you need to profile/measure your sql performance (time, how many reads) and inspect the

execution plan generated by optimizer (e.g. to detect full table scan that can be avoided by creating index). Start with measuring the baseline and then try to modify your table/sql (e.g. creating index, give optimizer hints) and measure again.

There are several ways to measure your sql performace in Oracle:

1. The simplest way: click "auto trace" or "explain plan" in the SQL worksheet in  JDeveloper/SQLDeveloper:

To use the autotrace you need to have permission to the dictionary view V$mystat

2. In SQLplus you can use explain plan:

EXPLAIN PLAN FOR select e.employee_id,e.last_name,j.job_title FROM employees e, jobs j WHERE salary < 4000 and j.job_id = e.job_id;

Explained.

SQL>  SELECT * FROM TABLE (DBMS_XPLAN.display);

PLAN_TABLE_OUTPUT

| Id  | Operation            | Name | Rows  | Bytes | Cost (%CPU)| Time     |

---------------------------------------------------------------------------------

|   0 | SELECT STATEMENT          |            |    98 |  4508 |     6  (17)| 00:00:01 |

|   1 |  MERGE JOIN               |            |    98 |  4508 |     6  (17)| 00:00:01 |

|   2 |   TABLE ACCESS BY INDEX ROWID| JOBS    |    19 |   475 |     2   (0)| 00:00:01 |

|   3 |    INDEX FULL SCAN        | JOB_ID_PK |    19 |   |     1   (0)| 00:00:01 

|*  4 |   SORT JOIN               |            |    98 |  2058 |     4  (25)| 00:00:01 |

|*  5 |    TABLE ACCESS FULL      | EMPLOYEES |    98 |  2058 |     3   (0)| 00:00:01 |

Predicate Information (identified by operation id):

---------------------------------------------------

   4 - access("J"."JOB_ID"="E"."JOB_ID")

       filter("J"."JOB_ID"="E"."JOB_ID")

   5 - filter("SALARY"<4000>

19 rows selected.

3. In SQLplus you can use autotrace so Oracle will display the explain plan or statistics everytime you perform a query.


To display the plan: SET AUTOTRACE TRACEONLY EXPLAIN or SET AUTOTRACE ON EXPLAIN;
To display the statistics: SET AUTOTRACE TRACEONLY STATISTICS or SET AUTOTRACE ON  STATISTICS;

Or display both: SET AUTOTRACE TRACEONLY STATISTICS EXPLAIN;

Interesting statistics information are included:

• recursive calls: how much works to construct query plan
• consistent gets / logical reads: number of logical read operation when Oracle constructing query plan and execute it)
• physical reads: number of disk read operations when Oracle can't find the data in the cache.

e.g.

Statistics

----------------------------------------------------------

       1097  recursive calls

        0  db block gets

      282  consistent gets

       24  physical reads

        0  redo size

       2434  bytes sent via SQL*Net to client

      464  bytes received via SQL*Net from client

        6  SQL*Net roundtrips to/from client

       13  sorts (memory)

        0  sorts (disk)

If you run the same query again, the performance is better due to caching:

Statistics

----------------------------------------------------------

       10  recursive calls

        0  db block gets

       17  consistent gets

        0  physical reads

        0  redo size

       2434  bytes sent via SQL*Net to client

      464  bytes received via SQL*Net from client

        6  SQL*Net roundtrips to/from client

        1  sorts (memory)

        0  sorts (disk)

       64  rows processed

To get accurate measure over your query you might need to flush the cache (execution plan & data) before you perform the sql again:

ALTER SYSTEM FLUSH SHARED_POOL;

ALTER SYSTEM FLUSH BUFFER_CACHE;

As an experiment we can see how big the infuence of indexing to the query performance. For example try to hide an index from the optimizer:

ALTER INDEX hr.job_id_pk INVISIBLE

or using hint:
SELECT /*+ NO_INDEX (j job_id_pk) */  e.employee_id, e.last_name, j.job_title FROM employees e, jobs j WHERE salary < 4000 and j.job_id = e.job_id;

The performance will be worse due to table full scan:

Execution Plan

----------------------------------------------------------

Plan hash value: 1300016118

--------------------------------------------------------------------------------

| Id  | Operation    | Name      | Rows  | Bytes | Cost (%CPU)| Time     |

--------------------------------------------------------------------------------

|   0 | SELECT STATEMENT   |        |    98 |  4508 |     7     (15)| 00:00:01 |

|*  1 |  HASH JOIN         |         |    98 |  4508 |     7     (15)| 00:00:01 |

|   2 |   TABLE ACCESS FULL| JOBS      |    19 |   475 |     3   (0)| 00:00:01 |

|*  3 |   TABLE ACCESS FULL| EMPLOYEES |    98 |  2058 |     3   (0)| 00:00:01 |

--------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

   4 - access("J"."JOB_ID"="E"."JOB_ID")

       filter("J"."JOB_ID"="E"."JOB_ID")

   5 - filter("SALARY"<4000>

19 rows selected.

Statistics

----------------------------------------------------------

       1240  recursive calls

        2  db block gets

      317  consistent gets

       38  physical reads

        0  redo size

       2434  bytes sent via SQL*Net to client

      464  bytes received via SQL*Net from client

        6  SQL*Net roundtrips to/from client

       12  sorts (memory)

        0  sorts (disk)

       64  rows processed

4. You can use DBMS_SYSTEM.set_sql_trace_in_session to tell Oracle to generate *.trc trace files and then use tkprof utility to summary the result. Personally I find this method unhandy except when I need the trace files.

Tips:

1. When using autotrace, Oracle might complain that you don't have PLUSTRACE role privelege.
Solution:
connect sys as sysdba, run @$ORACLE_HOME/sqlplus/admin/plustrce.sql to perform several grants. Then Grant PLUSTRACE to the user you used. Connect again as a normal user (e.g. hr) create PLAN_TABLE in the schema you use by using  @$ORACLE_HOME/rdbms/admin/utlxplan

2. When your query produces big result and you're interested only to the profiling statistics, use TRACEONLY so that Oracle will not display the query result.
e.g.
SET AUTOTRACE TRACEONLY STATISTICS
SET AUTOTRACE TRACEONLY EXPLAIN

3. You can set the statistics level e.g. ALTER SESSION SET statistics_level=ALL;

Source: Steve's blogs http://soa-java.blogspot.com/

Any comments are welcome :)

---

References:

Beginning Oracle Database 11g Administration by Iggy Fernandez

Oracle Database 11g Release 2 Performance Tuning Tips & Techniques by Niemiec

http://www.dba-oracle.com/t_OracleAutotrace.htm  by Burleson Consulting

Database: Constraint check vs Trigger check

Usage examples

• consistency check (e.g. if the employee address in HRM_apps table is changed the address in the Mailing_apps table should also be changed). It's important to maintain consistency if you have redundancy due to denormalization (in sake of read performance.)
• to avoid business error (e.g. compare the execution price with the historical average price if the stock execution price makes sense)
• recompute the summary table which is used to increase online performance
• security check

Why business check in the database instead of application

In most cases I believe that the business logic should be in the application business layer instead of in the database layer, but there are reasons why people often implement business logics in the database instead of the application:

• reusable and consistency across different applications if the schema is used by multiple applications
• hidden from application (external) programmers (in sake of secrecy and complexity) e.g. stock_evaluation_formula
• performance reason (computing the formula in the database will reduce the network traffic between application and databases, the formula then just send the result to the application, e.g. the datamining/statistics summary computation)

Tips

• It's important that the business logic in the database (e.g. stored procedure, user functions, constraints, triggers) are documented for application developers.
• Use constraint check & triggers sparingly since both of them can lead you to maintenance nightmare.
• Avoid to build your own check if you can use the standard feature e.g. primary key and unique constraints for entity integrity, foreign key constraint for referential integrity.
• Try to simplify the check formula for performance & maintanability.

Constraint check vs Trigger check

• use constraint check to constraint the domain i.e. about your data
• trigger is about how to handle data (e.g. cascading changes, rollback if the business constraint is violated e.g. the merchandised is out of order)

Constraint check example (Oracle):

ALTER TABLE employees ADD CONSTRAINT constraint_salarycheck CHECK (salary<1000>

Test: INSERT INTO employees (employee_id,naam,salary) VALUES (hrseq.nextval,'mr boss',10000);--not permitted

Trigger check example (Oracle):

CREATE OR REPLACE TRIGGER tr_salaryupdatecheck BEFORE UPDATE OF salary ON employees FOR EACH ROW

 BEGIN

  IF :new.salary < :old.salary THEN

   RAISE_APPLICATION_ERROR(-20003,'salary lower');

  END IF;

 END;

Test: UPDATE employees SET salary=100 WHERE name = 'mr boss';

Disable/enable:

ALTER TRIGGER tr_salaryupdatecheck DISABLE;

ALTER TRIGGER tr_salaryupdatecheck ENABLE;

Source: Steve's blogs http://soa-java.blogspot.com/

Any comments are welcome :)

---

References:
Oracle Database 11g & MySQL 5.6 Developer Handbook

PL/SQL tips: use %TYPE

Use tablename.columnname%TYPE in your plsql, so when the table column type is changed you don't have to change the types in plsql.

Example (Oracle PL/SQL):

DECLARE

  CURSOR cursorsalary IS SELECT employee_name,salary FROM employees WHERE salary>100;

  name employees.employee_name%TYPE;

  sal employees.salary%TYPE;

BEGIN

 OPEN cursorsalary;

 FOR arow IN cursorsalary LOOP

  FETCH cursorsalary INTO name,sal;

   DBMS_OUTPUT.PUT_LINE(TO_CHAR(name)||' has salary:'|| TO_CHAR(sal));

 END LOOP;

 CLOSE cursorsalary;

END

Source: Steve's blogs http://soa-java.blogspot.com/

Any comments are welcome :)

---

References:

Oracle PL/SQL Programming

Challenges with Indonesian & Chinese names from a software developer point a view

A lot of Indonesians have only 1 name (e.g. Soekarno, Suharto), while in western system a name commonly consist of (at least) first name & surname. Many administration software don't take this into account, the software/database don't let either the first name nor the surname to be empty. So this situation creates extra complexity for software developers.

Another problem is about the nobility title (e.g. Raden Soekarno), the title Raden is  comparable with Sir in English, so Raden should be in the title field on the GUI form / database field. Many people don't recognize this, so Raden will end up as the first name and Soekarno as the surname. Strangely further, people will infer that the first name Raden as his calling name, and people start to call him "Hi Raden" (this actually happened to a friend of mine here). I guess this complexity happens also to other non-western nobility title. (I know an African who has quite a long name due to multiple nobility titles).

Another well-known problem is over the order in Chinese names. The surname precede the first name, so for example the surname of Li Peng is Li instead of Peng. Many administrative operator and automation software make mistake by inferring that Peng is the surname.

Despite that the application/database nowadays supports also unicode encoding, in practice we still need to romanize the Chinese names (and other non latin character names such as the Russian Cyric). The probleem is that there is no standard for the Romanization. For example the Chinese family name of my mother can be translated as Guo or Kuo or even in Indonesia as Kwik or Kwiek while their semantic are the same. So the solution is the semantic web maybe?

Comparison of different database models for big data

A comparison of different database models for big data, including its scalability & CAP properties:
http://blog.softlayer.com/2012/breaking-down-big-data-database-models/