SQLyog in 'Read-Only mode'
With the read-only flag checked in the settings for a connection, 'Read-Only' is enforced client-side from the SQLyog GUI and the editor no matter if user's server-side privileges allows for writes. In 'Read-Only mode' you will be able to retrieve data from the server, use user variables, define SESSION-scope of server variables and everything else you need to work with data as long as you don't need to write to the server.
In the GUI, controls that trigger statements that write to the database are 'greyed out' and do nothing. In the editor the same statements will be caught and not sent. This is accomplished by filtering against a 'positive list' of statements that are allowed. They are SHOW, SELECT, DO, DESCRIBE, EXPLAIN, USE, SET (except for SET GLOBAL|SET @@GLOBAL..), HELP, HANDLER, CHECK, ANALYZE, OPTIMIZE as well all transactional statements.
Further we also allow for CALL and please observe that 'Read-Only mode' is not enforced when executing a Stored Procedure or Function. It is also not enforced in the SJA - no matter whether launching an SJA-job from the GUI or from command-line. The SJA simply does not read this flag.
For the reasons above - and also because any user can easily turn the 'Read-Only' flag OFF - you should not consider this feature a 'security feature' (not a bullet-proof one, at least), but rather a 'convenience feature': With this flag ON, you will not accidentally (due to distraction, accidentally clicking with the mouse the wrong place or whatever) execute a statement that writes to the database. You are maybe not 'fully secure', but you are 'safe'.
There are a few cases where there is a 'trade-off' involved, because it is sort of 'grey area' what 'read-only' and 'write' actually means exactly. Examples of this are:
- FLUSH. Executing a FLUSH statement does not write (from client to server) but you may argue that FLUSH causes the server itself to write (from memory to disk). We have decided to allow for it.
- Prepared Statements (PREPARE, EXECUTE etc.). We don't allow those, mostly because it in some cases would be extremely complex to determine if a Prepared Statement writes or not. And note that this is actually the opposite choice than with Stored Procedures and Functions.