526互联

数据库安全管理

发布时间 2023-09-13 11:52:39作者: louvice

数据库安全管理

可以使用TCP抓包工具，抓包到数据库的操作情况

history可以查看明文的密码

1、用户账户管理

## 创建用户，只允许192.168.79.128用户登录
mysql> create user 'louvice'@'192.168.79.128' identified by '123456';
Query OK, 0 rows affected (0.01 sec)

## 创建用户，可以从任何ip登录到该数据库
mysql> create user 'louvice'@'%' identified by '123456';
Query OK, 0 rows affected (0.01 sec)

## 查看系统用户
mysql> 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
mysql> select user, host from user;
+---------------+----------------+
| user          | host           |
+---------------+----------------+
| louvice       | %              |
| rep           | 192.168.79.%   |
| louvice       | 192.168.79.128 |
| mysql.session | localhost      |
| mysql.sys     | localhost      |
| root          | localhost      |
+---------------+----------------+
6 rows in set (0.00 sec)

#删除数据库用户
mysql> drop user louvice@'192.168.79.128';
Query OK, 0 rows affected (0.01 sec)

#查看用户密码，已加密
mysql> select user, host, authentication_string from user;
+---------------+--------------+-------------------------------------------+
| user          | host         | authentication_string                     |
+---------------+--------------+-------------------------------------------+
| root          | localhost    | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
| mysql.session | localhost    | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE |
| mysql.sys     | localhost    | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE |
| rep           | 192.168.79.% | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
| louvice       | %            | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
+---------------+--------------+-------------------------------------------+
5 rows in set (0.00 sec)

#创建用户并授权
mysql> create user wing@'localhost' identified by '123456';
Query OK, 0 rows affected (0.00 sec)

mysql> grant all on *.* to wing@'localhost';
Query OK, 0 rows affected (0.01 sec)
#刷新权限
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

##一条命令创建用户并赋予权限
mysql> grant all on *.* to wing@'localhost' identified by '123456';
Query OK, 0 rows affected, 1 warning (0.00 sec)

## 通过delete也可以删除用户
mysql> delete from mysql.user where user = 'wing' and host = 'localhost';
Query OK, 1 row affected (0.01 sec)


#修改root密码
方式1；通过shell修改
mysqladmin -uroot -p'123456' password '12345678'

方式2：数据库内更新密码
update mysql.user set authentication_string = password('123456') where user='root' and host='';
#刷新权限
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

方式3：设定密码
set password=password('123456')

2、访问权限系统

mysql.user全局授权

mysql> select * from mysql.user where user='root'\G
*************************** 1. row ***************************
Host: localhost
User: root
Select_priv: Y
Insert_priv: Y
Update_priv: Y
Delete_priv: Y
Create_priv: Y
Drop_priv: Y
Reload_priv: Y
Shutdown_priv: Y
Process_priv: Y
File_priv: Y
Grant_priv: Y
References_priv: Y
Index_priv: Y
Alter_priv: Y
Show_db_priv: Y
Super_priv: Y
Create_tmp_table_priv: Y
Lock_tables_priv: Y
Execute_priv: Y
Repl_slave_priv: Y
Repl_client_priv: Y
Create_view_priv: Y
Show_view_priv: Y
Create_routine_priv: Y
Alter_routine_priv: Y
Create_user_priv: Y
Event_priv: Y
Trigger_priv: Y
Create_tablespace_priv: Y
ssl_type:
ssl_cipher:
x509_issuer:
x509_subject:
max_questions: 0
max_updates: 0
max_connections: 0
max_user_connections: 0
plugin: mysql_native_password
authentication_string: *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9
password_expired: N
password_last_changed: 2023-09-12 04:55:16
password_lifetime: NULL
account_locked: N
1 row in set (0.00 sec)

mysql.db（数据库级别）

user > db > tables > columns

2.1 权限列表

all 所有权限（不包括授权权限）
单独授权 select,update,insert,delete

2.2 库名表名

