mysql审计权限_mysql数据库环境之二： 用户权限审计管理

2.1)mysql的权限：

用户+IP

如：

itpux@127.0.0.1

itpux@localhost

itpux@192.168.31.51

itpux@192.168.31.%

2.2) mysql的各种权限

-- sql语句类

​

create Create_priv 数据库、表、索引

​

drop Drop_priv 数据库、表

​

grant option Grant_priv 数据库、表、存储过程、函数

​

references References_priv 数据库、表

​

alter 修改表

​

delete 删除表

​

index 索引

​

insert 插入

​

select 查询

​

update 更新

​

create view 创建视图

​

show view 查看视图

​

create temporary tables 创建临时表

​

lock tables 锁表

​

create user 创建用户

​

-- 存储过程

​

alter routine 修改存储过程

​

create routine 创建存储过程

​

execute 执行存储过程

​

-- 管理类权限

​

process 服务器管理

​

reload 重新加载权限表

​

replication client 服务器管理

​

replication slave 服务器管理

​

show databases 查看数据库

​

shutdown 关闭服务器

​

super 超级权限

2.3)显示权限

SHOW GRANTS FOR dbaadmin@'localhost';

GRANT ALL PRIVILEGES ON *.* TO 'dbaadmin'@'localhost' WITH GRANT OPTION

​

显示当前登录用户的权限

mysql> show grants for current_user;

+---------------------------------------------------------------------+

| Grants for root@localhost |

+---------------------------------------------------------------------+

| GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' WITH GRANT OPTION |

| GRANT PROXY ON ''@'' TO 'root'@'localhost' WITH GRANT OPTION |

+---------------------------------------------------------------------+

2 rows in set (0.00 sec)

2.4)创建用户并授权

第一种方式：先创建用户，然后授权

语法：

mysql> help create user

Name: 'CREATE USER'

Description:

Syntax:

CREATE USER [IF NOT EXISTS]

user [auth_option] [, user [auth_option]] ...

[REQUIRE {NONE | tls_option [[AND] tls_option] ...}]

[WITH resource_option [resource_option] ...]

[password_option | lock_option] ...

​

user:

(see )

​

auth_option: {

IDENTIFIED BY 'auth_string'

| IDENTIFIED WITH auth_plugin

| IDENTIFIED WITH auth_plugin BY 'auth_string'

| IDENTIFIED WITH auth_plugin AS 'auth_string'

| IDENTIFIED BY PASSWORD 'auth_string'

}

​

tls_option: {

SSL

| X509

| CIPHER 'cipher'

| ISSUER 'issuer'

| SUBJECT 'subject'

}

​

resource_option: {

MAX_QUERIES_PER_HOUR count

| MAX_UPDATES_PER_HOUR count

| MAX_CONNECTIONS_PER_HOUR count

| MAX_USER_CONNECTIONS count

}

​

password_option: {

PASSWORD EXPIRE

| PASSWORD EXPIRE DEFAULT

| PASSWORD EXPIRE NEVER

| PASSWORD EXPIRE INTERVAL N DAY

}

​

lock_option: {

ACCOUNT LOCK

| ACCOUNT UNLOCK

}

​

案例

CREATE USER itpux1@localhost;

CREATE USER itpux1@'%' IDENTIFIED BY 'itpux1';

​

该用户只能登录，没有权限

mysql> show grants for 'itpux1'@'localhost';

+--------------------------------------------+

| Grants for itpux1@localhost |

+--------------------------------------------+

| GRANT USAGE ON *.* TO 'itpux1'@'localhost' |

+--------------------------------------------+

第二种方式，通过grant创建一个有权限的用户

语法：

mysql> help grant

Name: 'GRANT'

Description:

Syntax:

GRANT

priv_type [(column_list)]

[, priv_type [(column_list)]] ...

ON [object_type] priv_level

TO user [auth_option] [, user [auth_option]] ...

[REQUIRE {NONE | tls_option [[AND] tls_option] ...}]

[WITH {GRANT OPTION | resource_option} ...]

