MySQL Security Assessment Tool

Highly Confidential

Table of contents:

Security Checks   Configuration & Status

GDPR Cross ReferenceCIS Benchmarks Cross Reference


Statistics generated on: 2018-03-30 15:21:11 by: root@ as: skip-grants user@skip-grants host using: mysat.sql v.1.0.0 (2018-04-01)
Software by XeniaLAB

Security Checks

Database Access Control
Separation of Roles
CRUD users Evaluate xcall_rw@localhost
Application user credential protection
Connection strings ExternalCheck protection and obfuscation
Application user credential usage
Threads connections FailAccess found root@
Lifecycle management
Password expire Pass xcally xcall xcall_rw xcall_ro xsupport
OS privilege escalation
Anonymous user Pass

Monitoring and Audit

SQL controls
Suspect SQL Pass
Strict SQL mode Pass
Logging
Slow log FailSlow query log disabled
Slow log timeout Pass
Error Log Pass
Binlog Path Pass NULL
Error Level Fail
Log analysis
Automatic log analyzeExternalLog analysis is strongly suggested
Event management
Log ManagementExternalLog management is strongly suggested
Auditing
Auditing active FailAudit not enabled
Auditing event configuration
Auditing users whitelist

Data Protection

Application encryption
Tablespace encryption
Encryption enabled Pass
Suspect sensitive tables EvaluateSuspect tables motion2.analytics_extracted_reports motion2.attachments motion2.chat_interactions motion2.chat_visitors motion2.chat_websites motion2.cm_companies motion2.cm_contacts motion2.int_desk_accounts motion2.int_freshdesk_accounts motion2.int_salesforce_accounts motion2.int_sugarcrm_accounts motion2.int_zendesk_accounts motion2.mail_accounts motion2.mail_applications motion2.mail_dispositions motion2.mail_interaction_has_tags motion2.mail_interactions motion2.mail_messages motion2.mail_rooms motion2.mail_servers_in motion2.mail_servers_out motion2.report_call_transfer motion2.report_mail_queue motion2.report_square motion2.settings motion2.sms_accounts motion2.square_recordings motion2.team_has_mail_queues motion2.tools_schedules motion2.tools_trunks motion2.user_has_mail_interactions motion2.user_has_mail_queues motion2.users motion2.voice_chanspy motion2.voice_extensions motion2.voice_voicemail motion2.voice_voicemail_messages
Network encryption
SSL/TLS configured Pass
Users required to use encryption Pass
Backup
DB Backup execution EvaluateNo backup detected
Backup encryption ExternalCheck backup encryption
Backup policies ExternalCheck backup retention policies
Binlog retention FailNot configured correctly
Data Masking
miXen package Pass

Secure configuration

Version Check
MySQL version Pass 5.7
Database Hardening
Anonymous user Pass
Any host access FailUsers can connect from everywhere MIXEN my2 xcall_ro xcally xsupport
DB Password check Pass
Backdoor users Pass
Test schema Pass
Admin or Oper users <>root Evaluate xsupport@%
IDSExternalConfigure the IDS to monitor honeypot data
Spammable tables Pass
Dedicated datadir Pass
Memcache plugin Pass
secure_file_priv Pass
Master Info FailOn file
Automatic User Creation Pass
Password lenght FailToo short
Password policy FailNot secure
Performance statistics Pass
local_infile Fail
Symbolic Links Fail
Skip grant Externalskip_grant_tables must not be used in my.cnf
Patching
MySQL update Pass 5.7.21
MySAT update Pass 1.0.0
 
GDPR Countdown
Days since promulgation Pass 702
Days since application ExternalLaw not yet in force -56
 

DB Configuration

