Bug #27440 read_only allows create and drop database
Submitted: 26 Mar 2007 12:33 Modified: 30 Jul 2009 23:28
Reporter: KimSeong Loh (Candidate Quality Contributor) Email Updates:
Status: Won't fix Impact on me:
None 
Category:MySQL Server: Locking Severity:S2 (Serious)
Version:5.0.37, 5.1, 5.2 OS:Linux (Linux, Windows)
Assigned to: Kristofer Pettersson CPU Architecture:Any
Tags: qc, readonly

[26 Mar 2007 12:33] KimSeong Loh
Description:
When read_only option is enabled, a user without SUPER privilege can perform CREATE DATABASE and DROP DATABASE.

The drop database can occur even if the database is not empty, contains some tables.

How to repeat:
This test shows that 
1. read_only is enabled.
2. user has no SUPER privilege
3. can create database, wrong behaviour
4. unable to create table, correct behaviour of read_only
5. can drop database, wrong behaviour

mysql> select @@read_only;
+-------------+
| @@read_only |
+-------------+
|           1 |
+-------------+
1 row in set (0.00 sec)

mysql> show grants for current_user();
+-------------------------------------------------------------------------------
-----------------------------+
| Grants for abc@localhost
                             |
+-------------------------------------------------------------------------------
-----------------------------+
| GRANT USAGE ON *.* TO 'abc'@'localhost' IDENTIFIED BY PASSWORD '*0D3CED9BEC10A
777AEC23CCC353A8C08A633045E' |
| GRANT ALL PRIVILEGES ON `abc`.* TO 'abc'@'localhost'
                             |
+-------------------------------------------------------------------------------
-----------------------------+
2 rows in set (0.00 sec)

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
+--------------------+
1 row in set (0.00 sec)

mysql> create database abc;
Query OK, 1 row affected (0.00 sec)

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| abc                |
+--------------------+
2 rows in set (0.00 sec)

mysql> use abc;
Database changed
mysql> create table abc (i int);
ERROR 1290 (HY000): The MySQL server is running with the --read-only option so i
t cannot execute this statement
mysql> drop database abc;
Query OK, 0 rows affected (0.00 sec)

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
+--------------------+
1 row in set (0.00 sec)

mysql>

Suggested fix:
Do not allow the Create and Drop database to be executed when it is read_only.
[26 Mar 2007 15:01] Sveta Smirnova
Thank you for the report.

Verified as described.

Version 4.1 is not affected.
[7 Dec 2007 11:36] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/39515

ChangeSet@1.2589, 2007-12-07 12:40:08+01:00, thek@adventure.(none) +3 -0
  Bug #27440 read_only allows create and drop database
  
  When read_only option was enabled, a user without SUPER privilege could
  perform CREATE DATABASE and DROP DATABASE operations.
  
  This patch adds a check to make sure this isn't possible. It also attempts to 
  simplify the logic used to determine if relevant tables are updated,
  making it more human readable.
[7 Dec 2007 14:36] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/39534

ChangeSet@1.2589, 2007-12-07 15:39:41+01:00, thek@adventure.(none) +3 -0
  Bug #27440 read_only allows create and drop database
  
  When read_only option was enabled, a user without SUPER privilege could
  perform CREATE DATABASE and DROP DATABASE operations.
  
  This patch adds a check to make sure this isn't possible. It also attempts to 
  simplify the logic used to determine if relevant tables are updated,
  making it more human readable.
[16 Dec 2007 11:40] Bugs System
Pushed into 5.0.56
[16 Dec 2007 11:43] Bugs System
Pushed into 5.1.23-rc
[16 Dec 2007 11:44] Bugs System
Pushed into 6.0.5-alpha
[18 Dec 2007 5:06] Paul DuBois
Noted in 5.0.56, 5.1.23, 6.0.5 changelogs.

With the read_only system variable enabled, CREATE DATABASE and DROP
DATABASE were allowed to users who did not have the SUPER privilege.
[25 Jul 2009 4:16] Roel Van de Paar
Please re-triage.

This is still an issue for another instruction (SET PASSWORD) on 5.0.83, but not on 5.1.34:

5.0.83:
=====================
roel@roel-ubuntu-vm:/mysql/mysql-5.0.83-linux-i686-icc-glibc23/bin$ ./mysql -urtest --socket=/mysql/mysql-5.0.83-linux-i686-icc-glibc23/socket-5.0.83.sock
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 18
Server version: 5.0.83 MySQL Community Server (GPL)

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> SELECT USER(),CURRENT_USER();
+-----------------+-----------------+
| USER()          | CURRENT_USER()  |
+-----------------+-----------------+
| rtest@localhost | rtest@localhost | 
+-----------------+-----------------+
1 row in set (0.00 sec)

mysql> SHOW GRANTS FOR CURRENT_USER();
+--------------------------------------------+
| Grants for rtest@localhost                 |
+--------------------------------------------+
| GRANT SELECT ON *.* TO 'rtest'@'localhost' | 
+--------------------------------------------+
1 row in set (0.00 sec)

mysql> SHOW GLOBAL VARIABLES LIKE '%only%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| read_only     | ON    | 
+---------------+-------+
1 row in set (0.00 sec)

mysql> SET PASSWORD FOR 'rtest'@'localhost' = PASSWORD('new_pass');
Query OK, 0 rows affected (0.00 sec)
=====================

5.1.34:
=====================
roel@roel-ubuntu-vm:/mysql/mysql-5.1.34-linux-i686-icc-glibc23/bin$ ./mysql -urtest --socket=/mysql/mysql-5.1.34-linux-i686-icc-glibc23/socket-5.1.34.sock
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.1.34 MySQL Community Server (GPL)

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> SELECT USER(),CURRENT_USER();
+-----------------+-----------------+
| USER()          | CURRENT_USER()  |
+-----------------+-----------------+
| rtest@localhost | rtest@localhost | 
+-----------------+-----------------+
1 row in set (0.00 sec)

mysql> SHOW GRANTS FOR CURRENT_USER();
+--------------------------------------------+
| Grants for rtest@localhost                 |
+--------------------------------------------+
| GRANT SELECT ON *.* TO 'rtest'@'localhost' | 
+--------------------------------------------+
1 row in set (0.00 sec)

mysql> SHOW GLOBAL VARIABLES LIKE '%only%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| read_only     | ON    | 
+---------------+-------+
1 row in set (0.00 sec)

mysql> SET PASSWORD FOR 'rtest'@'localhost' = PASSWORD('new_pass');
ERROR 1290 (HY000): The MySQL server is running with the --read-only option so it cannot execute this statement
=====================
[29 Jul 2009 20:26] Peter Gulutzan
The standard says nothing about this.

My opinion is that SET PASSWORD should not be allowed, since
it causes an update on mysql.user, and the manual says that
read_only=ON means non-supers cannot "update". But without
knowing what caused the change, I can't opine if it's "proper".
[30 Jul 2009 23:31] Omer Barnir
Password issue Bug  issue mentioned [25 Jul 6:16] Roel Van de Paar will not be addressed in 5.0 at this point as it is available in 5.1