I somehow got to be 'in charge' of a mysql database server that is to be used for many groups for different uses.
My intent is to create a database for each group and give one member of that group full permissions to that database only, but no power to mess up any other database.
So I run (for example):
> create database ktest4;
> grant all on ktest4.* to 'abc123'@'%' identified by 'abc123' with grant option;
This seems to work and user 'abc123' can login to the database ktest4.
However, now when the user 'abc123' wants to change their password, I tell them to type:
>mysql> SET PASSWORD FOR 'abc123'@'%' = PASSWORD('newpassword');
but they get:
>ERROR 1044: Access denied for user: 'abc123@%' to database 'mysql'
Strangely, if they type just 'abc123' instead of 'abc123'@'%' - that works, even tho my documentation says it's the same thing.
Also, when they want to create a new user for that database with limited privileges, I tell them to type:
mysql> grant SELECT, INSERT on ktest4.* to 'user_ktest4'@'%' identified by 'user_ktest4';
and again, they get:
ERROR 1044: Access denied for user: 'abc123@%' to database 'mysql'
Please explain this to me. My primary documentation (MySQL by Paul DuBois) doesn't mention changes to the 'mysql' database...