mysql创建远程登录用户/权限管理

1、创建用户

创建用户的语句

CREATE USER 'itbase'@'%' IDENTIFIED BY 'sf123456qwe';

看下用户的信息，发现已经创建了用户

mysql> select user, host from user;
+---------------+-----------+
| user          | host      |
+---------------+-----------+
| itbase        | %         |
| root          | %         |
| mysql.session | localhost |
| mysql.sys     | localhost |
+---------------+-----------+
4 rows in set (0.00 sec)

 下面用这个新创建的用户登陆数据库看看相关信息，发现只有information_schema，那说明有的库还没有对这个库进行赋权

[appdeploy@VM_0_15_centos bin]$ ./mysql -h 127.0.0.1 -uitbase -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 21
Server version: 5.7.27 MySQL Community Server (GPL)

Copyright (c) 2000, 2019, 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 databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
+--------------------+
1 row in set (0.00 sec)

2、怎样给数据库赋权

查看授权的权限有哪些

#下面是执行的语句
mysql> grant all privileges on *.* to 'itbase'@'%';
Query OK, 0 rows affected (0.00 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0.01 sec)

mysql> show grants for itbase;
+---------------------------------------------+
| Grants for itbase@%                         |
+---------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'itbase'@'%' |
+---------------------------------------------+
1 row in set (0.00 sec)
#从上面按有所有的权限哦


# 然后连上去看看，查看多少个数据，发现下面已经有了
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
4 rows in set (0.00 sec)

怎样撤销权限呢？下面是演示

# 下面将用户itbase的赋予select和delete的权限
mysql> grant select,delete on idbbase.* to 'itbase'@'%';
Query OK, 0 rows affected (0.00 sec)

# 刷新权限
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

# 查询下itbase有什么权限，发现itbase有select和delete的权限
mysql> show grants for itbase;
+-----------------------------------------------------+
| Grants for itbase@%                                 |
+-----------------------------------------------------+
| GRANT USAGE ON *.* TO 'itbase'@'%'                  |
| GRANT SELECT, DELETE ON `idbbase`.* TO 'itbase'@'%' |
+-----------------------------------------------------+
2 rows in set (0.00 sec)

# 尝试用revoke释放delete的权限
mysql> revoke delete on idbbase.* from 'itbase'@'%';
Query OK, 0 rows affected (0.00 sec)

# 提交权限
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

# 查看权限，下面确实少了delete的权限
mysql> show grants for itbase;
+---------------------------------------------+
| Grants for itbase@%                         |
+---------------------------------------------+
| GRANT USAGE ON *.* TO 'itbase'@'%'          |
| GRANT SELECT ON `idbbase`.* TO 'itbase'@'%' |
+---------------------------------------------+
2 rows in set (0.00 sec)