MySQL Bugs: #58669: read_only not enforced on 5.5.x

Bug #58669	read_only not enforced on 5.5.x
Submitted:	2 Dec 2010 18:18	Modified:	21 Dec 2010 18:34
Reporter:	Leandro Morgado	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Options	Severity:	S1 (Critical)
Version:	5.5.6-rc, 5.5.7-rc	OS:	Any
Assigned to:	Ramil Kalimullin	CPU Architecture:	Any
Tags:	read_only

Description:
read_only is not enforced for non SUPER() users on at least 5.5.6 and 5.5.7. 

Manual states:

"This variable is off by default. When it is enabled, the server permits no updates except from users that have the SUPER privilege or (on a slave server) from updates performed by slave threads. In replication setups, it can be useful to enable read_only on slave servers to ensure that slaves accept updates only from the master server and not from clients."

http://dev.mysql.com/doc/refman/5.5/en/server-system-variables.html#sysvar_read_only

How to repeat:
Using the MySQL Sandbox tool and mysql-5.5.7-rc-linux2.6-x86_64.tar.gz on:

Linux hostname 2.6.18-194.26.1.el5 #1 SMP Tue Nov 9 12:54:20 EST 2010 x86_64 x86_64 x86_64 GNU/Linux
 

shell> ./m bac001 < $(locate world.sql)

shell> ./s1 -uxno_bacon -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 7
Server version: 5.5.7-rc-log MySQL Community Server (GPL)

Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

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

slave1 [localhost] {xno_bacon} ((none)) > SELECT CURRENT_USER(), USER();
+----------------+---------------------+
| CURRENT_USER() | USER()              |
+----------------+---------------------+
| xno_bacon@%    | xno_bacon@localhost |
+----------------+---------------------+
1 row in set (0.00 sec)

slave1 [localhost] {xno_bacon} (bac001) > SHOW GRANTS FOR xno_bacon@'%';
+-----------------------------------------------------------------------+
| Grants for xno_bacon@%                                                |
+-----------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'xno_bacon'@'%'                                 |
| GRANT SELECT, INSERT, UPDATE, DELETE ON `bac001`.* TO 'xno_bacon'@'%' |
+-----------------------------------------------------------------------+
2 rows in set (0.00 sec)

slave1 [localhost] {xno_bacon} (bac001) > SHOW VARIABLES LIKE 'read_only%'; 
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| read_only     | ON    |
+---------------+-------+
1 row in set (0.00 sec)

slave1 [localhost] {xno_bacon} ((none)) > use bac001
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed

slave1 [localhost] {xno_bacon} (bac001) > INSERT INTO City VALUES(NULL, 'MySQL Town', 'PRT', 'Braga', 100000);
Query OK, 1 row affected (0.00 sec)

slave1 [localhost] {xno_bacon} (bac001) > SELECT * FROM City WHERE District='Braga';
+------+------------+-------------+----------+------------+
| ID   | Name       | CountryCode | District | Population |
+------+------------+-------------+----------+------------+
| 2918 | Braga      | PRT         | Braga    |      90535 |
| 4080 | MySQL Town | PRT         | Braga    |     100000 |
+------+------------+-------------+----------+------------+
2 rows in set (0.00 sec)

slave1 [localhost] {xno_bacon} (bac001) > SHOW SLAVE STATUS\G
ERROR 1227 (42000): Access denied; you need (at least one of) the SUPER,REPLICATION CLIENT privilege(s) for this operation
slave1 [localhost] {xno_bacon} (bac001) > Bye

shell> ./s1 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 5.5.7-rc-log MySQL Community Server (GPL)

Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

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

