Java and the Timezones

Java 2 Go! - Thu, 2009-04-02 13:55
by Fábio Souza Timezone, when using Java, can be something tricky. You can get surprised when you change your OS timezone (something common to countries with DST) and it doesn't take any effect on...

This is a summary only. Please, visit the blog for full content and more.
Categories: Development

Web Component Developer Exam (SCWCD) for JEE 5

Java 2 Go! - Thu, 2009-04-02 13:39
by Wellington Carvalho I hope my feedback can help those who want to prepare for The Web Component Developer exam (SCWCD) for JEE 5. I will use the same structure of an older post that Fabio Souza...

This is a summary only. Please, visit the blog for full content and more.
Categories: Development

SCJP 6 Upgrade Exam (310-056)

Java 2 Go! - Thu, 2009-04-02 13:39
by Fábio Souza Guys and girls, I guess the best way to start this post is with my appologies. I really wanted to post it earlier, but you all know how things are =P. It has been almost three months...

This is a summary only. Please, visit the blog for full content and more.
Categories: Development

SCJP 5 and 6

Java 2 Go! - Thu, 2009-04-02 13:39
by Fábio Souza OK, I know that is a little late to talk about SCJP 5 but I will give my feedback about the exam. This will help people that feels unprepared to take SCJP 6 (that was my case) and the...

This is a summary only. Please, visit the blog for full content and more.
Categories: Development

OracleAS Java Object Cache

