Linux~数据库账户和权限管理

linuxMinx

墨衍会员 · AI 创作全网分发码龄15年

关注

原创 2024-08-15 19:41:34 586 阅读 AI 写同款· GEO 优化 ›

Linux权限阅读详情

一、数据介绍

（一）网站元素简介

#网站元素：
	· 存在服务器上的都有啥？
		- 视频、图片、文件、程序代码（html、css、js、）；
	· 存储在数据库：
		- 文章、订单、商品、钱、装备；

（二）数据库分类介绍

#数据库分为：
	- 关系型：二维表，excel
		· mysql（mariadb）
		· oracle（收费）
		· SQL Server（退出）
	- 非关系型：key = value
		· redis（内存）
		· MongoDB（文档类、json格式）
		· ES（文档、企业日志信息存储）；
	- 国产数据库：分布式，大数据存储；
		· tidb （TB级别数据量存储）
		· OB （蚂蚁金服）
		· 高斯（华为）
		· 达梦
	- 云厂商：
		· RDS
		· TDSQL

二、安装数据库

（一）安装服务端SERVER

yum -y install mariadb-server mariadb

（二）安装客户端CLINET

yum install --y MariaDB-client

三、数据库管理

（一）启动数据库

启动数据库方式一：

systemctl start mariadb.service

启动数据库方式二：systemctl enable mariadb.service

[root@10 code]# systemctl enable mariadb.service
Created symlink from /etc/systemd/system/multi-user.target.wants/mariadb.service to /usr/lib/systemd/system/mariadb.service.
[root@10 code]#

（二）查看数据库版本和状态

rpm -qa | grep mariadb-server

查看数据库状态：systemctl status mariadb.server

（三）数据库重要的目录文件管理

[root@10 code]# rpm -ql mariadb-server
/etc/logrotate.d/mariadb
。。。。。
。。。。。
。。。。。
/var/lib/mysql
/var/log/mariadb
/var/log/mariadb/mariadb.log
/var/run/mariadb
[root@10 code]#

（四）数据库重要的日志切割文件

cat /etc/logrotate.d/mariadb

（五）数据库配置文件

/etc/my.cnf.d/server.cnf

（六）数据库的数据存储目录

/var/lib/mysql/

（七）数据库日志

/var/log/mariadb/mariadb.log

四、用户管理

（一）创建数据库用户

创建数据库语法：create user harbor@'10.0.0.%' identified by '1'

MariaDB [(none)]> create user newUser01@'10.0.0.%' identified by '1';
Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> select user ,host from mysql.user;
+-----------+-------------------+
| user      | host              |
+-----------+-------------------+
| mysqlTest | 10.0.0.%          |
| newUser01 | 10.0.0.%          |
|           | 10.0.0.200-harbor |
| root      | 10.0.0.200-harbor |
| root      | 127.0.0.1         |
| root      | ::1               |
|           | localhost         |
| root      | localhost         |
+-----------+-------------------+
8 rows in set (0.00 sec)

MariaDB [(none)]>

创建新用户指定连接IP网段后，再次登录需要TCP登录，指定主机IP，否则无法登录成功

[root@10 phpshe1]# mysql -unewUser01 -p1
ERROR 1045 (28000): Access denied for user 'newUser01'@'localhost' (using password: YES)
[root@10 phpshe1]# mysql -unewUser01 -p1 -h10.0.0.200
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 144
Server version: 5.5.68-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)]> exit
Bye
[root@10 phpshe1]#

（二）删除数据库用户

删除数据库方式一：drop user 【用户名】@'[主机域]'

MariaDB [(none)]> drop user newUser02@'10.0.0.%';
Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]>

（三）修改数据库用户

无法修改名称，只能修改密码

MariaDB [(none)]> update mysql.user set password='1' where user='newUser01' and host='10.0.0.%';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

MariaDB [(none)]> 

