mysql审核平台Yearning

@admin  May 9, 2020

简述

Yearning 是一款基于golang开发的 MYSQL SQL语句审核平台。提供查询审计,SQL审核等多种功能。

主要功能

  • SQL查询

    • 查询导出
    • 查询自动补全
  • SQL审核

    • 流程化工单
    • SQL语句检测
    • SQL语句执行
    • SQL回滚
    • SQL自动审核执行
  • 历史审核记录
  • 查询审计
  • 推送

    • E-mail工单推送
    • 自定义webhook消息推送
  • 其他

    • LDAP登陆
    • 用户权限及管理
    • 权限组划分

安装

Yearning支持容器和普通两种方式安装。

安装注意事项

  • Yearning 不依赖于任何第三方SQL审核工具作为审核引擎,内部已自己实现审核/回滚相关逻辑。
  • 仅依赖Mysql数据库。
  • mysql版本必须5.7及以上版本,请事先自行安装完毕且创建Yearning库,字符集应为UTF8mb4 (仅Yearning所需mysql版本)
  • Yearning日志仅输出error级别,没有日志即可认为无运行错误!
  • Yearning 基于1080p分辨率开发仅支持1080p及以上显示器访问
  • 由于使用较多新的前端技术栈,请使用Chrome最新版本(不包括360等其他魔改版本)

容器安装

把安装包下载下来解压之后里面有docker-compose.yml,你可以直接docker-compose运行,或者使用Dockerfile build出来镜像运行。

[root@localhost software]# wgte https://github.com/cookieY/Yearning/releases/download/v2.2.0/Yearning-2.2.0.linux-amd64.zip
[root@localhost software]# unzip Yearning-2.2.0.linux-amd64.zip 
[root@localhost software]# cd Yearning-go/
[root@localhost Yearning-go]# ll
total 9772
-rw-r--r--. 1 root root     127 Aug  2  2019 conf.toml
drwxr-xr-x. 6 root root     116 Apr  7 10:32 dist
-rw-r--r--. 1 root root     620 Jan  9 10:06 docker-compose.yml
-rw-r--r--. 1 root root     597 Aug 21  2019 Dockerfile
-rw-r--r--. 1 root root     177 Aug 23  2019 # README
-rwxr--r--. 1 root root 9985356 Apr  7 10:35 Yearning
-rw-r--r--. 1 root root     283 Jan 15 16:55 yearning.service

# build打包镜像
[root@localhost Yearning-go]# docker build -t yearning:2.2.0 .
Sending build context to Docker daemon     27MB
Step 1/15 : FROM alpine:latest
 ---> a187dde48cd2
Step 2/15 : LABEL maintainer="HenryYee-2019/08/13"
 ---> Using cache
 ---> 8704a24ceec4
Step 3/15 : EXPOSE 8000
 ---> Using cache
 ---> cd529df25402
Step 4/15 : COPY Yearning  /opt/Yearning
 ---> 4c0b930209ee
Step 5/15 : COPY dist /opt/dist
 ---> 5d1bad52eb48
Step 6/15 : COPY conf.toml /opt/conf.toml
 ---> ea9caf810406
Step 7/15 : RUN mkdir /lib64 && ln -s /lib/libc.musl-x86_64.so.1 /lib64/ld-linux-x86-64.so.2
 ---> Running in 366708cbd832
Removing intermediate container 366708cbd832
 ---> ff2c0fc4aabf
Step 8/15 : RUN echo "http://mirrors.ustc.edu.cn/alpine/v3.3/main/" > /etc/apk/repositories
 ---> Running in c573938385aa
Removing intermediate container c573938385aa
 ---> 6b65712730c0
Step 9/15 : RUN apk add --no-cache tzdata
 ---> Running in 008148b075d7
fetch http://mirrors.ustc.edu.cn/alpine/v3.3/main/x86_64/APKINDEX.tar.gz
(1/1) Installing tzdata (2015g-r0)
Executing busybox-1.31.1-r9.trigger
OK: 9 MiB in 15 packages
Removing intermediate container 008148b075d7
 ---> e5323cab65dd
