MariaDB安全配置与用户管理-优快云博客

本文链接：https://blog.youkuaiyun.com/qq_44564366/article/details/104419005

在使用yum安装mariadb之后，启动的时候我们发现使用mysql命令或者mysql -xxx可以登录到数据库中。造成在这方面的原因就是数据库中的用户权限导致的。

MariaDB [mysql]> SELECT user,host,password FROM user;
+------+-----------+----------+
| user | host      | password |
+------+-----------+----------+
| root | localhost |          |
| root | ansible   |          |
| root | 127.0.0.1 |          |
| root | ::1       |          |
|      | localhost |          |
|      | ansible   |          |
+------+-----------+----------+

通过上边我们可以看到密码全部没有，还有的用户名空。-uxxx就是利用的用户名为空的匿名账户进行登录的。

未防止这种现象我们在安装完成之后要使用mysql_secure_installation重新引导一下

[root@ansible ~]# 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] yes    是否设置root的密码
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  #是否禁止root远程登录
 ... 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   #是否移除test数据库
 - 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@ansible ~]# mysql -uroot -h127.0.0.1 -p
Enter password: 

-u 指明用户
-h 指明地址
-p 输入密码，密码可以回车之后输，也可以直接在命令行中显式的指出


MariaDB [mysql]> SELECT user,host,password FROM user;
+------+-----------+-------------------------------------------+
| user | host      | password                                  |
+------+-----------+-------------------------------------------+
| root | localhost | *7232436906A1F0923BE1FCD0C52CF2006DDFCA18 |
| root | 127.0.0.1 | *7232436906A1F0923BE1FCD0C52CF2006DDFCA18 |
| root | ::1       | *7232436906A1F0923BE1FCD0C52CF2006DDFCA18 |
+------+-----------+-------------------------------------------+
3 rows in set (0.01 sec)

用户创建

CREATE USER 'USERNAME'@'HOST'[IDENTIFIED BY 'password']；

MariaDB [mysql]> CREATE USER 'ydong'@'192.168.199.%' IDENTIFIED BY 'ydong';

%表示任意199网段的主机 


MariaDB [mysql]> SELECT user,host,password FROM user;
+-------+---------------+-------------------------------------------+
| user  | host          | password                                  |
+-------+---------------+-------------------------------------------+
| root  | localhost     | *7232436906A1F0923BE1FCD0C52CF2006DDFCA18 |
| root  | 127.0.0.1     | *7232436906A1F0923BE1FCD0C52CF2006DDFCA18 |
| root  | ::1           | *7232436906A1F0923BE1FCD0C52CF2006DDFCA18 |
| ydong | 192.168.199.% | *FACC18173DBE912D34BC8FC77E596D70A6951013 |
+-------+---------------+-------------------------------------------+
4 rows in set (0.01 sec)

测试登录

[root@ansible ~]# mysql -uydong -h192.168.199.220 -pydong

MariaDB [(none)]> 
MariaDB [(none)]> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| information_schema |
+--------------------+
1 row in set (0.00 sec)

修改密码

1）使用set语句

MariaDB [(none)]> SET PASSWORD  FOR 'ydong'@'192.168.199.%'=PASSWORD('xiaohong');
Query OK, 0 rows affected (0.01 sec)

2)使用root账户直接修改mysql.user表里对应的用户

MariaDB [mysql]> UPDATE user SET password=PASSWORD('passwd') where user='ydong';
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings:

一定要添加where条件，否则所有的密码都要修改

MariaDB [mysql]> FLUSH PRIVILEGES;
再对用户做修改之后，需要刷新一下才能生效

用户重命名

RENAME USER old_user_name TO new_user_name

MariaDB [mysql]> RENAME USER 'ydong'@'192.168.199.%' TO 'ydong06';

ydong06后面也需要加主机地址，如果不加，默认所有主机

删除用户

DROP USER 'USERNAME'@'HOST‘

MariaDB [mysql]> DROP USER 'ydong06'@'%';

用户授权

在创建一个用户之后，它是没有对数据有增，删，改的权限。而且查也只能查到一个虚拟库
GRANT priv_type [(column_list)],... ON [object_type] priv_level TO 'user'@'host' [IDENTIFIED BY 'password'] [WITH GRANT OPTION];

 priv_type: ALL [PRIVILEGES]
object_type:TABLE | FUNCTION | PROCEDURE
priv_level: *(所有库) | *.* | db_name.* | db_name.tbl_name
| tbl_name(当前库的表) | db_name.routine_name(指定库的函数,存储过程,触发器)
 with_option:  GRANT OPTION
| MAX_QUERIES_PER_HOUR count
| MAX_UPDATES_PER_HOUR count
| MAX_CONNECTIONS_PER_HOUR count
| MAX_USER_CONNECTIONS count`

示例：

MariaDB [(none)]> GRANT ALL ON hellodb.* TO 'ydong'@'192.168.199.%';
Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| hellodb            |
+--------------------+
2 rows in set (0.02 sec)

ydong用户可以对hellodb进行任何操作


MariaDB [(none)]> GRANT SELECT (stuid,name) ON hellodb.students TO 'ydong'@'192.168.199.%' IDENTIFIED BY 'yydd';
# 只允许ydong对students表中的stuid，name字段进行查询

查看授权

MariaDB [(none)]> SHOW GRANTS FOR 'ydong'@'192.168.199.%';
+------------------------------------------------------------------------------------------------------------------+
| Grants for ydong@192.168.199.%                                                                                   |
+------------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'ydong'@'192.168.199.%' IDENTIFIED BY PASSWORD '*08D6B000745744EBACF139C5DFFA63D42E00CDF7' |
| GRANT ALL PRIVILEGES ON `hellodb`.* TO 'ydong'@'192.168.199.%'                                                   |
| GRANT SELECT (stuid, name) ON `hellodb`.`students` TO 'ydong'@'192.168.199.%'                                    |
+------------------------------------------------------------------------------------------------------------------+
当用户拥有多个权限时，已最大权限为准

收回授权

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

MariaDB [(none)]> REVOKE UPDATE,INSERT ON hellodb.* FROM 'ydong'@'192.168.199.%';
Query OK, 0 rows affected (0.00 sec)

注意：在使用对用户进行一些权限的设置时候需要使用flush privileges来让权限生效。