Database Summary
Item Value
Version : 5.7.21
Created : 2017-10-14 13:19:33
Started : 2018-03-30 13:12:54
Database Size (MB): 464
Buffers Size (MB): 170
Defined Users : 11
Defined Schemata : 10
Defined Tables : 460
Sessions : 1
Sessions (active) : 1
Questions (#/sec.) : 0.12835
Connections (#/sec.) : 0.00078
BinLog Writes Day (MB) : 0
Hostname : xxx-MacBook-Pro.local
Port : 3306

Users
User Host SL IUD CDGRIA CCS CAE RR SSPFSR Select Execute Grant Expired Password lifetime Locked
MIXEN % NN NNN NNNNNN NNN NNN NN NNNNNN N N N N NULL N
xxx256 localhost NN NNN NNNNNN NNN NNN NN NNNNNN N N N N NULL N
my2 % NN NNN NNNNNN NNN NNN NN NNYNNN N N N N NULL N
mysql.session localhost NN NNN NNNNNN NNN NNN NN YNNNNN N N N N NULL Y
mysql.sys localhost NN NNN NNNNNN NNN NNN NN NNNNNN N N N N NULL Y
root localhost YY YYY YYYYYY YYY YYY YY YYYYYY Y Y Y N NULL N
xcall localhost NN NNN NNNNNN NNN NNN NN NNNNNN N N N Y 0 N
xcall_ro % NN NNN NNNNNN NNN NNN NN NNNNNN N N N Y 90 N
xcall_rw localhost NN NNN NNNNNN NNN NNN NN NNNNNN N N N Y 0 N
xcally % NN NNN NNNNNN NNN NNN NN NNNNNN N N N N 90 N
xsupport % YY YYY YYYYYY YYY YYY YY YYYYYY Y Y Y Y 90 Y

Schema/Object Matrix
Database Tables Indexes Routines Triggers Views Primary Keys Foreign Keys All
information_schema 61 0 0 0 0 0 0 61
mixen 5 5 7 0 0 5 0 22
motion2 166 507 0 0 0 166 284 1123
my2 5 5 2 0 0 2 0 14
mysql 31 32 0 0 0 29 0 92
performance_schema 87 0 0 0 0 0 0 87
sys 101 1 48 2 100 1 0 253
xcally 1 0 0 0 0 0 0 1
NULL 460 553 57 2 100 203 284 1659

Space Usage
Database Row# Data size Index size Total size MyISAM InnoDB Created
information_schema NULL 163,840 0 163,840 0 163,840 2018-03-30
mixen 108,982 5,767,168 0 5,767,168 0 5,767,168 2017-10-25
motion2 60,814 25,001,984 9,650,176 34,652,160 0 34,652,160 2018-03-14
my2 1,977,557 122,060,800 32,768 122,093,568 0 122,093,568 2017-10-23
mysql 4,835 2,601,903 226,304 2,828,207 370,607 2,457,600 2017-10-14
performance_schema 1,325,902 0 0 0 0 0 NULL
sys 6 16,384 0 16,384 0 16,384 2017-10-14
xcally 1 16,384 0 16,384 0 16,384 2018-02-23
NULL 9,144,768 476,722,095 9,909,248 486,631,343 370,607 486,260,736 2017-10-14

Biggest Objects
Database Object Type Engine Bytes Est. rows
my2 status T InnoDB 122,028,032 1,977,542
motion2 voice_voicemail_messages T InnoDB 4,751,360 10
mixen mx_surname_us T InnoDB 3,686,400 89,028
motion2 cm_contacts T InnoDB 3,129,344 16,937
motion2 cm_hopper_final T InnoDB 2,342,912 5,222
motion2 cdr T InnoDB 2,293,760 2,978
motion2 report_call T InnoDB 2,293,760 5,678
motion2 cm_hopper_history T InnoDB 2,260,992 5,253

Processes
IdUserHost DBCommandTimeState
5 root information_schema Query 0 executing

Tuning Parameters (most used ones)
Parameter ValueType
binlog_cache_size 32,768 Client Cache
binlog_stmt_cache_size 32,768 Client Cache
innodb_buffer_pool_size 134,217,728 Cache
innodb_flush_log_at_timeout 1 Tuning and timeout
innodb_flush_log_at_trx_commit 1 Tuning and timeout
innodb_lock_wait_timeout 50 Tuning and timeout
innodb_log_buffer_size 16,777,216 Cache
innodb_log_files_in_group 2 Tuning and timeout
innodb_log_file_size 50,331,648 Cache
innodb_thread_concurrency 0 Tuning and timeout
join_buffer_size 262,144 Client Cache
key_buffer_size 8,388,608 Cache
log_bin OFF Flag
long_query_time 10 Tuning and timeout
max_connections 151 Client Cache
max_heap_table_size 16,777,216 Cache
query_cache_size 1,048,576 Cache
query_cache_type OFF Flag
read_buffer_size 131,072 Client Cache
read_rnd_buffer_size 262,144 Client Cache
slow_query_log OFF Flag
sort_buffer_size 262,144 Client Cache
sync_binlog 1 Tuning and timeout
table_open_cache 2,000 Cache
thread_stack 262,144 Client Cache
tmp_table_size 16,777,216 Cache
wait_timeout 28,800 Tuning and timeout

Performance Statistics Summary
StatisticValueSuggested valuePotential Action
Uptime (days) 0.1
Buffer Cache: MyISAM Read Hit Ratio 86.36 >95 Increase KEY_BUFFER_SIZE
Buffer Cache: InnoDB Read Hit Ratio 98.83 >95 Increase INNODB_BUFFER_SIZE
Buffer Cache: MyISAM Write Hit Ratio NULL >95 Increase KEY_BUFFER_SIZE
Log Cache: InnoDB Log Write Ratio 57.14 >95 Increase INNODB_LOG_BUFFER_SIZE
Query Cache: Efficiency (Hit/Select) 0.00 >30
Query Cache: Hit ratio (Hit/Query Insert) NULL >80
Threads_connected 1 /151 Far from maximum Increase MAX_CONNECTIONS
Threads_running 1 LOW Check user load
Slow_queries 0 LOW Check application
DBcpu (SUM_TIMER_WAIT) 0.00032
Connections #/sec. 0.00078
Questions #/sec. 0.13302
SELECT #/sec. 0.13211
COMMIT #/sec. (TPS) 0.00000
COM DML #/sec. 0.13211
Bytes_sent Mb/sec. 0.00027
Bytes_received Mb/sec. 0.00014

SQL Statements Representativeness: 100.00 %
Schema Text Count Sum Timer Human Timer Average (sec.) Rows affected Rows Sent Rows Examined TMP Disk Create TMP Create Sort Merge# No Index No Good Index
information_schema SELECT `performance_schema` . `events_waits_summary_global_by_event_name` . `EVENT_NAME` AS `events` , `performance_schema` . `event ... 9 1329925000000 00:00:01.3299 0.148 0 111 17862 213 2739 0 9 0
information_schema SELECT ? , `sk` , ? , SUM ( IF ( `otype` = ?, ... ) ) , ? , SUM ( IF ( `otype` = ?, ... ) ) , ? , SUM ( IF ( `otype` = ?, ... ) ) , ... 3 554975000000 00:00:00.5549 0.185 0 36 24000 159 948 0 3 0
information_schema SELECT IF ( COUNT ( * ) > ?, ... ) FROM `INFORMATION_SCHEMA` . `TABLES` WHERE `CREATE_OPTIONS` LIKE ? ... 3 186386000000 00:00:00.1863 0.062 0 3 1380 30 186 0 3 0
information_schema SELECT ? , `variable_name` , ? , `round` ( `variable_value` / ( ? * ? ) , ? ) , ?, ... FROM `performance_schema` . `global_status` W ... 3 151197000000 00:00:00.1511 0.050 0 54 25221 3 6 0 3 0
information_schema SELECT DISTINCTROW `concat` ( `table_schema` , ? , TABLE_NAME ) FROM `information_schema` . `columns` `c` WHERE `c` . `table_schema` ... 3 66118000000 00:00:00.0661 0.022 0 111 7584 3 6 0 3 0

Host Cache
Host IP Validated SUM Errors First Seen Last Seen Last Error Seen # Handshake Err. # Authentication Err. # ACL Err.

MySQL Parameters
Parameter Value
autocommit ON
automatic_sp_privileges ON
auto_generate_certs ON
auto_increment_increment 1
auto_increment_offset 1
avoid_temporal_upgrade OFF
back_log 80
basedir /usr/local/Cellar/mysql/5.7.21/
big_tables OFF
... ...
tx_isolation REPEATABLE-READ
tx_read_only OFF
unique_checks ON
updatable_views_with_limit YES
version 5.7.21
version_comment Homebrew
version_compile_machine x86_64
version_compile_os osx10.13
wait_timeout 28800

GDPR Cross Reference

GDPR Article Title Checks
6Lawfulness of processing Tablespace encryption, Encryption enabled, Suspect sensitive tables
7Conditions for consent
16Right to rectification
17Right to erasure (‘right to be forgotten’) Backup policies, Binlog retention, System backup,
18Right to restriction of processing Separation of Roles, Data Masking, miXen package
20Right to data portability
25Data protection by design and by default Application user credential protection, Password expire, Application encryption, Password policy, Security Flags, Separation of Roles, Data Masking, miXen package
29Processing under the authority of the controller or processor Separation of Roles, Data Masking, miXen package
30Records of processing activities Event management, Log Management, Auditing
32Security of processing Secure configuration, DB Password check, Performance statistics, Monitoring and Audit, Suspect SQL, Logging , IDS, Auditing, Network encryption, SSL/TLS configured, Users required to use encryption, DB Backup execution, System backup, Tablespace encryption, Encryption enabled, Suspect sensitive tables, Separation of Roles, Data Masking, miXen package
33Notification of a personal data breach to the supervisory authority Log Management, Auditing, IDS
34Communication of a personal data breach to the data subject Tablespace encryption, Encryption enabled, Suspect sensitive tables
35Data protection impact assessment Suspect sensitive tables
89Safeguards and derogations relating to processing for archiving purposes in the public interest, scientific or historical research purposes or statistical purposes Separation of Roles, Data Masking, miXen package
99Entry into force and application GDPR Countdown, Days since promulgation, Days since application

CIS Benchmark Cross Reference

CIS Recommentation
for MySQL 5.7 CE 		Title Checks
1.1Place Databases on Non-System Partitions Dedicated datadir
2.1.1 Backup policy in place DB Backup execution, Backup policies
2.1.4 The backups should be properly secured Backup encryption
2.6 Set a Password Expiry Policy for Specific Users Password expire
4.1 Ensure Latest Security Patches Are Applied MySQL update
4.2 Ensure the test Database Is Not Installed Test schema
4.4 Ensure local_infile Is Disabled local_infile
4.6 Ensure --skip-symbolic-links Is Enabled Symbolic Links
4.7 Ensure the daemon_memcached Plugin Is Disabled Memcache plugin
4.8 Ensure secure_file_priv Is Not Empty secure_file_priv
4.9 Ensure sql_mode Contains STRICT_ALL_TABLES Strict SQL mode
5.1 Ensure Only Administrative Users Have Full Database Access Users
5.2 Ensure file_priv Is Not Set to Y for Non-Administrative Users Admin or Oper users <>root, Users
5.3 Ensure process_priv Is Not Set to Y for Non-Administrative Users Users
5.4 Ensure super_priv Is Not Set to Y for Non-Administrative Users Admin or Oper users <>root, Users
5.5 Ensure shutdown_priv Is Not Set to Y for Non-Administrative Users Users
5.6 Ensure create_user_priv Is Not Set to Y for Non-Administrative Users Users
5.7 Ensure grant_priv Is Not Set to Y for Non-Administrative Users Users
5.8 Ensure repl_slave_priv Is Not Set to Y for Non-Administrative Users Users
5.9 Ensure DML/DDL Grants Are Limited to Specific Databases and Users CRUD users, Admin or Oper users <>root, Users
6.1 Ensure log_error Is Not Empty Error Log
6.2 Ensure Log Files Are Stored on a Non-System Partition Binlog Path
6.3 Ensure log_error_verbosity Is Not Set to 1 Error Level
6.4 Ensure Audit Logging Is Enabled Auditing active, Auditing event configuration, Auditing users whitelist
7.2 Ensure sql_mode Contains NO_AUTO_CREATE_USER Automatic User Creation
7.3 Ensure Passwords Are Set for All MySQL Accounts DB Password check
7.4 Ensure default_password_lifetime Is Less Than Or Equal To 90 Password expire
7.5 Ensure Password Complexity Is in Place Password lenght, Password policy
7.6 Ensure No Users Have Wildcard Hostnames Any host access
7.7 Ensure No Anonymous Accounts Exist Anonymous user
8.1 Ensure have_ssl Is Set to YES SSL/TLS configured
8.2 Ensure ssl_type Is Set to ANY, X509, or SPECIFIED for All Remote Users Users required to use encryption
9.2 Ensure MASTER_SSL_VERIFY_SERVER_CERT Is Set to YES or 1 Verify Master certificate
9.3 Ensure master_info_repository Is Set to TABLE Master Info
9.4 Ensure super_priv Is Not Set to Y for Replication Users Admin or Oper users <>root, Users
9.5 Ensure No Replication Users Have Wildcard Hostnames Users


The MIT License
Copyright © 2017-2018 XeniaLAB srl http://www.xenialab.it

Permission is hereby granted, free of charge, to any person obtaining a copy of this software and associated documentation files (the "Software"), to deal in the Software without restriction, including without limitation the rights to use, copy, modify, merge, publish, distribute, sublicense, and/or sell copies of the Software, and to permit persons to whom the Software is furnished to do so, subject to the following conditions:
The above copyright notice and this permission notice shall be included in all copies or substantial portions of the Software.

THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY, FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL THE AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING FROM, OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN THE SOFTWARE.


Statistics generated on: 2018-03-30 15:21:12

For more information or suggestions on MySAT contact XeniaLAB.