security_routines

SYNOPSIS

Security routines: stored functions managing security and privileges information.

EXAMPLES

Kill all connections made by the 'analytics` user:

mysql> CALL killall('analytics');

Duplicate (Copy+Paste) an existing account into a new one:

mysql> CALL duplicate_grantee('apps@localhost', 'apps@10.0.0.%');

Audit server's security:

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                                                                           |
| ---                                                                          |
| Report generated on '2012-09-21 11:49:52                                     |
+------------------------------------------------------------------------------+

 
common_schema documentation