Mysql新建用户及删除和权限管理

新建用户

方法一:insert

MariaDB [wg]> insert into mysql.user(host,user,password) values('localhost','zhangsan',password('123456'));
Query OK, 1 row affected, 4 warnings (0.00 sec)

#创建一个用户名为zhangsan,密码为123456的数据库用户;此处的"localhost"，是指该用户只能在本地登录，不能在另外一台机器上远程登录。如果想远程登录的话，将"localhost"改为"%"，表示在任何一台电脑上都可以登录。也可以指定某台机器可以远程登录。

方法二:create

MariaDB [wg]> create user'username'@'localhost' identified by 'password';
#username:指定要新建的用户名
#localhost:指定该用户在哪个主机上可以登录，如果是本地用户可用localhost，如果想让该用户可以从任意远程主机登陆，可以使用通配符%
#Password：该用户的登陆密码，密码可以为空 #密码用" " |'' 

#新建远程登陆用户slave密码是123
MariaDB [wg]> create user 'slave'@'%' identified by '123';
Query OK, 0 rows affected (0.00 sec)

##新建本地登陆用户master密码是123
MariaDB [wg]> create user 'master'@'localhost' identified by '123';
Query OK, 0 rows affected (0.00 sec)

方法三：grant

#*.*为所有数据库下的所有表 
MariaDB [(none)]> grant all on *.* to 'wg'@'%' identified by '123';
Query OK, 0 rows affected (0.00 sec)

删除用户

drop不仅会将user表中的数据删除，还会删除其他权限表的内容。而delete只删除user表中的内容，所以使用delete删除用户后需要执行flush privileges;刷新权限，否则下次使用create语句创建用户时会报错。

1.delete

#删除时遇到错误是因为我们操作时没有在mysql的数据库中
MariaDB [wg]> delete from user where user='master' and host='localhost';
ERROR 1146 (42S02): Table 'wg.user' doesn't exist 

#我们使用use mysql 进入到mysql的数据中就可以删除了
MariaDB [mysql]> delete from user where user='master' and host='localhost';
Query OK, 1 row affected (0.00 sec)

MariaDB [mysql]> flush privileges;
Query OK, 0 rows affected (0.00 sec)

#delete只删除user表中的内容，所以使用delete删除用户后需要执行FLUSH PRIVILEGES;刷新权限，否则下次使用create语句创建用户时会报错
#mysql>drop database testDB; 删除用户的数据库

2.drop

删除账户及权限：

MariaDB [mysql]> drop user slave@'%';
Query OK, 0 rows affected (0.00 sec)


MariaDB [mysql]> drop user zhangsan@'localhost';
Query OK, 0 rows affected (0.00 sec)

授权

#授权test用户远程对所有数据库增删改查删库的权限
MariaDB [mysql]> grant select,delete,update,create,drop on *.* to test@'%' identified by '123';
Query OK, 0 rows affected (0.00 sec)

#授予所有的权利
MariaDB [mysql]> grant all on *.* to test@'%' identified by '123';
Query OK, 0 rows affected (0.00 sec)

#授权test用户拥有wg数据库的所有权限（某个数据库的所有权限）：
MariaDB [mysql]> grant all privileges on wg.* to test@'localhost' identified by '123';
Query OK, 0 rows affected (0.00 sec)

#授权test用户拥有testDB数据库的所有权限（某个数据库的所有权限
MariaDB [mysql]> grant all privileges on testDB.* to test@'localhost' identified by '123';
Query OK, 0 rows affected (0.00 sec)
#我们看到用grant授权时会自动创建test的数据库

**####`注意`:####**
#创建的用户无法给其它用户进行授权
[root@c7-43 ~]# mysql -u test -p   #以test用户进入数据库
Enter password:
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 32
Server version: 5.5.65-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)]> grant all on *.* to bb@'%' identified by '123';
ERROR 1045 (28000): Access denied for user 'test'@'localhost' (using password: YES)   #拒绝

#我们用root进入mysql
[root@c7-43 ~]# mysql -uroot -p
Enter password:
#赋予test的用户有授权的权限
MariaDB [mysql]> grant all privileges on *.* to test@localhost identified by '123456' with grant option;
Query OK, 0 rows affected (0.00 sec)

MariaDB [mysql]> exit
Bye

#我们以test的用户进入mysql
[root@c7-43 ~]# mysql -u test -p
Enter password:
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 37
Server version: 5.5.65-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.
#我们授权一个用户bb 
MariaDB [(none)]> grant all on *.* to bb@'%' identified by '123';
Query OK, 0 rows affected (0.00 sec)

##ok

#查看当前用户的权限
MariaDB [(none)]> show grants;
+----------------------------------------------------------------------------------------------------------------------------------------+
| Grants for test@localhost                                                                                                              |
+----------------------------------------------------------------------------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'test'@'localhost' IDENTIFIED BY PASSWORD '*6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9' WITH GRANT OPTION |
| GRANT ALL PRIVILEGES ON `wg`.* TO 'test'@'localhost'                                                                                   |
| GRANT ALL PRIVILEGES ON `testDB`.* TO 'test'@'localhost'                                                                               |
+----------------------------------------------------------------------------------------------------------------------------------------+
3 rows in set (0.00 sec)

#查看test的权限
MariaDB [(none)]> show grants for test;
+--------------------------------------------------------------------------------------------------------------+
| Grants for test@%                                                                                            |
+--------------------------------------------------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'test'@'%' IDENTIFIED BY PASSWORD '*23AE809DDACAF96AF0FD78ED04B6A265E05AA257' |
+--------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)


MariaDB [(none)]> show grants for test@'localhost';
+----------------------------------------------------------------------------------------------------------------------------------------+
| Grants for test@localhost                                                                                                              |
+----------------------------------------------------------------------------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'test'@'localhost' IDENTIFIED BY PASSWORD '*6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9' WITH GRANT OPTION |
| GRANT ALL PRIVILEGES ON `wg`.* TO 'test'@'localhost'                                                                                   |
| GRANT ALL PRIVILEGES ON `testDB`.* TO 'test'@'localhost'                                                                               |
+----------------------------------------------------------------------------------------------------------------------------------------+
3 rows in set (0.00 sec)

取消权限

[root@c7-43 ~]# mysql -uroot -p   #root进入数据库
Enter password:
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 38
Server version: 5.5.65-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)]> revoke all on *.* from test@'localhost';   #取消test的权限
Query OK, 0 rows affected (0.00 sec)
MariaDB [(none)]> exit
Bye
[root@c7-43 ~]# mysql -u test -p
Enter password:
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 39
Server version: 5.5.65-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)]> grant all on *.* to aa@'%' identified by '123';
ERROR 1045 (28000): Access denied for user 'test'@'localhost' (using password: YES)