MySQL用户管理

MySQL用户管理

Mysql创建用户并且授权

mysql> grant all on *.* to 'user1'@'127.0.0.1' identified by '123456';
Query OK, 0 rows affected (0.00 sec)
mysql> quit
Bye
//说明：grant 表示授权，all表示所有，创建user1用户并授予其所有权限“*.*”（通配符），第一个*表示db_name；第二个*表示tb_name，同时指定其来源IP127.0.0.1，并设定密码

//测试
[root@dl-001 ~]# mysql -uuser1 -p123456 -h127.0.0.1        //登录成功
Warning: Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 15
Server version: 5.6.35 MySQL Community Server (GPL)

Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

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

mysql> quit
Bye

---

本地授权

[root@dl-001 ~]# mysql -uroot -p'mysqldl991124'    //登录
Warning: Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 17
Server version: 5.6.35 MySQL Community Server (GPL)

Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> grant all on *.* to 'user1'@'localhost' identified by '654321';
Query OK, 0 rows affected (0.00 sec)
mysql> quit
Bye

//测试
[root@dl-001 ~]# mysql -uuser1 -p654321
Warning: Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 18
Server version: 5.6.35 MySQL Community Server (GPL)

Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

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

mysql> show grants for user1@'127.0.0.1';    //查看user1用户的授权
+-----------------------------------------------------------------------------------------------------------------------+
| Grants for user1@127.0.0.1                                                                                            |
+-----------------------------------------------------------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'user1'@'127.0.0.1' IDENTIFIED BY PASSWORD '*6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9' |
+-----------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

---

针对具体的表进行授权

[root@dl-001 ~]# mysql -uroot -p'mysqldl991124'
Warning: Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 20
Server version: 5.6.35 MySQL Community Server (GPL)

Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

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

mysql>  GRANT SELECT, INSERT, UPDATE ON `db1`.* TO 'user2'@'127.0.0.1';
Query OK, 0 rows affected (0.00 sec)

mysql> show grants for user2@127.0.0.1;
+----------------------------------------------------------------+
| Grants for user2@127.0.0.1                                     |
+----------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'user2'@'127.0.0.1'                      |
| GRANT SELECT, INSERT, UPDATE ON `db1`.* TO 'user2'@'127.0.0.1' |
+----------------------------------------------------------------+
2 rows in set (0.00 sec)

mysql> quit
Bye