MariaDB [(none)]> select user ,host,password from mysql.user;
+-----------+-------------------+-------------------------------------------+
| user      | host              | password                                  |
+-----------+-------------------+-------------------------------------------+
| root      | localhost         | *E6CC90B878B948C35E92B003C792C46C58C4AF40 |
| root      | 10.0.0.200-harbor |                                           |
| root      | 127.0.0.1         |                                           |
| root      | ::1               |                                           |
|           | localhost         |                                           |
|           | 10.0.0.200-harbor |                                           |
| mysqlTest | 10.0.0.%          | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
| newUser01 | 10.0.0.%          | 1                                         |
+-----------+-------------------+-------------------------------------------+
8 rows in set (0.00 sec)

PASSWORD 函数

MariaDB [(none)]> update mysql.user set password=PASSWORD('1') where user='newUser01' and host='10.0.0.%';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

MariaDB [(none)]> select user ,host,password from mysql.user;
+-----------+-------------------+-------------------------------------------+
| user      | host              | password                                  |
+-----------+-------------------+-------------------------------------------+
| root      | localhost         | *E6CC90B878B948C35E92B003C792C46C58C4AF40 |
| root      | 10.0.0.200-harbor |                                           |
| root      | 127.0.0.1         |                                           |
| root      | ::1               |                                           |
|           | localhost         |                                           |
|           | 10.0.0.200-harbor |                                           |
| mysqlTest | 10.0.0.%          | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
| newUser01 | 10.0.0.%          | *E6CC90B878B948C35E92B003C792C46C58C4AF40 |
+-----------+-------------------+-------------------------------------------+
8 rows in set (0.00 sec)

MariaDB [(none)]>

2.方法二修改数据库用户

#了解
#另一种mysql修改密码方式
alter user 用户名@'主机域' identified by '新密码';

（四）查询数据库用户

查询数据库用户方法一：语法 show grants for 【用户名】@'主机域'

MariaDB [(none)]> show grants for newUser01@'10.0.0.%';
+-----------------------------------------------------------------------------------------------------------------+
| Grants for newUser01@10.0.0.%                                                                                   |
+-----------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'newUser01'@'10.0.0.%' IDENTIFIED BY PASSWORD '*E6CC90B878B948C35E92B003C792C46C58C4AF40' |
+-----------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

MariaDB [(none)]>

查看当前登录用户

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

MariaDB [(none)]>

五、初始密码修改管理员密码

（一）Mysqladmin 系统外修改

[root@10 code]# mysqladmin -u root -p password "1"
Enter password: 

[root@10 code]# mysql -uroot -p1
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 225
Server version: 5.5.68-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)]> quit

（二）通过登录MySQL系统修改

[root@10 code]# mysql -uroot -p1

#接下来更新mysql.user表

MariaDB [mysql]> select user,password,host from user;
+-----------+-------------------------------------------+-------------------+
| user      | password                                  | host              |
+-----------+-------------------------------------------+-------------------+
| root      | *E6CC90B878B948C35E92B003C792C46C58C4AF40 | localhost         |
| root      |                                           | 10.0.0.200-oldboy |
| root      |                                           | 127.0.0.1         |
| root      |                                           | ::1               |
|           |                                           | localhost         |
|           |                                           | 10.0.0.200-oldboy |
| mysqlTest | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 | 10.0.0.%          |
| newUser01 | *E6CC90B878B948C35E92B003C792C46C58C4AF40 | 10.0.0.%          |
+-----------+-------------------------------------------+-------------------+
8 rows in set (0.00 sec)

MariaDB [mysql]>

六、绕权特殊修改密码

背景：忘记密码，重修修改密码

（一）关闭数据库

语句：systemctl stop mariadb.service

如果关闭数据库，查看进程仍然存在： pkill mysqld

（二）关闭授权表和网络

--skip-grant-table #跳过验证权限表

--skip-network #跳过网络

[] # mysqld_safe --skip-grant-table --skip-network &

#查看是否启动

[root@10 ~]# ps -ef | grep mysqld

（三）本地登录数据库

