Spring Security Database Schema
August 19th, 2008 | by richfreedman |
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):
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]
22 Responses to “Spring Security Database Schema”
By Mackie on Dec 7, 2008 | Reply
This looks quite helpful, but it does not work out-of-the-box with my existing Spring XML configuration. Previously I was using the default USERNAME/AUTHORITIES table structure. After changing to your table structure, Spring Security complains that it can’t find the AUTHORITIES table.
This is my current config:
By Mackie on Dec 7, 2008 | Reply
Ah, so the forum stripped out my XML. I’ll escape and try posting it again.
<authentication-provider>
<jdbc-user-service data-source-ref="dataSource"/>
</authentication-provider>
By richfreedman on Dec 8, 2008 | Reply
Yes, it looks like I left out one critical piece of the configuration. I have a custom userManager, so my authentication provider xml looks like:
<authentication-provider user-service-ref='userManager'>My ‘userManager’ subclasses
org.springframework.security.userdetails.jdbc.JdbcUserDetailsManager,
and my jdbcUserManager has the following properties set:
<!-- enable lookup of permissions via user's group -->
<property name="enableGroups" value="true"/>
<!-- disable direct lookup of user's permissions (require lookup via group) -->
<property name="enableAuthorities" value="false"/>
Let me know if this fixes it for you – if you still have problems, I’ll try to post a full example.
By Metaele on Dec 10, 2008 | Reply
How about a full example? I think it would help as there are not many examples on this way of usage of Spring Security.
By phil on Dec 28, 2008 | Reply
Thumbs up on that. This is exactly what I’m looking to do and I’m very new to spring security. Can you post your example?
By Oleg on Jan 29, 2009 | Reply
Hi guys!
I’m looking for example of similar security implementation but without effect…
Could you give the realization of the userManager’
org.springframework.security.userdetails.jdbc.JdbcUserDetailsManager.
Or maybe your full example of SS 2.0.
Thanks!
By richfreedman on Jan 29, 2009 | Reply
For all of the folks who asked for an example:
I have put together a full example, including a Whitepaper, for my employer, Chariot Solutions.
It’s being reviewed, and should be ready shortly.
I’ll let everyone know as soon as it is available.
Download it, along with sample code, here.
By Oleg on Jan 30, 2009 | Reply
Thank you, richfreedman!
By phil on Feb 6, 2009 | Reply
Thanks Rich, looking forward to the example.
By naz on Mar 25, 2009 | Reply
hi guys,
i wanted to see if a white paper was ever published. I looked around the site and couldnt find anything. I am setting up spring security on a client with existing tables and wanted to see what needs to be extended / modified in order for those basic tables to work.
By richfreedman on Mar 25, 2009 | Reply
I did write the whitepaper, complete with examples, but the folks at work have not reviewed it, so I haven’t published it.
But, I will send you a copy by private email, for your review. Let me know if it works for you.
I hope to have it published soon.
By iloncar on Mar 30, 2009 | Reply
Hi,
Could you send me the paper also?
By richfreedman on Mar 30, 2009 | Reply
it’s on it’s way…
By blues464 on Mar 31, 2009 | Reply
I’d like a copy too if possible. Great work BTW! :)
By richfreedman on Mar 31, 2009 | Reply
the white paper and the examples are now available here
By Josh on Jul 20, 2009 | Reply
This was very useful in converting over to a role->permission model. It’s strange to me that the default is the pure role-based… but I suppose in the simple case that works well.
By ahmed on Aug 14, 2009 | Reply
While following the white paper for the 1st example application(springsecurity-role) and trying to run it.
I got the following error on the login page:
Login error.
Reason : Could not get JDBC Connection; nested exception is java.sql.SQLException: Connections could not be acquired from the underlying database!; nested exception is org.springframework.jdbc.CannotGetJdbcConnectionException: Could not get JDBC Connection; nested exception is java.sql.SQLException: Connections could not be acquired from the underlying database!
Any help to solve this would be appreciated.
By ahmed on Aug 14, 2009 | Reply
Please disregard my error comment.. its solved after providing correct DB configuration data.
However I have one comment for future convenience.
maybe you can add a link to the examples in the post? I came here by Google and had to run through comments to reach it.
Cheers
By Brian on Sep 5, 2009 | Reply
Can I get a copy of that white paper? I am having a heck of a time implementing Spring Security 2.0.5 with jdbc and can’t seem to find any working examples…
Thanks!
By richfreedman on Sep 6, 2009 | Reply
Brian – see my comment above from March 31, 2009 – it has the link for the whitepaper and the code.
By Michael on Aug 19, 2010 | Reply
The whitepaper and examples are not in the indicated location. Do they still exist somewhere?
By richfreedman on Aug 20, 2010 | Reply
I fixed the links – they should be available now.
By the way, this information is a bit old now – you should take a look at Spring Security 3.0 – it’s a lot easier now, especially with Roo