MySQL: Changing password and finding user permissions

Changing password can be done through the mysql command:

If you have no root password (which is how MySQL comes out of the box) then type the following:

shell> $MYSQLDIR/mysqladmin -u root password newpassword

now if you have a password and want to change it:

shell> $MYSQLDIR/mysqladmin -u root -p password newpassword
Enter password: oldpassword

There drawback to this technique is that your new password is saved in the shell history buffer in clear text which is undesirable but I have not found a better way yet.


Once you have changed the password and you want to see what users exists with what password you can see that by logging into the special "mysql" database as root:

shell> $MYSQLDIR/mysql -u root -p mysql
Enter Password:

Now when you can see all the tables in the special "mysql" database:

mysql> show tables;

mysql> show tables;
+---------------------------+
| Tables_in_mysql           |
+---------------------------+
| columns_priv              |
| db                        |
| func                      |
| help_category             |
| help_keyword              |
| help_relation             |
| help_topic                |
| host                      |
| proc                      |
| procs_priv                |
| tables_priv               |
| time_zone                 |
| time_zone_leap_second     |
| time_zone_name            |
| time_zone_transition      |
| time_zone_transition_type |
| user                      |
+---------------------------+
17 rows in set (0.00 sec)

Look into the user table to see the list of interesting fields as there are a lot of them:

mysql>  describe user;
mysql> describe user;
+-----------------------+-----------------------------------+------+-----+---------+-------+
| Field                 | Type                              | Null | Key | Default | Extra |
+-----------------------+-----------------------------------+------+-----+---------+-------+
| Host                  | char(60)                          | NO   | PRI |         |       |
| User                  | char(16)                          | NO   | PRI |         |       |
| Password              | char(41)                          | NO   |     |         |       |
| Select_priv           | enum('N','Y')                     | NO   |     | N       |       |
| Insert_priv           | enum('N','Y')                     | NO   |     | N       |       |
| Update_priv           | enum('N','Y')                     | NO   |     | N       |       |
| Delete_priv           | enum('N','Y')                     | NO   |     | N       |       |
| Create_priv           | enum('N','Y')                     | NO   |     | N       |       |
| Drop_priv             | enum('N','Y')                     | NO   |     | N       |       |
| Reload_priv           | enum('N','Y')                     | NO   |     | N       |       |
| Shutdown_priv         | enum('N','Y')                     | NO   |     | N       |       |
| Process_priv          | enum('N','Y')                     | NO   |     | N       |       |
| File_priv             | enum('N','Y')                     | NO   |     | N       |       |
| Grant_priv            | enum('N','Y')                     | NO   |     | N       |       |
| References_priv       | enum('N','Y')                     | NO   |     | N       |       |
| Index_priv            | enum('N','Y')                     | NO   |     | N       |       |
| Alter_priv            | enum('N','Y')                     | NO   |     | N       |       |
| Show_db_priv          | enum('N','Y')                     | NO   |     | N       |       |
| Super_priv            | enum('N','Y')                     | NO   |     | N       |       |
| Create_tmp_table_priv | enum('N','Y')                     | NO   |     | N       |       |
| Lock_tables_priv      | enum('N','Y')                     | NO   |     | N       |       |
| Execute_priv          | enum('N','Y')                     | NO   |     | N       |       |
| Repl_slave_priv       | enum('N','Y')                     | NO   |     | N       |       |
| Repl_client_priv      | enum('N','Y')                     | NO   |     | N       |       |
| Create_view_priv      | enum('N','Y')                     | NO   |     | N       |       |
| Show_view_priv        | enum('N','Y')                     | NO   |     | N       |       |
| Create_routine_priv   | enum('N','Y')                     | NO   |     | N       |       |
| Alter_routine_priv    | enum('N','Y')                     | NO   |     | N       |       |
| Create_user_priv      | enum('N','Y')                     | NO   |     | N       |       |
| ssl_type              | enum('','ANY','X509','SPECIFIED') | NO   |     |         |       |
| ssl_cipher            | blob                              | NO   |     |         |       |
| x509_issuer           | blob                              | NO   |     |         |       |
| x509_subject          | blob                              | NO   |     |         |       |
| max_questions         | int(11) unsigned                  | NO   |     | 0       |       |
| max_updates           | int(11) unsigned                  | NO   |     | 0       |       |
| max_connections       | int(11) unsigned                  | NO   |     | 0       |       |
| max_user_connections  | int(11) unsigned                  | NO   |     | 0       |       |
+----------------------+-----------------------------------+------+-----+---------+-------+
37 rows in set (0.05 sec)

Here is a query that would tell you user have permissions to certain operations on your database from different host:

mysql> select host, user, select_priv, insert_priv, delete_priv, create_priv, drop_priv, alter_priv from user;
+----------------+------+-------------+-------------+-------------+-------------+-----------+------------+
| host           | user | select_priv | insert_priv | delete_priv | create_priv | drop_priv | alter_priv |
+----------------+------+-------------+-------------+-------------+-------------+-----------+------------+
| localhost      | root | Y           | Y           | Y           | Y           | Y         | Y          |
| AliPower.local | root | Y           | Y           | Y           | Y           | Y         | Y          |
| AliPower.local |      | N           | N           | N           | N           | N         | N          |
| localhost      |      | N           | N           | N           | N           | N         | N          |
+----------------+------+-------------+-------------+-------------+-------------+-----------+------------+
4 rows in set (0.00 sec)

It seems the blank user name is there to allow to run mysql shell without a user.