Mysql – Disabling password change for a MySQL user

MySQLpermissionsusers

Is it possible for MySQL users to prevent/disable changing their password with just the USAGE option? There are no global permissions allowed.
I tried to google, but had no luck.

Best Answer

One way to achieve this could be by using pluggable authentication. This way the password is stored externally and therefore can't be changed from within mysql. This allows you to use PAM, LDAP or other authentication services.

For PAM, assuming you have configured PAM already in your OS (assuming Linux below), then in MySQL do:

INSTALL PLUGIN authentication_pam SONAME 'authentication_pam.so';
CREATE USER bob@'%' IDENTIFIED WITH authentication_pam AS 'mysql';

... where AS 'mysql' refers to a file /etc/pam.d/mysql.

If the user then tries to set their password (I've tested this only on MariaDB with the unix_socket plugin):

SET PASSWORD = PASSWORD('My_very_clever_password');

... that query will "work", however it gives a warning:

SET PASSWORD has no significance for users authenticating via plugins

Related Solutions

MySQL Permissions Necessary To Reset Password

A user should be able to change his/her own password strictly using SET PASSWORD.

For a connected user to set the new password to mynewpass, just run the following:

mysql> SET PASSWORD = PASSWORD('mynewpass');

According to the MySQL Documentation on SET PASSWORD, if the server is a read-only enabled server, you need SUPER privilege to do this. Otherwise, you can do this any time. There is no need for another user to set someone else's password. If you need a super user to set it you can still use SET PASSWORD.

To set the password of 'someuser'@'10.1.2.30' to hisnewpass, run this:

mysql> SET PASSWORD FOR 'someuser'@'10.1.2.30' = PASSWORD('hisnewpass');

According to the MySQL Documentation on SET PASSWORD, this is the equivalent of:

UPDATE mysql.user SET Password=PASSWORD('hisnewpass')
WHERE User='someuser' AND Host='10.1.2.30';
FLUSH PRIVILEGES;

Using SET PASSWORD does not warrant manipulating mysql.user.

Mysql – reset thesql root password on ubuntu

Here is a more secure way to reset password without skip-grant-tables

Suppose you want root to have myn3wp@ssw0rd as the password

Step 01 : Create a script to execute when mysqld first start up

Create a file called /var/lib/mysql/init-file.sql with these two lines

GRANT ALL PRIVILEGES ON *.* to root@localhost
IDENTIFIED BY 'myn3wp@ssw0rd' WITH GRANT OPTION;

Step 02 : Run these three(3) lines in the OS

chown mysql:mysql /var/lib/mysql/init-file.sql
service mysql restart --init-file=/var/lib/mysql/init-file.sql
rm -f /var/lib/mysql/init-file.sql

Step 03 : THERE IS NO STEP 03. YOU ARE DONE !!!

Give it a Try !!!

CAVEAT

I used to recommend two restarts of mysql that required editing my.cnf
I learned a better approach from @ShlomiNoach
This answer improves on it more by not editing my.cnf at all

UPDATE 2013-06-17 07:05 EDT

OK Since mysqld for Ubuntu does not like init-file on the command, you must edit the my.cnf. Please do these next two steps

STEP 01) Add these lines to my.cnf under the [mysqld] group header

[mysqld]
init-file=/var/lib/mysql/init-file.sql

STEP 02) Allow mysql to read files from /etc/mysql

sudo vim /etc/apparmor.d/usr.sbin.mysqld

Add the line:

/etc/mysql/*.sql r,

STEP 03) Restart MySQL

This should work for you. Give it a Try !!!