I have upgraded MariaDB 5.5 to 10.1 now and it says mysql uses secure-auth and change old password to new password. I have disabled secure-auth in my.cnf and able to login.
Now my question is how do I change old password to new password and enable secure-auth on the database.
suggest how do I do it.
I'm going to assume your MariaDB server doesn't have other users that you don't know the passwords for, and that you have requisite PRIVILEGES to modify users and set system level variables. Super_Priv may be enough :)
Note: You run the risk of locking yourself out, but as long as you are logged in, changing these won't log you out of your current session so you can recover if things go the wrong way, without having to restart. I always test with a second connection, because it is a buzzkill to lock yourself out of a database. Not that I would know anything about that.
Note2: I'd strongly recommend backing up the user table for safety with SQL similar to this:
CREATE TABLE mysql.user_backup_YYYYMMDD LIKE mysql.user;
INSERT INTO mysql.user_backup_YYYYMMDD SELECT * FROM mysql.user;
You can modify the "secure_auth" setting in real-time (so you don't have to restart your mariadb server) by changing the system variable "secure_auth" (if your user account has the required privileges) with the following sql (ie. run in a mysql client):
SET GLOBAL secure_auth="ON";
or SET GLOBAL secure_auth="OFF";
To see what the current value is:
SHOW GLOBAL VARIABLES LIKE '%auth%';
To make it permanent through a reboot, change the value in your: /etc/my.cnf.d/server.cnf (or possibly your /etc/my.cnf - hopefully it's not set in both, but it's worth checking)
[mysqld]
secure-auth=on
(Note: As you found out, changes in the .cnf won't be loaded until your mariadb server is restarted)
Once you enable auth_secure, users with "old passwords" won't be able to log in as they have been. The easiest approach, if possible, at the point is to upgrade the password types on all accounts to avoid any issues trying to support both.
You can view the current accounts and (encrypted) passwords with the following SQL:
SELECT user, host, password FROM mysql.user;
Before updating, you can verify that your password is what you think it is by comparing this sql to the PASSWORD field in the mysql.user table:
SELECT OLD_PASSWORD('What You Think Your Password Is');
And the same thing, but for the new passwords:
SELECT PASSWORD('newpass');
From there, you have options, but the normal way would be to set the passwords:
SET PASSWORD FOR 'foo'@'%' = PASSWORD('newpass');
where foo and % are the fields HOST and USER from the mysql.user table.
Another (funky) option would be to clear the passwords using the ALTER USER command and no INDENTIFIED BY clause as described here:
https://mariadb.com/kb/en/library/alter-user/
Obviously that could lead to security anti-joy.
You can also set the password fields by an update statement, for example, if you wanted to set it back to a saved value:
UPDATE mysql.user SET PASSWORD="SOME SAVED VALUE" where USER = "sqllove" and host = "localhost";
Or you could set it back to an "old password" format, if you know the password:
UPDATE mysql.user SET PASSWORD=OLD_PASSWORD("Old Password") where USER = "sqllove" and host = "localhost";
One (scary) nice thing about this approach is if you are setting a password for multiple users, you can do it in one command - for example, to make the password "easy" for people connecting on the same machine via localhost:
UPDATE mysql.user set PASSWORD=PASSWORD("easy") where host="localhost";
And to make sure your latest user changes are visible immediately, when done, some final SQL:
FLUSH privileges;
I'd stay logged into the MariaDB client the entire time - make changes, flush, test from another account, relog any services (they will continue to work even if something is wrong but will fail next login attempt) and once it's safe then log out.
Tip: If you are in unix and logged into the client and need to run a shell command, instead of logging out of your client, you can hit Ctrl-Z to get a shell prompt (suspending your client) program, and you can then run shell commands (ie. run a 2nd client! or restart services that connect to the database) and when ready, enter %1 to get back into the client (you might have to hit enter to reload the prompt). This works in most console programs in unix, not just the MariaDB client. If you do it repeatedly like Inception, the return command would be %2 and %3 and so on depending on how many levels you go down. But time won't go slower.
