SQL审计平台之Archery安装

一、简介

Archery是一个开源的SQL审核查询平台，基于python开发。支持多数据库的SQL上线和查询，同时支持丰富的MySQL运维功能，底层基于goinception和inception两个开源审计组件。Archery功能导图和部署文档。

二、安装

1.安装pyenv和virtualenv

2.准备虚拟环境

创建虚拟环境并激活

pyenv virtualenv 3.6.4 venv4archery
pyenv activate venv4archery

3.下载解压

wget https://github.com/hhyo/archery/archive/v1.8.0.tar.gz
tar xvf v1.8.0.tar.gz
cd Archery-1.8.0/

4.安装依赖

yum依赖

yum -y install gcc gcc-c++ python-devel mysql-devel openldap-devel unixODBC-devel gettext python36 python36-pip

pip依赖

 pip install -r requirements.txt

报错1

ModuleNotFoundError: No module named 'setuptools_rust'

解决办法

pip install --upgrade pip

报错2

ModuleNotFoundError: No module named 'Crypto'

解决办法

pip install pycrypto

5.安装redis

安装并启动

tar xf redis-5.0.3.tar.gz
cd redis-5.0.3
make PREFIX=/usr/local/redis install
mkdir  /usr/local/redis/etc
cp redis.conf  /usr/local/redis/etc/
/usr/local/redis/bin/redis-server  /usr/local/redis/etc/redis.conf

6.安装Inception

下载

wget https://github.com/hhyo/inception/releases/download/v2.1.52.2/Inception_2.1.52.2
chmod +x Inception_2.1.52.2

配置

cat inc.cnf
[inception]
general_log=1
general_log_file=inception.log
port=6669
socket=/tmp/inc.socket
character-set-client-handshake=0
character-set-server=utf8
inception_language_code=zh-CN
inception_remote_system_password=root
inception_remote_system_user=root
inception_remote_backup_port=3306
inception_remote_backup_host=127.0.0.1
inception_support_charset=utf8,utf8mb4
inception_enable_nullable=0
inception_check_primary_key=1
inception_check_column_comment=1
inception_check_table_comment=1
inception_osc_on=OFF
inception_osc_bin_dir=/usr/bin
inception_osc_min_table_size=1
inception_osc_chunk_time=0.1
inception_enable_blob_type=1
inception_check_column_default_value=1

启动

./Inception_2.1.52.2 --defaults-file=inc.cnf

7.安装goinception

wget https://github.com/hanchuanchuan/goInception/releases/download/v1.2.4/goInception-linux-v1.2.4.tar.gz
tar xvf goInception-linux-v1.2.4.tar.gz
mv config/config.toml.default config/config.toml
./goInception -config=config/config.toml

8.修改配置

vi archery/settings.py

# 关闭debug模式
DEBUG = False
# 该项目本身的mysql数据库地址
DATABASES = {
    'default': {
        'ENGINE': 'django.db.backends.mysql',
        'NAME': 'archery',
        'USER': 'root',
        'PASSWORD': '123456',
        'HOST': '127.0.0.1',
        'PORT': '3357',
        'OPTIONS': {
            'init_command': "SET sql_mode='STRICT_TRANS_TABLES'",
            'charset': 'utf8mb4'
        },
        'TEST': {
            'NAME': 'test_archery',
            'CHARSET': 'utf8mb4',
        },
    }
}
# 缓存配置
CACHES = {
    "default": {
        "BACKEND": "django_redis.cache.RedisCache",
        "LOCATION": "redis://127.0.0.1:6379/0",
        "OPTIONS": {
            "CLIENT_CLASS": "django_redis.client.DefaultClient",
            "PASSWORD": ""
        }
    },
    "dingding": {
        "BACKEND": "django_redis.cache.RedisCache",
        "LOCATION": "redis://127.0.0.1:6379/1",
        "OPTIONS": {
            "CLIENT_CLASS": "django_redis.client.DefaultClient",
            "PASSWORD": ""
        }
    }
}

建议MySQL版本5.6以上否则下面步骤会报错。

9.启动准备

# 数据库初始化
python3 manage.py makemigrations sql
python3 manage.py migrate 
# 数据初始化
python3 manage.py dbshell<sql/fixtures/auth_group.sql
python3 manage.py dbshell<src/init_sql/mysql_slow_query_review.sql
# 创建管理用户
python3 manage.py createsuperuser

MySQL版本5.6会报错，推荐使用5.7及以上版本
MySQLdb._exceptions.OperationalError: (1071, 'Specified key was too long）

10.启动

pyenv activate venv4archery
#启动Django-Q，需保持后台运行
python3 manage.py qcluster
#启动服务
python3 manage.py runserver 0.0.0.0:9123  --insecure

11.访问登陆

http://10.0.0.51:9123/

image

12.安装SQLAdvisor（可选）

安装依赖

yum install install cmake libaio-devel libffi-devel glib2 glib2-devel -y
yum install https://mirrors.cnnic.cn/percona/ps-56/yum/release/6/RPMS/x86_64/Percona-Server-shared-56-5.6.50-rel90.0.1.el6.x86_64.rpm
cd /usr/lib64/
ln -s libperconaserverclient_r.so.18 libperconaserverclient_r.so

下载源码包编译安装,先编译依赖项sqlparser，再安装SQLAdvisor源码

wget https://github.com/Meituan-Dianping/SQLAdvisor/archive/refs/tags/v2.0.tar.gz
tar xvf v2.0.tar.gz
cd SQLAdvisor-2.0/
cmake -DBUILD_CONFIG=mysql_release -DCMAKE_BUILD_TYPE=debug -DCMAKE_INSTALL_PREFIX=/usr/local/sqlparser ./
make && make install
cd SQLAdvisor/sqladvisor/
cmake -DCMAKE_BUILD_TYPE=debug ./
make

查看帮助

./sqladvisor --help
Usage:
  sqladvisor [OPTION?] sqladvisor

SQL Advisor Summary

Help Options:
  -?, --help              Show help options

Application Options:
  -f, --defaults-file     sqls file
  -u, --username          username
  -p, --password          password
  -P, --port              port
  -h, --host              host
  -d, --dbname            database name
  -q, --sqls              sqls
  -v, --verbose           1:output logs 0:output nothing