MySQL数据库管理

一、MySQL用户管理

1、新建用户

2、删除用户

3、重命名用户

一、MySQL用户管理

1、新建用户

语法格式：

create USER 'username'@'host' [identified by [password] 'password']；


## 创建一个用户，名为lili 密码为abc123 允许在本地登录
 
mysql> create user 'lili'@'localhost' identified by 'abc123';   ## 创建新用户lili
Query OK, 0 rows affected (0.00 sec)
 
 
###验证：
 
mysql> use mysql                     #### 切换数据库
 
mysql> show tables;                  #### 显示当前数据库的表
+---------------------------+
| Tables_in_mysql           |
+---------------------------+
| columns_priv              |
| db                        |
| engine_cost               |
...............................
 
mysql> select User,authentication_string from user    ## 在user中查找显示User和 
                                                        authentication_string两个字节
    -> ;
+---------------+-------------------------------------------+
| User          | authentication_string                     |
+---------------+-------------------------------------------+
| root          | *E56A114692FE0DE073F9A1DD68A00EEB9703F3F1 |
| mysql.session | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE |
| mysql.sys     | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE |
| lili          | *6691484EA6B50DDDE1926A220DA01FA9E575C18A |  ###  新用户创建成功
+---------------+-------------------------------------------+
4 rows in set (0.00 sec)
 
