Read only access to a particular table only

Question

I have a mariadb database which contains number of tables. Out of these tables, I have a table which stores new record only. How do I make the table such that, no update or delete command executes on this table. This will help me to stop the data manipulation in future. Is it possible? If so, how should I proceed?

Does this answer your question? How do I enforce a write-once then read only database table in SQL? — mustaccio, Dec 19 '23 at 13:04

score 1 · Answer 1 · answered Dec 19 '23 at 14:16

Short answer:
Don't grant UPDATE or DELETE privileges on that table.
At least not to "Regular" Users.

"Regular" users probably shouldn't be able to even read from a table like this.
Most likely, Triggers will write to this table on their behalf, but they'll never need to interact with it themselves.
The consumers of this Data will need SELECT access, but probably nothing more.
However, some "housekeeping" process will need to be able to get rid of this Data, when it's no longer required, and so [the owner of] that process will need DELETE access.

So there's [at least] three distinct User groups that may or may need to work with this Data and you have to come up with way to [easily] support that.
(Hint: This is what Roles are for).

score 0 · Answer 2 · answered Dec 19 '23 at 14:02

Yes, it is possible to configure a MariaDB table to disallow update and delete operations. This can be achieved through the use of triggers.

Create a BEFORE UPDATE Trigger: This trigger will be activated before an update operation is performed on the table. You can design this trigger to either raise an error or silently cancel the update operation, thus preventing any changes to existing records.
```
 DELIMITER //
 CREATE TRIGGER prevent_update
 BEFORE UPDATE ON your_table_name
 FOR EACH ROW
 BEGIN
 SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Update 
 operations are not allowed on this table.';
 END;
 //
 DELIMITER ;
```

Create a BEFORE DELETE Trigger: Similar to the update trigger, this trigger will be activated before any delete operation. It will prevent the deletion of any records from the table.

 DELIMITER //
 CREATE TRIGGER prevent_delete
 BEFORE DELETE ON your_table_name
 FOR EACH ROW
 BEGIN
 SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Delete 
 operations are not allowed on this table.';
 END;
 //
 DELIMITER ;

Read only access to a particular table only

2 Answers2