slave1 [localhost] {msandbox} ((none)) > SHOW SLAVE STATUS\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 127.0.0.1
                  Master_User: msandbox
                  Master_Port: 13012
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000001
          Read_Master_Log_Pos: 1477878
               Relay_Log_File: mysql_sandbox13013-relay-bin.000006
                Relay_Log_Pos: 1477216
        Relay_Master_Log_File: mysql-bin.000001
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB: 
          Replicate_Ignore_DB: 
           Replicate_Do_Table: 
       Replicate_Ignore_Table: 
      Replicate_Wild_Do_Table: 
  Replicate_Wild_Ignore_Table: 
                   Last_Errno: 0
                   Last_Error: 
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 1477878
              Relay_Log_Space: 1477385
              Until_Condition: None
               Until_Log_File: 
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_SSL_CA_File: 
           Master_SSL_CA_Path: 
              Master_SSL_Cert: 
            Master_SSL_Cipher: 
               Master_SSL_Key: 
        Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error: 
               Last_SQL_Errno: 0
               Last_SQL_Error: 
  Replicate_Ignore_Server_Ids: 
             Master_Server_Id: 1
1 row in set (0.00 sec)

Suggested fix:
Make sure read_only is enforced for non SUPER() users.

This need to be properly verified. Please give full instructions and test with the latest available version.

This bug has been verified. Simply install 5.5.7-rc, setup replication, create a non SUPER user on the slave and set it to read_only. Watch as the non SUPER user writes all over the slave despite it being read_only.

I've just tested this without replication and still, read_only is not enforced on 5.5.7-rc-log:

start server:
mysqld --no-defaults --read-only

mysql -uroot
drop database if exists db;
create database db;
grant insert on db.* to 'shane'@'localhost';
create table db.t select 1 a;
exit

mysql -ushane db
insert into t set a=5;

Leandro,

Thanks, more details are better. So, the read-only option does not work when set from the command-line?

read_only doesn't work either way you set it. Set it from the command line, set it from my.cnf, set it in the mysql> cli.

Give it a try and see for yourself.

Seems to work for me:

CREATE USER user1@localhost;
CREATE DATABASE db1;
GRANT ALL PRIVILEGES ON db1.* TO user1@localhost;

CREATE TABLE t1 (a INT);
INSERT INTO t1 VALUES (1);

connect (con1,localhost,user1,,);

connection default;
SHOW VARIABLES LIKE "%READ_ONLY%";
SELECT * FROM t1;
INSERT INTO t1 VALUES (1);
SET GLOBAL READ_ONLY=1;

connection con1;
--error ER_OPTION_PREVENTS_STATEMENT
INSERT INTO t1 VALUES (1);

and does not work when set from the command line.

mysql> use test;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> SELECT CURRENT_USER();
+-----------------+
| CURRENT_USER()  |
+-----------------+
| user1@localhost |
+-----------------+
1 row in set (0.00 sec)

mysql> SHOW GLOBAL VARIABLES LIKE "%READ_ONLY%";
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| read_only     | OFF   |
+---------------+-------+
1 row in set (0.00 sec)

mysql> INSERT INTO t1 VALUES (1);
Query OK, 1 row affected (0.00 sec)

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

mysql> INSERT INTO t1 VALUES (1);
ERROR 1290 (HY000): The MySQL server is running with the --read-only option so it cannot execute this statement
mysql> SELECT VERSION();
+--------------+
| VERSION()    |
+--------------+
| 5.5.7-rc-log |
+--------------+
1 row in set (0.00 sec)

mysql>

Leandro,

There is something seriously wrong if it does not work for you from a regular client. But beware that it won't work if you still feed --read-only from the command line. Are you sure it's not due to the command line?

Try replacing this:

GRANT ALL PRIVILEGES ON db1.* TO user1@localhost;

With this:

GRANT INSERT ON db1.* TO user1@localhost;

"This variable is off by default. When it is enabled, the server permits no updates except from users that have the SUPER privilege or (on a slave server) from updates performed by slave threads"
 http://dev.mysql.com/doc/refman/5.5/en/server-system-variables.html#sysvar_read_only

If read_only is set from CLI as:

mysql> SET GLOBAL READ_ONLY=1;

