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.