标签：GRANT ## 管理 sec user mysql 数据库安全 priv

数据库安全管理

可以使用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库 权限最⼩化

标签：GRANT,##,管理,sec,user,mysql,数据库安全,priv
From： https://www.cnblogs.com/louvice/p/17699209.html

数据库安全管理

数据库安全管理

1、用户账户管理

2、访问权限系统

2.1 权限列表

2.2 库名表名

2.3 客户端主机

2.4 with参数

3、回收权限

相关文章

赞助商

阅读排行

数据库安全管理

数据库安全管理

1、用户账户管理

2、访问权限系统

2.1 权限列表

2.2 库名表名

2.3 客户端主机

2.4 with参数

3、 回收权限

相关文章

赞助商

阅读排行

3、回收权限