A strange behavior is noticed MySQL is that a user without create privileges can re-create Database which was dropped. This post discusses about the same whether it is a bug or feature.
The MySQL 'root' account
As a developer of any (web based) application which uses MySQL/MariaDB, you should know that the admin account i.e. 'root' should not be used for every purpose. Note that this 'root' should not be confused with the GNU/Linux 'root' user.
Standard security practice says that as 'root':
- You should create a new user on MySQL/MariaDB, set the password for that user.
- Create a database.
- Grant all rights one the new created Database to that user.
This basically limits the access of that user to one and only one database. This in other terms is also a method for "Damage Control".
By allowing a limited access to a user you also limit the amount of damage that user can do with the given access.
MySQL strange behavior with 'create database'
While working with MySQL you can notice that a user without create privileges can create a Database. Now that is some interesting observations and could confuse people.
Create a user via 'root' in MySQL
- Login as root
- mysql -u root -p
- Run create user query
- CREATE USER 'pingu'@'localhost' IDENTIFIED WITH mysql_native_password BY 'pingu123';
- Now lets create a database and assign to user 'pingu';
- create database pingu;
- GRANT ALL ON pingu.* to 'pingu'@'localhost';
Now lets login as the above created user and see what we have
- Login as user 'pingu'
- mysql -u pingu -p
- List the available databases
- show databases;
Now let Drop this database and the try to create the same database again.
- Being logged in as 'pingu' drop the database and then recreate the same.
- drop database pingu;
- create database pingu;
At this point you will thing the database will not be created, but MySQL actually allows you to create the database again even though you do not have 'create' rights.
The point to notice is that you can recreate database with the same name that you dropped and not any other.
Why user in MySQL can recreate a database
In MySQL, once a user has dropped (deleted) a database, they can typically recreate it without needing additional privileges. This behavior is due to several reasons inherent in MySQL's design and permission handling.
So basically
- Allowing users to recreate a dropped database without additional privileges simplifies administrative tasks
- Means that users can easily manage and reorganize their databases
- Without requiring intervention from database administrators or additional permission grants.
That is it!!!