Spring Security Use 'groups' as table name results in bad SQL grammar

Describe the bug When using Spring Security's group authorization feature, get 'bad SQL grammar'.

To Reproduce 1. use the following sql to init schema

create table users(
    username varchar_ignorecase(50) not null primary key,
    password varchar_ignorecase(500) not null,
    enabled boolean not null
);

create table authorities (
    username varchar_ignorecase(50) not null,
    authority varchar_ignorecase(50) not null,
    constraint fk_authorities_users foreign key(username) references users(username)
);
create unique index ix_auth_username on authorities (username,authority);

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)
);

1. create a spring security project, and have the following configuration

My server port is 9000, after I inputed username and password to login, I got this error

I think it's because that the sql statement has 'groups' which is reserved word in mysql

Comment From: sjohnr

Hi @tuyu79. Unfortunately, if you're right about groups being the problematic syntax, I think it will be quite difficult to find a fix that solves it for your case (mysql) and does not break for other databases. I'm not aware of a syntax for escaping the existing identifiers that works for all databases (for example, back-ticks would only work for mysql variants I believe). Are you aware of anything else that could solve it?

Comment From: spring-projects-issues

If you would like us to look at this issue, please provide the requested information. If the information is not provided within the next 7 days this issue will be closed.

Comment From: tuyu79

Hi @tuyu79. Unfortunately, if you're right about groups being the problematic syntax, I think it will be quite difficult to find a fix that solves it for your case (mysql) and does not break for other databases. I'm not aware of a syntax for escaping the existing identifiers that works for all databases (for example, back-ticks would only work for mysql variants I believe). Are you aware of anything else that could solve it?

Hi @sjohnr. Why don't we just rename the table name with other words that are far away from database reserved words? Like has some prefix or suffix ?

Comment From: sjohnr

Hello @tuyu79. The reason is that it would be a breaking change for anyone using this component. As a framework, we have to be very careful about changing things out from under our users.

Have you explored using Spring Data JPA to implement your own UserDetailsService?

Comment From: tuyu79

Hi @sjohnr. Sorry for my slow reply. I just test again, and I found this error occurs only when I use MySQL8.0 . It's OK with MySQL5.7, and maybe also OK for other version before MySQL8.0. I also take your suggestion to use JPA to implement my own UserDetailsService, I found it still need to add back-ticks on table name to work well, like below. Anyway, We can fix this with a little code, and threre is a big chance to modify the table structure the demo provided in practice. Thanks for your answer.

Comment From: sjohnr

Absolutely, glad it helped!

I'm uncertain whether we should leave this issue open. Others may encounter it and in the meantime your experience may help them, but solving it in the codebase may require much more thought. I'm open to ideas! I'll leave this open for the moment.

Comment From: rwinch

I'm closing this issue as it is impractical for the default SQL to work with every database. For databases that it does not work with, custom SQL can be injected into the JdbcDaoImpl or a custom implementation of the interface can be made.