MySQL: Permit user to change his/her _own_ password

MySQLpasswordshadow

On a webserver there is a MySQL-service running, providing databases for the users of the server. Each user has it's own DB and it's own password and the right so INSERT/SELECT/UPDATE/DROP tables in it's own DB, but nothing more.

However, i want to give the user the privilege to UPDATE his own password. I've read "12.4.1.3. GRANT Syntax" from the MySQL 5.1 documentation. But i didn't found a way to grant the user the privilege to change his password, without granting full UPDATE-access to the mysql.user-table.

Maybe there is also a way to synchronize the passwords in /etc/shadow along with the passwords in mysql.user, so, each time when the user changes his password in Linux, the MySQL-password is also altered.

Regards
Macs

Best Answer

I don't think you need a special permission for doing that.
I just created a new user (GRANT USAGE only) and was able to change my password via:

SET PASSWORD = password('bla');

Related Solutions

Debian MySQL User – What is the debian-sys-maint MySQL User?

What is debian-sys-maint used for?

One major thing it is used for is telling the server to roll the logs. It needs at least the reload and shutdown privilege.

See the file /etc/logrotate.d/mysql-server

It is used by the /etc/init.d/mysql script to get the status of the server. It is used to gracefully shutdown/reload the server.

Here is the quote from the README.Debian

* MYSQL WON'T START OR STOP?:
=============================
You may never ever delete the special mysql user "debian-sys-maint". This user
together with the credentials in /etc/mysql/debian.cnf are used by the init
scripts to stop the server as they would require knowledge of the mysql root
users password else.

What is the easiest way to restore it after I've lost it?

The best plan is to simply not lose it. If you really lose the password, reset it, using another account. If you have lost all admin privileges on the mysql server follow the guides to reset the root password, then repair the debian-sys-maint.

You could use a command like this to build a SQL file that you can use later to recreate the account.

mysqldump --complete-insert --extended-insert=0 -u root -p mysql | grep 'debian-sys-maint' > debian_user.sql

Is the password in /etc/mysql/debian.cnf already hashed

The password is not hashed/encrypted when installed, but new versions of mysql now have a way to encrypt the credentials (see: https://serverfault.com/a/750363).

MySQL – How to Recover When MySQL Loses Its Root Password

This answer is in connection with Ruby on Rails framework where this behavior occurred

have mysql with user root and password
bootstrap new rails project using with mysql "rails new projectname
-d mysql"
do not modify config/database.yml - defaults credentials for server are name: root, password is blank
rake db:create prompt show up - asking for root password and then when you provide root password

the answer is in GRANT command on line 68 in here

basically it executed GRANT command with credentials matches your current database.yml

Best Answer

Related Solutions

Debian MySQL User – What is the debian-sys-maint MySQL User?

MySQL – How to Recover When MySQL Loses Its Root Password

Related Topic