基于shell自动完成mysql格式审计的项目

最新推荐文章于 2023-08-21 09:24:31 发布

huoyuanshen

最新推荐文章于 2023-08-21 09:24:31 发布

阅读量305

点赞数

分类专栏： shell Mysql综合

本文链接：https://blog.csdn.net/huoyuanshen/article/details/73546360

版权

Mysql综合同时被 2 个专栏收录

65 篇文章 0 订阅

订阅专栏

shell

11 篇文章 1 订阅

订阅专栏

1，对于mysql字段长度，表名长度等规范问题，完成shell自动校验，结果存入数据库中进行查看。

项目目录结构:

├── config
│ ├── config_audit.sh
│ └── config_db.sh
├── lib
│ ├── function.sh
│ └── main_in.sh
└── main.sh

#规则参考

url:http://blog.chinaunix.net/xmlrpc.php?r=blog/article&uid=28437434&id=4776846

http://blog.csdn.net/JeffreyNicole/article/details/48198587

https://zhidao.baidu.com/question/812502421126049892.html

http://blog.chinaunix.net/uid-20785090-id-5031014.html

具体脚本内容 :

1，数据库脚本:

create database explaindb character set 'utf8'; #version 5.7

CREATE TABLE `audit_tb` (
`id` int(11) NOT NULL AUTO_INCREMENT COMMENT '主键id',
`db_name` varchar(100) NOT NULL COMMENT '库名',
`tb_name` varchar(100) NOT NULL COMMENT '表名',
`col_name` varchar(100) NOT NULL COMMENT '列名',
`audit_type` varchar(50) NOT NULL COMMENT '审核类型:schema,table,column',
`audit_name` varchar(100) NOT NULL COMMENT '审计的具体库名、表名、列名',
`audit_title` varchar(100) NOT NULL COMMENT '审计类目标题,如table_name_length',
`audit_form` int(11) NOT NULL COMMENT '审计方式：1，比较大小；2，字符串比较；',
`audit_logic` varchar(100) DEFAULT NULL COMMENT '基于给定的审计方式确定的具体逻辑',
`cur_value` varchar(1000) DEFAULT NULL COMMENT '当前值',
`threshold` varchar(1000) DEFAULT NULL COMMENT '阈值',
`comment` varchar(1000) NOT NULL COMMENT '备注',
`status` int(11) NOT NULL COMMENT '状态:0:未通过；1:通过。2:异常',
`updated` datetime DEFAULT NULL COMMENT '最后更新时间',
PRIMARY KEY (`id`),
UNIQUE KEY `audit_type` (`audit_type`,`audit_name`,`audit_title`,`db_name`,`tb_name`,`col_name`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=1021 DEFAULT CHARSET=utf8 COMMENT='审计结果表';

2,审计规则脚本:config_audit.sh

:<<!
规则定义:
rule="审计项目名称,审计方式,审计逻辑,阈值,中文解释";
rule:有table_rule01,column_rule01,index_rule01分别表示表，列，索引等的规则，表名当前条目适应的范围
审计项目名称:table_name_length_limit，可以直观理解为表名长度限制的校验
审计方式:
	1,表示数值大小比较，对应逻辑有 gt,ge,e,le,lt,ne
	2,表示文本内容比较，对应逻辑有 ==,!=  。比较内容忽略大小写。
阈值：如果审计结果与该阈值比较为真，则审计结果插入数据库。
中文解释:对“审计项目名称”的中文解释


规则参考url:
http://blog.csdn.net/JeffreyNicole/article/details/48198587
https://zhidao.baidu.com/question/812502421126049892.html
http://blog.chinaunix.net/uid-20785090-id-5031014.html
!


table_rule01="
table_name_length_limit,1,gt,10,表名超过长度限制
table_engine_innodb_check,2,!=,INNODB,表的存储引擎须是innodb
";


column_rules="
column_name_length_limit,1,gt,9,列名超过长度限制
column_coment_is_null,2,==,null,列的备注不能为空
";

3，数据库连接脚本config_db.sh

#被审计数据库配置
user=root
password=123123
database=uei #被审计的具体库
port=3306
host=127.0.0.1

#查询结果存放数据库配置
touser=root
topassword=123123
todatabase=explaindb	#需提前建好
totable=audit_tb	#需提前建好
toport=3306
tohost=127.0.0.1

4，通用方法脚本function.sh



#editplus中寻找关键字： (.*)\n   替换关键字: (case when \1 is null or trim(\1) = '' then 'null' else \1 end),\"reqreq\",
#查询information_schema中tables表的sql。分割符： reqreq
sql_table="select concat((case when TABLE_CATALOG is null or trim(TABLE_CATALOG) = '' then 'null' else TABLE_CATALOG end),\"reqreq\",(case when TABLE_SCHEMA is null or trim(TABLE_SCHEMA) = '' then 'null' else TABLE_SCHEMA end),\"reqreq\",(case when TABLE_NAME is null or trim(TABLE_NAME) = '' then 'null' else TABLE_NAME end),\"reqreq\",(case when TABLE_TYPE is null or trim(TABLE_TYPE) = '' then 'null' else TABLE_TYPE end),\"reqreq\",(case when ENGINE is null or trim(ENGINE) = '' then 'null' else ENGINE end),\"reqreq\",(case when VERSION is null or trim(VERSION) = '' then 'null' else VERSION end),\"reqreq\",(case when ROW_FORMAT is null or trim(ROW_FORMAT) = '' then 'null' else ROW_FORMAT end),\"reqreq\",(case when TABLE_ROWS is null or trim(TABLE_ROWS) = '' then 'null' else TABLE_ROWS end),\"reqreq\",(case when AVG_ROW_LENGTH is null or trim(AVG_ROW_LENGTH) = '' then 'null' else AVG_ROW_LENGTH end),\"reqreq\",(case when DATA_LENGTH is null or trim(DATA_LENGTH) = '' then 'null' else DATA_LENGTH end),\"reqreq\",(case when MAX_DATA_LENGTH is null or trim(MAX_DATA_LENGTH) = '' then 'null' else MAX_DATA_LENGTH end),\"reqreq\",(case when INDEX_LENGTH is null or trim(INDEX_LENGTH) = '' then 'null' else INDEX_LENGTH end),\"reqreq\",(case when DATA_FREE is null or trim(DATA_FREE) = '' then 'null' else DATA_FREE end),\"reqreq\",(case when AUTO_INCREMENT is null or trim(AUTO_INCREMENT) = '' then 'null' else AUTO_INCREMENT end),\"reqreq\",(case when CREATE_TIME is null or trim(CREATE_TIME) = '' then 'null' else CREATE_TIME end),\"reqreq\",(case when UPDATE_TIME is null or trim(UPDATE_TIME) = '' then 'null' else UPDATE_TIME end),\"reqreq\",(case when CHECK_TIME is null or trim(CHECK_TIME) = '' then 'null' else CHECK_TIME end),\"reqreq\",(case when TABLE_COLLATION is null or trim(TABLE_COLLATION) = '' then 'null' else TABLE_COLLATION end),\"reqreq\",(case when CHECKSUM is null or trim(CHECKSUM) = '' then 'null' else CHECKSUM end),\"reqreq\",(case when CREATE_OPTIONS is null or trim(CREATE_OPTIONS) = '' then 'null' else CREATE_OPTIONS end),\"reqreq\",(case when TABLE_COMMENT is null or trim(TABLE_COMMENT) = '' then 'null' else TABLE_COMMENT end)) from tables where table_schema='${database}'   "


#editplus中寻找关键字： (.*)\n   替换关键字: ["\1"]="xxxx" 
#获取information_schema中tables表中数据的方法
function get_tb_val(){
declare -A map=(["TABLE_CATALOG"]="0" ["TABLE_SCHEMA"]="1" ["TABLE_NAME"]="2" ["TABLE_TYPE"]="3" ["ENGINE"]="4" ["VERSION"]="5" ["ROW_FORMAT"]="6" ["TABLE_ROWS"]="7" ["AVG_ROW_LENGTH"]="8" ["DATA_LENGTH"]="9" ["MAX_DATA_LENGTH"]="10" ["INDEX_LENGTH"]="11" ["DATA_FREE"]="12" ["AUTO_INCREMENT"]="13" ["CREATE_TIME"]="14" ["UPDATE_TIME"]="15" ["CHECK_TIME"]="16" ["TABLE_COLLATION"]="17" ["CHECKSUM"]="18" ["CREATE_OPTIONS"]="19" ["TABLE_COMMENT"]="20") 


local tmp_val2=$1
local talbe_row_val=(${tmp_val2//reqreq/ }); #分割符： reqreq
local map_index=${map["$2"]}
echo  ${talbe_row_val[$map_index]} | awk '{gsub("qpzmxxqpzm"," "); print $0 }'
}


#查询information_schema中column表的sql。
sql_column="select concat((case when TABLE_CATALOG is null or trim(TABLE_CATALOG) = '' then 'null' else TABLE_CATALOG end),\"reqreq\",(case when TABLE_SCHEMA is null or trim(TABLE_SCHEMA) = '' then 'null' else TABLE_SCHEMA end),\"reqreq\",(case when TABLE_NAME is null or trim(TABLE_NAME) = '' then 'null' else TABLE_NAME end),\"reqreq\",(case when COLUMN_NAME is null or trim(COLUMN_NAME) = '' then 'null' else COLUMN_NAME end),\"reqreq\",(case when ORDINAL_POSITION is null or trim(ORDINAL_POSITION) = '' then 'null' else ORDINAL_POSITION end),\"reqreq\",(case when COLUMN_DEFAULT is null or trim(COLUMN_DEFAULT) = '' then 'null' else COLUMN_DEFAULT end),\"reqreq\",(case when IS_NULLABLE is null or trim(IS_NULLABLE) = '' then 'null' else IS_NULLABLE end),\"reqreq\",(case when DATA_TYPE is null or trim(DATA_TYPE) = '' then 'null' else DATA_TYPE end),\"reqreq\",(case when CHARACTER_MAXIMUM_LENGTH is null or trim(CHARACTER_MAXIMUM_LENGTH) = '' then 'null' else CHARACTER_MAXIMUM_LENGTH end),\"reqreq\",(case when CHARACTER_OCTET_LENGTH is null or trim(CHARACTER_OCTET_LENGTH) = '' then 'null' else CHARACTER_OCTET_LENGTH end),\"reqreq\",(case when NUMERIC_PRECISION is null or trim(NUMERIC_PRECISION) = '' then 'null' else NUMERIC_PRECISION end),\"reqreq\",(case when NUMERIC_SCALE is null or trim(NUMERIC_SCALE) = '' then 'null' else NUMERIC_SCALE end),\"reqreq\",(case when DATETIME_PRECISION is null or trim(DATETIME_PRECISION) = '' then 'null' else DATETIME_PRECISION end),\"reqreq\",(case when CHARACTER_SET_NAME is null or trim(CHARACTER_SET_NAME) = '' then 'null' else CHARACTER_SET_NAME end),\"reqreq\",(case when COLLATION_NAME is null or trim(COLLATION_NAME) = '' then 'null' else COLLATION_NAME end),\"reqreq\",(case when COLUMN_TYPE is null or trim(COLUMN_TYPE) = '' then 'null' else COLUMN_TYPE end),\"reqreq\",(case when COLUMN_KEY is null or trim(COLUMN_KEY) = '' then 'null' else COLUMN_KEY end),\"reqreq\",(case when EXTRA is null or trim(EXTRA) = '' then 'null' else EXTRA end),\"reqreq\",(case when PRIVILEGES is null or trim(PRIVILEGES) = '' then 'null' else PRIVILEGES end),\"reqreq\",(case when COLUMN_COMMENT is null or trim(COLUMN_COMMENT) = '' then 'null' else COLUMN_COMMENT end)) from columns  where 
table_schema='${database}' and table_name = \"table_name_variable\"    "


#获取information_schema中columns表中数据的方法
function get_col_val(){
declare -A map2=(["TABLE_CATALOG"]="0" ["TABLE_SCHEMA"]="1" ["TABLE_NAME"]="2" ["COLUMN_NAME"]="3" ["ORDINAL_POSITION"]="4" ["COLUMN_DEFAULT"]="5" ["IS_NULLABLE"]="6" ["DATA_TYPE"]="7" ["CHARACTER_MAXIMUM_LENGTH"]="8" ["CHARACTER_OCTET_LENGTH"]="9" ["NUMERIC_PRECISION"]="10" ["NUMERIC_SCALE"]="11" ["DATETIME_PRECISION"]="12" ["CHARACTER_SET_NAME"]="13" ["COLLATION_NAME"]="14" ["COLUMN_TYPE"]="15" ["COLUMN_KEY"]="16" ["EXTRA"]="17" ["PRIVILEGES"]="18" ["COLUMN_COMMENT"]="19" ) 




local tmp_val2=$1
local col_row_val=(${tmp_val2//reqreq/ }); #分割符： reqreq
local col_row_val2=${tmp_val2//reqreq/ };
local map_index=${map2["$2"]}
echo  ${col_row_val[$map_index]} | awk '{gsub("qpzmxxqpzm"," "); print $0 }'
#echo  ${col_row_val[$map_index]} 
}




#执行特定的sql语句,只返回某一列第二行以后的数据
function sql_ext(){
	local e_result2=`mysql -u${user} -p${password} -h${host} -P${port} -N -e   "$1"  `
	echo $e_result2;
}


function to_sql_ext(){
	local e_result2=`mysql -u${touser} -p${topassword} -h${tohost} -P${toport} -e  "$1"  `
	echo $e_result2;
}


#通用方法
function general(){
	local dataTime=`date '+%Y-%m-%d %H:%M:%S'`;
	local flage=0;
	if [ $2 == '1' ]
	then
		local cur_value=`expr length $6`
		case $3 in
		'ge')
				if [ ${cur_value} -ge $4 ]
				    then
				    flage=1;
				fi
				;;
		'gt')
				if [ ${cur_value} -gt $4 ]
				    then
				    flage=1;
				fi
				;;
		'eq')
				if [ ${cur_value} -eq $4 ]
				    then
				    flage=1;
				fi
				;;
		'le')
				if [ ${cur_value} -le $4 ]
				    then
				    flage=1;
				fi
				;;
		'lt')
				if [ ${cur_value} -lt $4 ]
				    then
				    flage=1;
				fi
				;;
		'ne')
				if [ ${cur_value} -ne $4 ]
				    then
				    flage=1;
				fi
				;;


		*)
			echo "case error"
			;;
		esac
	fi


	if [ $2 == '2' ]
	then
	local cur_value=$6
	LOWERCASE1=$(echo $6 | tr '[A-Z]' '[a-z]')
	LOWERCASE2=$(echo $4 | tr '[A-Z]' '[a-z]')
		case $3 in
		'==')
				if [ ${LOWERCASE1} == $LOWERCASE2 ]
				    then
				    flage=1;
				fi
				;;
		'!=')
				if [ ${LOWERCASE1} != $LOWERCASE2 ]
				    then
				    flage=1;
				fi
				;;


		*)
			echo "case error"
			;;
		esac
	fi




	
	if [ ${flage} -eq 1 ]
	then
		tosql_tmp="use ${todatabase} ;insert into ${totable}(db_name,tb_name,col_name,audit_type,audit_name,audit_title,audit_form,audit_logic,cur_value,threshold,comment,status,updated) values('$8','$9','${10}','$7','$6','$1',$2,'$3','${cur_value}','$4','$5',0,'${dataTime}')";
		to_sql_ext "${tosql_tmp}"
	fi
}

