privileges

1、环境

# 使用ipconfig命令获取主机ip地址

 

 

 

# 查看当前版本的数据库支持哪些权限：

 > select version();
+---------------+
| version() |
+---------------+
| 5.7.25-28-log |
+---------------+
1 row in set (0.00 sec)

Thu Aug 27 14:18:01 2020

 > show privileges;
+-------------------------+---------------------------------------+-------------------------------------------------------+
| Privilege               | Context                               | Comment                                               |
+-------------------------+---------------------------------------+-------------------------------------------------------+
| Alter                   | Tables                                | To alter the table                                    |
| Alter routine           | Functions,Procedures                  | To alter or drop stored functions/procedures          |
| Create                  | Databases,Tables,Indexes              | To create new databases and tables                    |
| Create routine          | Databases                             | To use CREATE FUNCTION/PROCEDURE                      |
| Create temporary tables | Databases                             | To use CREATE TEMPORARY TABLE                         |
| Create view             | Tables                                | To create new views                                   |
| Create user             | Server Admin                          | To create new users                                   |
| Delete                  | Tables                                | To delete existing rows                               |
| Drop                    | Databases,Tables                      | To drop databases, tables, and views                  |
| Event                   | Server Admin                          | To create, alter, drop and execute events             |
| Execute                 | Functions,Procedures                  | To execute stored routines                            |
| File                    | File access on server                 | To read and write files on the server                 |
| Grant option            | Databases,Tables,Functions,Procedures | To give to other users those privileges you possess   |
| Index                   | Tables                                | To create or drop indexes                             |
| Insert                  | Tables                                | To insert data into tables                            |
| Lock tables             | Databases                             | To use LOCK TABLES (together with SELECT privilege)   |
| Process                 | Server Admin                          | To view the plain text of currently executing queries |
| Proxy                   | Server Admin                          | To make proxy user possible                           |
| References              | Databases,Tables                      | To have references on tables                          |
| Reload                  | Server Admin                          | To reload or refresh tables, logs and privileges      |
| Replication client      | Server Admin                          | To ask where the slave or master servers are          |
| Replication slave       | Server Admin                          | To read binary log events from the master             |
| Select                  | Tables                                | To retrieve rows from table                           |
| Show databases          | Server Admin                          | To see all databases with SHOW DATABASES              |
| Show view               | Tables                                | To see views with SHOW CREATE VIEW                    |
| Shutdown                | Server Admin                          | To shut down the server                               |
| Super                   | Server Admin                          | To use KILL thread, SET GLOBAL, CHANGE MASTER, etc.   |
| Trigger                 | Tables                                | To use triggers                                       |
| Create tablespace       | Server Admin                          | To create/alter/drop tablespaces                      |
| Update                  | Tables                                | To update existing rows                               |
| Usage                   | Server Admin                          | No privileges - allow connect only                    |
+-------------------------+---------------------------------------+-------------------------------------------------------+
31 rows in set (0.00 sec)

Thu Aug 27 14:16:19 2020

 

 

 

 

2、创建用户：

 

查看当前用户，证明自己是谁。
用户名和允许登录的ip

 

 

 

 

3、用户登录：

 

 

 

 

 

#需要注意的是：用户登录ip的限制和写法：localhost，127.0.0.1，和ip地址
#1、当用户登录的主机可以是mysql服务所在主机时，那么在本地登录时，可以不指定mysql服务所在的主机名，比如这里新建的apple用户，就只能在本地登录，不用指定主机就可以成功登录。适合localhost和%
#2、当指定为127.0.0.1时，则必须指定主机ip地址为127.0.0.1或者主机ip地址
4 #3、

 

 

 

 

 

修改密码

 

 

1、usage权限

只有usage权限的情况，user表的权限列表全是N，表示没有这些权限。 usage权限是创建一个用户之后的默认权限，其本身代表连接登录权限。

 

 

 2、all privileges权限

只有all privileges权限的情况，除了没有grant option权限之外，其他权限均有。即没有给其他用户授权的功能。

 

3、process权限

 

process权限是一个全局权限，不可以指定在某一个库上，即不能将该权限赋给某个库上。

 

 

 这是没有赋予process权限时，只能查看得到该用户的会话。

 

 

下面这是赋予process权限的情况，
首先，这个需要在给予process权限之后，需要执行flush privileges来刷新权限
其次，还需要新开一个会话才能见效，因为即使该用户添加该权限之后且刷新了权限，如果还是修改之前的会话，那么该权限还是表现不出来的。
如果是之前已经建立连接的会话，必须退出重新登录，否则依然只能看到当前用户的线程。