data masking for Ushahidi and put to S3 script

maskdata.sql

USE ushahidi;

SET NAMES utf8;

CREATE TABLE IF NOT EXISTS `maskdatas` (
        `id` int(11) unsigned NOT NULL auto_increment,
        `fromdata` varchar(255) default NULL,
        `todata` varchar(255) default NULL,
        PRIMARY KEY  (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

INSERT INTO maskdatas (fromdata ,todata) VALUES ('0','*');
INSERT INTO maskdatas (fromdata ,todata) VALUES ('1','*');
INSERT INTO maskdatas (fromdata ,todata) VALUES ('2','*');
INSERT INTO maskdatas (fromdata ,todata) VALUES ('3','*');
INSERT INTO maskdatas (fromdata ,todata) VALUES ('4','*');
INSERT INTO maskdatas (fromdata ,todata) VALUES ('5','*');
INSERT INTO maskdatas (fromdata ,todata) VALUES ('6','*');
INSERT INTO maskdatas (fromdata ,todata) VALUES ('7','*');
INSERT INTO maskdatas (fromdata ,todata) VALUES ('8','*');
INSERT INTO maskdatas (fromdata ,todata) VALUES ('9','*');
INSERT INTO maskdatas (fromdata ,todata) VALUES ('０','*');
INSERT INTO maskdatas (fromdata ,todata) VALUES ('１','*');
INSERT INTO maskdatas (fromdata ,todata) VALUES ('２','*');
INSERT INTO maskdatas (fromdata ,todata) VALUES ('３','*');
INSERT INTO maskdatas (fromdata ,todata) VALUES ('４','*');
INSERT INTO maskdatas (fromdata ,todata) VALUES ('５','*');
INSERT INTO maskdatas (fromdata ,todata) VALUES ('６','*');
INSERT INTO maskdatas (fromdata ,todata) VALUES ('７','*');
INSERT INTO maskdatas (fromdata ,todata) VALUES ('８','*');
INSERT INTO maskdatas (fromdata ,todata) VALUES ('９','*');
INSERT INTO maskdatas (fromdata ,todata) VALUES ('〇','*');
INSERT INTO maskdatas (fromdata ,todata) VALUES ('一','*');
INSERT INTO maskdatas (fromdata ,todata) VALUES ('二','*');
INSERT INTO maskdatas (fromdata ,todata) VALUES ('三','*');
INSERT INTO maskdatas (fromdata ,todata) VALUES ('四','*');
INSERT INTO maskdatas (fromdata ,todata) VALUES ('五','*');
INSERT INTO maskdatas (fromdata ,todata) VALUES ('六','*');
INSERT INTO maskdatas (fromdata ,todata) VALUES ('七','*');
INSERT INTO maskdatas (fromdata ,todata) VALUES ('八','*');
INSERT INTO maskdatas (fromdata ,todata) VALUES ('九','*');


delimiter //
CREATE FUNCTION maskdata(txt LONGTEXT) RETURNS LONGTEXT
BEGIN
  DECLARE done INT DEFAULT 0;
  DECLARE f,t varchar(255);
  DECLARE result TEXT;
  DECLARE mc CURSOR FOR SELECT fromdata,todata FROM maskdatas;
  DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;

  IF txt IS NULL THEN
    RETURN NULL;
  END IF;

  OPEN mc;
  SET result = txt;
  REPEAT
    FETCH mc INTO f, t;
    IF done < 1 THEN
      SET result = REPLACE(result,f,t);
    END IF;
  UNTIL 0 < done END REPEAT;
  CLOSE mc;
  RETURN result;
END;

CREATE PROCEDURE maskusers()BEGIN  DECLARE done INT DEFAULT 0;
  DECLARE c INT DEFAULT 0;
  DECLARE i INT(11) DEFAULT 0;
  DECLARE mc CURSOR FOR SELECT id from users order by id;
  DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;

  OPEN mc;
  REPEAT
    FETCH mc INTO i;
    IF done < 1 THEN
      SET c = c + 1;
      UPDATE users SET name='モデレータ',email= CONCAT('mod' ,c ,'@example.com'),username = CONCAT('mod',c) WHERE id = i;
    END IF;
  UNTIL 0 < done END REPEAT;
  CLOSE mc;
END;
//
delimiter ;

UPDATE alert SET alert_recipient = 'foo@example.com';UPDATE comment SET comment_author='comment_author',comment_email = 'bar@example.com',comment_description=maskdata(comment_description);UPDATE idp SET idp_idnumber='0',idp_orig_idnumber='1',idp_fname='idp_fname',idp_lname='idp_lname',idp_email='idp@example.com',idp_phone=maskdata(idp_phone);
UPDATE incident SET incident_title=maskdata(incident_title),incident_description=maskdata(incident_description);
UPDATE incident_lang SET incident_title=maskdata(incident_title),incident_description=maskdata(incident_description);UPDATE incident_person SET person_first ='太郎',person_last='災害',person_email='sinsai@example.com',person_phone=maskdata(person_phone);
UPDATE media SET media_title=maskdata(media_title),media_description=maskdata(media_description);UPDATE message SET service_messageid='svc_messageid',message_from='msgfrom',message_to='msgto', message=maskdata(message),message_detail=maskdata(message_detail);UPDATE reporter SET service_userid='service_userid', service_account='svc_account', reporter_first='次郎',reporter_last='震災', reporter_email = 'sinsai@examle.com',reporter_phone=maskdata(reporter_phone);
UPDATE sharing_incident SET incident_title=maskdata(incident_title);
UPDATE translatereports SET incident_description=maskdata(incident_description);
CALL maskusers();
UPDATE verified SET verified_comment=maskdata(verified_comment);

masking.sh

#!/bin/bash -e
BACKUPDIR=/home/ubuntu/dbbackup
MASKEDDIR=/home/ubuntu/masked
MYSQLUID=osm
MYSQLPSS=osmosm
echo "-------------------"
echo `date`
echo "-------------------"
echo
echo "-- get latest backup from sinsai-db-backup --"
LATEST_BK=$(s3cmd -c sinsai-db-backup.cfg ls s3://sinsai-db-backup | cut -f7 -d' ' | tail -1)
s3cmd -c sinsai-db-backup.cfg get $LATEST_BK $BACKUPDIR
echo "-- download complete  --"

ls -tr `find $BACKUPDIR -name "*.gz" -type f -print` | tail -1 | xargs gunzip
LATEST=$(ls -tr $(find $BACKUPDIR -name "*.sql" -type f -print) | tail -1)

mysqladmin -u$MYSQLUID -p$MYSQLPSS -f drop ushahidi
echo "-- import latest dump file --"
echo $LATEST
mysql -u$MYSQLUID -p$MYSQLPSS < $LATEST
echo "-- import complete         --"

echo "-- masking database...     --"
mysql -u$MYSQLUID -p$MYSQLPSS < maskdata.sql
echo "-- masking complete        --"

MASKED_FILE=$MASKEDDIR/ushahidi_masked_`date '+%Y%m%d_%H%M%S'`.sql.gz

echo "-- dumping database...     --"
time mysqldump --opt -u$MYSQLUID -p$MYSQLPSS --databases ushahidi | gzip > $MASKED_FILE
echo "-- dumping complete        --"

echo "-- put masked file to sinsai-public --"
s3cmd -c sinsai-public.cfg put $MASKED_FILE s3://sinsai-public
s3cmd -c sinsai-public.cfg put $MASKED_FILE s3://sinsai-public/ushahidi_masked_latest.sql.gz
echo "-- put complete --"

echo "-- remove working files... --"
rm -f $LATEST
rm -f $MASKED_FILE
echo "-- remove complete --"

MASKING_README

中心となるスクリプトファイルは、
 convert_to_masked.sh
  作業中に、
   /home/ubuntu/dbbackup
   /home/ubuntu/masked
  を使う。

この中で使用しているSQLは、
  maskdata.sql

S3Backetにアクセスする為にs3cmdを使うその設定ファイルは、
 sinsai-db-backup.cfg
 sinsai-public.cfg

動作としては、
 sinsai-db-backupのbacketからダンプを取得
 locahostのMySQLにデータベースをdrop create
  localhostのMySQLには予め、osm/osmosm なユーザが必要。
  localhostのMySQLには予め、ushahidiデータベースが必要、データベースがあれば良く中身は空でもよい。
 maskdata.sqlを使って、中身を無害化
 出来たデータベースをダンプして、sinsai-publicのバケットに突っ込む