5,主入口方法脚本:main_in.sh

function main(){


local e_result=`mysql -u${user} -p${password} -h${host} -P${port} information_schema -N -e "$sql_table" | awk '{gsub(" ","qpzmxxqpzm"); print $0 }' `;  
for table_row_val_pas in $e_result 
do 


table_name=`get_tb_val ${table_row_val_pas} TABLE_NAME`
local schema_name=`get_tb_val ${table_row_val_pas} TABLE_SCHEMA`
local create_time=`get_tb_val ${table_row_val_pas} CREATE_TIME `


	#对表规则进行校验	
	arr=(${table_rule01})  
	for table_rules in ${arr[@]} 
	do 
	    local target_rule=(${table_rules//,/" "});
	    #echo "target_rule" ${target_rule[@]}
	    #根据具体校验规则定义方法
		case ${target_rule[0]} in
                "table_name_length_limit")
			general   ${target_rule[@]} `get_tb_val ${table_row_val_pas} TABLE_NAME` "table" `get_tb_val ${table_row_val_pas} TABLE_SCHEMA` `get_tb_val ${table_row_val_pas} TABLE_NAME` ""
		;;


                "table_engine_innodb_check")
		general   ${target_rule[@]} `get_tb_val ${table_row_val_pas} ENGINE` "table" `get_tb_val ${table_row_val_pas} TABLE_SCHEMA` `get_tb_val ${table_row_val_pas} TABLE_NAME` ""
		 ;; 
		*)
		 echo "general 4 default table";
		 ;; 
		esac
	done


	local sql_column2=${sql_column//"table_name_variable"/"$table_name"};
	local col_results=`mysql -u${user} -p${password} -h${host} -P${port} information_schema -N -e "$sql_column2" | awk '{gsub(" ","qpzmxxqpzm"); print $0 }' `; 
	for col_row_val_pas in $col_results 
	do 
	get_col_val ${col_row_val_pas} COLUMN_COMMENT
	local column_name=`get_col_val ${col_row_val_pas} COLUMN_COMMENT`	
	col_arr=(${column_rules})  
	
	for col_rule in ${col_arr[@]}
	do 
	    local cur_col_rule=(${col_rule//,/" "});
	    #根据具体校验规则定义方法
		case ${cur_col_rule[0]} in
                "column_name_length_limit")
			general   ${cur_col_rule[@]} `get_col_val ${col_row_val_pas} COLUMN_NAME` "column" `get_tb_val ${col_row_val_pas} TABLE_SCHEMA` `get_tb_val ${col_row_val_pas} TABLE_NAME` `get_col_val ${col_row_val_pas} COLUMN_NAME`
		;;
		"column_coment_is_null")
			general   ${cur_col_rule[@]} `get_col_val ${col_row_val_pas} COLUMN_COMMENT` "column" `get_tb_val ${col_row_val_pas} TABLE_SCHEMA` `get_tb_val ${col_row_val_pas} TABLE_NAME` `get_col_val ${col_row_val_pas} COLUMN_NAME`
		;;
		*)
		 echo "general 4 default column";
		 ;; 
		esac
	done
	done
done
}

6,启动方法脚本:main.sh


#!/bin/sh
#coding=utf-8


realpath=$(readlink -f "$0")
basedir=$(dirname "$realpath")
export PATH=$PATH:$basedir/config
export PATH=$PATH:$basedir/lib


. config_db.sh
. config_audit.sh
. function.sh
. main_in.sh


:<<!
1,加密取出
2,结果分组，拆分单行数据为数组(解密)
3,业务处理
!


main


echo "successful!";

huoyuanshen

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
基于shell自动完成mysql格式审计的项目

1，对于mysql字段长度，表名长度等规范问题，完成shell自动校验，结果存入数据库中进行查看。项目目录结构:├── config│ ├── config_audit.sh│ └── config_db.sh├── lib│ ├── function.sh│ └── main_in.sh└── main.sh具体脚本内容 :1，
复制链接

扫一扫

专栏目录