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

[2 Dec 2010 18:18] Leandro Morgado
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.
[3 Dec 2010 1:01] Davi Arnaut
This need to be properly verified. Please give full instructions and test with the latest available version.
[3 Dec 2010 9:04] Leandro Morgado
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.
[3 Dec 2010 9:30] Leandro Morgado
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;
[3 Dec 2010 11:02] Davi Arnaut
Leandro,

Thanks, more details are better. So, the read-only option does not work when set from the command-line?
[3 Dec 2010 11:07] Leandro Morgado
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.
[3 Dec 2010 11:10] Davi Arnaut
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);
[3 Dec 2010 11:11] Davi Arnaut
and does not work when set from the command line.
[3 Dec 2010 11:16] Davi Arnaut
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>
[3 Dec 2010 11:17] Davi Arnaut
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?
[3 Dec 2010 11:18] Leandro Morgado
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
[3 Dec 2010 11:37] Leandro Morgado
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.
[3 Dec 2010 11:41] Davi Arnaut
Verified as described. Thanks!
[3 Dec 2010 13:20] Davi Arnaut
> 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."
[3 Dec 2010 16:00] Paul Dubois
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
[3 Dec 2010 17:19] 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/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().
[3 Dec 2010 17:32] Davi Arnaut
The (rather unacceptable) workaround is to SET GLOBAL READ_ONLY=TRUE at run-time.
[7 Dec 2010 17:10] 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/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
[8 Dec 2010 19:41] Paul Dubois
Noted in 5.5.8 changelog.

Setting the read_only system variable at server startup did not work.
[16 Dec 2010 21:47] Bugs System
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)
[16 Dec 2010 22:32] Bugs System
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)
[17 Dec 2010 12:50] Bugs System
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)
[17 Dec 2010 12:55] Bugs System
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)