HiveBrain v1.2.0
Get Started
← Back to all entries
principlesqlMinor

Revoke write privileges vs setting database to readonly

Submitted by: @import:stackexchange-dba··
0
Viewed 0 times
privilegeswriterevokedatabasesettingreadonly

Problem

I am planning to do a database migration to a new server. During the transition stage, I do not want Apache user to be able to write anything to the database. I have two options.

-
Revoke write privileges:

REVOKE INSERT, UPDATE ON `mydb`.* FROM 'apache'@'localhost';
FLUSH PRIVILEGES;


-
Set database to read-only mode:

FLUSH TABLES WITH READ LOCK;
SET GLOBAL read_only = 1;


Are there any practical considerations for choosing one over the other?

Solution

I would choose Option 2

  • If you use Option 1 and revoke privileges, you have to put them back. The mysql grant tables are MyISAM. Should any crash, human error, or other unexpected event corrupt the tables, you have a mess to clean up.



  • Only those with SUPER privilege can perform writes when read_only is enabled. SUPER is not a database-level grant.



  • With Option 2, grants remain unchanged on disk and in memory.



Option 2 could also be written as

FLUSH TABLES;
SET GLOBAL read_only = 1;


That way, you only have to run SET GLOBAL read_only = 0; to get writes going again. FLUSH TABLES WITH READ LOCK does not halt InnoDB writes to Transaction Logs, Redo Logs, an Undo Logs. That's why SET GLOBAL read_only = 1; is your friend.
CAVEAT

In my early days a a DBA, I used to do this:

CREATE TABLE mysql.usercopy LIKE mysql.user;
CREATE TABLE mysql.root     LIKE mysql.user;
INSERT INTO mysql.usercopy SELECT * FROM mysql.user;
INSERT INTO mysql.root SELECT * FROM mysql.user WHERE user='root';
TRUNCATE TABLE mysql.user;
INSERT INTO mysql.user SELECT * FROM mysql.root;
FLUSH PRIVILEGES;


When I was done, I did this

TRUNCATE TABLE mysql.user;
INSERT INTO mysql.user SELECT * FROM mysql.usercopy;
FLUSH PRIVILEGES;


Then, I discovered SUPER.
EPILOGUE

Go with Option 2 as is, or change FLUSH TABLES WITH READ LOCK; to FLUSH TABLES;

Code Snippets

FLUSH TABLES;
SET GLOBAL read_only = 1;
CREATE TABLE mysql.usercopy LIKE mysql.user;
CREATE TABLE mysql.root     LIKE mysql.user;
INSERT INTO mysql.usercopy SELECT * FROM mysql.user;
INSERT INTO mysql.root SELECT * FROM mysql.user WHERE user='root';
TRUNCATE TABLE mysql.user;
INSERT INTO mysql.user SELECT * FROM mysql.root;
FLUSH PRIVILEGES;
TRUNCATE TABLE mysql.user;
INSERT INTO mysql.user SELECT * FROM mysql.usercopy;
FLUSH PRIVILEGES;

Context

StackExchange Database Administrators Q#74670, answer score: 6

Revisions (0)

No revisions yet.