security_audit

NAME

security_audit(): Generate a server's security audit report.

TYPE

Procedure

DESCRIPTION

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                                     |
+------------------------------------------------------------------------------+

ENVIRONMENT

MySQL 5.1 or newer

SEE ALSO

killall, processlist_grantees, sql_accounts

AUTHOR

Shlomi Noach
 
common_schema documentation