​

GRANT PROXY ON user

TO user [, user] ...

[WITH GRANT OPTION]

​

object_type: {

TABLE

| FUNCTION

| PROCEDURE

}

​

priv_level: {

*

| *.*

| db_name.*

| db_name.tbl_name

| tbl_name

| db_name.routine_name

}

​

user:

(see https://dev.mysql.com/doc/refman/5.7/en/account-names.html)

​

auth_option: {

IDENTIFIED BY 'auth_string'

| IDENTIFIED WITH auth_plugin

| IDENTIFIED WITH auth_plugin BY 'auth_string'

| IDENTIFIED WITH auth_plugin AS 'auth_string'

| IDENTIFIED BY PASSWORD 'auth_string'

}

​

tls_option: {

SSL

| X509

| CIPHER 'cipher'

| ISSUER 'issuer'

| SUBJECT 'subject'

}

​

resource_option: {

| MAX_QUERIES_PER_HOUR count

| MAX_UPDATES_PER_HOUR count

| MAX_CONNECTIONS_PER_HOUR count

| MAX_USER_CONNECTIONS count

}

​

案例：

GRANT ALL PRIVILEGES ON *.* TO 'itpux2'@'%' IDENTIFIED BY 'itpux2';

​

授权的访问

​

on *.* ---- mysql.user表

on 库名.* ---- mysql.db表

on 库名.表名 ---- mysql.table_priv

on 库名.表名.列名 ---- mysql.columns_priv

​

mysql> show grants for 'itpux2'@'%';

+---------------------------------------------+

| Grants for itpux2@% |

+---------------------------------------------+

| GRANT ALL PRIVILEGES ON *.* TO 'itpux2'@'%' |

+---------------------------------------------+

1 row in set (0.00 sec)

​

刷新权限

flush privileges;

2.5)授权案例

1)授权普通数据用户，具有查询、插入、更新、删除数据库所有表数据的权限

GRANT SELECT,INSERT,UPDATE,DELETE ON itpux.* TO 'itpux3'@'%' IDENTIFIED BY 'itpux3';

flush privileges;

​

[root@itpuxdb ~]# mysql -u itpux3 -h 192.168.31.51 -p

Enter password:

mysql> show databases;

+--------------------+

| Database |

+--------------------+

| information_schema |

| itpux |

+--------------------+

2 rows in set (0.00 sec)

​

2)开发人员授权 (创建表/索引/视图/存储过程)

create user dev@'%' identified by 'dev123456';

grant create,drop,alter,delete,update,insert,select,index,create view,show view,create temporary tables,lock tables,alter routine,create routine,execute on itpux.* to 'dev'@'%';

flush privileges;

​

mysql> show grants for dev@'%';

+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

| Grants for dev@% |

+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

| GRANT USAGE ON *.* TO 'dev'@'%' |

| GRANT REFERENCES,SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, INDEX, ALTER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE ON `itpux`.* TO 'dev'@'%' |

+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

2 rows in set (0.00 sec)

​

​

3)授权dba可以管理数据库所有权限

​

create user dbaadmin@'%' identified by 'dba123456';

grant all privileges on *.* to 'dbaadmin'@'%';

flush privileges;

​

​

4)针对单个列

grant select(deptno,dname) on itpux.dept to dev@'localhost'

flush privileges;

​

2.6)权限回收

mysql> help revoke

Name: 'REVOKE'

Description:

Syntax:

REVOKE

priv_type [(column_list)]

[, priv_type [(column_list)]] ...

ON [object_type] priv_level

FROM user [, user] ...

​

REVOKE ALL [PRIVILEGES], GRANT OPTION

FROM user [, user] ...

​

REVOKE PROXY ON user

FROM user [, user] ...

​

技巧： 把to改成from

2.8)删除用户

drop user itpux3@'%';

drop user itpux2@'%';

drop user itpux1@'localhost';

​

mysql> select host,user from mysql.user;

2.9)修改用户密码

第一种：知道原密码

A 通过修改mysql.user表

