Year open source SQL audit platform installation and use

Keywords: Database MySQL SQL Linux

1, Introduction to year

   year is the MYSQL SQL statement audit platform. Provide query audit, SQL audit and other functions.

  • The edition of year 1. X needs the function of SQL audit and rollback provided by inception.
  • Since year 2.0 does not need to rely on perception, it has implemented SQL audit / rollback function
       the version of this article is Yearning2.2.0.

Two, installation

Download address

Matters needing attention

  • Year does not rely on any third-party SQL audit tool as an audit engine. Internal audit / rollback logic has been implemented by itself.
  • Only rely on Mysql database.
  • mysql version must be 5.7 or above. Please install it yourself and create Yearning Library in advance. The character set should be UTF8mb4 (only mysql version required by Yearning)
  • The annual log only outputs the error level. Without the log, no running error can be considered!
  • Year based on 1080p resolution development only supports 1080p and above display access
  • Due to the use of many new front-end technology stacks, please use the latest version of Chrome (excluding 360 and other magic versions)
    Installation steps
unzip -d /usr/local/
cd /usr/local/Yearning-go/

cat conf.toml
Db = "Yearning"
Host = ""
Port = "3306"
Password = "Yearning"
User = "Yearning"

[General]  #The database encryption and decryption key can only be changed once.
SecretKey = "dbcjqheupqjsuwsm"
SecretKey is the salt of token / database password encryption / decryption.
It is recommended that all users change the SecretKey before installing year for the first time (if not, there will be a security risk)
Format: both upper and lower case letters, length must be 16 bits
 Special attention:
This key can only be changed during the initial installation! It cannot be changed again after that! If it is changed again, the previously stored data source password will not be decrypted, and the related data source information will not be obtained
# ./Yearning -h
version: Yearning/2.2.0 author: HenryYee
Usage: Yearning [-m migrate] [-p port] [-s start] [-b web-bind] [-h help] [-c config file]

 -s  start-up Yearning
 -m  Data initialization(Executed on first installation)
 -p  port
 -b  Nail/Platform address displayed when mail is pushed
 -x  Table structure repair,It can be operated during upgrade. If there is an error, it can be ignored directly.
 -h  Help
 -c  Profile path
 -k  Change user authority to authority group(2.1.7 Upgrade below to 2.1.7 And above)
 -f  Initialization Admin User password

##Initialize data structure
./Yearning -m

[2020-03-01 18:17:10]  [0.47ms]  INSERT  INTO `core_accounts` (`username`,`password`,`rule`,`department`,`real_name`,`email`) VALUES ('admin','pbkdf2_sha256$120000$KMXHZJuaiVKW$EQ1XMcuApgEb6ysj6l9bOGzaVo+s/AGO13uOcNxpDD0=','admin','DBA','Super administrator','')  
[1 rows affected or returned ] 

[2020-03-01 18:17:10]  [0.80ms]  INSERT  INTO `core_global_configurations` (`authorization`,`ldap`,`message`,`other`,`stmt`,`audit_role`) 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}','{"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 ] 

[2020-03-01 18:17:10]  [0.62ms]  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 ] 
//Initialization succeeded!
 //User name: admin
//Password: year? Admin
##Start service
 ./Yearning -s
 Check for updates
 Data updated!

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

Welcome to Yearning
⇨ http server started on [::]:8000
 #Note that the port is open

3, Basic use

                       . Year recommends using Chrome browser.

In the homepage, you can see the current user number, data source, work order number, query number, personal information, work order submission statistics, high frequency library, and work order trend.

   in my work order interface, you can see the historical work order, execution status, execution information, whether to back up, etc..

The    work order submission interface is divided into DDL/DML. If the error level is not 0, it is abnormal. If the scheduled execution is not set, the work order will be executed automatically after approval!


   in the approval interface, you can see the work order to be approved and the approved work order.

   historical work orders and query audits can be seen in the record interface.

   configure users in the users column of the management interface.

   configure the data source and read-write rules in the database column.


   set N permission groups in permission group interface to correspond to different roles.

   message push (pin, mail) and LDAP can be set in the setting interface.

   you can set approval rules in the approval rules interface, which is quite rich.

   set AutoTask in the AutoTask interface.

   set group announcement in announcement interface.

Reference documents:

Posted by sasa on Mon, 02 Mar 2020 01:43:58 -0800