Then this read_only is enforced with:

ERROR 1290 (HY000): The MySQL server is running with the --read-only option so it cannot execute this statement

When set from my.cnf/startup option in command line, read_only is ignored.

Verified as described. Thanks!

> Try replacing this:
>
> GRANT ALL PRIVILEGES ON db1.* TO user1@localhost;
>
> With this:
>
> GRANT INSERT ON db1.* TO user1@localhost;

For what it's worth and to clear any possible misunderstanding, GRANT ALL PRIVILEGES as given above does not grant SUPER. Super is a global privilege. Quoting from the documentation:

"Global privileges are administrative or apply to all databases on a given server. To assign global privileges, use ON *.* syntax [..]" "The [..]  and SUPER privileges are administrative and can only be granted globally."

Small amplification on previous comment:

GRANT ALL does include SUPER, but only for the global level (GRANT ALL ON *.* ...)

GRANT ALL _as shown in the example_ does not grant SUPER, because it grants at the database level (ON db1.*) and thus does not include global privileges

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/125987

3165 Ramil Kalimullin	2010-12-03
      Fix for bug #58669: read_only not enforced on 5.5.x
      
      Problem: "read-only" option ignored if it's enabled in
      the command line (or in the config file).
      
      Fix: sync opt_readonly (which is used for checks) with 
      read_only (global var) when all server options are handled.
     @ mysql-test/r/bug58669.result
        Fix for bug #58669: read_only not enforced on 5.5.x
          - test result.
     @ mysql-test/t/bug58669-master.opt
        Fix for bug #58669: read_only not enforced on 5.5.x
          - test case.
     @ mysql-test/t/bug58669.test
        Fix for bug #58669: read_only not enforced on 5.5.x
          - test case.
     @ sql/mysqld.cc
        Fix for bug #58669: read_only not enforced on 5.5.x
          - sync opt_readonly boolean with read_only boolean
        in the get_options().
     @ sql/mysqld.h
        Fix for bug #58669: read_only not enforced on 5.5.x
          - sync opt_readonly boolean with read_only boolean
        in the get_options().
     @ sql/sys_vars.cc
        Fix for bug #58669: read_only not enforced on 5.5.x
          - sync opt_readonly boolean with read_only boolean
        in the get_options().

The (rather unacceptable) workaround is to SET GLOBAL READ_ONLY=TRUE at run-time.

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/126244

3176 Ramil Kalimullin	2010-12-07
      Fix for bug #58669: read_only not enforced on 5.5.x
      merged from mysql-5.5.8-release tree,
      revision: ramil@mysql.com-20101203174908-217tdkn150vieha9

Noted in 5.5.8 changelog.

Setting the read_only system variable at server startup did not work.

Pushed into mysql-trunk 5.6.1 (revid:alexander.nozdrin@oracle.com-20101216181820-7afubgk2fmuv9qsb) (version source revid:alexander.nozdrin@oracle.com-20101216181820-7afubgk2fmuv9qsb) (merge vers: 5.6.1) (pib:23)

Pushed into mysql-5.5 5.5.9 (revid:jonathan.perkin@oracle.com-20101216101358-fyzr1epq95a3yett) (version source revid:jonathan.perkin@oracle.com-20101216101358-fyzr1epq95a3yett) (merge vers: 5.5.9) (pib:24)

Pushed into mysql-5.5 5.5.9 (revid:georgi.kodinov@oracle.com-20101217124733-p1ivu6higouawv8l) (version source revid:ramil@mysql.com-20101207170854-z1zt6tjphp0ebu1y) (merge vers: 5.5.8) (pib:24)

Pushed into mysql-trunk 5.6.1 (revid:georgi.kodinov@oracle.com-20101217125013-y8pb3az32rtbplc9) (version source revid:anitha.gopi@sun.com-20101210041312-50t9adyhwwybsm6x) (merge vers: 5.6.1) (pib:24)