Database Logging Security Solutions

Database Logging Security Solutions http://netkiller.github.io/journal/mysql.security.html Mr.Neo Chen(Netkiller),Chen Jingfeng (bg7nyt)

China Guangdong province Shenzhen Khe Sanh Street, Longhua District, civil Administration
518131
+86 13113668890
+86 755 29812080
<[email protected]>

Copyright © Http://netkiller.github.io

Copyright Notice

Reprint please contact the author, please be sure to indicate the original source of the article and the author's information and this statement.

Document Source: Http://netkiller.github.io Http://netkiller.sourceforge.net

2014-08-19

Summary

Anti-delete, release, flexibility solutions

My series of documents

Netkiller Architect Codex	Netkiller Developer Codex	Netkiller PHP Codex	Netkiller Python Codex	Netkiller Testing Codex	Netkiller Cryptography Codex
Netkiller Linux Codex	Netkiller Debian Codex	Netkiller CentOS Codex	Netkiller FreeBSD Codex	Netkiller Shell Codex	Netkiller Security Codex
Netkiller Web Codex	Netkiller Monitoring Codex	Netkiller Storage Codex	Netkiller Mail Codex	Netkiller Docbook Codex	Netkiller Version Codex
Netkiller Database Codex	Netkiller PostgreSQL Codex	Netkiller MySQL Codex	Netkiller NoSQL Codex	Netkiller LDAP Codex	Netkiller Network Codex
Netkiller Cisco IOS Codex	Netkiller H3C Codex	Netkiller Multimedia Codex	Netkiller Perl Codex	Netkiller Amateur Radio Codex	Netkiller DevOps Codex

Directory

• 1. What is anti-deletion, anti-essays change
• 2. Why do you want to do anti-deletion, anti-modification restrictions
• 3. When to do anti-deletion, anti-modification restrictions
• 4. Where to do anti-deletion, anti-essays and change restrictions
• 5. Who to do anti-deletion, anti-change restrictions
• 6. How to do anti-deletion, anti-essays to change the limit
  • 6.1. Restrict deletion
  • 6.2. Restrictions on modification
  • 6.3. Version control

1. What is anti-deletion, anti-essays change

Prohibit data deletion, once data increase does not allow data to be deleted by anyone

Prohibit data modification, once the data is established does not allow the data to be modified operation

2. Why do you want to do anti-deletion, anti-modification restrictions

Many times our data is only increased and does not delete data. Some sensitive sub-segments once the data is not allowed to be modified at home, such as the Bank Account table of funds sub-paragraph.

Another reason is that we prevent mis-operation

3. When to do anti-deletion, anti-modification restrictions

I think that in the database design should consider these problems, if it is found that the data has been deleted or modified, it is not too late to mend, we can not allow the recurrence.

You can cancel the user's DELETE permission so that it can only do query operations, but modify (UPDATE) it? There's nothing you can do! If canceling the update program will not work correctly.

4. Where to do anti-deletion, anti-essays and change restrictions

You should think about these problems at the beginning of programming, and if you don't, you can only modify existing logic. As a general practice, all tables add a Delete state sub-segment, and the delete operation is the update state. The downside of this approach is that garbage data will keep expanding.

5. Who to do anti-deletion, anti-change restrictions

I think there are two kinds of people, one is a DBA and one is a developer. Here mainly the database section.

6. How to do anti-deletion, anti-essays to change the limit6.1. Restrict deletion

CREATE definer= ' dba ' @ ' 192.168.% ' TRIGGER ' account_before_delete ' before delete on ' account ' for each ROW beginsignal sqlst ATE ' 45000 ' SET message_text = ' Permission denied ', Mysql_errno = 1001; END

Delete the record in the Account table, the database throws an exception Permission denied

6.2. Restrictions on modification

CREATE definer= ' dba ' @ ' 192.168.% ' TRIGGER ' members_before_update ' before update on ' members ' for each ROW beginset NEW. ' ID ' = Old.id; SET NEW. ' Name ' = Old.name; SET NEW. ' Chinese_name ' = old.chinese_name; SET NEW. ' English_name ' = old.english_name; SET NEW. ' Sex ' = old.sex; SET NEW. ' Address ' = old.address; SET NEW. ' ZipCode ' = Old.zipcode; SET NEW. ' Country_code ' = Old.country_code; SET NEW. ' Mobile ' = old.mobile; SET NEW. ' Email ' = old.email; SET NEW. ' QQ ' = OLD.QQ; SET NEW. ' Question ' = old.question; SET NEW. ' Answer ' = old.answer; SET NEW. ' CTime ' = old.ctime; END

We overwrite the modified data before the database is modified so that the data remains unchanged after the update.

6.3. Version control

Main Table

CREATE TABLE ' article ' (' article_id ' Mediumint (8) UNSIGNED not NULL auto_increment, ' cat_id ' SMALLINT (5) NOT null DEFAULT ' 0 ', ' title ' varchar (LONGTEXT) NOT null default ' ', ' content ' is not null, ' author ' varchar (+) NOT null default ' ', ' keywords ' VARCHAR (255) Not NULL DEFAULT ', PRIMARY KEY (' article_id '), INDEX ' cat_id ' (' cat_id ')) engine=myisamrow_format= Defaultauto_increment=1

This version of the control table, used to record each change

CREATE TABLE ' article_history ' (' ID ' mediumint (8) UNSIGNED not NULL auto_increment, ' article_id ' Mediumint (8) UNSIGNED not NULL, ' cat_id ' SMALLINT (5) NOT null default ' 0 ', ' title ' VARCHAR ($) NOT null default ' ', ' content ' longtext not null, ' auth or ' varchar (+) NOT null default ' ', ' keywords ' varchar (255) is not null default ' ', PRIMARY KEY (' id '), INDEX ' article_id ' (' AR ticle_id ')) engine=myisamrow_format=defaultauto_increment=1

Version control triggers

DROP TRIGGER article_history;delimiter//create TRIGGER article_history before update on article for each rowbegininsert I NTO article_history SELECT * from article WHERE article_id = old.article_id; END; DELIMITER;

Any changes to the data will be copied to the history table, we can compare the changes of two version data at any time, I also developed a similar diff tool for this, can be compared by line, through color changes in the actual data.