I frequently see people doing things the hard way for another reason, and again it relates to the idea that we should strive for openness and database independence at all costs. The developers wish to avoid using closed, proprietary database features—even those as simple as stored procedures or sequences—because doing so will lock them into a database system.
Well, let me put forth the idea that the instant you develop a read/write application, you are already somewhat locked in. You will find subtle (and sometimes not so subtle) differences between the databases as soon as you start running queries and modifications. For example, in one database you might find that your SELECT COUNT(*) FROM T deadlocks with a simple update of two rows. In Oracle, you’ll find that the SELECT COUNT(*) never blocks on a writer of the data being counted.
You’ve seen the case where a business rule appears to get enforced on one database, due to side effects of the database’s locking model, and does not get enforced in another database. You’ll find that, given the same exact transaction mix, reports come out with different answers in different databases, all because of fundamental implementation differences.
You will find that it is a very rare application that can simply be picked up and moved from one database to another. Differences in the way the SQL is interpreted (for instance, the NULL=NULL example) and processed will always be there.
On one project, the developers were building a web-based product using Visual Basic, ActiveX controls, the IIS server, and the Oracle database. I was told that the development folks had expressed concern that since the business logic had been written in PL/SQL, the product had become database dependent, and was asked, “How can we correct this?”
I was a little taken aback by this question. In looking at the list of chosen technologies, I could not figure out how being database dependent was a “bad” thing:
•\ The developers had chosen a language that locked them into a single operating system supplied by a single vendor (they could have opted for Java).
•\ They had chosen a component technology that locked them into a single operating system and vendor (they could have opted for J2EE).
•\ They had chosen a web server that locked them into a single vendor and single platform (why not something more open?).
Every other technology choice they had made locked them into a very specific configuration—in fact, the only technology that offered them any choice in terms of operating systems was the database.
Regardless of this (they must have had good reasons to choose the technologies they did), we still have a group of developers making a conscious decision to not use the functionality of a critical component in their architecture, and doing so in the name of openness. It is my belief that you pick your technologies carefully and then you exploit them to the fullest extent possible.
You paid a lot for these technologies—isn’t it in your best interest to exploit them fully? I had to assume they were looking forward to using the full potential of the other technologies, so why was the database an exception? This was an even harder question to answer in light of the fact that it was crucial to their success.
We can put a slightly different spin on this argument if we consider it from the perspective of openness. You put all of your data into the database. The database is a very open tool. It supports data access via a large variety of open systems protocols and access mechanisms. Sounds great so far, the most open thing in the world.
Then, you put all of your application logic and, more importantly, your security outside of the database. Perhaps in your beans that access the data. Perhaps in the JSPs that access the data. Perhaps in your Visual Basic code. Perhaps in your Hibernate-generated code.
The end result is that you have just closed off your database—you have made it “non-open.” No longer can people hook in existing technologies to make use of this data; they must use your access methods (or bypass security altogether). This sounds all well and good today, but what you must remember is that the whiz-bang technology of today is yesterday’s concept and tomorrow’s old, tired technology.
What has persevered for over 30 years in the relational world (and probably most of the object implementations as well) is the database itself. The front ends to the data change almost yearly, and as they do, the applications that have all of the security built inside themselves, not in the database, become obstacles, roadblocks to future progress.
The Oracle database provides a feature called fine-grained access control (FGAC). In a nutshell, this technology allows developers to embed procedures in the database that can modify queries as they are submitted to the database.
This query modification is used to restrict the rows the client will receive or modify. The procedure can look at who is running the query, when they are running the query, what application is requesting the data, what terminal they are running the query from, and so on, and can constrain access to the data as appropriate.
With FGAC, we can enforce security such that, for example
•\ Any query executed outside of normal business hours by a certain class of users returns zero records.
•\ Any data can be returned to a terminal in a secure facility, but only nonsensitive information can be returned to a remote client terminal.
Basically, FGAC allows us to locate access control in the database, right next to the data. It no longer matters if the user comes at the data from a bean, a JSP, a Visual Basicapplication using ODBC, or SQL*Plus—the same security protocols will be enforced. You are well situated for the next technology that comes along.
Now I ask you, which implementation is more “open?” The one that makes all access to the data possible only through calls to the Visual Basic code and ActiveX controls (replace Visual Basic with Java and ActiveX with EJB if you like—I’m not picking on a particular technology but an implementation here) or the solution that allows access from anything that can talk to the database, over protocols as diverse as SSL, HTTP, and Oracle Net (and others) or using APIs such as ODBC, JDBC, OCI, and so on? I have yet to see an ad hoc reporting tool that will “query” your Visual Basic code. I know of dozens that can do SQL, though.
The decision to strive for database independence and total openness is one that people are absolutely free to take, and many try, but I believe it is the wrong decision. No matter what database you are using, you should exploit it fully, squeezing every last bit of functionality you can out of that product. You’ll find yourself doing that in the tuning phase (which again always seems to happen right after deployment) anyway. It is amazing how quickly the database independence requirement can be dropped when you can make the application run five times faster just by exploiting the database software’s capabilities.