archer+inception sql自动审核工具---docker安装部
操作系统：Centos 7
数据库： MySQL 5.7
一、Centos7下docker的安装：
https://www.cnblogs.com/yufeng218/p/8370670.html
二、inception docker 安装
参考地址：https://hub.docker.com/r/hhyo/inception
三、archer docker 安装
http://blog.chinaunix.net/uid-77311-id-5819040.html
1、安装
archer 项目地址：
https://github.com/jly8866/archer
# git clone https://github.com/jly8866/archer.git
2、archer配置准备
将配置文件放到如下目录：
#mkdir -p /opt/archer_docker_config #cp /home/tools/archer/archer/settings.py /opt/archer_docker_config/
修改/opt/archer_docker_config/settings.py文件，替换数据库、inception、email等相关配置项。其中docker已经包含了SQLADVISOR，请将SQLADVISOR配置项修改为
SQLADVISOR ='/opt/SQLAdvisor/sqladvisor/sqladvisor'
#同时修改如下配置：
# 该项目本身的mysql数据库地址 DATABASES = { 'default': { 'ENGINE': 'django.db.backends.mysql', 'NAME': 'archer', 'USER': 'root', 'PASSWORD': '123456', 'HOST': '192.168.2.12', 'PORT': '3306' } }
# inception组件所在的地址 INCEPTION_HOST = '192.168.2.12' INCEPTION_PORT = '6669' # 查看回滚SQL时候会用到，这里要告诉archer去哪个mysql里读取inception备份的回滚信息和SQL. # 注意这里要和inception组件的inception.conf里的inception_remote_XX部分保持一致. INCEPTION_REMOTE_BACKUP_HOST = '192.168.2.12' INCEPTION_REMOTE_BACKUP_PORT = 3306 INCEPTION_REMOTE_BACKUP_USER = 'inception_bak' INCEPTION_REMOTE_BACKUP_PASSWORD = 'inception_bak'
创建archer DB 和inception DB
-----进入安装的mysql5.7(注：此mysql需提前安装，docker安装archer的过程中，并不会安装mysql,这是一个坑）
mysql -h 192.168.2.12 -uroot -p create database archer default character set utf8; grant all privileges on archer.* to 'root'@'%' identified by '123456'; flush privileges;
create database inception;
3、启动服务
NGINX_PORT环境变量为宿主机映射端口，是为了解决重定向404的问题
创建容器得时候，
报错：
#docker run --name archer -v /opt/archer_docker_config/settings.py:/opt/archer/archer/settings.py -e NGINX_PORT=9123 -p 9123:9123 -dti hhyo/archer WARNING: IPv4 forwarding is disabled. Networking will not work. cabd13c556fd0b96aa9eaf5db50a0daddcc2b19f963aebb2614a706d588733b3
解决办法：
#vim /usr/lib/sysctl.d/00-system.conf
添加如下代码：
net.ipv4.ip_forward=1
重启network服务
#systemctl restart network.service
完成以后，删除错误得容器，再次创建新容器，就不再报错了
进入指定得容器：
[root@localhost ~]# docker ps -a CONTAINER ID IMAGE COMMAND CREATED STATUS PORTS NAMES c6d23a5d0341 hhyo/archer "/bin/sh -c 'bash /o…" 6 minutes ago Up 6 minutes 0.0.0.0:9123->9123/tcp archer ace3ba0db738 hhyo/inception "/bin/sh -c 'nohup /…" 19 hours ago Exited (137) 17 hours ago inception [root@localhost ~]# docker exec -it c6d23a5d0341 /bin/bash
4、初始化
说明：初次部署需要初始化数据库和账号信息，后续升级走增量变动脚本（具体见版本release说明），不再需要此操作,下面脚本是登陆docker后直接执行
[root@localhost ~]# docker exec -it archer /bin/bash [root@35929c2af7ab /]# cd /opt/archer/ [root@35929c2af7ab archer]# source /opt/venv4archer/bin/activate
通过model创建archer数据库本身的表：
(venv4archer) [root@35929c2af7ab archer]# python3 manage.py makemigrations sql Migrations for 'sql': 0001_initial.py: - Create model SlowQuery - Create model SlowQueryHistory - Create model users - Create model AliyunAccessKey - Create model AliyunRdsConfig - Create model DataMaskingColumns - Create model DataMaskingRules - Create model master_config - Create model QueryLog - Create model QueryPrivileges - Create model QueryPrivilegesApply - Create model slave_config - Create model workflow - Create model WorkflowAudit - Create model WorkflowAuditDetail - Create model WorkflowAuditSetting - Alter unique_together for workflowaudit (1 constraint(s)) - Add field cluster_name to aliyunrdsconfig
(venv4archer) [root@35929c2af7ab archer]# python3 manage.py migrate
Operations to perform:
Synchronize unmigrated apps: messages, staticfiles, django_admin_bootstrapped
Apply all migrations: contenttypes, django_apscheduler, auth, admin, sql, sessions
Synchronizing apps without migrations:
Creating tables...
Running deferred SQL...
Installing custom SQL...
Running migrations:
Rendering model states... DONE
Applying contenttypes.0001_initial... OK
Applying contenttypes.0002_remove_content_type_name... OK
Applying auth.0001_initial... OK
Applying auth.0002_alter_permission_name_max_length... OK
Applying auth.0003_alter_user_email_max_length... OK
Applying auth.0004_alter_user_username_opts... OK
Applying auth.0005_alter_user_last_login_null... OK
Applying auth.0006_require_contenttypes_0002... OK
Applying sql.0001_initial... OK
Applying admin.0001_initial... OK
Applying django_apscheduler.0001_initial... OK
Applying django_apscheduler.0002_auto_20180412_0758... OK
Applying sessions.0001_initial... OK
创建diango admin管理员账号：
(venv4archer) [root@35929c2af7ab archer]# python3 manage.py createsuperuser Username: admin Email address: 13611088649@qq.com Password: Password (again): Superuser created successfully.
Username: admin
Password: admin
注：该用户可以登陆diango admin来管理model。
5、查看日志：
#docker logs archer -f --tail=10
配置变更
修改配置文件后重启
6、重启archer服务
#docker restart archer
查看表是否创建成功：
[root@localhost archer_docker_config]# mysql -uroot -h192.168.2.12 archer -p -e "show tables" Enter password: +---------------------------------------+ | Tables_in_archer | +---------------------------------------+ | aliyun_access_key | | aliyun_rds_config | | auth_group | | auth_group_permissions | | auth_permission | | data_masking_columns | | data_masking_rules | | django_admin_log | | django_apscheduler_djangojob | | django_apscheduler_djangojobexecution | | django_content_type | | django_migrations | | django_session | | query_log | | query_privileges | | query_privileges_apply | | sql_master_config | | sql_slave_config | | sql_users | | sql_users_groups | | sql_users_user_permissions | | sql_workflow | | workflow_audit | | workflow_audit_detail | | workflow_audit_setting | +---------------------------------------+
三、访问archer:
http://192.168.2.12:9123/
可以看到diango登陆界面：使用上面创建的用户名和密码（admin/admin)登录
1、 点击Add 用户配置，用户名和密码自定义。至少创建一个工程师和一个审核人（用admin用户可以登录）后续新的工程师和审核人用户请用LDAP导入sql_users表或django admin增加
2、配置主库地址：
这一步是为了告诉archer你要用inception去哪些mysql主库里执行SQL，所用到的用户名密码、端口等。
— inception审核用户（主库配置用户，如果要使用会话管理需要赋予SUPER权限，如果需要使用OSC，请额外配置权限） GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, ALTER,REPLICATION CLIENT,REPLICATION SLAVE ON *.* TO 'inception_bak'@'%' identified by 'inception_bak';
3、配置从库
这一步是为了告诉archer你要用inception去哪些mysql从库里查询SQL，所用到的用户名密码、端口等。
GRANT SELECT ON *.* TO 'archer_read'@'%' identified by 'archer_read';
4、配置审核流程
这一步是为了设置开发查询sql时，所走的审核流程
该用户没有查询权限，转到“查询权限管理”进行权限申请：
提交申请后，审核人进行审核
进行查询，出现如下错误：
修改/opt/archer_docker_config/settings.py文件，
将INCEPTION_HOST=‘127.0.0.1’改成如下地址：
# inception组件所在的地址 INCEPTION_HOST = '192.168.2.12' INCEPTION_PORT = '6669'
然后重启一下docker：systemctl restart docker
SQL上线工单：
archer的SQL上线流程为：工程师提交SQL->审核人审核->DBA执行，审核人只能审核归属自己审核的数据，DBA执行全部数据
交给审核人：张三审核，
最后由DBA执行
原因： 检测首先会访问inception，再由inception去操作MySQL实例，这个提示是指archer无法连接inception，请检查相关配置，并且需要注意docker容器127.0.0.1访问的不是宿主机而是容器本身
mysql> inception get variables; +------------------------------------------+-------------------------------------------+ | Variable_name | Value | +------------------------------------------+-------------------------------------------+ | autocommit | OFF | | bind_address | * | | character_set_system | utf8 | | character_sets_dir | /share/charsets/ | | connect_timeout | 10 | | date_format | %Y-%m-%d | | datetime_format | %Y-%m-%d %H:%i:%s | | general_log | ON | | general_log_file | inception.log | | inception_check_autoincrement_datatype | ON | | inception_check_autoincrement_init_value | ON | | inception_check_autoincrement_name | ON | | inception_check_column_comment | ON | | inception_check_column_default_value | ON | | inception_check_dml_limit | ON | | inception_check_dml_orderby | ON | | inception_check_dml_where | ON | | inception_check_identifier | ON | | inception_check_index_prefix | ON | | inception_check_insert_field | ON | | inception_check_primary_key | ON | | inception_check_table_comment | ON | | inception_check_timestamp_default | ON | | inception_ddl_support | OFF | | inception_enable_autoincrement_unsigned | ON | | inception_enable_blob_type | ON | | inception_enable_column_charset | OFF | | inception_enable_enum_set_bit | OFF | | inception_enable_foreign_key | OFF | | inception_enable_identifer_keyword | OFF | | inception_enable_not_innodb | OFF | | inception_enable_nullable | OFF | | inception_enable_orderby_rand | OFF | | inception_enable_partition_table | OFF | | inception_enable_pk_columns_only_int | OFF | | inception_enable_select_star | OFF | | inception_enable_set_engine | ON | | inception_enable_sql_statistic | ON | | inception_language_code | zh-CN | | inception_max_char_length | 16 | | inception_max_key_parts | 5 | | inception_max_keys | 16 | | inception_max_primary_key_parts | 5 | | inception_max_update_rows | 10000 | | inception_merge_alter_table | ON | | inception_osc_alter_foreign_keys_method | none | | inception_osc_bin_dir | /usr/bin | | inception_osc_check_alter | ON | | inception_osc_check_interval | 5.000000 | | inception_osc_check_replication_filters | ON | | inception_osc_chunk_size | 1000 | | inception_osc_chunk_size_limit | 4.000000 | | inception_osc_chunk_time | 0.100000 | | inception_osc_critical_thread_connected | 1000 | | inception_osc_critical_thread_running | 80 | | inception_osc_drop_new_table | ON | | inception_osc_drop_old_table | ON | | inception_osc_max_lag | 3.000000 | | inception_osc_max_thread_connected | 1000 | | inception_osc_max_thread_running | 80 | | inception_osc_min_table_size | 1 | | inception_osc_on | OFF | | inception_osc_print_none | ON | | inception_osc_print_sql | ON | | inception_osc_recursion_method | processlist | | inception_password | | | inception_read_only | OFF | | inception_remote_backup_host | 127.0.0.1 | | inception_remote_backup_port | 3306 | | inception_remote_system_password | *81F5E21E35407D884A6CD4A731AEBFB6AF209E1B | | inception_remote_system_user | root | | inception_support_charset | utf8,utf8mb4 | | inception_user | | | interactive_timeout | 28800 | | max_allowed_packet | 1073741824 | | max_connect_errors | 100 | | max_connections | 151 | | net_buffer_length | 16384 | | net_read_timeout | 30 | | net_write_timeout | 60 | | port | 6669 | | query_alloc_block_size | 8192 | | query_prealloc_size | 8192 | | socket | /tmp/inc.socket | | thread_handling | one-thread-per-connection | | thread_stack | 262144 | | time_format | %H:%i:%s | | version | 2.1.52.2 | | version_comment | Source distribution | | version_compile_machine | x86_64 | | version_compile_os | Linux | | wait_timeout | 28800 | +------------------------------------------+-------------------------------------------+
所以我们应将inception 的配置文件inc.cnf ，修改如下：
[root@localhost inception]# ps -ef | grep inception root 1110 88239 0 18:33 pts/2 00:00:00 grep --color=auto inception root 120610 120595 0 18:25 pts/0 00:00:00 /bin/sh -c nohup /opt/inception/debug/mysql/bin/Inception --defaults-file=/etc/inc.cnf && bash root 120649 120610 0 18:25 pts/0 00:00:00 /opt/inception/debug/mysql/bin/Inception --defaults-file=/etc/inc.cnf
修改配置文件如下：
[root@localhost etc]# docker ps -a CONTAINER ID IMAGE COMMAND CREATED STATUS PORTS NAMES 35929c2af7ab hhyo/archer "/bin/sh -c 'bash /o…" 27 hours ago Up 8 minutes 0.0.0.0:9123->9123/tcp archer ace3ba0db738 hhyo/inception "/bin/sh -c 'nohup /…" 2 days ago Up 9 minutes 0.0.0.0:6669->6669/tcp inception [root@localhost etc]# docker exec -it inception /bin/bash [root@ace3ba0db738 /]# cd etc [root@ace3ba0db738 etc]# vi 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=inception_bak inception_remote_system_user=inception_bak inception_remote_backup_port=3306 inception_remote_backup_host=192.168.2.12 #切记将此处的地址从127.0.0.1改成inception所在机器的地址 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
注意：红色标记的地方应该与archer的setting.xml文件保持一致
点击查看回滚SQL，可以将执行的SQL进行回滚
出现上面的问题，应该是执行sql的数据库没开启binlog
配置脱敏字段：
配置完后，配置脱敏规则，如下图所示
如上图所示，身份证号和手机号脱敏查询。
参考地址：https://blog.csdn.net/leo0805/article/details/79741057
浙公网安备 33010602011771号