MySQL 8 创建用户并授权

最新推荐文章于 2024-06-26 00:54:06 发布

Shang.Liang

最新推荐文章于 2024-06-26 00:54:06 发布

阅读量1.3k

点赞数

分类专栏： # mysql

本文链接：https://blog.csdn.net/sl285720967/article/details/105714226

版权

mysql 专栏收录该内容

2 篇文章 0 订阅

订阅专栏

MySQL 创建用户并授权

MySQL 安装教程请查看 Linux (CentOS 7) 系统安装 MySQL8

本章我们进行 MySQL 用户及权限管理操作。

获取初始密码

安装 MySQL 安装完成为取得初始密码，请通过如下方式查询初始密码：

MySQL 安装完成后，初始密码保存在 /var/log/mysqld.log 文件中
执行命令：grep password /var/log/mysqld.log

[root@LiaNg mysql]# grep password /var/log/mysqld.log
2019-10-24T10:38:25.465382Z 5 [Note] [MY-010454] [Server] A temporary password is generated for root@localhost: *7C,#tQ6dc1f

如上所示：*7C,#tQ6dc1f 即 MySQL root 用户的初始密码

登陆 MySQL

执行登陆命令：mysql -u root -p ，根据提示输入密码

[root@LiaNg ~]# mysql -u root -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 29
Server version: 8.0.18

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; 显示所有库。出现如下报错信息：

mysql> show databases;
ERROR 1820 (HY000): You must reset your password using ALTER USER statement before executing this statement.

由于执行该命令之前必须先更改用户密码。我们需要对 root 用户密码进行修改操作。

修改 MySQL root 用户密码

执行如下所示命令：

mysql> ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'lnrCoder#!1024';
Query OK, 0 rows affected (0.02 sec)

lnrCoder#!1024 改为你要设置的密码。

执行： flush privileges;

使用 exit; 命令退出 MySQL，使用新密码重新登录。

重新执行 show databases; 命令

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
4 rows in set (0.01 sec)

新建数据库

创建一个名为 learning_mysql 的数据库，执行以下命令：

mysql> CREATE DATABASE learning_mysql;
Query OK, 1 row affected (0.02 sec)

再次查看数据库：

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| learning_mysql     |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
5 rows in set (0.00 sec)

数据库被创建为数据目录中的一个目录。默认数据目录为/var/lib/mysql。可以通过执行如下命令获知当前的数据目录

mysql> show variables like 'datadir';
+---------------+-----------------+
| Variable_name | Value           |
+---------------+-----------------+
| datadir       | /var/lib/mysql/ |
+---------------+-----------------+
1 row in set (0.01 sec)

执行：use learning_mysql; 进入 learning_mysql 库。

创建数据表

