MySQL数据库用户管理及用户授权

用户管理

跟用户相关的数据库及表

元数据数据库:mysql
系统授权表:db、host、user、columns_priv、tables_priv、procs_priv、proxies_priv

用户账号

'username'@'host'

@'host'         -- 主机名:可以IP地址也可以是主机名;通配符:%  _

-- 实例:
hooper@172.%.%.%

创建用户

CREATE USER [IF NOT EXISTS]
    user [auth_option] [, user [auth_option]] ...
    [REQUIRE {NONE | tls_option [[AND] tls_option] ...}]
    [WITH resource_option [resource_option] ...]
    [password_option | lock_option] ...

新建用户默认权限:USAGE

用户重命名

 RENAME USER old_user TO new_user
    [, old_user TO new_user] ...

删除用户

DROP USER [IF EXISTS] user [, user] ...

实例:

-- 删除默认的空用户
drop user ''@'localhost';

修改密码

新版的MySQL中用户、密码可以保存在mysql.usser表的authentication_string字段中,当authentication_stringpassword字段都保存密码,那么authentication_string优先生效

-- 方法一:使用 set password;此方法在MySQL8.0中不支持
SET PASSWORD [FOR user] = PASSWORD('auth_string');
-- MySQL 5.7.6以后,由于PASSWORD('auth_string') 这个函数也已经弃用,后面直接带密码的参数;
-- 使用set password会将修改的内容记录在~/.mysql_history,某种意义上是不安全的
SET PASSWORD [FOR user] = 'auth_string';

-- 方法二:使用update修改密码;
-- MySQL 5.7.6以后,不推荐使用此种方法修改密码
-- 因为这样修改的密码为明文的,并且PASSWORD('auth_string') 这个函数也已经弃用了
UPDATE mysql.user SET PASSWORD = PASSWORD('auth_string') WHERE user = 'username';
-- MySQL 5.7.6以后,推荐使用alter user修改用户密码
ALTER USER user IDENTIFIED BY 'auth_string';
-- MariaDB 10.3
UPDATE mysql.user SET authentication_string = PASSWORD('auth_string') WHERE user = 'username';

-- 修改完命令后,需要刷新
flush privileges;

MySQL 5.7.24 忘记root密码,重置密码的方法

# 关闭MySQL服务
systemctl stop mysqld.service
# 备份配置文件,并修改配置文件
cp -rp /etc/my.cnf{,.`datebak`}
# 在[mysqld]下面添加关闭验证
vim /etc/my.cnf
##################################################################
[mysqld]
# 关闭验证
skip-grant-tables
# 取消网络功能,这样只能从本机登录数据库,无法远程登录
skip-networking
##################################################################
# 启动MySQL服务,并进入到MySQL中
systemctl start mysqld.service
mysql -uroot
18:57:11 (root@localhost) [(none)]> select host,user,authentication_string from mysql.user;
+-----------+---------------+-------------------------------------------+
| host      | user          | authentication_string                     |
+-----------+---------------+-------------------------------------------+
| localhost | root          | *3A4CF0C22DF4D75E05738349EB11077C46A6F8A6 |
| localhost | mysql.session | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE |
| localhost | mysql.sys     | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE |
| localhost | hooper        | *440B693EFFA7D27D12FCBC717442DFE74DD6EB49 |
+-----------+---------------+-------------------------------------------+
4 rows in set (0.02 sec)

# 因为是进入到不验证模式下,所以无法使用set password 修改密码
18:57:45 (root@localhost) [(none)]> set password for hooper@localhost = '';
ERROR 1290 (HY000): The MySQL server is running with the --skip-grant-tables option so it cannot execute this statement

# 使用update修改密码,此时更新后的密码为明文,退出MySQL
19:01:34 (root@localhost) [(none)]> update mysql.user set authentication_string = '' where user = 'hooper' and host = 'localhost';
Query OK, 1 row affected (0.02 sec)
Rows matched: 1  Changed: 1  Warnings: 0

19:02:01 (root@localhost) [(none)]> select host,user,authentication_string from mysql.user;
+-----------+---------------+-------------------------------------------+
| host      | user          | authentication_string                     |
+-----------+---------------+-------------------------------------------+
| localhost | root          | *3A4CF0C22DF4D75E05738349EB11077C46A6F8A6 |
| localhost | mysql.session | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE |
| localhost | mysql.sys     | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE |
| localhost | hooper        |                                           |
+-----------+---------------+-------------------------------------------+
4 rows in set (0.00 sec)
19:09:44 (root@localhost) [(none)]> \q

# 还原配置文件
##################################################################
[mysqld]
# 关闭验证
# skip-grant-tables
# 取消网络功能
# skip-networking
##################################################################

# 重启MySQL服务
systemctl restart mysqld.service
systemctl status mysqld.service

# 直接使用hooper登录
mysql -u hooper
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.7.24-log MySQL Community Server (GPL)

Copyright (c) 2000, 2018, 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.

19:23:34 (hooper@localhost) [(none)]>

用户授权

权限的分类

权限分类

  • 管理类
  • 程序类
  • 数据库级别
  • 表级别
  • 字段级别

用户可以通过SHOW PRIVILEGES; 查看都有哪些权限

19:55:47 (root@(none)) [(none)]> 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
Time: 0.016s

管理类

CREATE USER
FILE
EVENT
PROCESS
PROXY
RELOAD
REPLICATION CLIENT
REPLICATION SLAVE 可不可以做主从复制权限
SHOW DATABASES
SHUTDOWN
SUPER
CREATE TABLESPACE
USAGE
LOCK TABLES
CREATE TEMPORARY TABLES

程序类

针对Function、Procedure、Trigger

CREATE
ALTER
DROP
EXCUTE

