Secure Simple EJB Bean making the DB tables.

Alright, it has been awhile but I swear I've been away for very good reasons.

So if you remember our basic EJB HelloBean, let's look at how to add security measures to provide a login for people to run our EJB code.

First we need to setup our JDBC realm. To do that we need an actual database. I'll be using MySQL as it is a wonderfully simple database to use and maintain, at the same time MySQL scales nicely and can be used for very big projects. So let's say you have your MySQL server up and running. Let's create a database called helloDB. To do that from the MySQL prompt (hereafter known as the sql prompt (since I hate having to hit the shift key for SQL)) type in:

CREATE DATABASE helloDB

Now let's create two tables, one to hold user names and passwords, the other to hold user names and groups. We'll call these tables USERTBL and GRPTBL.

CREATE TABLE USERTBL (
	USERNAME VARCHAR(13) NOT NULL,
	SHAPASSWD CHAR(64) NOT NULL,
	PRIMARY KEY (USERNAME)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE GRPTBL (
	ENTRYID INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
	USERNAME VARCHAR(13) NOT NULL,
	GROUPING VARCHAR(20) NOT NULL,
	PRIMARY KEY (ENTRYID),
	UNIQUE KEY (USERNAME, GROUPING),
	CONSTRAINT FOREIGN KEY (USERNAME) REFERENCES USERTBL (USERNAME) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Here we've created the two tables we need to start a security realm using the JDBCRealm module. The SHAPASSWD field of the USERTBL is to store our passwords in SHA-256 encoded strings. You can use any kind of hash function that is known to Java, like MD-5 or SHA-1, I'm going with SHA-256 because I like it among other things.

Also note that I did not make a composite primary key for the GRPTBL but instead created a unique key for the user name / group name pair. I don't like composite primary keys, I don't know many DB admins that do, they have a purpose but just using them anywhere is not really a good idea.

Well there you have the DB side of it. I'll look at the Glassfish end of it in the next post. Cheers!