MariaDB 基础使用实验二———权限设置

最新推荐文章于 2021-05-11 14:24:10 发布

KAIVI-Blog

最新推荐文章于 2021-05-11 14:24:10 发布

阅读量548

点赞数

分类专栏： mysql linux 文章标签： Mariadb 数据库用户实验 centos linux MySQL

kaivi's blog

本文链接：https://blog.csdn.net/weixin_45651006/article/details/103206981

版权

linux 同时被 2 个专栏收录

14 篇文章 0 订阅

订阅专栏

mysql

10 篇文章 0 订阅

订阅专栏

1.3.1 MariaDB 数据库用户介绍

数据库作为一个数据存储的软件服务程序，可以便捷高效的存储关系型数据信息，比我们平时使用的 office 表格程序效率高并易于管理。不过这个数据库程序到底谁有权限使用呢？我们之前学习 Linux 操作系统时，为了管理系统中资源使用的权限，Linux 系统设置了超级用户root、系统用户、普通用户等概念。和 Linux 系统相仿， MariaDB 也设置了对应的权限管理功能，其中 root 用户是超级管理员，他除了拥有数据库最高访的问权限外，还可以创建其他普通用户，并且设置这些普通用户的具体使用权限。 MariaDB 中的用户信息存放在系统库 mysql库中。
前提准备：

关闭防火墙
关闭selinux服务
时间同步

1.我们使用一台新创建的 CentOS7.6，然后使用 yum 安装 MariaDB 官方 yum 源中的 10.2版本，然后连接数据库:

[root@centos7K ~]#cd /etc/yum.repos.d
[root@centos7K yum.repos.d]#ls
aliyun.repo  bak2019-11-18_22:26:13
[root@centos7K yum.repos.d]#vim mariadb.repo

[root@centos7K yum.repos.d]#cat  mariadb.repo 
[mariadb]
name=MariaDB
baseurl=https://mirrors.tuna.tsinghua.edu.cn/mariadb/yum/10.2/centos7-amd64/
gpgkey=https://yum.mariadb.org/RPM-GPG-KEY-MariaDB
gpgcheck=1

[root@centos7K yum.repos.d]#cd
[root@centos7K yum.repos.d]#yum install -y MariaDB-server

启动数据库服务
在这里插入图片描述
进入数据库

2.我们查看一下新安装的 MariaDB 中，初始存在的数据库，其中 mysql 库是系统库，该库中的 user 表存放着所有的用户信息， user 表中的Host， User ，Password 三个字段分别指定了用户可以使用的主机或者 IP 地址，用户名，用户的密码，如图：

