Tag Archives: mysql

Showing MySQL Permissions

Posted on by
Reply

Usage:

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

Example:

Shell
1
2
3
4
5
6
7
8
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
2

Usage

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

Examples

Shell
1
2
3
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)
Shell
1
2
3
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