If your’e using JDBC Authentication with Spring Security, it’s pretty easy to find the database schema required for the “default” JdbcDaoImpl.

JdbcDaoImpl is pretty basic, though. It doesn’t support the concept of true role-based security. It only has ‘users’ and ‘authorities’. Sure, you can call a role and ‘authority’, and manage all of your security based entirely on roles. But that’s pretty brittle. A bit too coarse-grained for my taste.

Spring Security does support permissions based on roles. In this situation, roles are known as ‘groups’, and permissions are ‘authorities’. This lets you have, say, a “Users” group with “read” permission for something, and an “Admin” group with “read” and “write” permissions. Then, if you later find that your roles are too lumpy, you can refactor your roles in the database, without touching your code. The trick is to do all security checks in the code based on permissions, not roles.

If you decide to go this route, it can be difficult to find the required database schema. Of course, you can find it via trial-and-error (I did). To (hopefully) save others a little time, though, here it is (in MySQL dialect):

:::sql
CREATE TABLE `users` (
`username` varchar(255) NOT NULL,
`password` varchar(255) NOT NULL,
`enabled` tinyint(1) NOT NULL,
PRIMARY KEY (`username`)
);

CREATE TABLE `groups` (
`id` int(11) NOT NULL default '0',
`group_name` varchar(255) default NULL,
PRIMARY KEY (`id`)
);

CREATE TABLE `group_members` (
`group_id` int(11) NOT NULL default '0',
`username` varchar(255) default NULL,
PRIMARY KEY (`group_id`, `username`)
);

CREATE TABLE `group_authorities` (
`group_id` int(11) NOT NULL default '0',
`authority` varchar(255) NOT NULL default '',
PRIMARY KEY (`group_id`,`authority`)
);

[Update - the whitepaper and sample source code are here]