Why GRANT ALL is bad

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.

3 Responses to “Why GRANT ALL is bad”

  1. domas says:

    Ronald,

    database-level ALL does not grant FILE nor SUPER nor other dangerous stuff.
    I’m not sure if any LAMP packages really advice granting privs on *.*, usually it asks for database-level ones.

    And your ‘optimal’ permissions would be a failure in quite some near future, when ALTER or DROP command would come in by upgrade scripts.

  2. DK says:

    Where did you see advice to GRANT ALL on *.* .. ?

    Using “GRANT ALL PRIVILEGES ON userdb.* to username..” is standard practice – not on *.*.
    It doesn’t make the user superman – it lets the user fully manage their database – nothing more, nothing less.
    So your point may be true, but who really does this, or recommends to?

    > A common observation for many LAMP stack products is the use of poor MySQL security practices.
    Citation needed!

    I just checked WordPress install instructions, and there is no such thing – they suggest a database-level grant all.
    So where?

  3. Bill Salak says:

    As a matter of application architecture this needs to go a step further. Database access scenarios and corresponding database users and their permissions need to be defined and planned for upfront. Much in the same vein of this article an application should not use a single over permissioned database connection. Instead application should be architected to use database connections which have permissions to a subset of tables and table level permissions as appropriate to the types of functionality the application provides in certain scenarios.

    As a simple example, in an online magazine website public users who can only ever read the magazine articles should not be accessing those articles over a database connection which is permissioned to edit and create articles. In real world implementation there is some compromise needed to reduce the number and complexity of database users and their permissions.

    In my opinion the approach I’ve outlined is basic security and the conversation about GRANT ALL on *.* vs GRANT ALL on userdb.* is moot.