The Database Connection as a Leverage Point

Most databases are accessed over a network connection. We tend to think of these connections as an opaque tube between the database client and the database server: whatever happens in these tubes is mysterious, and there is nothing we can do about it. I’d like to show you how these connections can in fact be used to augment and improve your information systems.

By routing these database connections through a smart database proxy, we can observe, control and potentially modify the requests and responses going back and forth. This gives us a powerful leverage point:

  • We can observe the traffic between database clients and database servers for business intelligence, security, performance analysis, etc.

  • We can control the connections and reject or redirect certain requests based on their content, their origin, the time of day, or other reasons.

  • Most importantly, we can modify requests and responses, and thus change how our systems work without having to make any alterations to the database clients or servers.

Typical Use Cases

This concept of controlling and modifying database connections is pretty abstract so far, so let’s make it more real. Here are the most common use cases.

Request Modification

Sometimes you need to change how an application interacts with a database, but you cannot change that application. This is common with third-party applications that you do not control or applications that are no longer maintained.

This is the most obvious use case because there is no alternative: it’s either that or give up the application.

In practical terms, this is usually a fairly straightforward affair: You set up a filter in the proxy to catch a certain request and replace it with another request. In most cases, the replacement request is logically equivalent to the one it replaces, since its results must be consumed by the application. For instance, that could mean rephrasing a query to be more efficient or to behave differently, or changing syntax or feature that is no longer supported by the database.

In some cases, you might change a request so that it returns an error message (eg “This data is not available”), an empty result set, or even data from a different data source.

ResultSet Modification

This is the mirror image of request modification. There are situations in which you may need to change the data returned by some queries in a way that would be or impossible to do by modifying the request. You may need to change just one value in a large result set, convert currencies, insert external data, or mask certain values ​​in ways that are not supported by the database. Whatever the reason is, the proxy gives you the flexibility to change the results of queries at the most atomic level possible.

Fine-Grained Access Control

Sometimes, it’s impossible to express complex access control requirements using the database’s mechanisms. You may need to specify that certain users should get only some access to some very specific data items some of the time, and most databases are simply not very good at that: it’s assumed to be the job of the database clients. Even the databases that have some such capabilities (such as Oracle with its fine-grained access control) tend to make it painful and expensive.

In this context, a proxy can implement fine-grained access control for known queries, and other similar changes to the connection.

Request Control

Most applications access their database(s) in a relatively predictable manner. Therefore, it is easy to record the requests to the database during a period of time (call it the “recording phase”) and then lock down the system by rejecting any requests that have not been seen before (call that the “enforcement phase” ). This is easily done with a smart database proxy, with plenty of flexibility to accommodate the inevitable exceptions, and idiosyncrasies that are to be expected in any non-trivial system. This is not appropriate for all applications, of course, but it can be a simple security addition to many applications.

Business Intelligence

Getting a reliable, real-time view of what an application is doing in a database can be surprisingly difficult. Some databases offer an interface that gives you visibility into its activities, but they tend to focus on monitoring and performance. A proxy can easily pluck out whatever type of database activity is relevant, and record it or send it wherever needed. The selling points of the proxy are that it requires no special access to the database, has no effect on the database or the clients, and can be applied to a subset of all database accesses.

…And More

These are just common use cases, but you can get more creative. For instance, you can do some lightweight integration by meshing data from multiple sources into a single result set, generating test data dynamically, or doing on-the-fly encryption and decryption of data. It’s always interesting to see what innovative people can do when you give them this kind of power.

How Does It Work?

The principle is simple: instead of talking directly to one another, the database clients and the database servers communicate through a proxy. This can work with or without encryption, on-premise, or in the Cloud, and neither the clients nor the servers will notice any difference. The proxy works at the wire protocol level and is therefore completely transparent.

Once the proxy is in place, applications and servers behave exactly as they normally do. The proxy will forward all the traffic back and forth. Based on the criteria you specify, however, it may intervene and change that traffic when appropriate by executing your logic, which can read and modify the requests and responses as desired.

Advantages and Disadvantages of This Approach

Databases are central to most information systems. Therefore, anything that can leverage them has the potential to make a big difference. The clearest advantage of this approach is that it can be implemented quickly with existing database systems, without changing either the clients or the database servers. There are very few control points that can affect information systems so profoundly, with so little effort.

However, all these capabilities may carry a cost.

The additional complexity of a proxy is something to take into account. It’s one more system that must be planned, secured, and administered. In addition, any logic tested to the proxy must be managed, and source-controlled.

Also, like any other tool, a database proxy can be misused. It can be tempting to rely on it because it’s so easy to tweak a system at that level. As a system designer, you have to decide when this is the right thing to do, and when it’s a band-aid for issues that should be solved in some other way.

Conclusion

System design is hard, and we all need every bit of help we can get. Once you realize that the connections between database clients and database servers can be opened up and leveraged, all sorts of interesting possibilities open up. We’ve briefly looked at the most common uses, but I encourage you to let your imagination roam free.

Most people start by using a smart database proxy as a point solution, often to fix a specific issue in a specific application, but once the proxy is in place, it can become a great leverage point to elegantly solve problems that are otherwise almost impossible.

.

Leave a Comment