Step 10/15 : RUN ln -sf /usr/share/zoneinfo/Asia/Shanghai /etc/localtime
 ---> Running in 5d60af872408
Removing intermediate container 5d60af872408
 ---> f2235c7a9296
Step 11/15 : RUN echo "Asia/Shanghai" >> /etc/timezone
 ---> Running in b5f15ea8dd1f
Removing intermediate container b5f15ea8dd1f
 ---> 24ec8c26dce2
Step 12/15 : RUN echo 'hosts: files mdns4_minimal [NOTFOUND=return] dns mdns4' >> /etc/nsswitch.conf
 ---> Running in 6c3ed1e30df4
Removing intermediate container 6c3ed1e30df4
 ---> 66347f82ea35
Step 13/15 : WORKDIR /opt
 ---> Running in b9562af09601
Removing intermediate container b9562af09601
 ---> c8e7ad519786
Step 14/15 : ENTRYPOINT  ["/opt/Yearning"]
 ---> Running in b23a06d66241
Removing intermediate container b23a06d66241
 ---> 35807dd75678
Step 15/15 : CMD ["-m", "-s"]
 ---> Running in 977eef4bc7b8
Removing intermediate container 977eef4bc7b8
 ---> d4094177f7c6
Successfully built d4094177f7c6
Successfully tagged yearning:2.2.0
[root@localhost Yearning-go]# docker images
REPOSITORY                                      TAG                 IMAGE ID            CREATED             SIZE
yearning                                        2.2.0               d4094177f7c6        36 seconds ago      33.8MB
....

# 运行
docker run -d -it -p8000:8000 -e MYSQL_USER=root -e MYSQL_ADDR=10.0.0.3:3306 -e MYSQL_PASSWORD=123123 -e MYSQL_DB=Yearning yearning:2.2.0

# docker-compose运行
[root@localhost Yearning-go]# docker-compose up .

run起来容器之后,可以通过http://IP:8000访问yearning了。默认用户名/密码:admin/Yearning_admin,注意登录验证码区分大小写。

普通安装

修改配置文件

[root@localhost Yearning-go]# ll
total 9772
-rw-r--r--. 1 root root     127 Aug  2  2019 conf.toml
drwxr-xr-x. 6 root root     116 Apr  7 10:32 dist
-rw-r--r--. 1 root root     620 Jan  9 10:06 docker-compose.yml
-rw-r--r--. 1 root root     597 Aug 21  2019 Dockerfile
-rw-r--r--. 1 root root     177 Aug 23  2019 # README
-rwxr--r--. 1 root root 9985356 Apr  7 10:35 Yearning
-rw-r--r--. 1 root root     283 Jan 15 16:55 yearning.service
[root@localhost Yearning-go]# vim conf.toml  # 编辑配置文件
[Mysql]
Db = "Yearning"
Host = "127.0.0.1"
Port = "3306"
Password = "123456"
User = "root"

[General]
SecretKey = "6p4RMlDAYQ!PMgEC"

创建数据库

mysql> CREATE DATABASE IF NOT EXISTS Yearning DEFAULT CHARSET utf8;
Query OK, 1 row affected (0.00 sec)

初始化


