Database Reference
In-Depth Information
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 Basic application 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.
How Do I Make It Run Faster?
The question in the heading is one I get asked all the time. Everyone is looking for the fast = true switch, assuming
“database tuning” means that you tune the database. In fact, it is my experience that more than 80 percent (frequently
100 percent) of all performance gains are to be realized at the application design and implementation level—not the
database level. You can't tune a database until you have tuned the applications that run on the database.
As time goes on, there are some switches we can throw at the database level to help lessen the impact of
egregious programming blunders. For example, Oracle 8.1.6 added a new parameter,
CURSOR_SHARING=FORCE
.
This feature implements an
auto binder
, if you will. It will silently take a query written as
SELECT * FROM EMP WHERE
EMPNO = 1234
and rewrite it for us as
SELECT * FROM EMP WHERE EMPNO = :x
. This
can
dramatically decrease the
