MySQL : How to grant "ALTER DATABASES" to a USER

Question

A user needs to do :

ALTER DATABASE `dbname` DEFAULT CHARACTER SET utf8 DEFAULT COLLATE utf8_general_ci;

...but even with a :

GRANT ALL PRIVILEGES ON dbname.* TO 'user_name'@'localhost' IDENTIFIED BY '***';

...the user is not allowed to do that.

I found some websites where it is advised to do

GRANT USAGE ON SCHEMA dbname...

...but even in MySQL 8 (sorry we have Mysql 5 on this server) this does not seems to exist :

https://dev.mysql.com/doc/refman/8.0/en/grant.html

Sorry if this question is completely out of score, stupid, or whatsoever, but even with "good" MySQL knowledge I don't understand what I am doing wrong or what I should do.

Thanks a lot in advance for any help !

Denis

Answer 1

This answer is in the documentation. https://dev.mysql.com/doc/refman/8.0/en/alter-database.html says:

This statement requires the ALTER privilege on the database.

The GRANT USAGE only allows that user to connect to the server. It doesn't give privileges to do any SQL statement. In fact, it does not make sense to GRANT USAGE on a specific database. If you try, the user ends up with only USAGE privilege at the server level:

mysql> grant usage on `dbname`.* to 'testuser'@'%';
Query OK, 0 rows affected, 1 warning (0.03 sec)

mysql> show grants for 'testuser'@'%';
+--------------------------------------+
| Grants for testuser@%                |
+--------------------------------------+
| GRANT USAGE ON *.* TO 'testuser'@'%' |
+--------------------------------------+

Answer 2

The DEFAULT is unnecessary in the command. This will change the default character set and collation for a database:

ALTER DATABASE {DB_NAME} CHARACTER SET utf8 COLLATE utf8_unicode_ci;

However, you will also need to convert the character set and collation for each table in that database, otherwise some "bad things will happen" in the future when you try to join data.

ALTER TABLE {TABLE_NAME} CONVERT TO CHARACTER SET utf8 COLLATE utf8_unicode_ci;

Be sure to modify {DB_NAME} and {TABLE_NAME} to the correct database and table names where appropriate.

Note: Tested on MySQL 5.4 in a Docker container, and MySQL 8.0.23 on bare metal.

Answer 3

To be very short, the solution to the problem was :

GRANT ALTER ON dbname.* TO 'user_name'@'localhost' IDENTIFIED BY '***';

The explanations and details are in Bill Karwin solution.