mysql用户和权限管理

新建用户：insert into user(host,user,password,select_priv) values('localhost','testuser',password('123123'),'Y');
注意新建后必须用flush privileges刷新权限，否则新建的用户无法登录，除非重新启动mysql服务。

mysql> insert into user(host,user,password,select_priv) values('localhost','testuser',password('123123'),'Y');
Query OK, 1 row affected, 3 warnings (0.05 sec)

mysql> select host,user,password,select_priv,insert_priv,update_priv from user;
+---------------------------+------------------+-------------------------------------------+-------------+-------------+-------------+
| host                      | user             | password                                  | select_priv | insert_priv | update_priv |
+---------------------------+------------------+-------------------------------------------+-------------+-------------+-------------+
| localhost                 | root             | *9D244EF0DC2E16FC2EF469372F5E24ED87C94ECF | Y           | Y           | Y           |
| zhanghaipeng-lenovo-k2450 | root             | *9D244EF0DC2E16FC2EF469372F5E24ED87C94ECF | Y           | Y           | Y           |
| 127.0.0.1                 | root             | *9D244EF0DC2E16FC2EF469372F5E24ED87C94ECF | Y           | Y           | Y           |
| ::1                       | root             | *9D244EF0DC2E16FC2EF469372F5E24ED87C94ECF | Y           | Y           | Y           |
| localhost                 | debian-sys-maint | *B885D6BF31D9DBCCD1ACA8EBE20A5D381FE0CDAB | Y           | Y           | Y           |
| localhost                 | testuser         | *E56A114692FE0DE073F9A1DD68A00EEB9703F3F1 | Y           | N           | N           |
+---------------------------+------------------+-------------------------------------------+-------------+-------------+-------------+
6 rows in set (0.01 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0.03 sec)

除了上面的方法，还可以这样新增用户：grant select on mysql.* to 'test'@'localhost' identified by '123123';，这种方式不需要刷新权限即可登录。

mysql> grant select on mysql.* to 'test'@'localhost' identified by '123123'; 
Query OK, 0 rows affected (0.04 sec)

mysql> select host,user,password,select_priv,insert_priv,update_priv from user;
+---------------------------+------------------+-------------------------------------------+-------------+-------------+-------------+
| host                      | user             | password                                  | select_priv | insert_priv | update_priv |
+---------------------------+------------------+-------------------------------------------+-------------+-------------+-------------+
| localhost                 | root             | *9D244EF0DC2E16FC2EF469372F5E24ED87C94ECF | Y           | Y           | Y           |
| zhanghaipeng-lenovo-k2450 | root             | *9D244EF0DC2E16FC2EF469372F5E24ED87C94ECF | Y           | Y           | Y           |
| 127.0.0.1                 | root             | *9D244EF0DC2E16FC2EF469372F5E24ED87C94ECF | Y           | Y           | Y           |
| ::1                       | root             | *9D244EF0DC2E16FC2EF469372F5E24ED87C94ECF | Y           | Y           | Y           |
| localhost                 | debian-sys-maint | *B885D6BF31D9DBCCD1ACA8EBE20A5D381FE0CDAB | Y           | Y           | Y           |
| localhost                 | test             | *E56A114692FE0DE073F9A1DD68A00EEB9703F3F1 | N           | N           | N           |
+---------------------------+------------------+-------------------------------------------+-------------+-------------+-------------+
6 rows in set (0.00 sec)

授予权限：grant select,update,insert,drop on {database}.{table|*} to {user} ;
针对特定列授权：grant select(host,user) on mysql.user to test;
回收权限：revoke update,insert on {database}.{table|*} from user;
授予dba权限：grant all privileges on {database} to {user}; 或　grant all on {database} to {user};

权限列表：

• select
• update
• drop
• create
• execute : 执行存储过程
• delete
• index : create index 和 drop index
• reload : 使用flush
• create user
• create view
• create routine : create procedure
• alter routine : alter procedure 和 drop procedure
• shutdowm : mysqladmin shutdown