Duplicate value in the bank when inserting new users with an already used Role (Spring-Security)

1

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

    
asked by anonymous 06.02.2016 / 03:57

1 answer

2

I was able to resolve by changing the user annotation to @ManyToMany

As well explained above by Matheus,

  

OneToMany would be if the roles were only one user. For example: Table person and phone, would have a phone attribute in person annotated with OneToMany, one person has several phones, but the phone only belongs to one person. - Matheus Silva

    
10.02.2016 / 14:29