[root@zwb_mysql ~]# mysql -ulili -pabc123         ###使用lili用户名进行登录测试
mysql: [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 4
Server version: 5.7.20 Source distribution
 
Copyright (c) 2000, 2017, 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>

2、删除用户

语法格式：

drop user 'username'@'localhost';


mysql> select User,authentication_string from user            ### 查询用户
    -> ;
+---------------+-------------------------------------------+
| User          | authentication_string                     |
+---------------+-------------------------------------------+
| root          | *E56A114692FE0DE073F9A1DD68A00EEB9703F3F1 |
| mysql.session | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE |
| mysql.sys     | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE |
| lili          | *6691484EA6B50DDDE1926A220DA01FA9E575C18A |
+---------------+-------------------------------------------+
4 rows in set (0.00 sec)
 
mysql> drop user 'lili'@'localhost';                          ### 删除用户lili
Query OK, 0 rows affected (0.00 sec) 
 
mysql> select User,authentication_string from user            ### 验证，查询用户
    -> ;
+---------------+-------------------------------------------+
| User          | authentication_string                     |
+---------------+-------------------------------------------+
| root          | *E56A114692FE0DE073F9A1DD68A00EEB9703F3F1 |
| mysql.session | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE |
| mysql.sys     | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE |
+---------------+-------------------------------------------+
3 rows in set (0.00 sec)

3、重命名用户

语法格式：

rename user 'old_name'@'localhost' to 'new_name'@'localhost';


### 创建用户wangyi
 
mysql> create user 'wangyi'@'localhost' identified by 'abc123';
Query OK, 0 rows affected (0.00 sec)
 
### 查看新创建用户
 
mysql> select User,authentication_string from user;
+---------------+-------------------------------------------+
| User          | authentication_string                     |
+---------------+-------------------------------------------+
| root          | *E56A114692FE0DE073F9A1DD68A00EEB9703F3F1 |
| mysql.session | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE |
| mysql.sys     | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE |
| wangyi        | *6691484EA6B50DDDE1926A220DA01FA9E575C18A |
+---------------+-------------------------------------------+
4 rows in set (0.00 sec)
 
 
### 把用户名wangyi 修改为 wangwu
 
mysql> rename user 'wangyi'@'localhost' to 'wangwu'@'localhost';
Query OK, 0 rows affected (0.01 sec)
 
 
###查看修改后结果
 
mysql> select User,authentication_string from user;
+---------------+-------------------------------------------+
| User          | authentication_string                     |
+---------------+-------------------------------------------+
| root          | *E56A114692FE0DE073F9A1DD68A00EEB9703F3F1 |
| mysql.session | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE |
| mysql.sys     | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE |
| wangwu        | *6691484EA6B50DDDE1926A220DA01FA9E575C18A |
+---------------+-------------------------------------------+
4 rows in set (0.00 sec)

4、给用户设置密码

①修改当前登录的用户的密码

语法格式：(使用password（）函数对密码进行加密)

mysqladmin -u用户名 -p旧密码 password 新密码


##  修改root用户密码。设置为abc123
[root@zwb_mysql ~]# mysqladmin -uroot -p123123 password abc123;
mysqladmin: [Warning] Using a password on the command line interface can be insecure.
Warning: Since password will be sent to server in plain text, use ssl connection to ensure password safety.
 
 
### 实验用新密码登录
[root@zwb_mysql ~]# mysql -uroot -pabc123   
mysql: [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 6
Server version: 5.7.20 Source distribution
 
Copyright (c) 2000, 2017, 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>

二、授权控制

授权控制目的：

我们使用create user 创建的用户，只能登录进入数据库，但是无法做任何事情

还需要grant 命令进行用户授权。同时，此命令，如果当用户不存在，也可以创建该用户。

1、授予权限

授权控制遵循的原则：

①只授予能满足要求的最小权限，防止用户误操作

②创建用户时限制用户的登录主机

③初始化数据库时删除没有密码的用户

④为每个用户设置复杂的符合要求的密码

⑤定期清理不需要的用户

2、授予权限的语法格式

grant 权限列表 on 数据库名.表名 to '用户名'@'主机名 ' identified by '密码' with grant option ；

权限列表：多个权限用逗号‘,’ 隔开，如 'select,update'

数据库名.表名：用于指定授权操作的数据库名和表名，可以使用通配符' *' 表示所有。如 *.* 表示所有库，所有表。

'用户名'@'主机名 '：用户指定用户名和可以登录的客户端地址

identified by :用于设置用户连数据库时候的密码，在新建用户时，如果省略此部分，则用户密码为空。但是我们在配置文件中设置了'NO_AUTO_CREATE_USER'项，所以是无法创建空密码用户。会报错。

with grant option :让被授权的用户，可以将相同的权限授权给他人。


###授权 所有库的所有表的select权限给用户 test，登录密码是abc123,只能本地登录
 
mysql> grant select on *.* to 'test'@'localhost' identified by 'abc123';
 
### 退出数据库，使用用户 test ，密码abc123 本地登录mysql
[root@zwb_mysql ~]# mysql -utest -pabc123
mysql: [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 7
Server version: 5.7.20 Source distribution
 
Copyright (c) 2000, 2017, 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服务器的数据库
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| AAA                |
| class              |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
6 rows in set (0.00 sec)
 
 
 
### 切换数据库
mysql> use AAA;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
 
Database changed
 
 
 
 
#### 查看表
mysql> SHOW TABLES;
+---------------+
| Tables_in_AAA |
+---------------+
| tongxunlu     |
+---------------+
1 row in set (0.00 sec)
 
 
 
### 查看表的基本结构
mysql> desc tongxunlu;
+-----------+-------------+------+-----+---------+-------+
| Field     | Type        | Null | Key | Default | Extra |
+-----------+-------------+------+-----+---------+-------+
| 序号      | int(3)      | NO   | PRI | NULL    |       |
| 姓名      | varchar(20) | YES  |     | NULL    |       |
| 手机号    | int(15)     | YES  |     | NULL    |       |
+-----------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)
 
 
 
### 插入数据，提示拒绝添加数据
mysql> insert into tongxunlu values(4,大圣，12345);
ERROR 1142 (42000): INSERT command denied to user 'test'@'localhost' for table 'tongxunlu'
mysql> select * from tongxunlu;
+--------+--------+------------+
| 序号   | 姓名   | 手机号     |
+--------+--------+------------+
|      1 | 张三   |  188888888 |
|      2 | 张二   | 1888888888 |
+--------+--------+------------+
2 rows in set (0.00 sec)

三、查看用户权限

①用户显示自身的访问权限

语法格式：show grants;


mysql> show grants;   ### 以test普通用户的身份登录
+-------------------------------------------+
| Grants for test@localhost                 |
+-------------------------------------------+
| GRANT SELECT ON *.* TO 'test'@'localhost' |
+-------------------------------------------+
1 row in set (0.00 sec)

②管理员查看已拥有授权用户权限

语法格式：show grants for '用户名'@'登录地点'


mysql> show grants for 'test'@'localhost';    ### 以root身份登录的
+-------------------------------------------+
| Grants for test@localhost                 |
+-------------------------------------------+
| GRANT SELECT ON *.* TO 'test'@'localhost' |
+-------------------------------------------+
1 row in set (0.00 sec)

四、撤销用户权限

语法格式：revoke 权限 on 库名.表名 from '用户名'@'登录地点' ;


mysql> revoke select on *.* from 'test'@'localhost';
Query OK, 0 rows affected (0.00 sec)
 
 
## 查看结果
mysql> show grants for 'test'@'localhost';
+------------------------------------------+
| Grants for test@localhost                |
+------------------------------------------+
| GRANT USAGE ON *.* TO 'test'@'localhost' |
+------------------------------------------+
1 row in set (0.01 sec)

相关阅读:
SurroundDepth拜读：自监督环视多相机深度估计
 CSS3媒体查询与页面自适应
 LeetCode-662-二叉树最大宽度
 “飞桨+辨影相机”成为AI界的“预制菜”，工业AI质检落地更简单
 修改Jenkins主目录
 【面试经典150题】跳跃游戏Ⅱ
python每日一练-----电话号码的字母组合
 K8S最新版本集群部署(v1.28) + 容器引擎Docker部署（下）
基于单片机的智能饮水机系统
 Toronto Research Chemicals农药检测丨Naled-d6
原文地址：https://blog.csdn.net/m0_62948770/article/details/126862548