*.* 所有库下的所有表 Global level
wing.* 针对 wing 库下的所有表 Database level
wing.student 针对 wing 库下的 student 表 Table level
SELECT (id),INSERT (name,age) ON wing.t1 针对 wing 库下⾯ t1 表的字段 Column level

2.3 客户端主机

localhost 指定本机

10.1.106.70 指定具体主机

10.1.106.0 ⽹段的所有主机

10.1.106.% ⽹段的所有主机

% 指定所有主机

2.4 with参数

GRANT OPTION 授权选项

MAX_QUERIES_PER_HOUR 定义每⼩时允许执⾏的查询数

MAX_UPDATES_PER_HOUR 定义每⼩时允许执⾏的更新数

MAX_CONNECTIONS_PER_HOUR 定义每⼩时可以建⽴的连接数

MAX_USER_CONNECTIONS 定义单个⽤户同时可以建⽴的连接数

## 授权wing用户拥有所有库的操作权限
GRANT ALL ON *.* TO wing@'%' IDENTIFIED BY 'Wing@123';

## 授权wing用户授权其他用户的权限
GRANT ALL ON *.* TO wing1@'%' IDENTIFIED BY 'Wing@123' WITH GRANT OPTION;

## 授权bbs库给wing2⽤户
GRANT ALL ON bbs.* TO wing2@'%' IDENTIFIED BY 'Wing@123';

## 授权bbs库给wing3⽤户@10.1.106.70，只能通过该ip进行登录
GRANT ALL ON bbs.* TO wing3@'10.1.106.70' IDENTIFIED BY 'Wing@123';
mysql -uwing3 -p -h10.1.106.70

##  授权 wing4⽤户只能访问bbs.user表
GRANT ALL ON bbs.user TO wing4@'%' IDENTIFIED BY 'Wing@123';

## 授权wing5对 bbs库user表中id 只能查询, name和age字段可以插⼊
GRANT SELECT(id),INSERT(name,age) ON bbs.user TO wing5@'%' IDENTIFIED BY 'Wing@123';
mysql> select * from user;
ERROR 1142 (42000): SELECT command denied to user 'wing5'@'master' for table 'user'
mysql> 
mysql> select id from user;
+----+
| id |
+----+
|  1 |
+----+
1 row in set (0.00 sec)

mysql> insert into user(name,age) values('wing',18);
Query OK, 1 row affected (0.00 sec)

## 访问权限回收
mysql> show grants;
+---------------------------------------------------------------------+
| Grants for root@localhost |
+---------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' WITH GRANT OPTION |
| GRANT PROXY ON ''@'' TO 'root'@'localhost' WITH GRANT OPTION |
+---------------------------------------------------------------------+

## 查看其它⽤户权限
mysql> show grants for wing1@'%';
+--------------------------------------------------------------+
| Grants for wing1@% |
+--------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'wing1'@'%' WITH GRANT OPTION |
+--------------------------------------------------------------+
1 row in set (0.00 sec)

3、 回收权限

语法: REVOKE 权限列表 ON 数据库名 FROM ⽤户名@'客户端主机';

## 回收Delete权限
mysql> revoke DELETE on *.* from wing1@'%';

## 回收所有权限
mysql> revoke ALL PRIVILEGES ON *.* from 'wing1'@'%';
Query OK, 0 rows affected (0.00 sec)

mysql> show grants for wing1@'%'\G
*************************** 1. row ***************************
Grants for wing1@%: GRANT USAGE ON *.* TO 'wing1'@'%' WITH GRANT OPTION

## 回收grant权限
mysql> revoke GRANT OPTION ON *.* from 'wing1'@'%';
Query OK, 0 rows affected (0.00 sec)
mysql> show grants for wing1@'%'\G
*************************** 1. row ***************************
Grants for wing1@%: GRANT USAGE ON *.* TO 'wing1'@'%'

开发⼈员 就是select 权限 或者对某⼀个库有权限 ⼀定不能root权限
业务测java程序 连接都有单独⾃⼰权限 app ⽤户只能访问app库 权限最⼩化