MySQL Bugs: #27440: read_only allows create and drop database

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

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.

Thank you for the report.

Verified as described.

Version 4.1 is not affected.

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.

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.

Pushed into 5.0.56

Pushed into 5.1.23-rc

Pushed into 6.0.5-alpha

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.

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
=====================

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".

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