No Password – No Problem

[Chris]

In the past I have needed to gain access to MySQL servers when the password had been strangely forgotten. Below there are thee different processes to gain access to MySQL if you just don’t have the right kind of access you need or want.

Option 1: --skip-grant-tables

This will allow you to login to the server and change what you need to in the mysql.user table. Of course you will need to restart the server again without --skip-grant-tables if you want the current and newly added or modified user account to work.

This option is a good way to gain access to the server in the event that you have no elevated (root) privileges on the MySQL server. You do need root on the server you are on so you can kill the pid and restart with --skip-grant-tables.

Option 2: elevating your privileges

You will need to have an account on the server with WRITE access to the mysql database.

mysql> show grants for ‘chris’@’localhost’;
+----------------------------------------------------------+
| Grants for chris@localhost |
+----------------------------------------------------------+
| GRANT USAGE ON *.* TO 'chris'@'localhost' |
| GRANT SELECT, INSERT ON `mysql`.* TO 'chris'@'localhost' |
+----------------------------------------------------------+
2 rows in set (0.00 sec)

mysql> select * from user where user = 'chris'\\\\G
*************************** 1. row ***************************
Host: localhost
User: chris
Password:
Select_priv: N
Insert_priv: N
Update_priv: N
Delete_priv: N
Create_priv: N
Drop_priv: N
Reload_priv: N
Shutdown_priv: N
Process_priv: N
File_priv: N
Grant_priv: N
References_priv: N
Index_priv: N
Alter_priv: N
Show_db_priv: N
Super_priv: N
Create_tmp_table_priv: N
Lock_tables_priv: N
Execute_priv: N
Repl_slave_priv: N
Repl_client_priv: N
Create_view_priv: N
Show_view_priv: N
Create_routine_priv: N
Alter_routine_priv: N
Create_user_priv: N
Event_priv: N
Trigger_priv: N
ssl_type:
ssl_cipher:
x509_issuer:
x509_subject:
max_questions: 0
max_updates: 0
max_connections: 0
max_user_connections: 0
1 row in set (0.00 sec)

mysql> insert into mysql.user VALUES ('%','sneekyuser','','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','','','','',0,0,0,0);
Query OK, 1 row affected (0.00 sec)

mysql> flush privileges;
ERROR 1227 (42000): Access denied; you need the RELOAD privilege for this operation

Note the ERROR above.

mysql> select * from user where user = 'sneekyuser'\\\\G
*************************** 1. row ***************************
Host: %
User: sneekyuser
Password:
Select_priv: Y
Insert_priv: Y
Update_priv: Y
Delete_priv: Y
Create_priv: Y
Drop_priv: Y
Reload_priv: Y
Shutdown_priv: Y
Process_priv: Y
File_priv: Y
Grant_priv: Y
References_priv: Y
Index_priv: Y
Alter_priv: Y
Show_db_priv: Y
Super_priv: Y
Create_tmp_table_priv: Y
Lock_tables_priv: Y
Execute_priv: Y
Repl_slave_priv: Y
Repl_client_priv: Y
Create_view_priv: Y
Show_view_priv: Y
Create_routine_priv: Y
Alter_routine_priv: Y
Create_user_priv: Y
Event_priv: Y
Trigger_priv: Y
ssl_type:
ssl_cipher:
x509_issuer:
x509_subject:
max_questions: 0
max_updates: 0
max_connections: 0
max_user_connections: 0
1 row in set (0.00 sec)

The problem with this method is that you need a MySQL restart, a full server restart or a user with RELOAD to run “FLUSH PRIVILEGES”. Although these actions can occur on production systems you might have to wait a while if you’re not the one in control of these actions. For this example I restarted the MySQL server and obtained the grants I wanted.

shell> mysql -usneekyuser -S /tmp/mysql.sock

mysql> show grants;
+-------------------------------------------------------------------+
| Grants for sneekyuser@% |
+-------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'sneekyuser'@'%' WITH GRANT OPTION |
+-------------------------------------------------------------------+
1 row in set (0.00 sec)


Option 3: MyISAM is GREAT

All of the system tables that MySQL uses are in the MyISAM storage engine. That said and knowing that the privilege tables are loaded into memory at runtime you can just replace the user.frm, user.MYD and user.MYI file on the OS level and restart.

Please keep in mind that these are “operational tasks” that could prove useful to your environment given the right situation. The processes listed above are NOT the gateway into hacking the MySQL server given that you need some sort of preexisting access to the server or MySQL.

[Glynn Durham]

I like this option: In your my.cnf file, add

init-file=/home/mysql/db1/warmup.sql # Any path and file will do, as long as mysqld can read it (and hopefully others can't read or write the file(!)

In this file /home/mysql/db1/warmup.sql, put:
GRANT ALL ON *.* TO breakin@localhost IDENTIFIED BY 'breakin' WITH GRANT OPTION

(Hard return delimiter on the statement--no semicolon.)

Stop and start your server. This command will run on server restart, so then you'll have this new DBA account (user=breakin, password=breakin). Log in to this new account to do all the things you want with your server.

After the one restart, remove this entry in your warmup file, as you don't want to keep "creating" this account or resetting its password on every server restart.

[Chris]

Great Addition!

[Sheeri Cabral]

And if you have no access at all, and don't know what users exist, you can do:

strings -n2 user.MYD

to see the usernames, hosts, and password hashes.

That's if you have disk access. This has helped me a few times :)