CREATE TABLE `learning_mysql`.`t_user`  (
  `id` int(0) NOT NULL,
  `name` varchar(255) NULL,
  `age` int(0) NULLR
  `address` varchar(255) NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB;

ENGINE 指定存储引擎。MySQL8 默认为 InnoDB。
执行命令 SHOW ENGINES\G 查看所有引擎。

列出所有表命令：SHOW TABLES;
查看表结构命令：show create table t_user\G 或 desc t_user;
插入 2 条测试数据

insert into t_user(id,name,age,address) values (1,'张三',20,'上海'),(2,'李四',30,'北京');

查看数据

mysql> select * from t_user;
+----+--------+------+---------+
| id | name   | age  | address |
+----+--------+------+---------+
|  1 | 张三   |   20 | 上海    |
|  2 | 李四   |   30 | 北京    |
+----+--------+------+---------+
2 rows in set (0.00 sec)

用户管理

创建用户

到目前为止，我们一直在使用 root 用户连接 MySQL 并执行命令。但其实不应该在访问 MySQL 时使用 root 用户，除非是 localhost 的管理任务。你应该创建用户、限制访问、限制资源使用等等。

执行如下命令创建用户：

mysql> CREATE USER IF NOT EXISTS 'lnrcoder'@'%' IDENTIFIED WITH mysql_native_password by 'lnrCoder#!1024' ;
Query OK, 0 rows affected (0.01 sec)

创建名为 lnrcoder 的用户（如不存在）
’ % ’ 表示用户可以从任何主机访问
密码为 lnrCoder#!1024
使用 mysql_native_password 身份验证。MySQL 8 默认为：caching_sha2_password，使用默认加密会导致不支持该加密方式客户端无法连接问题。建议设置 mysql_native_password

注：如需设置用户的查询更新次数限制，上述语句添加 WITH MAX_QUERIE_PER_HOUR 1000每小时最大查询次数为1000次和 MAX_UPDATE_PER_HOUR 100每小时最大更新次数为100次

我们继续创建一个名为 dbadmin 的用户，执行命令：

mysql> CREATE USER IF NOT EXISTS 'dbadmin'@'%' IDENTIFIED WITH mysql_native_password by 'lnrCoder#!1024';
Query OK, 0 rows affected (0.02 sec

锁定|解锁用户

锁定 dbadmin 用户：

mysql> alter user 'dbadmin'@'%' account lock;
Query OK, 0 rows affected (0.02 sec)

解锁账户

mysql> alter user 'dbadmin'@'%' account unlock;
Query OK, 0 rows affected (0.00 sec)

权限管理

用户授权

将 learning_mysql 数据库的只读权限(select)授予 lnrcoder 用户：

mysql> grant select on learning_mysql.* to 'lnrcoder'@'%';
Query OK, 0 rows affected (0.01 sec)

上述 SQL 中的 select 可替换为 INSERT、UPDATE、DELETE 等权限，多个权限使用 , (逗号)分隔
指定某个表：将 learning_mysql.* 调整为 learning_mysql.table_name，如 learning_mysql.t_user
指定某列：select(id,name) 表示只授权 id 和 name 两列

授予所有权限（不包含 GRANT 权限）给 dbadmin 用户，执行命令：

mysql> grant all on *.* to 'dbadmin'@'%';
Query OK, 0 rows affected (0.05 sec)

授予 GRANT 权限的命令如下：

mysql> GRANT GRANT OPTION ON *.* TO 'dbadmin'@'%';
Query OK, 0 rows affected (0.01 sec)

GRANT 权限：当前登录用户需要拥有 GRANT 权限，才能为其他用户授予权限。

查询授权

mysql> SHOW GRANTS FOR 'dbadmin'@'%'\G
*************************** 1. row ***************************
Grants for dbadmin@%: GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, RELOAD, SHUTDOWN, PROCESS, FILE, REFERENCES, INDEX, ALTER, SHOW DATABASES, SUPER, CREATE TE
MPORARY TABLES, LOCK TABLES, EXECUTE, REPLICATION SLAVE, REPLICATION CLIENT, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, CREATE USER, EVENT, TRIGGER, CREATE TABLESPACE, CREATE ROLE, DROP ROLE ON *.* TO `dbadmin`@`%` WITH GRANT OPTION*************************** 2. row ***************************
Grants for dbadmin@%: GRANT APPLICATION_PASSWORD_ADMIN,AUDIT_ADMIN,BACKUP_ADMIN,BINLOG_ADMIN,BINLOG_ENCRYPTION_ADMIN,CLONE_ADMIN,CONNECTION_ADMIN,ENCRYPTION_KEY_ADMI
N,GROUP_REPLICATION_ADMIN,INNODB_REDO_LOG_ARCHIVE,PERSIST_RO_VARIABLES_ADMIN,REPLICATION_APPLIER,REPLICATION_SLAVE_ADMIN,RESOURCE_GROUP_ADMIN,RESOURCE_GROUP_USER,ROLE_ADMIN,SERVICE_CONNECTION_ADMIN,SESSION_VARIABLES_ADMIN,SET_USER_ID,SYSTEM_USER,SYSTEM_VARIABLES_ADMIN,TABLE_ENCRYPTION_ADMIN,XA_RECOVER_ADMIN ON *.* TO `dbadmin`@`%` WITH GRANT OPTION2 rows in set (0.00 sec)

撤销用户授权

撤销授权与创建授权语法相同。向用户授权用 TO，撤销用户的权限用 FROM

撤销 lnrcoder 用户对 learning_mysql 库的查询权限，执行如下命令：

mysql> revoke select on learning_mysql.* from 'lnrcoder'@'%';
Query OK, 0 rows affected (0.02 sec)

创建角色

我们可以直接将权限授予用户。可以将权限授予「角色」，然后指定用户的角色来达到同样的目的。
下面我们讲述如何使用角色管理用户权限。

创建角色，角色名为 read_role :

create role 'read_role';

将权限赋予 read_role 角色：

grant select on learning_mysql.* to 'read_role';

将 learning_mysql 库的只读权限授予 read_role 角色

将角色指定给用户

grant 'read_role' to 'lnrcoder'@'%';

对 lnrcoder 用户指定 read_role 角色。
一个用户可指定多个角色，使用 ,(逗号)分隔。

注意事项

如果你使用 GRANT、REVOKE、SET PASWORD 或 RENAME USER 等账户管理语句间接修改授权表，则服务器会通知这些更改，并立即再次将授权表加载到内存中。

如果使用 INSERT、UPDATE 或 DELETE 等语句直接修改授权表，则更改不会影响权限检查。可以通过执行 FLUSH PRIVILEGES 语句来完成 GRANT 表的重新加载。

总结

因为 root 的权限太大，可能对其他数据库造成修改。故项目开发过程中，需要创建一权限较小的用户，使其只能对特定的数据库操作，以保证数据安全。在实际项目中，请坚持最小可用权限的原则对用户进行授权。

Shang.Liang

关注

0
点赞
踩
4

收藏

觉得还不错? 一键收藏
0
评论
MySQL 8 创建用户并授权

MySQL 创建用户并授权获取初始密码登陆 MySQL修改 MySQL root 用户密码新建数据库创建数据表用户管理创建用户锁定|解锁用户权限管理MySQL用户授权撤销用户授权创建角色注意事项总结MySQL 安装教程请查看 Linux (CentOS 7) 系统安装 MySQL8本章我们进行 MySQL 用户及权限管理操作。获取初始密码安装 MySQL 安装完成为取得初始密码，请通过...
复制链接

扫一扫

专栏目录