How to prohibit the removal of any rows in a specific mysql table ?

Question

Is there a way to configure a mysql table so that writting and reading is possible but not deleting ?

Exampe is : a table that contains many log that are legally important and that must never be deleted !

Answer 1

Revoke the delete privilege from all users for that table.

Answer 2

You could do as he says below or... What you could do, is give users 'roles' in number form, then pass this number to a script which would remove the row... but if the passed number is below a certain 'minimum role expectation' then they are denied access to the script?

Answer 3

You would just grant the INSERT and SELECT privileges on the table in question (this prevents the possibility of a row being changed)

GRANT INSERT,SELECT ON mydb.mytable 
TO secureduser@localhost 
IDENTIFIED BY 'password';

From this you would go on to add wider permissions to the other tables in the database for this user.

Also, check out the Archive Storage Engine, which is custom designed for this kind of audit-trail application.

Answer 4

Another option is to use the Archive storage engine. It only allows insertion, no updates or deletes (from anyone - even a privileged account)