Tag Archives: administration

Showing MySQL Permissions

Posted on by

Usage:

mysql> show grants for 'USERNAME'@'SERVER';

Example:

mysql> show grants for 'root'@'localhost';

+---------------------------------------------------------------------------------------------------------------+
| Grants for root@localhost                                                                                     |
+---------------------------------------------------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' IDENTIFIED BY PASSWORD '2e71fce803407b4a' WITH GRANT OPTION |
+---------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

See Also

Granting MySQL Permissions

Posted on by

Usage

mysql> grant PERMISSIONS on DBNAME.* TO 'USERNAME'[@'SERVER'] 
    -> identified by 'PASSWORD' [with grant option];
mysql> flush privileges;

Examples

mysql> GRANT SELECT,INSERT,UPDATE,DELETE,CREATE,DROP,ALTER,INDEX on exampledb.* 
    -> TO 'exampleuser'@'10.10.10.10' IDENTIFIED BY 'secret';
Query OK, 0 rows affected (0.07 sec)
mysql> GRANT ALL PRIVILEGES on exampledb.* TO 'exampleuser'@'10.10.10.10' 
    -> IDENTIFIED BY 'secret' WITH GRANT OPTION;
Query OK, 0 rows affected (0.06 sec)

Notes

  • WITH GRANT OPTION should only be added when really needed; this privilege allows a user to grant to others (more info).
  • The location from where a user can connect (i.e. ‘username’@’server’) can be also a network if specified as  ‘username’@’10.120.%.%’

See Also