How to Prevent tables from being cleared

In general, in the future, we will prevent users from deleting or clearing tables and data. You can directly grant them a small amount of permissions.
For example, to prevent users from performing truncate operations, you can grant the following permissions:

t_girl=# create role ytt3 with login connection limit 1  password 'ytt3';    CREATE ROLEt_girl=# alter schema ytt owner to ytt3;ALTER SCHEMAt_girl=# grant select on all tables in schema ytt to ytt3;GRANT

Now log on to the new user ytt3 and execute TRUNCATE.

bash-4.1$ psql -U ytt3 t_girlpsql (9.3.4)Type "help" for help.t_girl=> truncate table j2;ERROR:  permission denied for relation j2

However, during the test, the Administrator is too lazy to assign various sub-permissions for convenience. Therefore, when creating a table, we have to apply corresponding restrictions to this table. Of course, this is not recommended in the production environment.
This implementation is relatively simple. It is okay to create a statement-based trigger.

t_girl=# \sf prevent_truncateCREATE OR REPLACE FUNCTION public.prevent_truncate() RETURNS trigger LANGUAGE plpgsqlAS $functio$BEGINRAISE EXCEPTION 'Prevent "%"  to be truncated!', TG_TABLE_SCHEMA||TG_TABLE_NAME;RETURN NEW;END;$function$

t_girl=# \d j2        Table "ytt.j2" Column |  Type   | Modifiers --------+---------+----------- id     | integer |  str2   | text    | Triggers:    trigger_truncate_before BEFORE TRUNCATE ON j2 FOR EACH STATEMENT EXECUTE PROCEDURE ytt.prevent_truncate()

 

t_girl=# truncate table j2;ERROR:  Prevent "ytt.j2"  to be truncated!

This method is only available for databases that provide this function. For example, MySQL triggers only provide row-based operations, so the statement operations cannot be triggered. Therefore, it is troublesome to implement this in MySQL. Or, start with the permission,

mysql> truncate table j2;ERROR 1142 (42000): DROP command denied to user 'ytt3'@'localhost' for table 'j2'

Or, encapsulate database operations with SPROC,

+------------------------------------+| Error                              |+------------------------------------+| Prevent t_girl.j2 to be truncated! |+------------------------------------+1 row in set (0.00 sec)