mysql 默认的账户也是root,同linux一样也是超级管理员,权利特别大,所以我们不可以随便就把mysql密码告诉其他人。
另外一种情况,我们的数据库中有很多个库和表,针对不同的库和表有着不同的权限,所以我们就需要针对某个用户授权某一个指定的库,或者某个表有权限。
1.1 授权用户访问:
grant all on *.* to 'user1'@'127.0.0.1' identified by 'asd9577';
grant:授权;
*.*:所有的库;
all:所有的权限(查看,创建,删除);
user1::被授权的用户;
127.0.0.1:来源IP;当然我们可以写%,意味着所有的来源IP。
identified by:密码;
再次测试:
[[email protected] ~]# mysql -uuser1 -pasd9577
Warning: Using a password on the command line interface can be insecure.
ERROR 1045 (28000): Access denied for user 'user1'@'localhost' (using password: YES)
因为默认是使用socket连接,所以我们需要指定-h127.0.0.1
[[email protected]03 ~]# mysql -uuser1 -pasd9577 -h127.0.0.1
1.2 如何在登陆的时候不需要指定 -h 呢?
grant all on *.* to 'user1'@'localhost' identified by 'asd9577';
这样,下次我们再次进入mysql的时候就可以直接使用如下去登陆:
[root@zhdy-03 ~]# mysql -uuser1 -pasd9577
1.3 查看一个用户的授权;
使用例如一个user1用户登录,你直接输入如下就可以查看user1这个用户的授权,一样的道理,使用root账户登录,查看的时候直接就是root用户的授权。
mysql> show grants;
+----------------------------------------------------------------------------------------------------------------------------------------+
| Grants for [email protected] |
+----------------------------------------------------------------------------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' IDENTIFIED BY PASSWORD '*AE5ADFD6527BEAE360A3F86E1A2BD72DDE83EFDE' WITH GRANT OPTION |
| GRANT PROXY ON ''@'' TO 'root'@'localhost' WITH GRANT OPTION |
+----------------------------------------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)
或者使用root账户查看user1用户的授权:
mysql> show grants for [email protected];
+-----------------------------------------------------------------------------------------------------------------------+
| Grants for [email protected] |
+-----------------------------------------------------------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'user1'@'127.0.0.1' IDENTIFIED BY PASSWORD '*AE5ADFD6527BEAE360A3F86E1A2BD72DDE83EFDE' |
+-----------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
1.4 授权user2 来源IP 为192.168.59.1可以SECLECT,UPDATE,INSERT,且在db1库中。
mysql> grant SELECT,UPDATE,INSERT on db1.* to 'user2'@'192.168.59.1' identified by 'asd9577';
Query OK, 0 rows affected (0.00 sec)
再次查看一下:
mysql> show grants for user2;
ERROR 1141 (42000): There is no such grant defined for user 'user2' on host '%'
mysql> show grants for [email protected];
+-----------------------------------------------------------------------------------------------------------------+
| Grants for [email protected] |
+-----------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'user2'@'192.168.59.1' IDENTIFIED BY PASSWORD '*AE5ADFD6527BEAE360A3F86E1A2BD72DDE83EFDE' |
| GRANT SELECT, INSERT, UPDATE ON `db1`.* TO 'user2'@'192.168.59.1' |
+-----------------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)
有这样一个请求,这个user2用户不仅需要在192.168.59.1上面使用mysql,而且也需要在192.168.59.2需要,但是我们不知道密码,只是知道root的密码。其目的只是增加IP地址其它的不变(我们就需要如上框内查询出来的信息去更改):
mysql> GRANT USAGE ON *.* TO 'user2'@'192.168.59.2' IDENTIFIED BY PASSWORD '*AE5ADFD6527BEAE360A3F86E1A2BD72DDE83EFDE';
Query OK, 0 rows affected (0.00 sec)
mysql> GRANT SELECT, INSERT, UPDATE ON `db1`.* TO 'user2'@'192.168.59.1';
Query OK, 0 rows affected (0.00 sec)
再次确认下:
mysql> show grants for [email protected];
+-----------------------------------------------------------------------------------------------------------------+
| Grants for [email protected] |
+-----------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'user2'@'192.168.59.2' IDENTIFIED BY PASSWORD '*AE5ADFD6527BEAE360A3F86E1A2BD72DDE83EFDE' |
+-----------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
已经查到了相关的信息!