alexalmazan.com

A brief example on resetting the MySQL admin password.

First stop the active MySQL Process

[root@host root]# service mysqld stop

Next, the mysqld process should be restarted without grants loaded

/usr/bin/mysqld --skip-grant-tables --user=root

Go back into MySQL with the client:

[root@host root]# mysql
Welcome to the MySQL monitor.  Commands end with ; or g.
Your MySQL connection id is 1 to server version: 3.23.41

Type 'help;' or 'h' for help. Type 'c' to clear the buffer.

mysql> USE mysql
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> UPDATE user
    -> SET password=password("newpassword")
    -> WHERE user="root";
Query OK, 2 rows affected (0.04 sec)
Rows matched: 2  Changed: 2  Warnings: 0

mysql> flush privileges;
Query OK, 0 rows affected (0.01 sec)

mysql> exit;
[root@host root]#killall mysqld

This should permit a fresh start of MySQL with the new password established. Start the serveice as normal and confirm the changes.

SQL grants can certainly leave a host vulnerable, especially if the door is left open for a remote ‘consultant’. Here is a quick example of a revocation of grants:

mysql> show grants for root@ip.address;
+-------------------------------------------------------------------------------------------------------------------+
| Grants for root@ip.address                                                                                     |
+-------------------------------------------------------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'root'@'ip.address' IDENTIFIED BY PASSWORD '2ac2db3645cd1434' WITH GRANT OPTION |
+-------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> REVOKE  ALL ON *.* FROM 'root'@'ip.address' IDENTIFIED BY PASSWORD '2ac2db3645cd1434';
Query OK, 0 rows affected (0.02 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

MySQL uses a separate set of users than the Linux system users, so you’ll need to create a mysql user to work with databases. The syntax for adding a mysql user is as follows

 
mysql> GRANT [privs] ON [db] TO [user]@[host] IDENTIFIED BY [pass];

e.g.

mysql> GRANT ALL PRIVILEGES ON testdb.* TO 'bob'@'localhost' IDENTIFIED BY '3kuh3Ok';

The above grants all privileges on the testdb database to the local user bob, with password 3kuh3Ok.

A few things to note:

% is used to indicate any remote host… therefore bob@’%’ is bob at any host (other than localhost)

Users consist of both a username AND a host, so bob@localhost, bob@192.168.1.23, and bob@’bob.com’ are all distinct users.

Grant statements can also change the permissions on an existing user.

e.g.

mysql> GRANT ALL PRIVILEGES ON bobs_db.* TO bob@'%' IDENTIFIED BY 'a5u19kb';
mysql> GRANT SELECT,INSERT ON blog_db.abcdata TO bob@'%';

Would create a user bob@’%’ (any remote host) with password a5u19kb, and grant the user full privileges on database ‘bobs_db’, and then ALSO grant that user SELECT and INSERT privileges on the abcdata table of the blog_db database.

To create a user with no db access, you can use:

mysql> GRANT USAGE ON *.* TO user@host IDENTIFIED BY 'password';

Full DB access to all databases:

mysql> GRANT ALL PRIVILEGES ON *.* TO user@host IDENTIFIED BY 'password';