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安全之审计