MariaDB [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| test               |
+--------------------+
4 rows in set (0.00 sec)

MariaDB [(none)]> 
MariaDB [(none)]> use mysql;
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
MariaDB [mysql]> show tables;
+---------------------------+
| Tables_in_mysql           |
+---------------------------+
| column_stats              |
| columns_priv              |
| db                        |
| event                     |
| func                      |
| general_log               |
| gtid_slave_pos            |
| help_category             |
| help_keyword              |
| help_relation             |
| help_topic                |
| host                      |
| index_stats               |
| innodb_index_stats        |
| innodb_table_stats        |
| plugin                    |
| proc                      |
| procs_priv                |
| proxies_priv              |
| roles_mapping             |
| servers                   |
| slow_log                  |
| table_stats               |
| tables_priv               |
| time_zone                 |
| time_zone_leap_second     |
| time_zone_name            |
| time_zone_transition      |
| time_zone_transition_type |
| user                      |
+---------------------------+
30 rows in set (0.00 sec)

MariaDB [mysql]> desc user;
+------------------------+-----------------------------------+------+-----+----------+-------+
| Field                  | Type                              | Null | Key | Default  | Extra |
+------------------------+-----------------------------------+------+-----+----------+-------+
| Host                   | char(60)                          | NO   | PRI |          |       |
| User                   | char(80)                          | NO   | PRI |          |       |
| Password               | char(41)                          | NO   |     |          |       |
| Select_priv            | enum('N','Y')                     | NO   |     | N        |       |
| Insert_priv            | enum('N','Y')                     | NO   |     | N        |       |
| Update_priv            | enum('N','Y')                     | NO   |     | N        |       |
| Delete_priv            | enum('N','Y')                     | NO   |     | N        |       |
| Create_priv            | enum('N','Y')                     | NO   |     | N        |       |
| Drop_priv              | enum('N','Y')                     | NO   |     | N        |       |
| Reload_priv            | enum('N','Y')                     | NO   |     | N        |       |
| Shutdown_priv          | enum('N','Y')                     | NO   |     | N        |       |
| Process_priv           | enum('N','Y')                     | NO   |     | N        |       |
| File_priv              | enum('N','Y')                     | NO   |     | N        |       |
| Grant_priv             | enum('N','Y')                     | NO   |     | N        |       |
| References_priv        | enum('N','Y')                     | NO   |     | N        |       |
| Index_priv             | enum('N','Y')                     | NO   |     | N        |       |
| Alter_priv             | enum('N','Y')                     | NO   |     | N        |       |
| Show_db_priv           | enum('N','Y')                     | NO   |     | N        |       |
| Super_priv             | enum('N','Y')                     | NO   |     | N        |       |
| Create_tmp_table_priv  | enum('N','Y')                     | NO   |     | N        |       |
| Lock_tables_priv       | enum('N','Y')                     | NO   |     | N        |       |
| Execute_priv           | enum('N','Y')                     | NO   |     | N        |       |
| Repl_slave_priv        | enum('N','Y')                     | NO   |     | N        |       |
| Repl_client_priv       | enum('N','Y')                     | NO   |     | N        |       |
| Create_view_priv       | enum('N','Y')                     | NO   |     | N        |       |
| Show_view_priv         | enum('N','Y')                     | NO   |     | N        |       |
| Create_routine_priv    | enum('N','Y')                     | NO   |     | N        |       |
| Alter_routine_priv     | enum('N','Y')                     | NO   |     | N        |       |
| Create_user_priv       | enum('N','Y')                     | NO   |     | N        |       |
| Event_priv             | enum('N','Y')                     | NO   |     | N        |       |
| Trigger_priv           | enum('N','Y')                     | NO   |     | N        |       |
| Create_tablespace_priv | enum('N','Y')                     | NO   |     | N        |       |
| ssl_type               | enum('','ANY','X509','SPECIFIED') | NO   |     |          |       |
| ssl_cipher             | blob                              | NO   |     | NULL     |       |
| x509_issuer            | blob                              | NO   |     | NULL     |       |
| x509_subject           | blob                              | NO   |     | NULL     |       |
| max_questions          | int(11) unsigned                  | NO   |     | 0        |       |
| max_updates            | int(11) unsigned                  | NO   |     | 0        |       |
| max_connections        | int(11) unsigned                  | NO   |     | 0        |       |
| max_user_connections   | int(11)                           | NO   |     | 0        |       |
| plugin                 | char(64)                          | NO   |     |          |       |
| authentication_string  | text                              | NO   |     | NULL     |       |
| password_expired       | enum('N','Y')                     | NO   |     | N        |       |
| is_role                | enum('N','Y')                     | NO   |     | N        |       |
| default_role           | char(80)                          | NO   |     |          |       |
| max_statement_time     | decimal(12,6)                     | NO   |     | 0.000000 |       |
+------------------------+-----------------------------------+------+-----+----------+-------+
46 rows in set (0.00 sec)

MariaDB [mysql]> select user,host,password from user;
+------+-----------+----------+
| user | host      | password |
+------+-----------+----------+
| root | localhost |          |
| root | centos7k  |          |
| root | 127.0.0.1 |          |
| root | ::1       |          |
|      | localhost |          |
|      | centos7k  |          |
+------+-----------+----------+
6 rows in set (0.00 sec)

MariaDB [mysql]>

通过上图我们发现，新建的数据库中有4个 root 用户，而且密码都为空，这也是我们连接 MariaDB 后，使用 mysql 客户端程序可以直接访问的原因，因为 root 用户没有密码。除了 root 用外还有还有两个匿名用户，他们的用户名为空，而且也没有密码。 Host 字段代表用户连接 MariaDB 时的主机名或者IP地址，上图中的所有用户的 Host 都是本地连接的 Host。

3.下面我们使用匿名用户登录一下现在的数据库，然后使用数据库初始化安全脚本修改一下默认用户权限：

MariaDB [mysql]> 
MariaDB [mysql]> quit
Bye
[root@centos7K ~]#
[root@centos7K ~]#mysql
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 9
Server version: 10.2.29-MariaDB MariaDB Server

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> 
MariaDB [(none)]> status;
--------------
mysql  Ver 15.1 Distrib 10.2.29-MariaDB, for Linux (x86_64) using readline 5.1

Connection id:		9
Current database:	
Current user:		root@localhost
SSL:			Not in use
Current pager:		stdout
Using outfile:		''
Using delimiter:	;
Server:			MariaDB
Server version:		10.2.29-MariaDB MariaDB Server
Protocol version:	10
Connection:		Localhost via UNIX socket
Server characterset:	latin1
Db     characterset:	latin1
Client characterset:	utf8
Conn.  characterset:	utf8
UNIX socket:		/var/lib/mysql/mysql.sock
Uptime:			6 min 30 sec

Threads: 7  Questions: 45  Slow queries: 0  Opens: 36  Flush tables: 1  Open tables: 30  Queries per second avg: 0.115
--------------

MariaDB [(none)]> 
MariaDB [(none)]> exit
Bye
[root@centos7K ~]#
[root@centos7K ~]#mysql -u ' '
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 10
Server version: 10.2.29-MariaDB MariaDB Server

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> status;
--------------
mysql  Ver 15.1 Distrib 10.2.29-MariaDB, for Linux (x86_64) using readline 5.1

Connection id:		10
Current database:	
Current user:		 @localhost
SSL:			Not in use
Current pager:		stdout
Using outfile:		''
Using delimiter:	;
Server:			MariaDB
Server version:		10.2.29-MariaDB MariaDB Server
Protocol version:	10
Connection:		Localhost via UNIX socket
Server characterset:	latin1
Db     characterset:	latin1
Client characterset:	utf8
Conn.  characterset:	utf8
UNIX socket:		/var/lib/mysql/mysql.sock
Uptime:			7 min 23 sec

Threads: 7  Questions: 49  Slow queries: 0  Opens: 36  Flush tables: 1  Open tables: 30  Queries per second avg: 0.110
--------------

MariaDB [(none)]> exit
Bye
[root@centos7K ~]#
[root@centos7K ~]#mysql_secure_installation 

NOTE: RUNNING ALL PARTS OF THIS SCRIPT IS RECOMMENDED FOR ALL MariaDB
      SERVERS IN PRODUCTION USE!  PLEASE READ EACH STEP CAREFULLY!

In order to log into MariaDB to secure it, we'll need the current
password for the root user.  If you've just installed MariaDB, and
you haven't set the root password yet, the password will be blank,
so you should just press enter here.

Enter current password for root (enter for none): 
OK, successfully used password, moving on...

Setting the root password ensures that nobody can log into the MariaDB
root user without the proper authorisation.

Set root password? [Y/n] y
New password: 
Re-enter new password: 
Password updated successfully!
Reloading privilege tables..
 ... Success!


By default, a MariaDB installation has an anonymous user, allowing anyone
to log into MariaDB without having to have a user account created for
them.  This is intended only for testing, and to make the installation
go a bit smoother.  You should remove them before moving into a
production environment.

Remove anonymous users? [Y/n] y
 ... Success!

Normally, root should only be allowed to connect from 'localhost'.  This
ensures that someone cannot guess at the root password from the network.

Disallow root login remotely? [Y/n] y
 ... Success!

By default, MariaDB comes with a database named 'test' that anyone can
access.  This is also intended only for testing, and should be removed
before moving into a production environment.

Remove test database and access to it? [Y/n] y
 - Dropping test database...
 ... Success!
 - Removing privileges on test database...
 ... Success!

Reloading the privilege tables will ensure that all changes made so far
will take effect immediately.

Reload privilege tables now? [Y/n] y
 ... Success!

Cleaning up...

All done!  If you've completed all of the above steps, your MariaDB
installation should now be secure.

Thanks for using MariaDB!
[root@centos7K ~]#mysql -u " " -p
Enter password: 
ERROR 1045 (28000): Access denied for user ' '@'localhost' (using password: YES)
[root@centos7K ~]#mysql -u root -p
Enter password: 
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 20
Server version: 10.2.29-MariaDB MariaDB Server

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> use mysql;
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
MariaDB [mysql]> select user,host,password from user;
+------+-----------+-------------------------------------------+
| user | host      | password                                  |
+------+-----------+-------------------------------------------+
| root | localhost | *128977E278358FF80A246B5046F51043A2B1FCED |
| root | 127.0.0.1 | *128977E278358FF80A246B5046F51043A2B1FCED |
| root | ::1       | *128977E278358FF80A246B5046F51043A2B1FCED |
+------+-----------+-------------------------------------------+
3 rows in set (0.00 sec)

MariaDB [mysql]>

上图中我们运行了安全启动脚本，然后使用空用户名登录系统，发现登录失败。使用 root 用户需要输入密码，登录成功后查看 MariaDB的 user 表，发现只剩下三条 root 用户登录权限的信息，密码都一样，是调用加密函数实现的，但是 Host 分别是 3 个不相同的。

下面我们使用另外一台刚安装的 CentOS7.，安装 MariaDB 的客户端连接程序，并将这台主机的 hostname 设置为
client7，我们将运行 MariaDB 服务的主机为centos7K，然后尝试远程通过网络连接 centos7K 的MariaDB
服务：

[root@centos7 ~]#hostnamectl set-hostname client7
[root@centos7 ~]#exit
logout
Connection closing...Socket close.

Connection closed by foreign host.

Disconnected from remote host(centos 7) at 21:00:53.

Type `help' to learn how to use Xshell prompt.
[c:\~]$ 

Connecting to 192.168.32.7:22...
Connection established.
To escape to local shell, press 'Ctrl+Alt+]'.

[root@client7 ~]#hostname
client7
[root@client7 ~]#systemctl start mariadb

在这里插入图片描述
centos7K服务器操作，ip 地址： 192.168.32.142

[root@centos7K ~]#hostname
centos7K
[root@centos7K ~]#firewall-cmd --state
not running          #防火墙是关闭状态
[root@centos7K ~]#ss -ntl
State      Recv-Q Send-Q             Local Address:Port                            Peer Address:Port              
LISTEN     0      128                            *:22                                         *:*                  
LISTEN     0      100                    127.0.0.1:25                                         *:*                  
LISTEN     0      80                            :::3306                                      :::*                  
LISTEN     0      128                           :::22                                        :::*                  
LISTEN     0      100                          ::1:25                                        :::*

下面我们在 client7 上使用 MariaDB 客户端命令 mysql -h -u -p 方式远程连接 centos7K 中的 MariaDB 服务

client7 服务器操作，ip 地址： 192.168.32.7

[root@client7 ~]#mysql -h 192.168.32.142 -u root -p 
Enter password: 
ERROR 1130 (HY000): Host '192.168.32.7' is not allowed to connect to this MariaDB server
[root@client7 ~]#
[root@client7 ~]#

mysql 命令连接失败，因为 MariaDB 的 root 用户没有授权客除了户端 IP 地址是 192.168.130.128 的主机可以连接，也就是现在只能本地和本机连接，不支持远程连接。

下面我们使用 SQL 手动修改 MariaDB root 的一条 Host 记录为client7的地址 192.168.32.7, 然后更新当前用户权限状态 flush privileges.

centos7K服务器操作，ip 地址： 192.168.32.142

[root@centos7K ~]#mysql -u root -p
Enter password: 
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 22
Server version: 10.2.29-MariaDB MariaDB Server

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> use mysql;
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
MariaDB [mysql]> select user,host,password from user;
+------+-----------+-------------------------------------------+
| user | host      | password                                  |
+------+-----------+-------------------------------------------+
| root | localhost | *128977E278358FF80A246B5046F51043A2B1FCED |
| root | 127.0.0.1 | *128977E278358FF80A246B5046F51043A2B1FCED |
| root | ::1       | *128977E278358FF80A246B5046F51043A2B1FCED |
+------+-----------+-------------------------------------------+
3 rows in set (0.00 sec)

MariaDB [mysql]> 
MariaDB [mysql]> update user set host="192.168.32.7" where host="::1";
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

MariaDB [mysql]> select user,host,password from user;
+------+--------------+-------------------------------------------+
| user | host         | password                                  |
+------+--------------+-------------------------------------------+
| root | localhost    | *128977E278358FF80A246B5046F51043A2B1FCED |
| root | 127.0.0.1    | *128977E278358FF80A246B5046F51043A2B1FCED |
| root | 192.168.32.7 | *128977E278358FF80A246B5046F51043A2B1FCED |
+------+--------------+-------------------------------------------+
3 rows in set (0.00 sec)

MariaDB [mysql]> flush privileges;
Query OK, 0 rows affected (0.00 sec)

MariaDB [mysql]> 
MariaDB [mysql]>

下面我们在client7中再次访问 centos7K的 MariaDB 服务：

client7服务器操作，ip 地址： 192.168.32.7

[root@client7 ~]#
[root@client7 ~]#mysql -h 192.168.32.142 -u root -p 
Enter password: 
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 23
Server version: 10.2.29-MariaDB MariaDB Server

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> status;
--------------
mysql  Ver 15.1 Distrib 10.2.29-MariaDB, for Linux (x86_64) using readline 5.1

Connection id:		23
Current database:	
Current user:		root@192.168.32.7
SSL:			Not in use
Current pager:		stdout
Using outfile:		''
Using delimiter:	;
Server:			MariaDB
Server version:		10.2.29-MariaDB MariaDB Server
Protocol version:	10
Connection:		192.168.32.142 via TCP/IP
Server characterset:	latin1
Db     characterset:	latin1
Client characterset:	utf8
Conn.  characterset:	utf8
TCP port:		3306
Uptime:			37 min 17 sec

Threads: 8  Questions: 144  Slow queries: 0  Opens: 36  Flush tables: 1  Open tables: 30  Queries per second avg: 0.064
--------------

MariaDB [(none)]> 
MariaDB [(none)]>

上图中我们通过远程client7 成功连接上了 centos7K的MariaDB 服务，登录后查看状态。

1.3.2 MariaDB 数据库用户管理

上述为了实现 MariaDB 用户配置的相关功能，采取的是直接修改 mysql 库中的 user 表得以实现的。下面我们使用 MariaDB 内部提供的用户管理命令和直接修改的方式对比实现，用户管理配置。

1.在 node1 的 MariaDB 服务中创建一个 kaivi 和 duanxin 用户：

MariaDB [mysql]> select user,host,password from user;
+------+--------------+-------------------------------------------+
| user | host         | password                                  |
+------+--------------+-------------------------------------------+
| root | localhost    | *128977E278358FF80A246B5046F51043A2B1FCED |
| root | 127.0.0.1    | *128977E278358FF80A246B5046F51043A2B1FCED |
| root | 192.168.32.7 | *128977E278358FF80A246B5046F51043A2B1FCED |
+------+--------------+-------------------------------------------+
3 rows in set (0.00 sec)

MariaDB [mysql]> 
MariaDB [mysql]> insert into user(user,host,password)
 values("192.168.32.7","kaivi",password("centos1"));
ERROR 1364 (HY000): Field 'ssl_cipher' doesn't have a default value
MariaDB [mysql]> 
MariaDB [mysql]> 
MariaDB [mysql]> insert into user(user,host,password,ssl_cipher, x509_issuer, x509_subject, authentication_string) 
values("kaivi","192.168.32.7",password("centos1"),"", "", "", "");
Query OK, 1 row affected (0.00 sec)

MariaDB [mysql]> select user,host,password from user;
+--------------+--------------+-------------------------------------------+
| user         | host         | password                                  |
+--------------+--------------+-------------------------------------------+
| root         | localhost    | *128977E278358FF80A246B5046F51043A2B1FCED |
| root         | 127.0.0.1    | *128977E278358FF80A246B5046F51043A2B1FCED |
| root         | 192.168.32.7 | *128977E278358FF80A246B5046F51043A2B1FCED |
| kaivi        | 192.168.32.7 | *07012D77331829FBC7415FCFE0041354CE238D41 |
+--------------+--------------+-------------------------------------------+
4 rows in set (0.00 sec)

MariaDB [mysql]> 
MariaDB [mysql]> flush privileges;
Query OK, 0 rows affected (0.00 sec)

MariaDB [mysql]> 
MariaDB [mysql]> 
MariaDB [mysql]> create user "duanxin"@"192.168.32.7" identified by "centos2";
Query OK, 0 rows affected (0.00 sec)

MariaDB [mysql]> select user,host,password from user;
+--------------+--------------+-------------------------------------------+
| user         | host         | password                                  |
+--------------+--------------+-------------------------------------------+
| root         | localhost    | *128977E278358FF80A246B5046F51043A2B1FCED |
| root         | 127.0.0.1    | *128977E278358FF80A246B5046F51043A2B1FCED |
| root         | 192.168.32.7 | *128977E278358FF80A246B5046F51043A2B1FCED |
| duanxin      | 192.168.32.7 | *1CF9815FD56E839A519A21CFABB148B4109FEDB7 |
| kaivi        | 192.168.32.7 | *07012D77331829FBC7415FCFE0041354CE238D41 |
+--------------+--------------+-------------------------------------------+
5 rows in set (0.00 sec)

MariaDB [mysql]> 
MariaDB [mysql]>

上图中我们使用 insert 直接在 user 表中创建了 kaivi 用户。之后又使用create user 方式创建了 duanxin 用户，这两个用户的密码都不同，一个是centos1 ，一个是centos2，对比发现 user 表的 Password字段，发现密码的密文不一样。其中密文都一样的是root账号的密码centos。密码一样则加密的密文也一样。

下面我们在 client7中尝试用kaivi和duanxin 用户登录 centos7K 的 MariaDB 服务:

[root@client7 ~]#mysql -h 192.168.32.142 -u kaivi -p
Enter password: 
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 32
Server version: 10.2.29-MariaDB MariaDB Server

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> 
MariaDB [(none)]> select user();
+--------------------+
| user()             |
+--------------------+
| kaivi@192.168.32.7 |
+--------------------+
1 row in set (0.00 sec)

MariaDB [(none)]> 
MariaDB [(none)]> exit
Bye
[root@client7 ~]#
[root@client7 ~]#mysql -h 192.168.32.142 -u duanxin -p
Enter password: 
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 34
Server version: 10.2.29-MariaDB MariaDB Server

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> select user();
+----------------------+
| user()               |
+----------------------+
| duanxin@192.168.32.7 |
+----------------------+
1 row in set (0.00 sec)

MariaDB [(none)]> quit
Bye
[root@client7 ~]#
[root@client7 ~]#

在 client7中成功访问 centos7K 的 MariaDB，创建用户功能完成。

2.删除 MariaDB 中的指定用户

MariaDB [mysql]> 
MariaDB [mysql]> select user,host,password from user;
+---------+--------------+-------------------------------------------+
| user    | host         | password                                  |
+---------+--------------+-------------------------------------------+
| root    | localhost    | *128977E278358FF80A246B5046F51043A2B1FCED |
| root    | 127.0.0.1    | *128977E278358FF80A246B5046F51043A2B1FCED |
| root    | 192.168.32.7 | *128977E278358FF80A246B5046F51043A2B1FCED |
| duanxin | 192.168.32.7 | *1CF9815FD56E839A519A21CFABB148B4109FEDB7 |
| kaivi   | 192.168.32.7 | *07012D77331829FBC7415FCFE0041354CE238D41 |
+---------+--------------+-------------------------------------------+
5 rows in set (0.00 sec)

MariaDB [mysql]> 
MariaDB [mysql]> delete from user where user="kaivi";
Query OK, 1 row affected (0.00 sec)

MariaDB [mysql]> flush priveleges;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'priveleges' at line 1
MariaDB [mysql]> flush privileges;
Query OK, 0 rows affected (0.00 sec)

MariaDB [mysql]> select user,host,password from user;
+---------+--------------+-------------------------------------------+
| user    | host         | password                                  |
+---------+--------------+-------------------------------------------+
| root    | localhost    | *128977E278358FF80A246B5046F51043A2B1FCED |
| root    | 127.0.0.1    | *128977E278358FF80A246B5046F51043A2B1FCED |
| root    | 192.168.32.7 | *128977E278358FF80A246B5046F51043A2B1FCED |
| duanxin | 192.168.32.7 | *1CF9815FD56E839A519A21CFABB148B4109FEDB7 |
+---------+--------------+-------------------------------------------+
4 rows in set (0.00 sec)

MariaDB [mysql]> 
MariaDB [mysql]> 
MariaDB [mysql]> drop user "duanxin"@"192.168.32.7";
Query OK, 0 rows affected (0.00 sec)

MariaDB [mysql]> select user,host,password from user;
+------+--------------+-------------------------------------------+
| user | host         | password                                  |
+------+--------------+-------------------------------------------+
| root | localhost    | *128977E278358FF80A246B5046F51043A2B1FCED |
| root | 127.0.0.1    | *128977E278358FF80A246B5046F51043A2B1FCED |
| root | 192.168.32.7 | *128977E278358FF80A246B5046F51043A2B1FCED |
+------+--------------+-------------------------------------------+
3 rows in set (0.00 sec)

MariaDB [mysql]> 
MariaDB [mysql]> flush privileges;
Query OK, 0 rows affected (0.00 sec)

MariaDB [mysql]>

上图中我们使用了 delete 表数据，和 drop user 两种方式实现了 kaivi 和 duanxin 用户的删除操作，使用 delete 后，一定要 flush privileges。

下面我们在client7中尝试使用 kaivi 和 duanxin 用远程登陆：

[root@client7 ~]#mysql -h 192.168.32.142 -u kaivi -p
Enter password: 
ERROR 1045 (28000): Access denied for user 'kaivi'@'192.168.32.7' (using password: YES)
[root@client7 ~]#mysql -h 192.168.32.142 -u duanxin -p
Enter password: 
ERROR 1045 (28000): Access denied for user 'duanxin'@'192.168.32.7' (using password: YES)
[root@client7 ~]#^C
[root@client7 ~]#

删除用户后，对应用户不可再登陆。

3.修改 MariaDB 用户密码和之前的创建删除用户类似，我们依旧可以使用两种方法修改用户密码,处理这两种方法，还可以使用 mysqladmin 命令修改用户密码，下面演示一下：

[root@centos7K ~]#mysqladmin -u root -pcentos  password "magedu"
[root@centos7K ~]#mysql -u root -p"centos"
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)
[root@centos7K ~]#mysql -u root -p"magedu"
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 48
Server version: 10.2.29-MariaDB MariaDB Server

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> 

MariaDB [(none)]> use mysql
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
MariaDB [mysql]> 

MariaDB [mysql]> insert into user(user,host,password,ssl_cipher, x509_issuer, x509_subject, authentication_string)
 values("kaivi","192.168.32.7",password("centos1"),"", "", "", "");
Query OK, 1 row affected (0.00 sec)

MariaDB [mysql]> create user "duanxin"@"192.168.32.7" identified by "centos2";
Query OK, 0 rows affected (0.00 sec)

MariaDB [mysql]> select user,host,password from user;
+---------+--------------+-------------------------------------------+
| user    | host         | password                                  |
+---------+--------------+-------------------------------------------+
| root    | localhost    | *128977E278358FF80A246B5046F51043A2B1FCED |
| root    | 127.0.0.1    | *128977E278358FF80A246B5046F51043A2B1FCED |
| root    | 192.168.32.7 | *128977E278358FF80A246B5046F51043A2B1FCED |
| kaivi   | 192.168.32.7 | *07012D77331829FBC7415FCFE0041354CE238D41 |
| duanxin | 192.168.32.7 | *1CF9815FD56E839A519A21CFABB148B4109FEDB7 |
+---------+--------------+-------------------------------------------+
5 rows in set (0.00 sec)

MariaDB [mysql]> 
MariaDB [mysql]> 
MariaDB [mysql]> update user set password=password("magedu") where user="kaivi";
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

MariaDB [mysql]> select user,host,password from user;
+---------+--------------+-------------------------------------------+
| user    | host         | password                                  |
+---------+--------------+-------------------------------------------+
| root    | localhost    | *128977E278358FF80A246B5046F51043A2B1FCED |
| root    | 127.0.0.1    | *128977E278358FF80A246B5046F51043A2B1FCED |
| root    | 192.168.32.7 | *128977E278358FF80A246B5046F51043A2B1FCED |
| kaivi   | 192.168.32.7 | *6B8CCC83799A26CD19D7AD9AEEADBCD30D8A8664 |
| duanxin | 192.168.32.7 | *1CF9815FD56E839A519A21CFABB148B4109FEDB7 |
+---------+--------------+-------------------------------------------+
5 rows in set (0.00 sec)

MariaDB [mysql]> set password for "duanxin"@"192.168.32.7"=password("magedu");
Query OK, 0 rows affected (0.00 sec)

MariaDB [mysql]> select user,host,password from user;
+---------+--------------+-------------------------------------------+
| user    | host         | password                                  |
+---------+--------------+-------------------------------------------+
| root    | localhost    | *128977E278358FF80A246B5046F51043A2B1FCED |
| root    | 127.0.0.1    | *128977E278358FF80A246B5046F51043A2B1FCED |
| root    | 192.168.32.7 | *128977E278358FF80A246B5046F51043A2B1FCED |
| kaivi   | 192.168.32.7 | *6B8CCC83799A26CD19D7AD9AEEADBCD30D8A8664 |
| duanxin | 192.168.32.7 | *6B8CCC83799A26CD19D7AD9AEEADBCD30D8A8664 |
+---------+--------------+-------------------------------------------+
5 rows in set (0.00 sec)

MariaDB [mysql]> 
MariaDB [mysql]> flush privileges;

三种修改密码的方式，其中我们使用 mysqladmin 修改了root 的密码为 magedu, kaivi 和 duanxin 用户我们使用
client7节点远程连词测试一下：

[root@client7 ~]#mysql -h 192.168.32.142 -u kaivi -pcentos1
ERROR 1045 (28000): Access denied for user 'kaivi'@'192.168.32.7' (using password: YES)
[root@client7 ~]#mysql -h 192.168.32.142 -u duanxin -pcentos2
ERROR 1045 (28000): Access denied for user 'duanxin'@'192.168.32.7' (using password: YES)

[root@client7 ~]#mysql -h 192.168.32.142 -u kaivi -pmagedu
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 56
Server version: 10.2.29-MariaDB MariaDB Server

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MariaDB [(none)]> select user();
+--------------------+
| user()             |
+--------------------+
| kaivi@192.168.32.7 |
+--------------------+
1 row in set (0.00 sec)

MariaDB [(none)]> 

MariaDB [(none)]> exit

[root@client7 ~]#mysql -h 192.168.32.142 -u duanxin -pmagedu
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 51
Server version: 10.2.29-MariaDB MariaDB Server

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> select user();
+----------------------+
| user()               |
+----------------------+
| duanxin@192.168.32.7 |
+----------------------+
1 row in set (0.00 sec)

MariaDB [(none)]>

密码修改验证成功

4.破解 root 口令日常使用 MariaDB 是，有可能忘记 MariaDB 的 root 管理员密码，这时候，我们可以关闭 MariaDB 的网络
功能，同时取消登录权限检查功能，然后使用 root 登录到 MariaDB 中重置一个新密码即可，切记修改完后要再次打开网络
和登录权限检查功能。网络功能和权限检查功能可以在 MariaDB 的配置文件中定义，下面我们演示一下：

[root@centos7K ~]#mysql -u root -p
Enter password: 
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)
[root@centos7K ~]#vi /etc/my.cnf.d/server.cnf 
[root@centos7K ~]#cat /etc/my.cnf.d/server.cnf
# this is read by the standalone daemon and embedded servers
[server]

# this is only for the mysqld standalone daemon
[mysqld]
skip-networking
skip-grant-tables

在[mysqld]下面增加skip-networking 和skip-grant-tables

在这里插入图片描述

[root@centos7K ~]#systemctl restart mysqld
[root@centos7K ~]#ss -ntl
State      Recv-Q Send-Q             Local Address:Port                            Peer Address:Port              
LISTEN     0      128                            *:22                                         *:*                  
LISTEN     0      100                    127.0.0.1:25                                         *:*                  
LISTEN     0      128                           :::22                                        :::*                  
LISTEN     0      100                          ::1:25                                        :::*             

因为 skip-networking 所以服务端口3306没有打开，但是本地可以连接数据库


[root@centos7K ~]#mysql -u root
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 8
Server version: 10.2.29-MariaDB MariaDB Server

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> select user();
+--------+
| user() |
+--------+
| root@  |
+--------+
1 row in set (0.00 sec)

MariaDB [(none)]> use mysql
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
MariaDB [mysql]> 
MariaDB [mysql]>

这里注意的是，密码选项在低版本只有 password 但是到了高版本就多了一项authentication_string

MariaDB [mysql]> select user,host,password,authentication_string from user;
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
Connection id:    8
Current database: mysql

+---------+--------------+-------------------------------------------+-------------------------------------------+
| user    | host         | password                                  | authentication_string                     |
+---------+--------------+-------------------------------------------+-------------------------------------------+
| root    | localhost    | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 | *6B8CCC83799A26CD19D7AD9AEEADBCD30D8A8664 |
| root    | 127.0.0.1    | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |                                           |
| root    | 192.168.32.7 | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |                                           |
| kaivi   | 192.168.32.7 | *6B8CCC83799A26CD19D7AD9AEEADBCD30D8A8664 |                                           |
| duanxin | 192.168.32.7 | *6B8CCC83799A26CD19D7AD9AEEADBCD30D8A8664 | *6B8CCC83799A26CD19D7AD9AEEADBCD30D8A8664 |
+---------+--------------+-------------------------------------------+-------------------------------------------+
5 rows in set (0.02 sec)

MariaDB [mysql]> update user set authentication_string=password("centos") where user="root";
Query OK, 3 rows affected (0.00 sec)
Rows matched: 3  Changed: 3  Warnings: 0

MariaDB [mysql]> select user,host,password,authentication_string from user;
+---------+--------------+-------------------------------------------+-------------------------------------------+
| user    | host         | password                                  | authentication_string                     |
+---------+--------------+-------------------------------------------+-------------------------------------------+
| root    | localhost    | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 | *128977E278358FF80A246B5046F51043A2B1FCED |
| root    | 127.0.0.1    | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 | *128977E278358FF80A246B5046F51043A2B1FCED |
| root    | 192.168.32.7 | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 | *128977E278358FF80A246B5046F51043A2B1FCED |
| kaivi   | 192.168.32.7 | *6B8CCC83799A26CD19D7AD9AEEADBCD30D8A8664 |                                           |
| duanxin | 192.168.32.7 | *6B8CCC83799A26CD19D7AD9AEEADBCD30D8A8664 | *6B8CCC83799A26CD19D7AD9AEEADBCD30D8A8664 |
+---------+--------------+-------------------------------------------+-------------------------------------------+
5 rows in set (0.00 sec)

MariaDB [mysql]> flush privileges;
Query OK, 0 rows affected (0.00 sec)

MariaDB [mysql]> Ctrl-C -- exit!
Aborted
[root@centos7K ~]#
[root@centos7K ~]#mysql -u root -pcentos
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 11
Server version: 10.2.29-MariaDB MariaDB Server

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MariaDB [(none)]> select user();
+----------------+
| user()         |
+----------------+
| root@localhost |
+----------------+
1 row in set (0.00 sec)

MariaDB [(none)]> 
MariaDB [(none)]> 
MariaDB [(none)]> Ctrl-C -- exit!
Aborted

[root@centos7K ~]#

[root@centos7K ~]#vi /etc/my.cnf.d/server.cnf 
[root@centos7K ~]#cat /etc/my.cnf.d/server.cnf

# this is read by the standalone daemon and embedded servers
[server]

# this is only for the mysqld standalone daemon
[mysqld]
#skip-networking
#skip-grant-tables

#

注释掉之前所加的配置文件skip-networking和skip-grant-tables

在这里插入图片描述

[root@centos7K ~]#systemctl restart mariadb
[root@centos7K ~]#ss -ntl
State      Recv-Q Send-Q             Local Address:Port                            Peer Address:Port              
LISTEN     0      128                            *:22                                         *:*                  
LISTEN     0      100                    127.0.0.1:25                                         *:*                  
LISTEN     0      80                            :::3306                                      :::*                  
LISTEN     0      128                           :::22                                        :::*                  
LISTEN     0      100                          ::1:25                                        :::*

5.通常情况下，我们不会使用 root 用户直接访问数据库去运行业务系统。通常情况下我们创建一个数据库用
着业务使用，然后授权一个专门的用户来操作和访问这个数据库，下面演示一下:

[root@centos7K ~]#mysql -u root -p
Enter password: 
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 9
Server version: 10.2.29-MariaDB MariaDB Server

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
+--------------------+
3 rows in set (0.00 sec)

MariaDB [(none)]> create database school;
Query OK, 1 row affected (0.00 sec)

MariaDB [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| school             |
+--------------------+
4 rows in set (0.00 sec)

MariaDB [(none)]> 
MariaDB [(none)]> grant all on school.* to "likai"@"192.168.32.%" identified by "centos";
Query OK, 0 rows affected (0.01 sec)

MariaDB [(none)]> use mysql
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
MariaDB [mysql]> select user,host,password,authentication_string from user;
+---------+--------------+-------------------------------------------+-------------------------------------------+
| user    | host         | password                                  | authentication_string                     |
+---------+--------------+-------------------------------------------+-------------------------------------------+
| root    | localhost    | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 | *128977E278358FF80A246B5046F51043A2B1FCED |
| root    | 127.0.0.1    | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 | *128977E278358FF80A246B5046F51043A2B1FCED |
| root    | 192.168.32.7 | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 | *128977E278358FF80A246B5046F51043A2B1FCED |
| kaivi   | 192.168.32.7 | *6B8CCC83799A26CD19D7AD9AEEADBCD30D8A8664 |                                           |
| duanxin | 192.168.32.7 | *6B8CCC83799A26CD19D7AD9AEEADBCD30D8A8664 | *6B8CCC83799A26CD19D7AD9AEEADBCD30D8A8664 |
| likai   | 192.168.32.% | *128977E278358FF80A246B5046F51043A2B1FCED |                                           |
+---------+--------------+-------------------------------------------+-------------------------------------------+
6 rows in set (0.00 sec)

MariaDB [mysql]> Ctrl-C -- exit!
Aborted
[root@centos7K ~]#

接下来，我们在 client7中使用 admin 用户连接 centos7K 的 MariaDB 服务:

[root@client7 ~]#mysql -h 192.168.32.143 -u likai -p"centos"
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 10
Server version: 10.2.29-MariaDB MariaDB Server

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| school             |
+--------------------+
2 rows in set (0.00 sec)

MariaDB [(none)]> Ctrl-C -- exit!
Aborted
[root@client7 ~]#

可以发现这个likai账户只能管理school数据库，其他库看不到，即没有对应的访问操作权限。

KAIVI-Blog

关注

0
点赞
踩
1

收藏

觉得还不错? 一键收藏
0
评论
MariaDB 基础使用实验二———权限设置

1.3.1 MariaDB 数据库用户介绍数据库作为一个数据存储的软件服务程序，可以便捷高效的存储关系型数据信息，比我们平时使用的 office 表格程序效率高并易于管理。不过这个数据库程序到底谁有权限使用呢？我们之前学习 Linux 操作系统时，为了管理系统中资源使用的权限，Linux 系统设置了超级用户root、系统用户、普通用户等概念。和 Linux 系统相仿， MariaDB 也设置了...
复制链接

扫一扫