Once you have done this, only the owner of a resource (such as a table, view, index, etc.) has any rights to view, modify, or delete the resource. The owner is the one who created the resource.

The owner can then grant and revoke specific rights for that resource to other users. The rights supported by Java DB are: DELETE, EXECUTE, INSERT, SELECT, REFERENCES, TRIGGER, UPDATE. For more information on SQL authorization using GRANT and REVOKE, you can check out the Derby documentation

So, for example, lets say you create a table named "races". You can then let the Marx Brothers see the races by executing the following command:

GRANT SELECT ON TABLE races TO groucho, harpo, chico

No More Java DB Password in The Clear

As I mentioned in my previous blog, Java DB's legacy is in the embedded world, where there is no such thing as sending the password over the wire.

But when you introduce the network client, this becomes an issue. And sending users and passwords in the clear is just not acceptable. So this blog tells you how to stop that from happening.

Java DB allows you to configure how credentials are sent to the server using four different modes, documented here. and here.

• CLEAR_TEXT_PASSWORD_SECURITY (0x03) - User and password are sent in the clear. This is the default
• USER_ONLY_SECURITY (0x04) - Only the user is sent. This doesn't work if authentication is enabled on the server
• STRONG_PASSWORD_SUBSTITUTE_SECURITY (0x08) - "When using this mechanism, a strong password substitute is generated and used to authenticate the user with the network server. The original password is never sent in any form across the network." (from the docs)
• ENCRYPTED_USER_AND_PASSWORD_SECURITY (0x09) - The user and password are encrypted using a pluggable encryption mechanism

Now, why on earth am I including the hex value for the security mechanism here? Because (and I kid you not), when you specify the security mechanism you want to use on your URL, you have to pass the numeric value for the mechanism, not the name.

Needless to say, when I discovered this, I immediately logged a bug. It didn't help that nowhere do the docs actually tell you you need to do this. I had to figure this out through a series of experiments and failures.

And get this, you have to convert the hex value to an integer, if you pass in 0x08 you get a NumberFormatException. Sweet.

Anyway, I'm here to help. If you want security, you probably want to choose STRONG_PASSWORD_SUBSTITUTE or ENCRYPTED_USER_AND_PASSWORD. I personally don't know the difference in terms of strength/breakability, but I do know that if you choose ENCRYPTED_USER_AND_PASSWORD, then you also need to install IBM JCE and configure the JRE to use an appropriate security provider. You have to do this for the JRE used by both the client and the server. If you are deploying your client to a wide swath of machines, this particular approach is not feasible, as far as I can tell. I am pinging the Derby user group about this, more info will be posted here if I get it.

So, personally, I'm opting for STRONG_PASSWORD_SUBSTITUTE (or 8 if you're thinking in integers). Here's how you do it:

First, if you want to enforce that only this security mechanism be used, you can configure the network server by putting this line in derby.properties:

derby.drda.securityMechanism=STRONG_PASSWORD_SUBSTITUTE_SECURITY

Next, when you connect, include the securityMechanism property in your URL, e.g

jdbc:derby://localhost:1527/mydb;create=true;securityMechanism=8

Now your password is no longer flying over the wire in the clear.

UPDATE: After some discussion on the derby-user list, I have some more clarity about securing your network pipe.

First of all, the reason you can't use Sun's Java runtime to do password encryption with Java DB is because Java DB network communication uses DRDA, a standard network protocol (used primarily by IBM). DRDA's encryption protocol uses a 256-bit key, which is considered too short (and thus too weak) by Sun's encryption engine. IBM JCE does support a 256-bit key, so that's why you would need to install it. I have asked if there other encryption engines that support a 256-bit key.

The STRONG_PASSWORD_SUBSTITUTE mechanism I describe above was implemented as an alternative because standard encryption has these issues with support and configuration overhead.

There was also general agreement on the list that what you really want is encryption using industry-standard SSL (or its new incarnation, TLS). Java DB 10.3, which is getting ready to be released, will include support for SSL/TLS. Using this mechanism is recommended unless you are concerned about the performance impact of encrypting all communications and you are fine with just protecting the password.

Authentication and Authorization with the Java DB Network Server

The original Cloudscape was built as an embedded-only database -- that is, it could only run inside the VM of another application. It did not have a client/server mode. That was added later.

