mysql用户授权操作
mysql 对用户授权的操作
?
? grant? 权限? on? 数据库名.表名 to 用户名@'可以访问的地址' identified by "密码"
??
?
权限：? all? 所有权限??? usage 无权限?? select,update,insert,delete，等权限
数据库.表名：? *.*?????????? 所有数据库的所有表
????????????????????????????? 数据库名.*???? 单个数据库的所有表
???????????????????????????? 数据库名.表名???? 单个数据库的某个表
用户名： 授权的用户名
可以访问的地址：?? % 所有地址，但是localhost不能访问
??????????????????????????????????????? localhost?? 只有localhost可以访问
?????????????????????????????????????? 192.168.1.0/24 可以访问网段地址
?????????????????????????????????????? 192.168.1.1 只能某个地址访问
?实验部分：
?
??? 1、给zhaoyun用户授予在localhost登陆，对zhaoyun数据库可以进行 create,select,update,insert,delete操作，但是不设置密码就可以登陆；????
mysql> grant create,select,update,insert,delete on zhaoyun.* to zhaoyun@'localhost';
?
登陆测试
[root@zhaoyun ~]# mysql -uzhaoyun
mysql> use mysql ;?? #zhaoyun用户对mysql数据库没有任何权限。
ERROR 1044 (42000): Access denied for user 'zhaoyun'@'localhost' to database 'mysql'
mysql>
mysql> use zhaoyun ;? #可以使用zhaoyun数据库
Database changed
mysql> show grants ;??? #查看当前用户拥有的权限
+--------------------------------------------------+
| Grants for zhaoyun@localhost???????????????????????????????????????????????????????? |
+--------------------------------------------------+
| GRANT USAGE ON *.* TO 'zhaoyun'@'localhost'????????????????????????????????????????? |
| GRANT SELECT, INSERT, UPDATE, DELETE, CREATE ON `zhaoyun`.* TO 'zhaoyun'@'localhost' |
+--------------------------------------------------+
2 rows in set (0.00 sec)
mysql> create table test(user char(3));???? #建表测试
Query OK, 0 rows affected (0.00 sec)
mysql> select * from test ;??? #查询测试
Empty set (0.00 sec)
mysql> insert into test values('zhaoyun');?? #插入数据测试
Query OK, 1 row affected, 1 warning (0.01 sec)
?
2、给zhaoyun用户授予从192.168.0.7的机器登陆，并有相应的权限。
mysql> grant create,select,insert,update on zhaoyun.* to zhaoyun@'192.168.0.7' i
dentified by "zhaoyun";
Query OK, 0 rows affected (0.00 sec)
客户端登陆测试
[root@zhaoyun ~]# mysql -h192.168.0.55 -uzhaoyun -pzhaoyun;
Welcome to the MySQL monitor.? Commands end with ; or \g.
Your MySQL connection id is 11
Server version: 5.1.32-community MySQL Community Server (GPL)
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql> show grants ;
+------------------------------------------+
| Grants for zhaoyun@192.168.0.7?????????????????????????????????????????????????????????????????????????????????? |
+------------------------------------------+
| GRANT USAGE ON *.* TO 'zhaoyun'@'192.168.0.7' IDENTIFIED BY PASSWORD '*875232B4D3487BBF724E4A0B22DB6A8DFC489C11' |
| GRANT SELECT, INSERT, UPDATE, CREATE ON `zhaoyun`.* TO 'zhaoyun'@'192.168.0.7'?????????????????????????????????? |
+------------------------------------------+
2 rows in set (0.01 sec)
把ip地址改成192.168.0.8测试，就 不行了
[root@zhaoyun ~]# ifconfig eth1 192.168.0.8
[root@bogon red hat 5]# ifconfig eth1 |grep addr
????????? inet addr:192.168.0.8? Bcast:192.168.0.255? Mask:255.255.255.0
?? [root@bogon red hat 5]# mysql -h192.168.0.55 -uzhaoyun -pzhaoyun
ERROR 1130 (00000): Host '192.168.0.8' is not allowed to connect to this MySQL server
再改回来：就可以了。
[root@bogon red hat 5]# ifconfig eth1 192.168.0.7
[root@bogon red hat 5]# mysql -h192.168.0.55 -uzhaoyun -pzhaoyun
Welcome to the MySQL monitor.? Commands end with ; or \g.
Your MySQL connection id is 13
Server version: 5.1.32-community MySQL Community Server (GPL)
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql>
上面授予的权限没有delete权限，测试下
有问题了吧
mysql> delete from t1 ;
ERROR 1142 (42000): DELETE command denied to user 'zhaoyun'@'BOGON' for table 't1'
?
3、给用户授予可以把自己权限再授给其他人的权限。
mysql> grant create on zhaoyun.t1 to zhaoyun@'192.168.0.7' identified by "zhaoyu
n" with grant option ;
Query OK, 0 rows affected (0.00 sec)
4、查看权限
mysql> show grants ;? #查看自己的权限
mysql> show grants for zhaoyun@'192.168.0.7'; #查看其他用户的权限。
?
mysql> show grants ;
+---------------------------------+
| Grants for root@localhost?????????????????????????????????????????? |
+---------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' WITH GRANT OPTION |
+---------------------------------+
1 row in set (0.00 sec)
mysql> show grants for zhaoyun@'192.168.0.7';
+-------------------------------------------
-----------------------------------+
| Grants for zhaoyun@192.168.0.7
?????????????????????????????????? |
+-------------------------------------------
-----------------------------------+
| GRANT USAGE ON *.* TO 'zhaoyun'@'192.168.0.7' IDENTIFIED BY PASSWORD '*875232B
4D3487BBF724E4A0B22DB6A8DFC489C11' |
| GRANT SELECT, INSERT, UPDATE, CREATE ON `zhaoyun`.* TO 'zhaoyun'@'192.168.0.7'
?????????????????????????????????? |
| GRANT CREATE ON `zhaoyun`.`t1` TO 'zhaoyun'@'192.168.0.7' WITH GRANT OPTION
?????????????????????????????????? |
+-------------------------------------------
-----------------------------------+
3 rows in set (0.01 sec)
?
5、撤销权限
mysql> revoke create on zhaoyun.* from zhaoyun@'192.168.0.7' ;
mysql> revoke create on zhaoyun.* from zhaoyun@'192.168.0.7' ;
Query OK, 0 rows affected (0.02 sec)
?
?
#客户端测试
mysql> use zhaoyun ;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> select * from zhaoyun ;
ERROR 1142 (42000): SELECT command denied to user 'zhaoyun'@'BOGON' for table 'zhaoyun'
mysql>? 可以登陆但是没有权限了。
将用户删除就不能登陆了。
mysql> delete from user where user='zhaoyun';
Query OK, 2 rows affected (0.00 sec)
mysql> flush privileges ;
Query OK, 0 rows affected (0.01 sec)
[root@bogon red hat 5]# mysql -h192.168.0.55 -uzhaoyun -pzhaoyun
ERROR 1130 (00000): Host 'BOGON' is not allowed to connect to this MySQL server
