What's the difference between --read-only and just permit SELECT queries?

Question

I'm using Amazon Aurora and I have two endpoints, my endpoint which can read/write are having some issues to execute queries, these are the issues:

SQLSTATE[HY000]: General error: 1290 The MySQL server is running with the --read-only option so it cannot execute this statement, query was: UPDATE `integration` SET `setup_type` = ? WHERE (integration_id='4')

---

SQLSTATE[HY000]: General error: 1290 The MySQL server is running with the --read-only option so it cannot execute this statement, query was: UPDATE `indexer_state` SET `indexer_id` = ?, `status` = ?, `updated` = '2018-03-12 14:38:45', `hash_config` = ? WHERE (state_id='1')

Amazon uses the global --read-only configuration in one of the servers. What's the difference between to use --read-only and a user which can just execute SELECT queries?

Answer 1

When a user gets read only permission (select), it will not be able to write/update/etc even when the global read-only value is 0. However, a user with \[SUPER\] privilege, will be able to write on a server where global read-only value is set to 1.

One more difference is that read only permissions are very granular, while read-only variable is a server wide variable.

Usually, you'd give SELECT permissions to users who you want them to read from a Server/DB/Table where other users can write. On the other hand, you set read-only variable to 1 when you want all but super users to be able to write to. For example, a read slave.

Answer 2

To the user, probably no difference.

To the system integrity, --read-only prevents you from accidentally writing to a Slave, thereby messing up the Replication integrity.

Aurora has no control (I assume) over whether you grant write permission and happen to let them hit a Slave with an UPDATE. This is an easy, simple, prevention.