Is any working way to set a MySQL row to READ-ONLY?

Question

I have read all threads here and did not found any simple and working solution to my question. So i am running site on php script which is using DB on 10.1.37-MariaDB.

I have there 1 table in that i need to set only several rows (from 90k) to be read only. Not updated or deleted by php script anymore.

All these rows have unique entry_id with number values, lets say entry_id = 12345.

In needed table i have this also:

SELECT * FROM `cb_aggregator_content` ORDER BY `model_last_checked` ASC

Tried to add:

SELECT * FROM `cb_aggregator_content` WHERE `entry_id`=12345 FOR UPDATE;

Got only errors. Thanks.

`SELECT ... FOR UPDATE` just locks the row during the transaction, it doesn't make it read-only. — Barmar, Apr 06 '19 at 15:55
I would not do this on mysql level, I would build the access control in your php application. — Shadow, Apr 06 '19 at 16:31
You could use a [`BEFORE UPDATE` and `BEFORE DELETE` trigger](https://dev.mysql.com/doc/refman/8.0/en/trigger-syntax.html) to retain the values of the desired rows. `IF NEW.entry_id IN(12345,12346) THEN NEW.* = OLD.*; END IF;` replacing `NEW.*` and `OLD.*` with the available columns. This will facilitate a read-only row, causing the values to equal the original values, and the `UPDATE` modifying 0 rows. Where the `BEFORE DELETE`, you could throw an error. — Will B., Apr 06 '19 at 16:34

score 4 · Accepted Answer · answered Apr 06 '19 at 16:52

Here's a potential solution:

Create a table to store the primary key values you want to be read-only:

CREATE TABLE cb_aggregator_content_readonly (
  entry_id INT PRIMARY KEY
);
INSERT INTO cb_aggregator_content_readonly SET entry_id = 12345;

Make triggers to throw an error if you try to update or delete rows with entry id's that are supposed to be read-only:

DELIMITER ;;
CREATE TRIGGER no_upd_content BEFORE UPDATE ON cb_aggregator_content
FOR EACH ROW BEGIN
  IF EXISTS(SELECT * FROM cb_aggregator_content_readonly WHERE entry_id = OLD.entry_id) THEN
    SIGNAL SQLSTATE '45000' 
      SET MESSAGE_TEXT = 'Please do not update entry ';
  END IF;
END;;

CREATE TRIGGER no_del_content BEFORE DELETE ON cb_aggregator_content
FOR EACH ROW BEGIN
  IF EXISTS(SELECT * FROM cb_aggregator_content_readonly WHERE entry_id = OLD.entry_id) THEN
    SIGNAL SQLSTATE '45000' 
      SET MESSAGE_TEXT = 'Please do not delete entry';
  END IF;
END;;
DELIMITER ;

Now it should prevent you from updating or deleting the rows you want to keep:

mysql> delete from cb_aggregator_content where entry_id = 123;
Query OK, 1 row affected (0.02 sec)

mysql> delete from cb_aggregator_content where entry_id = 12345;
ERROR 1644 (45000): Please do not delete entry

If you want to add more entry_id's to the set of those to keep, just insert more values to the cb_aggregator_content_readonly table.

Bill Karwin - many thanks for solution, you saved me. Tested, everything works like a charm. Thank you and good luck ! — Smeklinis, Apr 06 '19 at 17:41

score 0 · Answer 2 · answered Apr 06 '19 at 15:59

MySQL doesn't have per-row access control. The way to do this is to make the whole table read-only, but create a special user that has write permission to it. Then write a stored procedure that updates the table, and make it owned by that user. The stored procedure can check whether entry_id = 12345 and not perform the update.

SELECT ... FOR UPDATE doesn't prevent writing the row, it just locks it for the duration of the transaction. Other transactions will simply be blocked until the transaction completes, but then they'll be able to update the row.

Jujubes · Answer 3 · 2019-04-06T16:36:30.930

0

You could change the access of the user accessing the database to have only read privileges

or you can create another column call it "locked" and add a parameter in your queries to not update any rows with a value in the locked.

Set the locked field to a datetime and a default of null. that way you can select values that are locked and create an order by locked date. you could also add another field as locked_by and append a user id.

Alternatively, you could create a locked table and use joins - if the data being locked is not standard i would opt for this

ex:

  UPDATE 
   date = NOW()
  FROM table
  WHERE id = 123 AND locked IS NULL

edited Apr 06 '19 at 16:36

answered Apr 06 '19 at 16:30

Jujubes

424
1
6
36

On that table rows are changed/deleted by script every day. And i need to set only several row to read only. So did not can user privileges for all DB. – Smeklinis Apr 06 '19 at 16:37
Instead of deleting the rows you can just use a status field (locked) you would need to adjust your queries which could take forever but its an option – Jujubes Apr 06 '19 at 16:57
Im assuming you have a cron which runs at a particular time and then changes the dataset. Look at what the purpose of the job is to do. i suspect the answer is right there. a database is meant to be interactive. it is not a white paper so if this functionality is desired you need to build it into your design (such as using some sort of status field) – Jujubes Apr 06 '19 at 16:58
look at @Bill Karwin answer because that is essentially what i am saying – Jujubes Apr 06 '19 at 17:00

Is any working way to set a MySQL row to READ-ONLY?

3 Answers3