update mysql.user set authentication_string=PASSWORD('Lsf@8816') where user='root';

flush privileges;

​

B 通过alter user

mysql> alter user root@'localhost' identified by 'Aa123456';

Query OK, 0 rows affected (0.00 sec)

​

mysql> flush privileges;

Query OK, 0 rows affected (0.00 sec)

​

​

C 通过grant更改密码

mysql> grant usage on *.* to 'dbaadmin'@'%' identified by 'dba123456';

Query OK, 0 rows affected, 1 warning (0.00 sec)

​

mysql> flush privileges;

Query OK, 0 rows affected (0.00 sec)

​

第二种：root密码忘记

停止数据库

在my.cnf添加--skip-grant-tables

启动数据库

登录数据库

update mysql.user set authentication_string=PASSWORD('Lsf@8816') where user='root';

flush privileges;

删除--skip-grant-tables

重启数据库

​

2.10)免密数据库登录

使用login-path

[root@itpuxdb ~]# mysql_config_editor set --login-path=dbaadmin --user=dbaadmin --password=dba123456 --host=localhost

mysql_config_editor: [ERROR] mysql_config_editor: option '--password' cannot take an argument

[root@itpuxdb ~]# mysql_config_editor set --login-path=dbaadmin --user=dbaadmin --password --host=localhost

Enter password:

[root@itpuxdb ~]# mysql_config_editor print --all

[dbaadmin]

user = dbaadmin

password = *****

host = localhost

[root@itpuxdb ~]# mysql --login-path=dbaadmin

Welcome to the MySQL monitor. Commands end with ; or \g.

Your MySQL connection id is 22

Server version: 5.7.30-log MySQL Community Server (GPL)

​

Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved.

​

Oracle is a registered trademark of Oracle Corporation and/or its

affiliates. Other names may be trademarks of their respective

owners.

​

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

​

mysql>

2.11)mysql角色管理

mysql5.7:proxies_priv

角色(role)可以批量管理用户，用一个角色下面的数据都有具有相同的权限

​

SHOW VARIABLES LIKE '%proxy%';

SET GLOBAL check_proxy_users=ON;

SET GLOBAL mysql_native_password_proxy_users=ON;

​

-- 如果永久打开，需要加到my.cnf,重启才能生效

​

-- 创建用户

CREATE USER 'itpux_dba';

CREATE USER 'itpux_a';

CREATE USER 'itpux_b';

CREATE USER 'itpux_c';

​

-- 权限映射

GRANT proxy ON itpux_dba TO itpux_a;

GRANT proxy ON itpux_dba TO itpux_b;

​

-- 给itpux_dba赋予实际权限

GRANT SELECT,INSERT,UPDATE ON itpux.* TO 'itpux_dba';

FLUSH PRIVILEGES;

​

-- 检查权限并设置

SHOW GRANTS FOR itpux_dba;

-- GRANT SELECT, INSERT, UPDATE ON `itpux`.* TO 'itpux_dba'@'%'

SHOW GRANTS FOR itpux_a;

-- GRANT PROXY ON 'itpux_dba'@'%' TO 'itpux_a'@'%'

SHOW GRANTS FOR itpux_b;

-- GRANT PROXY ON 'itpux_dba'@'%' TO 'itpux_b'@'%'

​

mysql> SELECT * FROM mysql.proxies_priv;

+-----------+---------+--------------+--------------+------------+----------------------+---------------------+

| Host | User | Proxied_host | Proxied_user | With_grant | Grantor | Timestamp |

+-----------+---------+--------------+--------------+------------+----------------------+---------------------+

| localhost | root | | | 1 | boot@connecting host | 0000-00-00 00:00:00 |

| % | itpux_a | % | itpux_dba | 0 | root@localhost | 0000-00-00 00:00:00 |

| % | itpux_b | % | itpux_dba | 0 | root@localhost | 0000-00-00 00:00:00 |

+-----------+---------+--------------+--------------+------------+----------------------+---------------------+

3 rows in set (0.00 sec)

​

​

2.11)mysql安全之审计