No Password – No Problem

No Password – No Problem: "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.
PlanetMySQL Voting:
Vote UP /
Vote DOWN"