principlesqlMinor
Revoke write privileges vs setting database to readonly
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:
-
Set database to read-only mode:
Are there any practical considerations for choosing one over the other?
-
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
Option 2 could also be written as
That way, you only have to run
CAVEAT
In my early days a a DBA, I used to do this:
When I was done, I did this
Then, I discovered SUPER.
EPILOGUE
Go with Option 2 as is, or change
- 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.