Adding groups in Spring security
I want to use the group abstraction in my application. However I don't understand how to configure it nor which tables I need. Is it right that you have one user table with the username, password and enabled column in addition to other things such as first name? Then you need these tables: groups, groups_authorities and group_members?
Wouldn't it be bad to duplicate the username in both the user table and group_members table? I don't get how it relates to eachother. Some of what I have found...
create table groups ( id bigint generated by default as identity(start with 0) primary key, group_name varchar_ignorecase(50) not null); create table group_authorities ( group_id bigint not null, authority varchar(50) not null, constraint fk_group_authorities_group foreign key(group_id) references groups(id)); create table group_members ( id bigint generated by default as identity(start with 0) primary key, username varchar(50) not null, group_id bigint not null, constraint fk_group_members_group foreign key(group_id) references groups(id));
Q1: I am not sure that I understand completely your question. For now I think the response will be yes.
Q3: it depends. If you have / may have in the future a "change username" feature then it's bad. Good news is that you can customize DB schema. For example add an id field to users table and use this field as PK (make sure that username field is unique in this case). Now you must ovverride default SQL queries from org.springframework.security.core.userdetails.jdbc.JdbcDaoImpl:
public static final String DEF_USERS_BY_USERNAME_QUERY = "select username,password,enabled " + "from users " + "where username = ?"; public static final String DEF_AUTHORITIES_BY_USERNAME_QUERY = "select username,authority " + "from authorities " + "where username = ?"; public static final String DEF_GROUP_AUTHORITIES_BY_USERNAME_QUERY = "select g.id, g.group_name, ga.authority " + "from groups g, group_members gm, group_authorities ga " + "where gm.username = ? " + "and g.id = ga.group_id " + "and g.id = gm.group_id";
You can do it in your security config:
<authentication-manager> <authentication-provider> <jdbc-user-service data-source-ref="securityDataSource" users-by-username-query="your customized SQL goes here" authorities-by-username-query="your customized SQL goes here" group-authorities-by-username-query="your customized SQL goes here" /> </authentication-provider> </authentication-manager>