security_routines
SYNOPSIS
Security routines: stored functions managing security and privileges information.
- duplicate_grantee(): Create new account (grantee), identical to given account.
- grant_access(): (META) Grant SELECT & EXECUTE to all grantees on common_schema.
- killall(): Kill connections with by matching GRANTEE, user or host.
- match_grantee(): Match an existing account based on user+host.
- mysql_grantee(): Return a qualified MySQL grantee (account) based on user and host.
- security_audit(): Generate a server's security audit report.
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 | +------------------------------------------------------------------------------+