#此时直接登录数据库
[root@10 ~]# mysql
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 1

MariaDB [(none)]>

（四）修改用户名密码

1.本地查看用户信息


MariaDB [(none)]> select user,host,password from mysql.user;
+-----------+-------------------+-------------------------------------------+
| user      | host              | password                                  |
+-----------+-------------------+-------------------------------------------+
| root      | localhost         | *E6CC90B878B948C35E92B003C792C46C58C4AF40 |
| root      | 10.0.0.200-harbor |                                           |
| root      | 127.0.0.1         |                                           |
| root      | ::1               |                                           |
|           | localhost         |                                           |
|           | 10.0.0.200-harbor |                                           |
| mysqlTest | 10.0.0.%          | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
| newUser01 | 10.0.0.%          | *E6CC90B878B948C35E92B003C792C46C58C4AF40 |
+-----------+-------------------+-------------------------------------------+
8 rows in set (0.00 sec)

MariaDB [(none)]>

2.修改用户表中的密码字段信息

MariaDB [(none)]> update mysql.user set password=PASSWORD("1") where user='root' and host='localhost';

3.刷新落盘

MariaDB [(none)]> flush privileges;

4.退出数据库:exit \quit;

（五）杀死mysqld进程

[root@10 ~]# pkill mysqld

（六）正常启动数据库

[root@10 ~]# systemctl start mariadb.service

查看数据库端口

[root@10 code]# netstat -tnulp
Active Internet connections (only servers)
Proto Recv-Q Send-Q Local Address           Foreign Address         State       PID/Program name    
tcp        0      0 0.0.0.0:22              0.0.0.0:*               LISTEN      1223/sshd           
tcp        0      0 127.0.0.1:25            0.0.0.0:*               LISTEN      1374/master         
tcp        0      0 127.0.0.1:9000          0.0.0.0:*               LISTEN      1224/php-fpm: maste 
tcp        0      0 0.0.0.0:3306            0.0.0.0:*               LISTEN      1878/mysqld         
tcp        0      0 0.0.0.0:8080            0.0.0.0:*               LISTEN      2930/nginx: master  
tcp        0      0 0.0.0.0:80              0.0.0.0:*               LISTEN      2930/nginx: master  
tcp6       0      0 :::22                   :::*                    LISTEN      1223/sshd           
tcp6       0      0 ::1:25                  :::*                    LISTEN      1374/master         
udp        0      0 127.0.0.1:323           0.0.0.0:*                           794/chronyd         
udp        0      0 0.0.0.0:68              0.0.0.0:*                           907/dhclient        
udp6       0      0 ::1:323                 :::*                                794/chronyd         
[root@10 code]#

（七）登录数据库测试

[root@10 ~]# mysql -uroot -p1
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 4
Server version: 5.5.68-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)]>

七、用户权限管理

（一）给用户授权

数据库内授权语法：grant 权限 on 库.表 to newUser01@'10.0.0.%'

MariaDB [(none)]> show grants for newUser01@'10.0.0.%';
+-----------------------------------------------------------------------------------------------------------------+
| Grants for newUser01@10.0.0.%                                                                                   |
+-----------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'newUser01'@'10.0.0.%' IDENTIFIED BY PASSWORD '*E6CC90B878B948C35E92B003C792C46C58C4AF40' |
+-----------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

MariaDB [(none)]>

对权限进行修改

MariaDB [(none)]> grant all on *.* to newUser01@'10.0.0.%';
Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> show grants for newUser01@'10.0.0.%';
+--------------------------------------------------------------------------------------------------------------------------+
| Grants for newUser01@10.0.0.%                                                                                            |
+--------------------------------------------------------------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'newUser01'@'10.0.0.%' IDENTIFIED BY PASSWORD '*E6CC90B878B948C35E92B003C792C46C58C4AF40' |
+--------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

MariaDB [(none)]>

（二）查看MySQL系统所有权限

语句：show privileges;

MariaDB [(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