Revoke access to a database not applied on mysql active DB Connection

Question

I just want to ask how can I apply grants on active connection ? Currently I'm trying to revoke access to a user for a specific database on active connection and it doesn't work. For exemple if I revoke all privileges to fooUser on database foo, if the user already have some active connections he would still be able to insert/update/select on the database until he makes a new connection... I thought revoking all privileges would kill active connections but it seems like not.

I expect that if I revoke access to a user connected and or using a specific database he should be "ejected" somehow but it doesn't.

Answer 1

In the src/main/resources/application.properties file list the below line along with database URL, username, and password for creating a new connection with a database.

spring.jpa.hibernate.ddl-auto=update

You must begin with either create or update because you do not yet have the database structure. Use update when you want to make some change to the database structure. The default for H2 and other embedded databases is create-drop. For other databases, such as MySQL, the default is none.

Once your database is in a production state, set this to none, revoke all privileges from the MySQL user connected to the Spring application, and give the MySQL user only SELECT, UPDATE, INSERT, and DELETE.