Java 2 Go! - Thu, 2009-04-02 13:39
by Fábio Souza Before I start, I would like to say that many texts and observations were took from the Oracle® Containers for J2EE Services Guide 10g (, Chapter 7. What is it? The Java...

This is a summary only. Please, visit the blog for full content and more.
Categories: Development

Diagnostics beyond OracleAS Control Console

Java 2 Go! - Thu, 2009-04-02 13:39
by Eduardo Rodrigues One of the many companies recently acquired by Oracle is a small one called "Auptyma", whose founder and former CEO, Mr. Virag Saksena, was previously Director of the CRM...

This is a summary only. Please, visit the blog for full content and more.
Categories: Development

There's a cook in JDev's development team indeed

Java 2 Go! - Thu, 2009-04-02 13:37
Sometime ago I was surprised with a peculiar "tip of the day" which simply mentioned a traditional angel cake recipe. Well, today I got the confirmation. There's certainly a cook amongst JDev's...

This is a summary only. Please, visit the blog for full content and more.
Categories: Development

Windows XP SP3 crashing JDeveloper

Java 2 Go! - Thu, 2009-04-02 13:37
by Eduardo Rodrigues Attention!!! Today, my Windows XP automatically upgraded to SP3 and, after rebooting, my JDev began to crash during its initialization, generating a JVM core dump in...

This is a summary only. Please, visit the blog for full content and more.
Categories: Development

A comprehensive XML processing benchmark

Java 2 Go! - Thu, 2009-04-02 13:37
by Eduardo Rodrigues h4 { margin-bottom: 0; font-style: italic; font-size: 110%; text-decoration: none; }Introduction I think I've already mentioned it here but, anyway, I'm currently leading a very...

This is a summary only. Please, visit the blog for full content and more.
Categories: Development

Fixed blog's appearance on IE6

Java 2 Go! - Thu, 2009-04-02 13:37
Great news folks! We've just fixed our blog's template so it'll be displayed 100% correctly on Internet Explorer 6 browser. Now, we expect this blog to render identically on both Internet Explorer...

This is a summary only. Please, visit the blog for full content and more.
Categories: Development

Is there a cook in JDev's team?

Java 2 Go! - Thu, 2009-04-02 13:37
This week I found something at least very curious when I launched my JDeveloper as I do almost every morning. This was the "Tip of the Day" it showed me: Well, I don't know what this...

This is a summary only. Please, visit the blog for full content and more.
Categories: Development

Book Review: Processing XML documents with Oracle JDeveloper 11g

Pawel Barut - Sun, 2009-03-29 05:55
Written by Paweł Barut
This time I would like to recommend a book Processing XML documents with Oracle JDeveloper 11g published by As I have some experience with XML processing inside Oracle DB (XMLDB) I was lacking the knowledge of XML manipulation in Java, especially using Oracle JDeveloper. This book is rather not for beginners - you should have basic knowledge of what is XML, at least theoretically. From the book you can learn how to manage XML Schemas in JDeveloper, then write XML documents based on those schema and finally validate them in JDeveloper or write Java code to validate it automatically. Chapters that I value much are those describing how to generate PDF documents. There are two methods described - one using Apache FOP API , and Oracle XML Publisher. As I was not generating PDF's this way so far, I found those 2 chapters very interesting. There is also chapter on generatin Excel by transformation of XML using Apache HSSF API .

Book is very practical. If you want to start with subjects described above then this book is for you. Of course it does not mean that you do not have to study documentation. However, it will be easier for you to make first steps. Beside describing Oracle JDeveloper, author also shows how to install and use Oracle Berkley XML DB to store, query and update XML Documents - using command line and XML DB API.

I especially recommend eBook version. All URLs in eBook version are active, so linking to web pages are very simple. Also coping sample code is easy. This book contains many practical and usefull tips on XML processing and tools as well. So if those XML aspects are in your interest, then it's definitelly good to invest in this book.

Keep reading,

Related Articles on Paweł Barut blog:
    Categories: Development

    ActiveRecord Oracle enhanced adapter also on JRuby and Ruby 1.9

    Raimonds Simanovskis - Sun, 2009-03-22 17:00

    So far if you wanted to use Ruby on Rails on Oracle database you needed to use different adapters depending on the Ruby platform that you wanted to use. If you used original MRI (Matz Ruby interpreter) 1.8.6 then hopefully you were using Oracle enhanced adapter. But if you wanted to use JRuby then you needed to use JDBC adapter that is maintained by JRuby team (and which sometimes might work differently than Oracle enhanced adapter). And if you wanted to use new Ruby 1.9.1 then you were out of luck as no adapter supported it.

    Therefore I wanted to announce great news that ActiveRecord Oracle enhanced adapter 1.2.0 is released and it supports all three major Ruby platforms!

    • Use Oracle enhanced adapter on MRI 1.8.6 with ruby-oci8 1.0.x library or gem
    • Use Oracle enhanced adapter on JRuby (so far tested with 1.1.6) with JDBC Oracle driver
    • Use Oracle enhanced adapter on Ruby/YARV 1.9.1 with ruby-oci8 2.0 library or gem

    This provides you with much more flexibility to develop on one Ruby platform but deploy on another and on all three platforms you can use the same additional functionality that Oracle enhanced adapter provides on top of standard ActiveRecord functionality.

    And during testing of Oracle enhanced adapter on all platforms additional milestone was achieved – Oracle enhanced adapter passes 100% ActiveRecord unit tests! But to be honest I need to tell that I needed to patch quite many unit tests for Oracle specifics as not all SQL that runs on MySQL is also valid on Oracle. I published my patched branch of ActiveRecord unit tests at my GitHub fork of Rails – you can clone the repository and verify by yourself.

    So please try out new version of Oracle enhanced adapter on any Ruby platform:

    gem install activerecord-oracle_enahnced-adapter

    If you have any questions please use discussion group or post comments here. In nearest future I will also add more instructions how to install Oracle enhanced adapter on JRuby and Ruby 1.9.1 at GitHub wiki page.

    Categories: Development

    Core ADF 11: Designing the Page and Taskflow Structure

    JHeadstart - Fri, 2009-03-20 00:53

    JDeveloper's tagline is "productivity with choice". In previous releases this choice merely applied to the various technologies that could be used in each of the ADF layers. With release 11 this has changed. When choosing the "Fusion" technology stack within ADF (ADF Faces, ADF Task Flows and ADF Business Components), there are many new and powerful concepts and techniques available that allow you to build the required functionality in different ways. Concepts like (un)bounded taskflows, page fragments, (dynamic) regions, page templates, declarative components, declarative transaction support, XML Menu Model, ADF Faces Layout Components and ADF Libraries open up a wide range of new possibilities to build the view and controller layers.

    These new possibilities imply that new design decisions must be made on how to build the required functionality. This is not a trivial task. Many of the concepts relate to each other, and choosing how to use one concept can have impact on the usage of other concepts. I admit that's pretty vague, but in this new "Core ADF 11" series, we (the JHeadstart team) will try to make this more concrete. In this series we will share with you the experiences, issues, challenges, design considerations, design decisions and best practices that we faced/encountered/developed when building JHeadstart release 11.

    Note that we will assume that you have a basic understanding of the new ADF 11 concepts. If this is not the case, we recommend you take the ADF 11 course from Oracle University, or if you prefer self-study, take a look at the ADF Learning Center.

    In this first post, we will start at the beginning: you want to build your first "serious" ADF 11 page, and you immediately need to choose: do I create a page or a page fragment, do I base the page (fragment) on a template, and do I put the page in an unbounded or bounded task flow.

    Page Templates
    This one is easy: we recommend to always use a page template. Design the template whatever way you want but make sure you include all the UI elements that need to be displayed on each and every page. Typical content of a page template include a branding image/application logo, global actions like Home, Help, Log Out, Preferences, the menu of the application (either a tree menu, or various levels of navigation panes like tabs, buttons or lists) and a footer area with for example a copyright message. Use page template attributes to pass in properties that might differ between pages like the page title, and to conditionally show/hide parts of the page template for specific pages. In a later post, we will go into more details on designing page templates.
    Note that you can also dynamically change the page template associated with a page at runtime. Instead of hardcoding the page template path, you can use an EL expression that binds to a look and feel bean that contains the current page template path.

    Bounded or Unbounded Taskflows
    Each ADF 11 web application has one unbounded taskflow, and can have one or more bounded task flows. So, the choice is not so much about using bounded or unbounded task flows, but about putting a JSF page inside the unbounded taskflow, or inside a bounded task flow.
    When building transactional applications, bounded task flows have major advantages over unbounded task flows. For a start, bounded task flows support additional features that can be very useful when building transactional applications:

    • Save for Later
    • Declarative Transaction Support
    • Exception Handling
    • Own Memory Scope
    • Back button handling

    In addition, bounded task flows allow you to benefit from concepts well-known in the world of object oriented programming and component-based development. Using bounded task flows:

    • you can create truly reusable user interface components through page fragments and regions;
    • with a well-defined service contract; through taskflow parameters you can configure the behavior of the taskflow;
    • that are loosely coupled; to use a task flow, you only need to know about the available task flows parameters. The internal flow, structure of user interface components and data structures can be changed without any impact on the pages or other taskflows that are using/calling the taskflow.

    In short, well-designed bounded task flows have many advantages over the unbounded task flow. We recommend to only use the unbounded task flow for introductionary pages like the home page or a UI shell page when using regions (see below). All application uses cases should be implemented using a bounded task flow. In a later post in this series, we will discuss in more detail how to design your task flows for maximum reusability.

    Stand-alone Pages or Page Fragments with (Dynamic) Regions
    For your page design, you basically have three choices

    • Create stand-alone .jspx pages
    • Create .jsff page fragments within a region, and create a shell page for each region that contains the af:region tag.
    • Create .jsff page fragments within a region, and create one common UI shell .jspx page, that uses a dynamic region to display the requested region. This is the "One Page Application" model, where the UI shell page is the only .jspx page in the application, and a menu displayed in the shell page is used to set the current region displayed on the page.

    Some considerations and implications to take into account for each choice:
    Stand-alone pages:

    • Stand-alone pages are easiest to build and understand, it is like building your app the "10.1.3 way".
    • Stand-alone pages offer little reuse, they cannot be used inside regions
    • When you are building a menu-driven application, and each menu entry starts a bounded taskflow with stand-alone pages, there is no easy way to abandon the current task flow. When the user wants to leave the current task flow and clicks on another menu entry, nothing will happen! This is because the control flow case to start the taskflow associated with the other menu item is defined in the unbounded task flow, which is not reachable once a bounded taskflow is started. The easy solution is to not use bounded task flows at all. You will lose all the advantages of bounded task flows, but when simplicity is more important to you then reusability, this is a valid choice. We discuss a more sophisticated solution to the "abandon taskflow with stand-alone pages issue" in this post of the ADF 11 Core series.
    • When you are using the XMLMenuModel to create your menu, you can associate one focusViewId with each menu item. If you navigate to the page with this view id, the associated menu item will be highlighted automatically. However, if you then navigate to a detail page from the focusViewId page, you loose the menu item highlighting. In this post, we discuss how you can extend the XMLMenuModel to associate multiple pages with one menu item

    Static regions with shell page:

    • With this design, you leverage the reuse options of bounded task flows with page fragments
    • You do not run into the XMLMenuModel multi-page problem, since each menu item is associated with the shell page which remains the same while navigating between the page fragments within the ADF region embedded in the shell page
    • You do not run into the "abandon task flow" issue that occurs with stand-alone pages, since the shell pages are all located in the unbounded task flow.
    • If you navigate away to another shell page, you need to be aware that ADF will auto-execute some logic to clean up the abandoned task flow of the region in the previous shell page. The actual clean-up logic executed depends on the transaction settings of the task flow. For example, if the task flow started a new transaction, a rollback will be performed. Note that when using shared data controls, the rollback might have a performance impact since already executed queries in other task flows will need to be re-executed as well. In a later post, we will discuss taskflow transaction management in more detail.

    One-page application:
    With the previous design option, a shell page is created for each ADF region. Typically, all these shell pages will be very thin and very similar: they are based on the same page template that renders the menu and other common page components, the only difference is the name of the ADF region that is embedded in the shell page. In a one page application, we create one UI Shell page and use a dynamic region to display the correct region. Here are the implications:

    • Performance increases because partial page rendering can be used to replace the content of the dynamic region, the UI shell page does not need to be reloaded. In our experience, the performance difference is quite significant. However, according to the documentation there is a feature called Partial Page Navigation which would allow for a similar performance when using separate shell pages or stand-alone pages. I did not get this feature to work though.
    • The one-page application raises a problem with XMLMenuModel, opposite to the stand-alone pages design: there is only one page used by all menu items. This can be solved by creating a subclass of XMLMenuModel that highlights the correct menu item based on the currently selected region as set in the dynamic region bean. We will discuss this technique in more detail in a future post.
    • When using a dynamic region, the parameters for all region task flows can be defined against the dynamic task flow binding in the page definition of the UI shell page. This quickly becomes ugly and unmanageable: one long list of parameters for all task flows in the application. A better approach is to use the parameter map property of the dynamic task flow binding, and reference the parameter map of the current region through the dynamic region bean. This technique is discussed in more detail in this post.

    A variation of this implementation is the use of dynamic tabs rather than a dynamic region. This enables multi-tasking for the end user, see this post for more details on the implementation of dynamic tabs.  

    While JDeveloper makes it easy to later on convert stand-alone pages to fragments, and unbounded task flows to bounded task flows, it is better to make these design decisions upfront. The choices you make have impact on the usage of XMLMenuModel, menu-driven navigation in general, reusability, transaction management and performance.
    We recommend to use bounded task flows with page fragments to maximize reusability. While traditional transactional applications are mostly menu-driven, composite, task-oriented user interfaces are likely to become mainstream in the future. When you start building your ADF 11 app today using (dynamic) regions accessible from a menu, you can reuse the same task flows when building more task-oriented user interfaces using the WebCenter and Human Workflow capabilities planned for JDeveloper 11 release 2.

    Categories: Development

    Multiplying texts and null effect

    Pawel Barut - Sun, 2009-03-15 16:39
    Written by Paweł Barut
    This time short notice on side effect of implicit type conversion ans short expression evaluation. Let me show this on example - try this statement:
    SQL> select 'a'*'b' from dual;
    select 'a'*'b' from dual
    ERROR at line 1:
    ORA-01722: invalid number
    As expected we get error. It is due to fact that multiplexing operator (*) is expecting numbers on both sides, an implicit conversion takes place. However this statement:
    SQL> select ''*'b' from dual;

    does not give error. How is it possible? In oracle empty string is in fact an null. null multiplied by any number gives null, so due to optimization oracle decided to evaluate this expression to null.
    As you know also 0 (zero) multiplied by any number gives zero. So let's test it:
    SQL> select '0'*'b' from dual;
    select '0'*'b' from dual
    ERROR at line 1:
    ORA-01722: invalid number
    In that case Oracle did not optimized evaluation, and decided to make full calculation. I do not know the reason for different behaviour but is repeatable (at least on instances I've access to).
    My recommendation: always make sure that you provide data in proper data-type. Make explicit data conversion using to_number, to_date or to_char functions.

    Keep reading,

    Related Articles on Paweł Barut blog:
    Categories: Development

    JHeadstart 11.1.1 Technical Preview Now Available

    JHeadstart - Thu, 2009-03-05 22:15

    Oracle JHeadstart 11.1.1 Technical Preview is now available for download. Customers who own a JHeadstart supplement option license can download it from the Consulting Supplement Option portal.

    An evaluation version of JHeadstart 11.1.1 is not yet available. We plan to make an evaluation version available in the fall of calendar year 2009.

    In addition to many small enhancements, the following features have been added in JHeadstart 11.1.1:

    • New Application Level in Application Definition Editor: In previous versions, a project typically contained multiple JHeadstart application definition files, that were edited and generated separately. In release 11, these application definition files have been renamed to service definition files, and can be edited and generated all together in the application definition editor. The editor has a new top level application node that shows all services. The application node contains a number of properties that were previously found at the service level, but had to be the same accross all services anyway.

    • Generation of (un)Bounded Task Flows: JHeadstart now uses the new ADF 11 controller facilities that extend the JSF controller functionality. For each top-level group a bounded task flow is generated. Through the new metadata element Group Parameters you can specify specific custom taskflow parameters. By default, the generated task flows already contain a number of parameters to maximize reuse of the task flows. For example, you can set parameters to query or set a specific row as the current row or to start the flow in "create mode" (useful in wizard-style layouts).

    • Ability to Generate Page Fragments and (Dynamic) Regions: Through the new properties (Default) Group Usage and (Default) Group Region Access you can specify how groups are generated. You can continue to generate the groups as stand-alone pages like in version 10.1.3. However, to increase the reusability of your groups, it is recommended to generate them as page fragments within a region. You then have the option to generate one common UI shell page, with all group regions displayed through a dynamic region ("One-page application"), or to generate separate group-specific UI Shell pages.

    • Enhanced Menu Generation: The generated menu now uses the new ADF 11 XML Menu Model, which greatly increases the flexibility of the menu generation, both in terms of menu structure as well as menu layout. The menu structure is specified in an XML file, and is
      separated from the layout of the menu. Using the ADF Faces NavigationPane or Tree elements included in the page templates that ship with JHeadstart, you can configure how the menu is displayed. The menu structure is now integrated accross services as well, allowing you to create a true application-level menu. Through the new application-level property Content First-Level Menu Tabs with allowable values "Base groups current service", "All services", and "All base groups" you can determine which levels of the menu are shown with group pages, and whether menu items of different services are "merged" into one menu level.
      If the declarative properties are not sufficient, you can use custom Velocity templates for the generation of the XML menu model files to easily generate menu structures that introduce new intermediate menu levels, effectively "hiding" the underlying structure of services and groups.

    • Support for Page Templates: JHeadstart ships with sophisticated sample page templates. You can use these default templates, or create your own based on one of the sample templates. Using the new Page Template and Region Template properties you can specify which template is used by the generated page (fragments). You can even make the page templates dynamic, JHeadstart will then generate a drop down list to switch page templates which allows you to change the page template used by a page at runtime. This might be useful in prototyping situations where you you want to evaluate different page designs with end users.

    • Enhanced Support for Deeplinking: Through a new item display value groupLink and associated new property Link Group Name, you can now declaratively set up taskflow calls between groups in any service. Using the new Item Parameters element, you can specify parameters that should be passed into the taskflow of the called group. For example, in the employees group, you can deeplink to the Jobs group, showing the job details of the current employee, by passing the JobId of the current employee as parameter. You can choose whether you want to display the called group in a popup window, or in the base window replacing the calling group. Using group taskflow parameters, that are referenced by the Insert/Update/Delete allowed properties you can easily show the Jobs form page in read-only mode, when deep-linked, and in editable mode when called through the menu.

    • Easy Reuse of Groups using a Group Region: In version 10.1.3, group regions were introduced to provide more control over the layout of detail groups that are displayed on the same page as the master group. In version 11 we have significantly extended the functionality of this concept. By checking the new checkbox property Include as ADF Region, you can select any group from any service to be displayed on the page of the group owning the group region. In other words, the group region is now the key concept to reuse a group across your application. You can reuse the same group in many pages by including it as a group region. Through the new child element Group Region Parameters, you can specify parameters that should be passed into the taskflow of the group. For example, using these new concepts it becomes trivial to have one group "Employees" that is used to generate a top-level page with just employees, and reuse that same group as child group under the Departments group to generate a master-detail page with department and its employees. By using group query bind parameters property, that reference taskflow input parameters, you can implement the "master-detail" synchronization. By using additional task flow parameters, you can hide the "DepartmentId" item, the search area, the Save and cancel buttons in the employees group when used as child of the Departments group.

    • Support for Model-based LOV's: ADF Business Components 11 has extensive support for list of values. Using JHeadstart you can choose to use these new model-based LOV's, or the existing web-tier JHeadstart LOV's. When running the "New Jheadstart Service Definition" wizard you can choose which LOV style you want to use. When choosing model-based LOV's, JHeadstart will auto-generate View Accessors and List-of-Values definitions in the View Objects, based on the view links that exist between the view objects. You can choose whether the View Accessors are based on separate auto-generated read-only View Objects or on the existing entity-based view objects. When choosing read-only View Objects, you can choose to create the view accessors based on a shared application module instance, which allows you to reuse the LOV queries across user sessions, which is useful for static reference tables.

    • LOV Groups Reusable Accross Services: It is now possible to create an LOV item that uses an LOV group that resides in a different service.

    • Ability to generate skin switcherThrough a new application-level property, you can generate a drop down list to switch between all skins registered in trinidad-config.xml at runtime.

    • Enhanced Template Editing: The templates tab of the JHeadstart Application Definition Editor is now context-sensitive. You will only see the templates that apply to the node selected in the tree navigator. Furthermore, you can now create and modify custom templates within the application definition editor. On the Templates tab, you can change the file name of the default template, and when you tab out the modified file name field, a dialog pops up that asks for confirmation to create a custom template based on the default template. When you confirm this action, the custom template will be created, and you can edit it directly in the application definition editor, in a new area at the bottom of the templates tab.

    • JHeadstart Forms2ADF Generator: This utility allows you to reuse Oracle Forms .fmb files to create ADF web applications. The utility creates ADF Business Components based on the blocks, LOV's and Record Groups defined in the form. It also creates a JHeadstart Application Definition file which allows you to generate fully functional web pages based on the form definition. The PL/SQL logic is extracted and displayed as documentation nodes into the Application Definition. The Application Definition Editor includes an option to move the PL/SQL logic to the database. This utility is still lacking some functionality and has not yet proven itself in a real customer project. We invite you to test the JFG and report your findings to us. See the chapter "JHeadstart Forms2ADF Generator' in the JHeadstart Developer's Guide, section "Troubleshooting" for more information on how to send us your feedback. This utility replaces the JHeadstart Designer-Forms Migrator which is no longer available in this release.

    For a complete list of all existing features, use this link.

    Categories: Development

    I Am But a Humble Filing Clerk

    Kenneth Downs - Sun, 2009-03-01 18:08

    This week we are returning to the series on Philosophy, and we will nail down the role of data and the database in any application that requires such things.

    This is the Database Programmer blog, for anybody who wants practical advice on database use.

    There are links to other essays at the bottom of this post.

    This blog has two tables of contents, the Topical Table of Contents and the list of Database Skills.

    Review of The Absolute

    In the first essay in this series, The Quest For the Absolute, I offered the opinion that all programmers by nature seek absolutes to simplify and inform the development effort. Taking a page from the ancient Greek philosopher Aristotle, I suggested that the best absolute was the quest for the virtuous program, which is to say a program that served its purpose well.

    A program that serves its purpose well is one that meets the needs of the check signer, the end-user, and the programmer. The check signer needs some strategic goal to be met, the end-user must be productive, and the programmer must make a living. If a program achieves all of these, it is an ideal virtuous program, and has satisfied the absolute requirements that are true of all programs.

    Considering the Decision Maker

    Normally we think of a decision maker as some important person who has the power to choose your product or services, or to give her money to your competitor. She makes her decision based on how well she can judge who will meet her strategic needs.

    Although the decision maker will have vastly different needs in different situations, and is usually thinking at a high level, she has at least one need that is universal: the simple need to keep and use records. She needs a filing system. All of her stated goals will assume that you both know this unstated goal is down there at the foundation of the entire proposed system.

    We programmers often forget this simple fact because computers have been around long enough that we do not remember that in their original forms it was impossible to mistake that computers were just electronic filing systems. Way back when I was a kid the day came when phone bills started arriving with an "IBM Card" slipped into them. You returned the card with your check -- they were moving their files into the electronic age. Then came electronic tickets on airlines -- nothing more than a big filing system. The modern web sites we visit to buy tickets are nothing but an interface to what remains a filing system at its heart.

    The Virtuous Programmer

    So if we go back to the idea of "virtue" as the Greeks thought of it, which means serving your function well, a virtuous programmer will remember always that he is but humble filing clerk. This is not his entire purpose, but it is the beginning of all other purposes and the foundation that the higher purposes are built upon.

    Not Just Relational

    This principle is general to all programming. An email server is a program that must receive and store email for later retrieval. What good is an email server that cannot store anything? What good is a camera without its memory card? What good is a mobile phone without its contacts list? What good is a image editing program if it cannot read and write files?

    So all programs exist to process data, and the business application programmer knows that in his context this means we are really making giant sexy record-keeping systems. We are the guys that color-code the filing cabinets.

    Does Not Mean Relational Is Required

    This idea, that we are filing clerks, does not automatically mean we must pick relational databases for the persistence layer -- the question of what filing system to use is a completely different question.


    If we begin with the idea that the ideal program meets the needs of decision maker, end-user, and programmer, and if we consider first the needs of the decision maker, then we begin with the universal strategic need to keep good records. The ideal programmer knows this need is at the bottom of all other needs, and remembers always that we are but humble filing clerks.

    Related Essays

    This blog has two tables of contents, the Topical Table of Contents and the list of Database Skills.

    Other philosophy essays are:

    Categories: Development

    Download Oracle Application Express 3.2

    Oracle Apex Notebook - Fri, 2009-02-27 19:05
    The new APEX release 3.2 is finally available for download. This new version covers essentially the Oracle Forms to APEX converter, but also has some extras regarding security.Download Oracle Application Express 3.2Oracle Application Express 3.2 Release NotesApplication Express Documentation and Tutorials
    Categories: Development

    A Comprehensive Database Security Model

    Kenneth Downs - Sat, 2009-02-14 13:05

    This week I am taking a bit of a departure. Normally I write about things I have already done, but this week I want to speculate a bit on a security model I am thinking of coding up. Basically I have been asking myself how to create a security model for database apps that never requires elevated privileges for code, but still allows for hosts sharing multiple applications, full table security including row level and column level security, and structural immunity to SQL injection.

    The Functional Requirements

    Let's consider a developer who will be hosting multiple database applications on a server, sometimes instances of the same application for different customers. The applications themselves will have different needs, but they all boil down to this:

    • Some applications will allow surfers to join the site and create accounts for themselves, while others will be private sites where an administrator must make user accounts.
    • Some applications will not contain sensitive data, and so the site owner wants to send forgotten passwords in email -- which means the passwords must be stored in plaintext. Other site owners will need heightened security that disallows storing of passwords in plaintext.
    • In both cases, administrators must of course be able to manage accounts themselves.
    • The system should be structurally immune to SQL injection.
    • It must be possible to have users with the same user id ("Sheilia", "John", etc.) on multiple applications who are actually totally different people.
    • The application code must never need to run at an elevated privelege level for any reason -- not even to create accounts on public sites where users can join up and conduct transactions.
    • It must be possible for the site owners or their agents to directly connect to the database at very least for querying and possibly to do database writes without going through our application.
    • Users with accounts on one app must never be able to sign on to another app on the same server.

    These requirements represent the most flexible possible combination of demands that I have so far seen in real life. The question is, can they be met while still providing security? The model I'd like to speculate on today says yes. Informed Paranoia Versus Frightened Ignorance

    Even the most naive programmer knows that the internet is not a safe place, but all too often a lot of security advice you find is based on frightened ignorance and takes the form, "never do x, you don't know what might happen." If we are to create a strong security model, we have to do better than this.

    Much better is to strive to be like a strong system architect, whose approach is based on informed paranoia. This hypothetical architect knows everybody is out to compromise his system, but he seeks a thorough knowledge of the inner workings of his tools so that he can engineer the vulnerabilities out as much as possible. He is not looking to write rules for the programmer that say "never do this", he is rather looking to make it impossible for the user or programmer to compromise the system.

    Two Examples

    Let us consider a server hosting two applications, which are called "social" and "finance".

    The "social" application is a social networking site with minimal security needs. Most important is that the site owners want members of the general public to sign up, and they want to be able to email forgotten passwords (and we can't talk them out of it) -- so we have to store passwords in plaintext.

    The "finance" application is a private site used by employees of a corporation around the world. The general public is absolutely not welcome. To make matters worse however, the corporation's IT department demands to be able to directly connect to the database and write to the database without going through the web app. This means the server will have an open port to the database. Sure it will be protected with SSL and passwords, but we must make sure that only users of "finance" can connect, and only to their own application.

    Dispensing With Single Sign-On

    There are two ways to handle connections to a database. One model is to give users real database accounts, the other is to use a single account to sign on to the database. Prior to the web coming along, there were proponents of both models in the client/server world, but amongst web developers the single sign-on method is so prevalent that I often wonder if they know there is any other way to do it.

    Nevertheless, we must dispense with the single sign-on method at the start, regardless of how many people think that Moses carved it on the third tablet, because it just has too many problems:

    • Single Sign-on is the primary architectural flaw that makes SQL injection possible. As we will see later, using real database accounts makes your site (almost) completely immune to SQL injection.
    • Single Sign-on requires a connection at the maximum privilege level that any system user might have, where the code then decides what it will let a particular user do. This is a complete violation of the requirement that code always run at the lowest possible privilege level.
    • Single Sign-on totally prevents the requirement that authorized agents be allowed to connect to the database and directly read and write values.

    So single sign-on just won't work with the requirements listed. This leads us to creating real accounts on the database server.

    Real Accounts and Basic Security

    When you use a real database account, your code connects to the database using the username and password provided by the user. Anything he is allowed to do your code will be allowed to do, and anything he is not allowed to do will throw and error if your code tries to do it.

    This approach meets quite a few of our requirements nicely. A site owner's IT department can connect with the same accounts they use on the web interface -- they have the same privileges in both cases. Also, there is no need to ever have application code elevate its privilege level during normal operations, since no regular users should ever be doing that. This still leaves the issue of how to create accounts, but we will see that below.

    A programmer who thinks of security in terms of what code can run will have a very hard time wrapping his head around using real database accounts for public users. The trick to understanding this approach is to forget about code for a minute and to think about tables. The basic fact of database application security is that all security resolves to table permissions. In other words, our security model is all about who can read or write to what tables, it is not about who can run which program.

    If we grant public users real database accounts, and they connect with those accounts, the security must be handled within the database itself, and it comes down to:

    • Defining "groups" as collections of users who share permissions at the table level.
    • Deciding which groups are allowed select, insert, update, and delete privileges on which tables.
    • Granting and revoking those privileges on the server itself when the database is built.
    • At very least row-level security will be required, wherein a user can only see and manipulate certain rows in a table. This is how you keep users from using SQL Injection to mess with each other's order history or member profiles.
    • Column security is also very nice to finish off the picture, but we will not be talking about that today as it does not play into the requirements.

    Now we can spend a moment and see why this approach eliminates most SQL Injection vulnerabilities. We will imagine a table of important information called SUPERSECRETS. If somebody could slip in a SQL injection exploit and wipe out this table we'd all go to jail, so we absolutely cannot allow this. Naturally, most users would have no privileges on this table -- even though they are directly connected to the database they cannot even see the table exists, let alone delete from it. So if our hypothetical black hat somehow slips in ";delete from supersecrets" and our code fails to trap for it, nothing happens. They have no privlege on that table. On the other side of things, consider the user who is privileged to delete from that table. If this user slips in a ";delete from supersecrets" he is only going to the trouble with SQL Injection to do something he is perfectly welcome to do anyway through the user interface. So much for SQL injection.

    To repeat a point made above: row-level security is a must. If you grant members of a social site global UPDATE privileges on the PROFILES table, and you fail to prevent a SQL Injection, all hell could break loose. Much better is the ability to limit the user to seeing only his own row in the PROFILE table, so that once again you have created a structural immunity to SQL injection.

    Anonymous Access

    Many public sites allow users to see all kinds of information when they are not logged on. The most obvious example would be an eCommerce site that needs read access to the ITEMS table, among others. Some type of anonymous access must be allowed by our hypothetical framework.

    For our two examples, the "social" site might allow limited viewing of member profiles, while the "finance" application must show absolutely nothing to the general public.

    If we want a general solution that fits both cases, we opt for a deny-by-default model and allow each application to optionally have an anonymous account.

    First we consider deny-by-default. This means simply that our databases are always built so that no group has any permissions on any tables. The programmer of the "social" site now has to grant certain permissions to the anonymous account, while the programmer of the "finance" application does nothing - he already has a secure system.

    But still the "finance" site is not quite so simple. An anonymous user account with no privileges can still log in, and that should make any informed paranoid architect nervous. We should extend the deny-by-default philosophy so the framework will not create an anonymous account unless requested. This way the programmer of the "finance" application still basically does nothing, while the programmer of the "social" must flip a flag to create the anonymous account.

    Virtualizing Users

    If we are having real database accounts, there is one small detail that has to be addressed. If the "social" site has a user "johnsmith" and the finance application has a user of the same name, but they are totally different people, we have to let both accounts exist but be totally separate.

    The answer here is to alias the accounts. The database server would actually have accounts "finance_johnsmith" and "social_johnsmith". Our login process would simply take the username provided and append the code in front of it when authenticating on the server. 'nuf said on that.

    Allowing Public Users To Join

    The "social" site allows anybody to join up and create an account. This means that somehow the web application must be able to create accounts on the database server. Yet it must do this without allowing the web code to elevate its privileges, and while preventing the disaster that would ensue if a user on the "social" site somehow got himself an account on the "finance" site.

    Believe it or not, this is the easy part! Here is how it works for the "social" site:

    • Create a table of users. The primary key is the user_id which prevents duplication.
    • For the social site, there is a column called PASSWORD that stores the password in plaintext.
    • Allow the anonymous account to INSERT into this table! (Remember though that deny-by-default means that so far this account has no other privileges).
    • Put an INSERT trigger on the table that automatically creates an aliased user account, so that "johnsmith" becomes "social_johnsmith". The trigger also sets the password.
    • A DELETE trigger on the table would delete users if the row is deleted.
    • An UPDATE trigger on the table would update the password if the user UPDATES the table.
    • Row level security is an absolute must. Users must be able to SELECT and UPDATE table, but only their own row. If your database server or framework cannot support row-level security, it's all out the window.

    This gives us a system that almost gets us where we need to be: the general public can create acounts, the web application does not need to elevate its privileges, users can set and change their passwords, and no user can see or set anything for any other user. However, this leaves the issue of password recovery.

    In order to recover passwords and email them to members of the "social" site, it is tempting to think that the anonymous account must be able to somehow read the users table, but that is no good because then we have a structural flaw where a successful SQL injection would expose user accounts. However, this also turns out to be easy. There are two options:

    • Write a stored procedure that the anonymous user is free to execute, which does not return a password but actually emails it directly from within the database server. This requires your database server be able to send emails. (Postgres can, and I assume SQL Server can, and I don't really know about mySql).
    • Create a table for password requests, allow inserts to it but nothing else. A trigger sends the email. In this approach you can track email recovery requests.

    For the "finance" application we cannot allow any of this to happen, so again we go to the deny-by-default idea. All of the behaviors above will not happen unless the programmer sets a flag to turn them on when the database is built.

    This does leave the detail of how users of the "finance" application will reset their passwords. For details on how a secure app can still allow password resets, see my posting of Sept 7 2008 Secure Password Resets.

    One More Detail on Public Users

    We still have one more detail to handle for public users. Presumably a user, having joined up, has more privileges than the anonymous account. So the web application must be able to join them into a group without elevating its privileges. The solution here is the same as for creating the account: there will be a table that the anonymous user can make inserts into (but nothing else), and a trigger will join the user to whatever group is named.

    Except for one more detail. We cannot let the user join whatever group they want, only the special group for members. This requirement can be met by defining the idea of a "freejoin" group and also a "solo" group. If the anonymous user inserts into a user-group table, and the requested group is flagged as allowing anybody to join, the trigger will allow it, but for any other group the trigger will reject the insert. The "solo" idea is similar, it means that if a user is in the "members" group, and that group is a "solo" group, they may not join any other groups. This further jails in members of the general public.

    Almost Done: User Administration

    In the last two sections we saw the idea of a table of users and a cross-reference of users to groups. This turns out to solve another issue we will have: letting administrators manage groups. If we define a group called "user_administrators" and give them total power on these tables, and also give them CRUD screens for them, then we have a user administrator system. This works for both the "social" and the "finance" application.

    The triggers on the table have to be slightly different for the two cases, but that is a small exercise to code them up accordingly.

    Cross-Database Access

    Believe it or not, the system outlined above has met all of our requirements except one. So far we have a system that never requires the web server to have any elevated priveleges within the database, allows members of the public to join some sites while barring them from others, is structurally immune from SQL injection, allows different people on different sites to have the same user id, and allows administrators of both sites to directly manage accounts. Moreover, we can handle both plaintext passwords and more serious reset-only situations.

    This leaves only one very thorny issue: cross-database access. The specific database server I use most is PostgreSQL, and this server has a problem (for this scenario) anyway, which is that out-of-the-box, a database account can connect to any database. This does not mean the account has any priveleges on the database, but we very seriously do not want this to happen at all. If a member of the "social" site can connect to the "finance" app, we have a potential vulnerability even if he has zero privileges in that database. We would be much happier if he could not connect at all.

    In Postgres there is a solution to this, but I've grown to not like it. In Postgres you can specify that a user can only connect to a database if they are in a group that has the same name as the database. This is easy to set up, but it requires changing the default configuration of Postgres. However, for the sheer challenge of it I'd like to work out how to do it without requiring that change. So far I'm still puzzling this out. I'd also like to know that the approach would work at very least on MS SQL Server and mySql.


    Most of what is in this week's essay is not that radical to any informed database veteran. But to web programmers who were unfortunate enough to grow up in the world of relational-databases-must-die nonsense, it is probably hard or impossible to imagine a system where users are connecting with real database accounts. The ironic thing is that the approached described here is far more secure than any single sign-on system, but it requires the programmer to shift thinking away from action-based code-centric models to what is really going on: table-based privileges. Once that hurdle is past, the rest of it comes easy.

    Categories: Development

    Oracle Forms convertion to Apex is here... almost!

    Oracle Apex Notebook - Wed, 2009-02-11 18:29
    Finnaly fresh news... the demo server was upgraded to Application Express You can't download it yet, but you can test it online. Very soon we'll have a production release available.You can read all about Oracle Application Express Release 3.2 new features details here.Additional info on Joel Kallman's post about the new release.
    Categories: Development


    Subscribe to Oracle FAQ aggregator - Development