MySQL Paas password change - is this a bug or a feature?



Now I'm no MySQL expert which is one of the main reasons we are using the (relatively) new MySQL PaaS offering from Microsoft in Azure - today we've seen some odd behaviour when trying to do some grants and password setting. I'm not sure if this is how MySQL somehow behaves when you are doing things wrong or if this is a restriction of PaaS or indeed if it's a bug in the Microsoft implementation of a MySQL PaaS - see what you think.......

So here is the simple test case



MySQL [phpipam]> grant all privileges on phpipam.* to richtest@localhost identified by 'phpipamadmin';
Query OK, 0 rows affected, 2 warnings (0.28 sec)

That runs just fine and creates a new user called richtest and gives it a password - this syntax seems a little alien to me comnig from the oracle world - it seems to do 2 things in 1 command - anyway no matter it seems to work fine

The odd thing is when i try and run it again - which as i understand it should just re grant the rights and change the password - i get this message

MySQL [phpipam]> grant all privileges on phpipam.* to richtest@localhost identified by 'phpipamadmin';
ERROR 1044 (42000): Access denied for user 'admin'@'%' to database 'mysql'

So it seems that i need some rights on the core myql database that i don't have - and indeed this syntax doesnt work

MySQL [phpipam]> set password for 'richtest'@'localhost' = 'newpass';
ERROR 1044 (42000): Access denied for user 'admin'@'%' to database 'mysql'

And neither does this (this syntax feels like hacking by the way - like updating user$ directly in oracle....)

MySQL [phpipam]> UPDATE mysql.user SET Password=PASSWORD('newpass')WHERE USER='richtest' and host='localhost';
ERROR 1044 (42000): Access denied for user 'admin'@'%' to database 'mysql'

However i happily have rights to drop the user......

MySQL [phpipam]> drop user richtest@localhost;
Query OK, 0 rows affected (0.25 sec)


This can't be right can it? I have to drop a user to be able to choose a new password? There is a seperate azure super admin account (that i don't have a password for) that does seem to have more rights but there is no facility in the PaaS GUI to be able to do anything to change any users password other than the default admin one you get.

Is this a PaaS bug? 

Thoughts from MySQL experts?

3 comments:

  1. have you tried logging in as richtest and changing the password that way?

    I can see how a hosted MySQL system would not want you to muck directly with the system tables (mysql database).

    According to https://dev.mysql.com/doc/refman/5.7/en/set-password.html, the preferred way to assign passwords is ALTER USER. What happens when you try that?

    ReplyDelete
  2. hi Rich,

    I happen to be working with MySQL quite a lot recently.
    Can you check what privs the user has you are trying to change password with?

    show grants for XYZ@%;

    Cheers,
    Balazs

    ReplyDelete
  3. On a second thought I wouldn't know how to enable create user rights then after the user is created automatically disable the access to the mysql.user table without messing with the built in functionalities therefore source code...

    ReplyDelete