In some areas, Java DB continues to carry have this embedded "legacy" with it. One particular area you should be aware of is authentication and authorization.

By default when you start up the Java DB Network Server, it accepts connections on localhost only [1], but it allows any user/password combination, and it allows any user to do anything they darn well please. And on the client, by default, the user and password are sent in the clear.

If you don't like this (and you shouldn't if you are running Java DB with sensitive data and/or or listening on ports other than localhost), there are some things you can do (NOTE: I'm assuming you're using Java DB 10.2 or greater):

• Enable authentication on the server
• Configure the client to not send the password in the clear
• Control access and privileges using GRANT/REVOKE

In this blog I'll talk about how to enable authentication on the server. In subsequent blogs I'll talk about configuring the client to not send the password in the clear, and how to use GRANT/REVOKE.

Continue Reading...

Searching the Derby Manuals

A common complaint about learning Derby is "I can't figure out where in the manuals it talks about how you do 'X'". As much as I whine about Google's desire for all our data, they do have an excellent search engine, and you can apply it to help you here.

Someone recently asked on the mailing list how to do this, and Oystein Grovlen gave this very helpful response:

I agree that the documentation is difficult to navigate in. An common index for all manuals would have helped a lot. Now, it is not always clear which manual to look in. I often end up using Google to search the manuals. If I add "site:db.apache.org/derby/docs/10.2" to my Google search criteria, I will only search the 10.2 manuals.

I have used this approach and it has helped me a lot, both for myself and when I'm trying to answer user questions.

Has Google let the genie out of the bottle?

I think we all know this: Google Loves Data. They want all of our data, every last bit of it. They live for data. They want to touch it, caress it, sift it, search it, organize it, categorize it, and monetize it. They are building data center after data center to store and serve it all.

Let's just assume I'm right here. And let's assume some of us don't really want all our data on Google, and don't fully trust Google.

Enter Google Gears (and similar solutions like Java DB). Let's assume that client-side storage gets very popular, and everyone now a local database on their machine. Combine this with iTunes, which comes with Bonjour, which uses DNS Service Discovery, an IETF standard DNS-based auto-discovery service.

The consequences haven't escaped me, nor have they escaped Laurence Rozier or Frank Sommers

These are enabling technologies on a majority of people's machines that would allow people to build and use massive, secure, Internet-based data collaboration applications and services without ever putting a single byte of their data on Google's servers.

Ouch.

Some thoughts about Google Gears

But it all depends on where you are coming from. Simon Morris crystallized my thinking around this. If you are a Browserist, or even if you're not but you need to build a browser-based solution because your boss or your market tells you you have to, then Google Gears is a very good fit.

But if you're a Neo-Desktopian, then all you really need is the ability to store data in the client. But you need this in a way where your desktop application "can float free of a physical local client installation, deploying on demand just like web pages" (to quote Simon). Traditional databases like MySQL and Oracle just don't cut it here. SQLite can fit in this space, and that's why it's not surprising to me that Adobe AIR (previously known as Apollo) has chosen to include it.

If you are using Java (and Java Web Start) as your deployment technology of choice, and I personally like this very much, and I think it's going to get better, then Java DB is an excellent choice for your client side storage.

Others may choose Berkeley DB for Java, as it is lighter weight. But when I mention this choice to people I regularly get two questions: "but isn't that controlled by Oracle?" and "don't I have to pay to distribute this if my application is not open source?" Um, yes, and yes, and that is a problem, I have to admit. Dual-licensed open source technologies controlled by a single company are definitely barriers to adoption IMHO.

If you're using client-side storage for offline support (versus just say caching local state), then you also need a synchronization solution that works over the Internet. I wrote about how you might do this, and I'm sure others, like Zimbra, are solving this too. But you don't need all the fancy shmancy page caching and synch technology that Google Gears comes with because your application is self-contained and not served up off of the web.

So, although Google Gears is cool and definitely has legs (mostly because it comes from Google), it is not a fit for all situations.

Gems from Database Users

A number of folks responded (some in comments) to my request for folks interested in participating in a series of interviews.

A few of the responses were so funny/delightful that I have to share them here. Names have been removed to protect the innocent.

We are currently using ER-Win to build our database schema. I don't know if you have ever used it, but I think it is both craptastic AND sucktacular.

