Saari Development

Ali Rizvi's Technical Blog as a Professional Software Development Engineer

MySQL: Changing password and finding user permissions

leave a comment »

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.

Advertisements

Written by imsaar

November 19, 2005 at 11:51 pm

Posted in general

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: