Network Security Internet Technology Development Database Servers Mobile Phone Android Software Apple Software Computer Software News IT Information

In addition to Weibo, there is also WeChat

Please pay attention

WeChat public account

Shulou

Yearning and inception build MySQL Audit platform

2025-01-28 Update From: SLTechnology News&Howtos shulou NAV: SLTechnology News&Howtos > Database >

Share

Shulou(Shulou.com)06/01 Report--

Preface

Using open source Yearning and inception open source software, build an audit platform for MYSQL audit and online MYSQL statement update.

Function description

Yearning: a complete mysql-sql audit platform solution based on Vue.js and Django. Provide Inception-based SQL detection and execution, provide visualization for Inception.

Inception: it is an automated operation and maintenance system that integrates audit, execution and rollback. It is modified according to MySQL code. It can be used to clearly, detailedly and accurately audit the SQL statements of MySQL. Its working mode is exactly the same as MySQL. It can directly use MySQL client to connect, but does not need to verify permissions. It is a server relative to the application (upper audit process system, etc.). When connecting, you need to specify the address of the server and the port of the Inception server. Compared with the online MySQL server corresponding to the statements to be audited or executed, it is a client. It needs to connect to the database server in real time to get the required information, or directly execute the corresponding statements online and obtain binlog. Inception is an intermediate service.

Operating system and IP information

Serial number

Server name

IP

Operating system

one

Centos_6

10.21.88.51

CentOS release 6.8 (Final)

Software version and installation path

Serial number

Software

Version information

Installation path

Code directory

one

Python

3.6.6

/ usr/local/python

two

Yearning

None (latest)

/ opt/Yearning

three

Inception

2.1.50

/ usr/local/inception

four

Nginx

1.10.2

Yum installation

/ usr/share/nginx/html

five

Mysql

5.7.21

/ usr/local/mysql

Installation process

Dependent package installation

Yum-y install cmake bison ncurses-devel gcc gcc-c++ openssl-devel

Inception installation

Cd / optwget https://github.com/mysql-inception/inception/archive/master.zipmkdir-p / usr/local/inceptionmkdir-p / inception/ {data Logs} unzip master.zip-d / inceptioncd / inception/inception-mastercmake-DWITH_DEBUG=OFF-DCMAKE_INSTALL_PREFIX=/usr/local/inception-DMYSQL_DATADIR=/inception/data-DWITH_SSL=yes-DCMAKE_BUILD_TYPE=RELEASE-DWITH_ZLIB=bundled-DMY_MAINTAINER_CXX_WARNINGS= "- Wall-Wextra-Wunused-Wwrite-strings-Wno-strict-aliasing-Wno-unused-parameter-Woverloaded-virtual"-DMY_MAINTAINER_C_WARNINGS= "- Wall-Wextra-Wunused-Wwrite-strings-Wno-strict-aliasing-Wdeclaration-after-statement" make & & make install

Configuration