From a veteran with 23 years in the industry:

I went from an ISAM based system (no, not BTrieve -- it was based on an Alpha Micro (Alpha who??)) straight in to Informix, and from there to Oracle. I heard an anecdote about some "yahoo" criticizing my Informix DB Schema of the day, saying "it wasn't relational". His argument was that since I didn't have any foreign keys, and thus no RI, it couldn't have been a relational database. Mind, Informix was, of course, an RDBMS. But also, when I was working on it -- we didn't HAVE foreign keys. They came later, and we weren't about to retrofit them into the schema. *snort* Kids!

Talking Engineerese

I was talking with my brother on IM last night and he complained about sotware engineers. He showed me a conversation between himself and an engineer. I thought it was a made-up joke dialog, to make his point.

But he blogged about it today, and said it was an actual transcript. OMG! Here it is in its full glory. Read it and weep.

AVC - (presenting plan and budget) Can you do this?
AB - Absolutely
AVC - Can you get it done by the due date?
AB - Yep
AVC - Can we get it done using this budget?
AB - Yes, could do
AVC - Can you get it done in this time *and* with this budget
AB - If you can get someone to work for free
AVC - So you can’t do this by yourself?
AB - Yes, I can
AVC - But not by the due date…
AB - I could…
AVC - But not with the other stuff you’re doing?
AB - Yes
AVC - Meaning no, you can’t get it done and do your other work too….
AB - Not without help
AVC - Which costs money
AB - Normally
AVC - So this current plan of you doing this by yourself by this date and with this budget won’t work
AB - Not without divine intervention
AVC - So when you said “absolutely”, you meant “absolutely not”?
AB - Absolutely

Looking for a few good datbase users

As Sun's architect for databases in NetBeans, one of my responsibilities is to lead the charge in figuring out what we should be doing in upcoming releases of NetBeans for database tooling.

Now, those of us in NetBeans have our own ideas and favorite features. But to be honest, we are not actually building database apps here. We're building an IDE. So what we want and like may not necessarily be what you want and like.

I could do a survey, but the survey questions would most likely be skewed to what I know. I wouldn't ask questions about what I don't know.

So, before I do any wider-ranging surveys, I want to have some heart-to-heart talks with people actually building database apps for a living, who have used database tools, and know what works and what doesn't work.

I'm going to hold a series of one hour nterviews, as many as I can manage (probably 10-15), some in person in the Bay Area and some with folks all over the world by phone.

If you're interested and would like to help, please let me know at david dot vancouvering at sun dot com. Your time and energy helping me out here will be most appreciated.

Oh, Come On, You Never Go Offline

I keep hearing this from the technorati: why is everyone so worried about offline? We're always online, and if we're not, we soon will be. My distinguished colleague Tim Bray seems to be of this ilk: what is this offline of which we speak, he says.

To which I respectfully say, piffle :)

Rather than compose something all over again, I'll quote my comment to his blog here

You should count yourself very lucky indeed if you're always online. Not the case for me as a two-hour-each-way train/bus commuter. And I've noticed that at most conferences (which I know you go to a lot :)), you're lucky to get a good connection because of so many geeks trying to read email and blog up a storm. In the last three years I have yet to go to a conference that has had good, solid WiFi. And don't get me started about hotels.

But more importantly, "always-on-the-net" is so not the case for many many people on the globe. They either have an intermittent connection, a poor connection, or no connection.

When I was doing some computer service in a village near Mumbai, we had a local Exchange server connecting/reconnecting/reconnecting all night with the Internet trying to send/receive a drip-drip-drip of a message or two before losing the connection again.

That was before a rat bit through the phone cable somewhere, and we had to take the server on a rickshaw once a day down to the next village to synch up the emails.

On a lucky tram-to-airport ride in Portland with Tim O'Reilly a few years ago, Tim told me of a similar "moto-mail" in South America [1], where a motorcycle with a server on it would tootle around to each village, people would run up with their computers and "synch" with the moto-server, and then once he got back to the city he'd do a massive send/receive. I bet those villagers wouldn't mind a little offline support for Google Reader and GMail...

[1] Actually, for the sake of accuracy re-thinking our conversation, I'm not sure of the developing country in which this took place.

This work is licensed under a Creative Commons License.