A common observation for many LAMP stack products is the use of poor MySQL security practices. Even for more established products such as Wordpress don’t always assume that the provided documentation does what it best for you. As per my earlier posts where I detailed installation instructions and optimal permissions for both WordPress and Mediawiki, and not just directed readers to online documentation.

In this post I will detail why GRANT ALL is bad.

Let’s start with what GRANT ALL [PRIVILEGES] provides. As per the MySQL 5.1 Reference Manual you get the following:

ALTER, ALTER ROUTINE, CREATE, CREATE ROUTINE, CREATE TEMPORARY TABLES, CREATE USER, CREATE VIEW, DELETE, DROP, EVENT, EXECUTE, FILE, INDEX, INSERT, LOCK TABLES, PROCESS, REFERENCES, RELOAD, REPLICATION CLIENT, REPLICATION SLAVE, SELECT, SHOW DATABASES, SHOW VIEW, SHUTDOWN, SUPER, TRIGGER, UPDATE, USAGE

I am going to focus on just one privilege that is included with ALL, and that is SUPER. This privilege can do the following which can be destructive for an application level user:

• Bypasses read_only
• Bypasses init_connect
• Can Disable binary logging
• Change configuration dynamically
• No reserved connection

User Permissions

This is how a user should be created, granting only the required permissions to a given schema.

CREATE USER goodguy@localhost IDENTIFIED BY 'sakila';
GRANT CREATE,SELECT,INSERT,UPDATE,DELETE ON odtug.* TO goodguy@localhost;

This is what is commonly seen.

CREATE USER superman@'%';
GRANT ALL ON *.* TO superman@'%';

Bypasses read_only

Many MySQL replication environments rely on ensuring the MySQL slave is consistent with the master. Did you know that an application can bypass this security when read_only=true is used?

$ mysql -ugoodguy -psakila odtug
mysql> insert into test1(id) values(1);
ERROR 1290 (HY000): The MySQL server is running with the --read-only option so it cannot execute this statement

$ mysql -usuperman odtug
mysql> insert into test1(id) values(1);
Query OK, 1 row affected (0.01 sec)

GRANT ALL is bad for data consistency.

Bybasses init_connect

A common practices used for UTF8 communications is to use the init_connect configuration variable.

#my.cnf
[client]
init_connect=SET NAMES utf8

$ mysql -ugoodguy -psakila odtug

mysql> SHOW SESSION VARIABLES LIKE 'ch%';
+--------------------------+----------+
| Variable_name            | Value    |
+--------------------------+----------+
| character_set_client     | utf8     |
| character_set_connection | utf8     |
| character_set_database   | latin1   |
| character_set_filesystem | binary   |
| character_set_results    | utf8     |
| character_set_server     | latin1   |
| character_set_system     | utf8     |
+--------------------------+----------+

$ mysql -usuperman odtug

mysql> SHOW SESSION VARIABLES LIKE 'character%';
+--------------------------+----------+
| Variable_name            | Value    |
+--------------------------+----------+
| character_set_client     | latin1   |
| character_set_connection | latin1   |
| character_set_database   | latin1   |
| character_set_filesystem | binary   |
| character_set_results    | latin1   |
| character_set_server     | latin1   |
| character_set_system     | utf8     |
+--------------------------+----------+

GRANT ALL is bad for data integrity.

Disables Binary Logging.

$ mysql -usuperman odtug

mysql> SHOW MASTER STATUS;
+-------------------+----------+--------------+------------------+
| File              | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+-------------------+----------+--------------+------------------+
| binary-log.000001 |      354 |              |                  |
+-------------------+----------+--------------+------------------+

mysql> DROP TABLE time_zone_leap_second;
mysql> SET SQL_LOG_BIN=0;
mysql> DROP TABLE time_zone_name;
mysql> SET SQL_LOG_BIN=1;
mysql> DROP TABLE time_zone_transition;
mysql> SHOW MASTER STATUS;
+-------------------+----------+--------------+------------------+
| File              | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+-------------------+----------+--------------+------------------+
| binary-log.000001 |      674 |              |                  |
+-------------------+----------+--------------+------------------+

$ mysqlbinlog binary-log.000001 --start-position=354 --stop-position=674

# at 354
#100604 18:00:08 server id 1  end_log_pos 450 Query thread_id=1 exec_time=0 error_code=0
use mysql/*!*/;
SET TIMESTAMP=1275688808/*!*/;
DROP TABLE time_zone_leap_second
/*!*/;
# at 579
#100604 18:04:31 server id 1  end_log_pos 674 Query thread_id=2 exec_time=0 error_code=0
use mysql/*!*/;
SET TIMESTAMP=1275689071/*!*/;
DROP TABLE time_zone_transition
/*!*/;
DELIMITER ;
# End of log file
ROLLBACK /* added by mysqlbinlog */;

Should that statement be run on MySQL Slaves?
Is the binary log used for any level of auditing?

GRANT ALL is bad for slave consistency.

The reserved connection

MySQL reserved one connection for an administrator to be able to login to a server. For example.

$ mysql -uroot

mysql> show global variables like 'max_connections';
+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| max_connections | 3     |
+-----------------+-------+
1 row in set (0.07 sec)

mysql> show global status like 'threads_connected';
+-------------------+-------+
| Variable_name     | Value |
+-------------------+-------+
| Threads_connected | 4     |
+-------------------+-------+

mysql> SHOW PROCESSLIST;
+----+------+-----------+-------+---------+------+------------+---------------
| Id | User | Host      | db    | Command | Time | State      | Info
+----+------+-----------+-------+---------+------+------------+---------------
| 13 | good | localhost | odtug | Query   |  144 | User sleep | UPDATE test1 ...
| 14 | good | localhost | odtug | Query   |  116 | Locked     | select * from test1
| 15 | good | localhost | odtug | Query   |   89 | Locked     | select * from test1
| 15 | root | localhost | odtug | Query   |   89 | Locked     | SHOW PROCESSLIST

However if all application users are already using the SUPER privilege, the administrator will get.

$ mysql -uroot
ERROR 1040 (HY000): Too many connections

There is no way to be able to login and see what’s happening, or kill threads for example. In this case you either wait, or you are required to kill the mysqld process, or fine the client threads to kill. The result of the former may lead to a corrupt database requiring additional recovery.

GRANT ALL is bad for system administration and monitoring.

Conclusion

Don’t use GRANT ALL for application users. For more information, including why I only listed just 5 issues, check out my MySQL Idiosyncrasies that BITE presentation. I will also be presenting this talk at MySQL Sunday at Oracle Open World in September.