Edit the parameter file vim / etc/ inc.cnf [inception] general_log=1 # which is the parameter of the native MySQL and is used to record which statements have been executed on the Inception service Used to locate some problems, such as general_log_file=/usr/local/inception/data/inception.log # set the file path written by general log # Inception service port socket=/usr/local/inception/data/inc.socket # Inception socket file storage location character-set-server=utf8 # mysql native parameters # Inception audit rules inception_check_autoincrement_datatype=1 # when creating a table, the type of self-incrementing column is not int or bigint Times error inception_check_ Autoincrement_init_value=1 # when creating a table, the value of the self-incrementing column is not specified as 1 Then report an error inception_check_autoincrement_name=1 # when building a table, if the name of the specified self-incrementing column is not ID, the error is reported, which means it is meaningful. When prompting inception_check_column_comment=1 # to build a table, the column has no comments and inception_check_column_default_value=0 # check whether the new column attribute should have a default value when creating a table, modifying a column, or adding a column. Inception_check_dml_limit=1 # when LIMIT is used in the DML statement Do you want to report an error inception_check_dml_orderby=1 # when you use Order By in the DML statement, do you want to report an error inception_check_dml_where=1 # when there is no WHERE condition in the DML statement, do you want to report an error inception_check_identifier=1 # turn on and off Inception's checking of various names in the SQL statement, if set to ON, if you find that there are characters other than numbers, letters and underscores in the name Will be reported Identifier "invalidname" is invalid, valid options: [a valid options ZMagol 0-9 inception_check_index_prefix=1 # is to check whether the prefix of the index name is "idx_" and whether the unique index prefix is "uniq_" inception_check_insert_field=1 # is to check the existence of the linked list of columns in the insert statement inception_check_primary_key=1 # if there is no primary key, it will report an error inception_check_table_comment=0 # when building the table There is no comment on the table inception_check_timestamp_default=0 # when building a table, if you do not specify a default value for the timestamp type, you will report an error if the inception_enable_autoincrement_unsigned=1 # self-increment column is to check whether the BLOB field is supported for the unsigned inception_enable_blob_type=0 #. Including table creation, column modification, and new column operations are enabled by default inception_enable_column_charset=0 # allows columns to set their own character set inception_enable_enum_set_bit=0 # whether the enum,set,bit data type inception_enable_foreign_key=0 # supports foreign key inception_enable_identifer_keyword=0 # check whether there is an identifier written as the keyword MySQL in the SQL statement. The default value is alarm. Inception_enable_not_innodb=0 # the storage engine specified for table creation is not Innodb, and does not report errors if inception_enable_nullable=0 # is listed as NULL when creating or adding columns. Do not report errors when inception_enable_orderby_rand=0 # orderby rand? is inception_enable_partition_table=0 # supported when partitioning table inception_enable_select_star=0 # Select*? is inception_enable_sql_statistic=1 # set to support statistics of statements executed by Inception? What is the proportion of various statements? if this parameter is turned on, each execution will store the statistics of this operation as a record in the statistic table of the inception library in the backup database instance. Each operation corresponds to a record. The information contained in this record is the number of statements executed of various types. Inception_max_char_length=16 # when the length of the char type is greater than this value, it is prompted to convert it to VARCHARinception_max_key_parts=5 # the maximum number of columns in an index, more than this number will report an error inception_max_keys=16 # the maximum number of indexes in a table, inception_max_update_rows=10000 # the maximum number of rows expected to be affected in a modification statement If you exceed this number, inception_merge_alter_table=1 # will report an error in multiple statements that change the same table, prompting you to synthesize a # inception supporting OSC parameter inception_osc_bin_dir=/user/bin # to specify the location of the pt-online-schema-change script, which cannot be modified. Set inception_osc_check_interval=5 # corresponding to the OSC parameter-check-interval in the configuration file. The meaning is that Sleep time between checks for-max-lag.inception_osc_chunk_size=1000 # corresponds to OSC parameter-chunk-sizeinception_osc_chunk_size_limit=4 # corresponds to OSC parameter-chunk-size-limitinception_osc_chunk_time=0.1 # corresponds to OSC parameter-chunk-timeinception_osc_critical_thread_connected=1000 # corresponds to parameter-thread_connected part inception_osc_critical_thread_running=80 # in critical-load corresponds to parameter-thread_ in critical-load Running part inception_osc_drop_new_table=1 # corresponding parameter-[no] drop-new-tableinception_osc_drop_old_table=1 # corresponding parameter-[no] drop-old-tableinception_osc_max_lag=3 # corresponding parameter-max-laginception_osc_max_thread_connected=1000 # corresponding parameter-thread_connected part inception_osc_max_thread_running=80 # corresponding parameter in max-load-thread_running part inception_osc in max-load The parameter _ min_table_size=0 # is actually a switch for OSC If set to 0, all ALTER statements go OSC, and if set to non-0, the OSC mode is used when the table occupies a space larger than this value. In M, the table size is calculated by the statement: "select (DATA_LENGTH + INDEX_LENGTH) / 1024 INDEX_LENGTH 1024 from information_schema.tables where table_schema = 'dbname' and table_name =' tablename'". A global OSC switch, which is turned on by default, is set to OFF if you want to turn it off, so that inception_osc_print_none=1 # is directly modified to set in the Inception return result set. As to whether the standard output information of the original OSC in the execution process should be printed to the error message column corresponding to the result set, if it is set to 1, it will not be printed, if set to 0, it will be printed. If there is an error, it will print the corresponding parameter inception_osc_print_sql=1 #-print# backup server information, pay attention to change it to your machine. Used for rollback. Inception_remote_system_password=P@ssw0rdinception_remote_system_user=incep_rwinception_remote_backup_port=3306inception_remote_backup_host=10.10.3.70inception_support_charset=utf8 # indicates the character set supported when building a table or database. If more than one is needed, it is separated by a comma. The scope of influence is to create a table, set a session character set, modify the attributes of a table character set, etc.

Start and test

