Table design when using authorization with jdbcrealm in Glassfish v3

When using jdbcrealm in Glassfish v3 how strictly m开发者_C百科ust I follow the recommendations regarding tables? Currently I have the following setup:

CREATE TABLE roles (
    id INTEGER PRIMARY KEY NOT NULL AUTO_INCREMENT,
    username VARCHAR(255) NOT NULL,
    rolename VARCHAR(255) NOT NULL,     
);

CREATE TABLE users (
id INTEGER PRIMARY KEY NOT NULL AUTO_INCREMENT, 
    username VARCHAR(255) PRIMARY KEY NOT NULL,
    password VARCHAR(255) NOT NULL, 
    firstname VARCHAR(255),
    lastname VARCHAR(255),  
    email VARCHAR(255),
    status VARCHAR(255),
    role_id INTEGER,
    CONSTRAINT FOREIGN KEY(role_id) REFERENCES roles(id)    
);

Is it possible to use this setup without changing anything to create a jdbcrealm or must I change my tables?

Thanks in advance!

Have you tried it? It seems ok. The strange thing with the jdbcRealm is that it expects an unnormalized database, one would like something more like:

user (userid, username, passw, ...)
security_group (security_groupid, name)
user_in_group (user_in_groupid, userid, security_groupid)

Wich is more normalized. However this setup does not work. But if you're like me and think this should work take a look at the lovely custom Flexible JDBC Realm. It worked for me.