mysql对用户授权

mysql查看授权信息

1
2
3
4
5
6
7
8


MariaDB [(none)]> show grants;
+----------------------------------------------------------------------------------------------------------------------------------------+
| Grants for root@localhost                                                                                                              |
+----------------------------------------------------------------------------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' IDENTIFIED BY PASSWORD 'xxx' WITH GRANT OPTION |
| GRANT PROXY ON ''@'' TO 'root'@'localhost' WITH GRANT OPTION                                                                           |
+----------------------------------------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)

默认是root@localhost的权限

查看指定用户的权限

mysql把user和host组成二元组，构成一个用户，所以

1
2
3
4
5
6
7


MariaDB [(none)]> show grants for 'root'@'192.168.10.204';
+---------------------------------------------------------------------------------------------------------------------------+
| Grants for root@192.168.10.204                                                                                            |
+---------------------------------------------------------------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'root'@'192.168.10.204' IDENTIFIED BY PASSWORD 'xxx' |
+---------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

添加一个用户，并赋予特定权限

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14


MariaDB [(none)]> grant select on *.* TO 'test'@'192.168.0.230' identified by 'test';
Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> show grants for 'test';
ERROR 1141 (42000): There is no such grant defined for user 'test' on host '%'
MariaDB [(none)]> show grants for 'test'@'192.168.0.230';
+------------------------------------------------------------------------------------------------------------------+
| Grants for test@192.168.0.230                                                                                    |
+------------------------------------------------------------------------------------------------------------------+
| GRANT SELECT ON *.* TO 'test'@'192.168.0.230' IDENTIFIED BY PASSWORD '*94BDCEBE19083CE2A1F959FD02F964C7AF4CFC29' |
+------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

MariaDB [(none)]>

这个时候test就有了select查询的权限

删除一个用户的权限

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12


MariaDB [(none)]> revoke select on *.* for 'test'@'192.168.0.230';
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'for 'test'@'192.168.0.230'' at line 1
MariaDB [(none)]> revoke select on *.* from 'test'@'192.168.0.230';
Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> show grants for 'test'@'192.168.0.230';
+-----------------------------------------------------------------------------------------------------------------+
| Grants for test@192.168.0.230                                                                                   |
+-----------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'test'@'192.168.0.230' IDENTIFIED BY PASSWORD '*94BDCEBE19083CE2A1F959FD02F964C7AF4CFC29' |
+-----------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

修改一个用户的密码

1
2
3
4


MariaDB [(none)]> drop user 'test';
ERROR 1396 (HY000): Operation DROP USER failed for 'test'@'%'
MariaDB [(none)]> drop user 'test'@'192.168.0.230';
Query OK, 0 rows affected (0.00 sec)