Month: July 2009

Sub-optimal Pagination with Oracle & Hibernate

There seem to be a bug in Hibernate 3 that results in a sub-optimal query if one attempts to fetch one specific portion of the result set, as is typically the case with pagination.

The best-practice to extract one specific page out of the complete result set is to use the ROWNUM keyword with Oracle. The ROWNUM keyword is used to truncate the size of the result set after N items. Oracle will optimize the usage of ROWNUM which can result in drastical improvement of some queries. The outline of a paginated query looks like following:

SELECT *
FROM (SELECT row_.*, ROWNUM rownum_
FROM (SELECT this_.ID AS id3_0_, this_.VERSION AS version3_0_,
this_.NAME AS name3_0_, this_.TYPE AS type3_0_,
this_.marketstatus AS marketst5_3_0_
FROM customer this_
ORDER BY this_.ID ASC) row_
WHERE ROWNUM <= ?)
WHERE rownum_ > ?

There seem unfortunately that there is a bug in Hibernate, and the query that is generated will be:

SELECT *
FROM (SELECT row_.*, ROWNUM rownum_
FROM (SELECT   this_.ID AS id3_0_, this_.VERSION AS version3_0_,
this_.NAME AS name3_0_, this_.TYPE AS type3_0_,
this_.marketstatus AS marketst5_3_0_
FROM customer this_
ORDER BY this_.ID ASC) row_)
WHERE rownum_ <= ? AND rownum_ > ?

Though they are semantically the same, the second one will not benefit from Oracle’s optimizations.

Here is the corresponding issue I’ve reported. More information can be found about Oracle ROWNUM in AskTom

Threat modeling: tools in practice

We’ve investigated two tools for our threat model. Here is an overview of both tools (from Microsoft) and our experience with them.

Threat Modeling Tool

The first tool supports system modelling with the definition of Entry Points, Trust Levels, Protected Resources, plus some general background information. Data Flows can be authored directory with the tool or imported from Visio. The tool main strength comes however from its Threat Tree modelling features. Threats can be identified and decomposed into a series of step following the same approach as Attack Tree. The tool supports AND and OR semantics when constructing advanced threat tree. E.g.: “Disclose password” can be achieved with either “Brute force password” or “Use default password”. Each step in the threat tree can be rated according to DREAD and mitigation notes can be added.

Microsoft Threat Analysis & Modeling

The second tool supports system modelling – called application decomposition in this case – with the definition of Roles, Data, Components and External dependencies. The traditional data flow are replaced with “application use cases” that can be modelled right form the tool using the predefined components, data and roles. The threat identification and decomposition follows the threat-attack-vulnerability-countermeasures model. The tool comes with existing base of attacks and vulnerability. E.g.:  Attack “SQL injection” is made possible because of “Usage of Dynamic SQL” vulnerability. Threats that have been identified can then be categorized using the CIA (confidentiality, integrity, availability) classification. Threats are not related to attacks or vulnerability but directly to countermeasures. This was a bit awkward to me and I don’t understand exactly the rationale behind this choice. It’s also interesting to note the existence of so-called “Relevancies” that can then be linked to attacks. E.g.: relevancy “Component utilizes HTTP” is linked to attack “Response Splitting”, “Session Hijacking”, and “Repudiation Attack”.
The tool’s ambitions are much bigger and go beyond a simple documentation of the system security. The tool contains analytics and visualization features. Analytics features aim at assessing the system automatically (e.g.: “Data access control matrix”), whereas visualization features help to understand the system from different view points (e.g.: “call flow”, “data flow”, etc.). These features make sense only if the complete system was modelled with the tool, especially the application use cases.
The tool is actively maintained and significant efforts have been invested in it in to promote the Threat Modelling practice.

Conclusion

Threat modelling is not easy. Several approaches can be used and it can be a time consuming activity. We didn’t model the system enough in detail to leverage the analytics and visualization features of the Microsoft Threat Analysis & Modeling tool. We can then not assess the relevance of such an analysis. Speaking generally about the process, our main problem was to decide to which level of details we wanted to go, and then how to organize our findings in a meaningful way.

The basic notion of threats, attack, vulnerability and countermeasures can be vague or overlapping and they must first be defined in the scope of the analysis in a way to ensure a consistency of the threat model. Sample questions to ask prior to start the analysis are:

  •  What will be the granularity of the threats to identify? E.g.: “disclose customer information” vs. “disclose customer document”, “disclose customer number”, “disclose credit card number”. Knowing to which extent the threat analysis will be performed must be defined in advanced and will drive the complete process.
  • What will be the granularity and the nature of the attacks to identify? An attack can be a concrete (e.g.: “password brute force attack”) or abstract (e.g.: “denial of service attack”). In the first case, an attack is a concrete technique that can be applied to exploit vulnerability. In the later case, an attack is an abstraction of a set of existing techniques that are related. Such abstract attack could be refined into a list of concrete attacks (e.g.: “SYN flood”, “XML bomb”, etc.).
  • How to capture generic attack/threats? This again related to the question of granularity of the analysis. Consider for instance the attack “Submit HTTP form twice”. Because it can be applied on all web pages, the list of threats will be an exhaustive listing of the application features: “Place wrong order”, “Rate the item more than once”, etc. Such attack can be capture into generic threats such as “Abuse web application” or “Disrupt system”.  Similarly, all the “Denial of service attacks” will lead to generic threat “Degrade service availability”.

The threat model must no be an exhaustive, useless document. Therefore, the analyst must find a balance between generic attack and threat (and the corresponding generic hardening best practice) and more detailed attacks and threats related to the specificities of the system under study.