库及表级别

针对database、table

CREATE
ALTER
CREATE VIEW
DROP INDEX
SHOW VIEW
WITH GRANT OPTION 能将自己获得的权限转赠给其他用户

数据操作

SELECT
INSERT
DELETE
UPDATE

字段级别

SELECT(col1,col2...)
UPDATE(col1,col2...)
INSERT(col1,col2...)

所有权限

ALL PRIVILEGE 或 ALL

授权

GRANT
    priv_type [(column_list)]
      [, priv_type [(column_list)]] ...
    ON [object_type] priv_level
    TO user [auth_option] [, user [auth_option]] ...
    [REQUIRE {NONE | tls_option [[AND] tls_option] ...}]
    [WITH {GRANT OPTION | resource_option} ...]

实例:

-- 'hooper'@'localhost' 在所有的库及所有表上给replication slave的权限
20:39:24 (root@(none)) [(none)]> grant replication slave on *.* to 'hooper'@'localhost';
Query OK, 0 rows affected
Time: 0.021s

-- 'hooper'@'localhost'所有的权限
20:41:22 (root@(none)) [(none)]> grant all privileges on *.* to 'hooper'@'localhost';
Query OK, 0 rows affected
Time: 0.021s

20:42:07 (root@(none)) [(none)]> grant all privileges on *.* to 'hooper'@'localhost' WITH GRANT OPTION;
Query OK, 0 rows affected
Time: 0.031s

取消授权

REVOKE
    priv_type [(column_list)]
      [, priv_type [(column_list)]] ...
    ON [object_type] priv_level
    FROM user [, user] ...

实例:

20:43:59 (root@(none)) [(none)]> revoke all privileges on *.* from 'hooper'@'localhost';
Query OK, 0 rows affected
Time: 0.010s

查看指定用户获得的授权

SHOW GRANTS [FOR user]
SHOW GRANTS FOR CURRENT_USER();

实例:

20:36:35 (root@(none)) [(none)]> show grants for 'hooper'@'localhost';
+--------------------------------------------+
| Grants for hooper@localhost                |
+--------------------------------------------+
| GRANT USAGE ON *.* TO 'hooper'@'localhost' |
+--------------------------------------------+
1 row in set
Time: 0.009s

20:37:00 (root@(none)) [(none)]> show grants for 'root'@'localhost';
+---------------------------------------------------------------------+
| Grants for root@localhost                                           |
+---------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' WITH GRANT OPTION |
| GRANT PROXY ON ''@'' TO 'root'@'localhost' WITH GRANT OPTION        |
+---------------------------------------------------------------------+
2 rows in set
Time: 0.009s

注意⚠️

MariaDB服务进程启动时,会读取mysql库中所有授权表至内存
GRANT或REVOKE等执行权限操作会保存于系统表中,MariaDB的服务进程通常会自动重读授权表,使之生效
对于不能或不能及时重读授权表的命令,可手动让MariaDB服务进程重读授权表:flush privileges;

参考文献

MySQL之权限管理
【MySQL】第二篇:MySQL的用户和权限管理
mysql简介

<span style="font-size:14px;">一、课程简介</span><br /><p> <span style="font-size:14px;"><span style="color:#E56600;">『Java学习指南系列』</span>的第8篇教程 ,介绍MySQL数据库的安装使用,常见SQL语句,以及使用JDBC进行数据库开发的技术。</span> </p> <p> <span style="font-size:14px;"><br /></span> </p> <p> <span style="font-size:14px;">二、主要内容 </span> </p> <p> <span style="font-size:14px;">* MySQL的安装和使用</span> </p> <p> <span style="font-size:14px;">* 常用SQL语句,增删改查操作</span> </p> <p> <span style="font-size:14px;">* 高级SQL查询,多表关联查询</span> </p> <p> <span style="font-size:14px;">* 表的设计:主键,索引,内连接外连接,外键</span><span style="font-size:14px;"> </span> </p> <p> <span style="font-size:14px;">* MySQL配置管理: 用户授权,远程访问,备份还原</span> </p> <p> <span style="font-size:14px;">* JDBC连接: 使用JDBC连接数据库,执行增删改查操作</span> </p> <span style="font-size:14px;">* 基于JDBC数据库框架的设计(afsql框架),以此阐述数据库框架的设计原理</span><br /><p> <span style="font-size:14px;">* JDBC连接池,JDBC数据库开发的必备技术</span> </p> <p> <span style="font-size:14px;">* 数据库事务 Transaction 的支持 </span> </p> <p> <span style="font-size:14px;">* SQL的转义,数据库注入攻击的预防</span> </p> <p> <span style="font-size:14px;">* 数据库的动态访问:获取数据库和表的结构,根据表的结构自动生成POJO</span> </p> <p> <span style="font-size:14px;"><br /></span> </p> <p> <span style="font-size:14px;">三、课程体系</span> </p> <p> <span style="font-size:14px;color:#E56600;">〖Java学习指南</span><span style="font-size:14px;color:#E56600;">系列</span><span style="font-size:14px;"><span style="color:#E56600;">〗</span>:包含入门与进阶语法,Swing桌面开发,安卓开发,JavaFX开发,网络通信Socket,数据结构与算法等课程。</span> </p> <p> <span style="font-size:14px;color:#E56600;">〖网站开发</span><span style="font-size:14px;color:#E56600;">系列</span><span style="font-size:14px;"><span style="color:#E56600;">〗</span>:包含网页基础、网站入门、数据库、网站中级、FreeMarker、网站高级、项目应用、MyBatis、Redis等课程。</span> </p>
相关推荐
©️2020 CSDN 皮肤主题: 数字20 设计师:CSDN官方博客 返回首页