认证:who am I ?
授权:what I can do ?
认证实际上就是一个验证凭证的过程、而进入MySQL 需要出示的凭证有:host、username、password
连接MySQL 常见有 2 种:
① TCP/IP 连接
加 -h 参数、通过TCP/IP 连接MySQL 实例、mysql.user 对来者进行认证
显然、对于TCP/IP 这种请求、MySQL 是要求您出示凭证的
② Socket
-S 参数 、只能在 MySQL 客户端和实例在同一台服务器上使用
㈡ 缺省有 2 个高危险用户:
㈢ % 不是万能的、至少无法替代 localhost、而 MySQL 默认却是以 localhost登陆
mysql> grant all on *.* to 'david'@'%' identified by 'oracle';
Query OK, 0 rows affected (0.05 sec)
[mysql@odd ~]$ mysql -udavid -poracle
ERROR 1045 (28000): Access denied for user 'david'@'localhost' (using password: YES)
[mysql@odd ~]$ mysql -udavid -poracle -h 127.0.0.1
ERROR 1045 (28000): Access denied for user 'david'@'localhost' (using password: YES)
mysql> grant all on *.* to 'david'@'localhost' identified by 'oracle';
Query OK, 0 rows affected (0.01 sec)
[mysql@odd ~]$ mysql -udavid -poracle
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 9
Server version: 5.5.16-log Source distribution
Copyright (c) 2000, 2011, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
㈣ 什么时候使用 FLUSH PRIVILEGES
先认清 2 个事实:
● MySQL 权限库 mysql 永驻内存
● FLUSH PRIVILEGES会强行刷新内存中的权限信息
要做:
直接对mysql 下的grant tables 进行DML、要做!
不做:
grant、revoke、create user、drop user 、不做!
㈤ 权限等级的那些事儿
⑴ 权限等级及语法规则
2 点补充:
① GRANT ALL 可授予某个Level 的所有可用权限
② WITH GRANT OPTION 子句可级联授权
⑵ 生效时间
Global Level 或 Database Level 权限变更后、需先 KILL 重连接
Table Level 或 Column Level 变更立即生效
⑶ 校验顺序
⑷ 对应的 grant tables
Globa Level ←●→ mysql.user
Database Level ←●→ mysql.db
Table Level ←●→ mysql.table_priv
Column Level ←●→ mysql.column_priv
By David lin
2013-05-31
Good Luck