Nohup / usr/local/inception/bin/Inception-- defaults-file=/etc/inc.cnf & netstat-tulpn | the default port of grep 6669 / / inception is 6669/usr/local/inception/bin/mysql-uroot-h227.0.0.1-P6669 / / Connect inception, inception get variables; / / View all parameters of inception in the same way as mysql

Install PYTHON

Cd / usr/local/srctar-xvf Python-3.6.6.tgzmkdir-p / usr/local/python/3.6.6/lib./configure-- enable-shared-- prefix=/usr/local/python/3.6.6 LDFLAGS= "- Wl -rpath / usr/local/python/3.6.6/lib "make & & make install soft connection cp / usr/bin/python / usr/bin/python2.6.6ln-fs / usr/local/python/3.6.6/bin/python3.6 / usr/bin/pythonln-fs / usr/local/python/3.6.6/bin/pip3 / usr/bin/pippython-vYum modify vi / usr/bin/yum to change the header #! / usr/bin/python to #! / usr/bin/python2.6.6

Yearning installation

Cd / optgit clone https://github.com/cookieY/Yearning.git

Edit Yearning/src/deploy.conf

[mysql] db = created library name address = database address port = database port password = database password username = database user [host] ipaddress = server ip address (platform address is displayed when messages are pushed)

Since Inception does not support pymysql natively, you need to change the source code related to pymysql. Note: connections.py and cursors.py that have been modified under the install folder can be replaced directly.

Pip3 install-r requirements.txt # install the corresponding python dependency library python3 manage.py makemigrations core & & python3 manage.py migrate core # initialize database echo "from core.models import Account;Account.objects.create_user (username='admin', password='Yearning_admin', group='admin',is_staff=1)" | python manage.py shell # add initialization user echo "from core.models import grained Grained.objects.get_or_create (username='admin', permissions= {'person': [],' ddl':'1, 'ddlcon': [],' dml':'1, 'dmlcon': [],' dic':'1), 'diccon': [],' dicedit':'0, 'user':' 1, 'base':' 1, 'dicexport':' 0, 'query':' 1' 'querycon': []}) "| python manage.py shell # initialization permission echo" from core.models import globalpermissions Globalpermissions.objects.get_or_create (authorization='global', inception= {'host':', 'port':', 'user':', 'password':', 'back_host':', 'back_port':', 'back_user':', 'back_password':'}, ldap= {'type':', 'host':', 'sc':'' 'domain':', 'user':', 'password':'}, message= {'webhook':', 'smtp_host':', 'smtp_port':', 'user':', 'password':', 'to_user':', 'mail': False,' ding': False}, other= {'limit':', 'con_room': [' AWS', 'Aliyun' 'Own',' Other'], 'foce':', 'multi': False,' query': False, 'sensitive_list': [] 'sensitive':'}) "| python manage.py shell # initialization dynamic configuration information cp-rf Yearning/webpage/dist/* $NGINX_HOME/html/ # copy the compiled static file to the nginx html directory (if you change the Nginx static path address, copy the static file to the corresponding static file directory) systemctl start nginx # start nginx$PYTHON_HOME/bin/gunicorn settingConf.wsgi:application-c gunicorn.conf # startup Django default account: admin password: Yearning_admin

instructions

You need to create a user first (the permissions of Yearning are divided into administrator and user. By default, only admin administrator account is used for audit. Create: user account is used for MYSQL audit submitted), and enter user name and password to log in:

User

Click "manage" after logging in, and the red circle is shown below.

Click "user" to add users. (example: an appuser user has been added as follows)

Database settings (configure database information for connection auditing, set a test connection as follows)

Settings (inception related settings, other settings slightly)

After the "users", "database" and "settings" under the relevant "management" above, the data audit operation can be carried out.

Submission and review of work orders

Work order submission

Create a work order after the user logs in, as shown in the following figure: (after filling in the information, check it correctly before submitting)

Audit (log in with the administrator account and enter the following interface for audit)

Click the "Action" of the work order to be reviewed to enter the following interface:

Performing the operation has completed the audit operation.

Rollback operation: (performed by the administrator and must be set up for the inception backup server)

After the work order is approved, there is a "View rollback statement" in the following figure. Click submit work order after entering the following interface, and the rollback operation is completed after re-approval.

The above is the MySQL audit platform built by Yearning and inception, and a simple introduction to its use. For more information on the use of Yearning, please refer to its official documentation.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.

Views: 230

*The comments in the above article only represent the author's personal views and do not represent the views and positions of this website. If you have more insights, please feel free to contribute and share.

Share To

Wechat

© 2024 shulou.com SLNews company. All rights reserved.

12
Report