security_audit
NAME
security_audit(): Generate a server's security audit report.TYPE
ProcedureDESCRIPTION
Audit a server's security setup, including reviewing accounts and settings.
This audit generates a human readable report with recommendations on actions to take so as to enhance server security. It does not take action nor modify any data.
security_audit() reviews the following:
- Non-local root accounts
- Anonymous users
- Accounts accessible by any host
- Password-less accounts
- Accounts sharing same password
- Non-root accounts with admin privileges
- Non-root accounts with global DDL privileges
- Non-root accounts with global DML privileges
- sql_mode
- Old passwords
SYNOPSIS
security_audit() READS SQL DATA
This procedure takes no input.
EXAMPLES
Audit a server:
mysql> CALL security_audit(); +------------------------------------------------------------------------------+ | report | +------------------------------------------------------------------------------+ | | | Checking for non-local root accounts | | ==================================== | | Recommendation: limit following root accounts to local machines | | > rename 'root'@'central' to 'root'@'localhost' | | | | Checking for anonymous users | | ============================ | | OK | | | | Looking for accounts accessible from any host | | ============================================= | | Recommendation: limit following accounts to specific hosts/subnet | | > rename user 'apps'@'%' to 'apps'@'<specific host>' | | > rename user 'world_user'@'%' to 'world_user'@'<specific host>' | | | | Checking for accounts with empty passwords | | ========================================== | | Recommendation: set a decent password to these accounts. | | > set password for 'apps'@'%' = PASSWORD(...) | | > set password for 'world_user'@'localhost' = PASSWORD(...) | | > set password for 'wu'@'localhost' = PASSWORD(...) | | | | Looking for accounts with identical (non empty) passwords | | ========================================================= | | Different users should not share same password. | | Recommendation: Change passwords for accounts listed below. | | | | The following accounts share the same password: | | 'temp'@'10.0.%' | | 'temp'@'10.0.0.%' | | 'gromit'@'localhost' | | | | The following accounts share the same password: | | 'replication'@'10.0.0.%' | | 'shlomi'@'localhost' | | | | The following accounts share the same password: | | 'shlomi'@'127.0.0.1' | | 'monitoring_user'@'localhost' | | | | Looking for (non-root) accounts with admin privileges | | ===================================================== | | Normal users should not have admin privileges, such as | | SUPER, SHUTDOWN, RELOAD, PROCESS, CREATE USER, REPLICATION CLIENT. | | Recommendation: limit privileges to following accounts. | | > GRANT <non-admin-privileges> ON *.* TO 'monitoring_user'@'localhost' | | > GRANT <non-admin-privileges> ON *.* TO 'shlomi'@'localhost' | | | | Looking for (non-root) accounts with global DDL privileges | | ========================================================== | | OK | | | | Looking for (non-root) accounts with global DML privileges | | ========================================================== | | OK | | | | Testing sql_mode | | ================ | | Server's sql_mode does not include NO_AUTO_CREATE_USER. | | This means users can be created with empty passwords. | | Recommendation: add NO_AUTO_CREATE_USER to sql_mode, | | both in config file as well as dynamically. | | > SET @@global.sql_mode := CONCAT(@@global.sql_mode, ',NO_AUTO_CREATE_USER') | | | | Testing old_passwords | | ===================== | | OK | | | | Checking for `test` database | | ============================ | | `test` database has been found. | | `test` is a special database where any user can create, drop and manipulate | | table data. Recommendation: drop it | | > DROP DATABASE `test` | | --- | | Report generated on '2012-09-21 11:49:52 | +------------------------------------------------------------------------------+