mariadb数据库用户管理（创建、赋权、）

数据库查看当前用户 select user（）；

MariaDB [(none)]> select user();
+----------------+
| user()         |
+----------------+
| root@localhost |
+----------------+
1 row in set (0.00 sec)

数据库新建用户（--创建了一个名为：test 密码为：1234 的用户）

1 2	`MariaDB [(none)]> create user` `'test'@'localhost'` `identified by` `'1234';` `Query OK, 0 rows affected (0.00 sec)`

查询用户

MariaDB [zabbix]> select user,host from mysql.user;
+--------+-----------+
| user   | host      |
+--------+-----------+
| root   | 127.0.0.1 |
| root   | ::1       |
| root   | localhost |
| test   | localhost |
| zabbix | localhost |
+--------+-----------+
5 rows in set (0.00 sec)

修改用户密码

方法1，密码实时更新；修改用户“test”的密码为“5678”

1 2	`MariaDB [mysql]>` `set` `password` `for` `test@localhost =password('5678');` `Query OK, 0 rows affected (0.00 sec)`

MariaDB [mysql]> exit
Bye

[root@192 ~]# mysql -utest -p5678
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 271
Server version: 10.2.36-MariaDB MariaDB Server

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> select user();
+----------------+
| user() |
+----------------+
| test@localhost |
+----------------+
1 row in set (0.00 sec)

　方法2 ，需要刷新；修改用户“test”的密码为“1234”

MariaDB [(none)]> update mysql.user set password =password('root') where user='test';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0
 
MariaDB [(none)]> flush privileges;  刷新权限
Query OK, 0 rows affected (0.00 sec)

　　方法3，命令行修改 mysqladmin -u用户名 -p旧密码 password 新密码

[root@192 ~]# mysqladmin -uroot -p1 password root
[root@192 ~]# mysql -uroot -proot
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 95
Server version: 10.2.36-MariaDB MariaDB Server

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]>

分配用户权限

授予用户test通过外网IP对所有数据库所有表的全部权限

1 2	`MariaDB [(none)]> grant all privileges on . to` `'test'@'%'` `identified by` `'1234';` `Query OK, 0 rows affected (0.00 sec)`

　注意：修改完权限以后一定要刷新服务，或者重启服务，刷新服务用：flush privileges;　

查看用户权限 show grants for test；

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

　查看自己的权限 show grants

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

回收用户权限 revoke

MariaDB [(none)]> show grants for test;
+--------------------------------------------------------------------------------------------------------------+
| Grants for test@%                                                                                            |
+--------------------------------------------------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'test'@'%' IDENTIFIED BY PASSWORD '*A4B6157319038724E3560894F7F932C8886EBFCF' |
+--------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
 
MariaDB [(none)]> revoke all on *.* from test@'%';
Query OK, 0 rows affected (0.00 sec)
 
MariaDB [(none)]> show grants for test;           
+-----------------------------------------------------------------------------------------------------+
| Grants for test@%                                                                                   |
+-----------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'test'@'%' IDENTIFIED BY PASSWORD '*A4B6157319038724E3560894F7F932C8886EBFCF' |
+-----------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)