如何改变mysql的授权问题_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

分享到：

2012-04-28 20:18

浏览 697

分类:数据库

评论