[root@localhost Yearning-go]# ./Yearning -m
(/var/jenkins_home/workspace/Yearning-go/src/service/migrate.go:31) 
 [2020-04-20 17:11:26]  [0.75ms]  INSERT INTO `core_accounts` (`username`,`password`,`rule`,`department`,`real_name`,`email`) VALUES ('admin','pbkdf2_sha256$120000$4KRI3kek0QHf$WmWFJwOUgyaSY/GYRq1GTsPYq33xcGBc571BUOhxSxE=','admin','DBA','超级管理员','')  
    [1 rows affected or returned ] 
    
    (/var/jenkins_home/workspace/Yearning-go/src/service/migrate.go:39) 
    [2020-04-20 17:11:26]  [1.38ms]  INSERT INTO `core_global_configurations` (`authorization`,`ldap`,`message`,`other`,`stmt`,`audit_role`,`board`) VALUES ('global','{"url":"","user":"","password":"","type":1,"sc":"","ldaps":false}','{"web_hook":"","host":"","port":25,"user":"","password":"","to_user":"","mail":false,"ding":false,"ssl":false,"push_type":false}','{"limit":"1000","idc":["Aliyun","AWS"],"multi":false,"query":false,"exclude_db_list":[],"insulate_word_list":[],"register":false,"export":false,"per_order":2,"ex_query_time":60,"query_timeout":0}',0,'{"DMLInsertColumns":false,"DMLMaxInsertRows":10,"DMLWhere":false,"DMLOrder":false,"DMLSelect":false,"DDLCheckTableComment":false,"DDlCheckColumnComment":false,"DDLCheckColumnNullable":false,"DDLCheckColumnDefault":false,"DDLTimeFieldDefault":false,"DDLEnableAcrossDBRename":false,"DDLEnableAutoincrementInit":false,"DDLEnableAutoIncrement":false,"DDLEnableAutoincrementUnsigned":false,"DDLEnableDropTable":false,"DDLEnableDropDatabase":false,"DDLEnableNullIndexName":false,"DDLIndexNameSpec":false,"DDLMaxKeyParts":5,"DDLMaxKey":5,"DDLMaxCharLength":10,"MaxTableNameLen":10,"MaxAffectRows":1000,"MaxDDLAffectRows":0,"EnableSetCollation":false,"EnableSetCharset":false,"SupportCharset":"","SupportCollation":"","CheckIdentifier":false,"MustHaveColumns":"","DDLMultiToSubmit":false,"DDLPrimaryKeyMust":false,"DDLAllowColumnType":false,"DDLImplicitTypeConversion":false,"DMLMinimalRollback":false,"DDLAllowPRINotInt":false,"IsOSC":false,"OscBinDir":"","OscDropNewTable":false,"OscDropOldTable":false,"OscCheckReplicationFilters":false,"OscCheckAlter":false,"OscAlterForeignKeysMethod":"rebuild_constraints","OscMaxLag":1,"OscRecursionMethod":"processlist","OscCheckInterval":1,"OscMaxThreadConnected":25,"OscMaxThreadRunning":25,"OscCriticalThreadConnected":20,"OscCriticalThreadRunning":20,"OscPrintSql":false,"OscChunkTime":0.5,"OscSize":0,"AllowCreateView":false,"AllowCreatePartition":false,"AllowSpecialType":false}','')  
    [1 rows affected or returned ] 
    
    (/var/jenkins_home/workspace/Yearning-go/src/service/migrate.go:46) 
    [2020-04-20 17:11:26]  [0.74ms]  INSERT INTO `core_graineds` (`username`,`rule`,`permissions`,`group`) VALUES ('admin','','{"ddl":"1","ddl_source":[],"dml":"1","dml_source":[],"user":"1","base":"1","auditor":[],"query":"1","query_source":[]}',NULL)  
    [1 rows affected or returned ] 
    初始化成功!
     用户名: admin
    密码:Yearning_admin

启动

[root@localhost Yearning-go]# ./Yearning -s
检查更新.......
数据已更新!

__    __  _____       ___   _____    __   _   _   __   _   _____  
\ \  / / | ____|     /   | |  _  \  |  \ | | | | |  \ | | /  ___| 
 \ \/ /  | |__      / /| | | |_| |  |   \| | | | |   \| | | |     
  \  /   |  __|    / / | | |  _  /  | |\   | | | | |\   | | |  _  
  / /    | |___   / /  | | | | \ \  | | \  | | | | | \  | | |_| | 
 /_/     |_____| /_/   |_| |_|  \_\ |_|  \_| |_| |_|  \_| \_____/  golang.ver

Welcome to Yearning
https://yearning.io
____________________________________O/_______
                                    O\
⇨ http server started on [::]:8000

测试链接

请输入图片描述
请输入图片描述

参考资料

官方手册
GITHUB

/wp-content/uploads/2020/04/20/yearning-1.png

/wp-content/uploads/2020/04/20/yearning-2.png


添加新评论