alexalmazan.com

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';