Hello
I'm creating a simple web application with SpringMVC and Spring Security. I made the User and Roles class as follows:
@Entity
public class Usuario implements UserDetails {
private static final long serialVersionUID = 1L;
@Id
private String username;
private String password;
private String nome;
@OneToMany(fetch=FetchType.EAGER)
private List<Role> roles = new ArrayList<Role>();
...
}
@Entity
public class Role implements GrantedAuthority {
private static final long serialVersionUID = 1L;
@Id
private String role;
private String name;
...
}
I'm letting Hibernate create the tables automatically. The code generated by Hibernate for creating the table is this:
Hibernate: create table Usuario (username varchar(255) not null, nome varchar(255), password varchar(255), primary key (username))
Hibernate: create table Usuario_Role (Usuario_username varchar(255) not null, roles_role varchar(255) not null)
Hibernate: alter table Usuario_Role add constraint UK_9ljdlf4fugq6jh14x7obwpi37 unique (roles_role)
Hibernate: alter table Usuario_Role add constraint FKb32xr1fddmr4pxdxuj5u14f56 foreign key (roles_role) references Role (role)
Hibernate: alter table Usuario_Role add constraint FKm7abqk7krlrd3bb61ecux2fnx foreign key (Usuario_username) references Usuario (username)
I added 3 Roles to the database and the user's registry they are displayed and the person can select the permissions of that user. When I add a user and select a Role that was not used it works normally, but when I select one that is being used by another user, the duplicity error in the table 'user_role' (This table is being created automatically by Hibernate by annotation @ OneToMany). Even if I try to insert the user in the database hand by the same error, then I think it's a problem to create the tables because neither the roles_role nor the username must be unique in the table, but I do not know what to do correct.
Testing done at the bank:
mysql> show tables;
+-----------------+
| Tables_in_kmcdb |
+-----------------+
| atendimento |
| role |
| usuario |
| usuario_role |
+-----------------+
4 rows in set (0.00 sec)
mysql> select * from role;
+------------+---------------+
| role | name |
+------------+---------------+
| ROLE_ADMIN | Administrador |
| ROLE_SUP | Supervisor |
| ROLE_TEC | Técnico |
+------------+---------------+
3 rows in set (0.00 sec)
mysql> insert into usuario(username, nome, password) value('admin','admin','admin');
Query OK, 1 row affected (0.03 sec)
mysql> insert into usuario(username, nome, password) value('fillipe','fillipe','fillipe');
Query OK, 1 row affected (0.00 sec)
mysql> insert into usuario_role(usuario_username, roles_role) value('admin','ROLE_ADMIN');
Query OK, 1 row affected (0.05 sec)
mysql> insert into usuario_role(usuario_username, roles_role) value('fillipe','ROLE_SUP');
Query OK, 1 row affected (0.00 sec)
mysql> insert into usuario_role(usuario_username, roles_role) value('fillipe','ROLE_TEC');
Query OK, 1 row affected (0.00 sec)
mysql> select * from usuario_role;
+------------------+------------+
| Usuario_username | roles_role |
+------------------+------------+
| admin | ROLE_ADMIN |
| fillipe | ROLE_SUP |
| fillipe | ROLE_TEC |
+------------------+------------+
3 rows in set (0.00 sec)
mysql> insert into usuario_role(usuario_username, roles_role) value('fillipe','ROLE_ADMIN');
ERROR 1062 (23000): Duplicate entry 'ROLE_ADMIN' for key 'UK_9ljdlf4fugq6jh14x7obwpi37'
mysql> insert into usuario_role(usuario_username, roles_role) value('admin','ROLE_SUP');
ERROR 1062 (23000): Duplicate entry 'ROLE_SUP' for key 'UK_9ljdlf4fugq6jh14x7obwpi37'
What do I need to modify to be able to insert a new user with a role that is already being used?
Thank you