2023.6.15 08.数据库安全管理

08.数据库安全管理 ⽤户账户管理 访问权限系统 访问权限回收 在讨论安全时，我们需要考虑整个服务器主机安全（⽽不仅仅是MySQL服务）需要抵御攻击, 窃听, 扫描, 破解等。 MySQL 对所有连接数据库⽤户进⾏了 ACL 访问控制,减少服务器被内部不规范操作导致故障。 MySQL 还⽀持客户端和服务器之间的 SSL 加密连接。 当然这⾥讨论的许多概念都不是特定于 MySQL ⼏乎所有的应⽤程序都适⽤相同的⼀般思路。 MySQL 运⾏时，请遵循以下准则: 不要给⽤户(应⽤)配置超级⽤户权限,授权最⼩化 不要在数据库中存储明⽂密码 不要使⽤较为简单的字符密码 不允许⾮授信任主机使⽤扫描 数据库跟谁打交道 业务层java 公司堡垒机开放   ⽤户账户管理 1.登录和退出 MySQL , 使⽤ mysql -u root -p 可以连接数据库, 但这只是本地连接数据库的⽅式, 在⽣产很多情况 下都是连接⽹络中某⼀个主机上的数据库 -P //指定连接远程数据库端⼝[默认3306] -h //指定连接远程数据库地址[默认localhost] -u //指定连接远程数据库账户[默认root] -p //指定连接远程数据库密码[默认密码为空] -e //执⾏mysql数据库sql指令 -S //指定mysql数据库Socket //不安全 [root@sql ~]# mysql -uroot -p"Wing@123" //推荐⽅式 [root@sql ~]# mysql -uroot -p Enter password: //推送远程登录⽅式 [root@sql ~]# mysql -h10.1.106.70 -P3306 -uroot -p Enter password: //⾮交互式操作数据库 [root@sql ~]# mysql -uroot -pWing@123 -e "show databases;" mysql: [Warning] Using a password on the command line interface can be insecure. +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | shop | | sys | | testdb | | wing | +--------------------+ 2.创建⽤户 MySQL中创建⽤户的语法如下： CREATE USER 'username'@'127.0.0.1' IDENTIFIED BY 'password'; username 是要创建的⽤户名， hostname 是该⽤户所在的主机名或IP地址， password 是该⽤户的密码。 如果要创建⼀个可以从任何主机连接到MySQL服务器的⽤户，可以将 hostname 设置为 % ，如下所示： CREATE USER 'username'@'%' IDENTIFIED BY 'password' 创建⽤户后，需要为该⽤户授予适当的权限，以便该⽤户可以执⾏所需的操作。可以使⽤ GRANT 语句来授予权限， 例如： GRANT SELECT, INSERT, UPDATE ON database.* TO 'username'@'hostname'; 授予⽤户在 database 数据库中执⾏ SELECT 、 INSERT 和 UPDATE 操作的权限。 示列: //先创建⽤户, 后授权(CREATE USER 语句创建) mysql> create user wing@'localhost' identified by 'Wing@123'; //基于已有⽤户进⾏授权 mysql> grant all on *.* to wing@'localhost'; //使⽤GRANT 语句创建⽤户并授权 mysql> grant all on *.* to wing@'localhost' identified by 'Wing@123';   3.删除⽤户 //1.drop user 语句删除 mysql> drop user 'wing'@'localhost'; #注意mysql5.6:先回收权限, 然后删除 mysql> revoke all privilege user_name; mysql> drop user user_name; //2.delete语句删除 mysql> delete from mysql.user where user='wing' and host='localhost'; mysql> select user,host from user; +---------------+-----------+ | user | host | +---------------+-----------+ | root | % | | mysql.session | localhost | | mysql.sys | localhost | | root | localhost | +---------------+-----------+ 4 rows in set (0.00 sec)   4.修改 root ⽤户密码 //⽅法1, Shell修改⽅式 # mysqladmin -uroot -p'Wing@123' password 'NewWing@123' //⽅法2, 修改数据表 # mysql -uroot -p'NewWing@123' mysql> update mysql.user set authentication_string=password('Wing@123') where user='root' and host='localhost'; //刷新权限 mysql> flush privileges; //⽅法3, 设定密码 mysql> set password=password('Wing@123');   5.修改其他⽤户密码 //⽅法1 mysql> create user wing1@'localhost' identified by 'Wing1@123'; mysql> set password for wing1@'localhost'=password('NewWing1@123'); //⽅法2, 修改数据表 mysql> update mysql.user set authentication_string=password('Wing1@123') where user='wing1' and host='localhost'; //刷新权限 mysql> flush privileges; //普通⽤户⾃⼰修改⾃⼰密码 set password=password("Wing1@123"); 访问权限系统 mysql权限表 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: *4147336C7F7536D206BD558035D39F064E07A2E3 password_expired: N password_last_changed: 2023-05-25 17:15:33 password_lifetime: NULL account_locked: N mysql.db (数据库级) ⽤户字段 权限字段 mysql> select * from mysql.db\G *************************** 1. row *************************** Host: localhost Db: performance_schema User: mysql.session Select_priv: Y Insert_priv: N Update_priv: N Delete_priv: N Create_priv: N Drop_priv: N Grant_priv: N References_priv: N Index_priv: N Alter_priv: N Create_tmp_table_priv: N Lock_tables_priv: N Create_view_priv: N Show_view_priv: N Create_routine_priv: N Alter_routine_priv: N Execute_priv: N Event_priv: N Trigger_priv: N mysql.tables_priv(表级) mysql.columns_priv(列级) 权限应⽤的顺序 user->db->tables->columns 语法格式 grant 权限列表 on 库名.表名 to '⽤户名'@'客户端主机' [identified by ' 密码' with option 参数]; 相关参数: 1.权限列表 all 所有权限（不包括授权权限） 单独授权 select,update,insert,delete 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 3.客户端主机 localhost 指定本机 10.1.106.70 指定具体主机 10.1.106.0 ⽹段的所有主机 10.1.106.% ⽹段的所有主机 % 指定所有主机   参数 GRANT OPTION 授权选项 MAX_QUERIES_PER_HOUR 定义每⼩时允许执⾏的查询数 MAX_UPDATES_PER_HOUR 定义每⼩时允许执⾏的更新数 MAX_CONNECTIONS_PER_HOUR 定义每⼩时可以建⽴的连接数 MAX_USER_CONNECTIONS 定义单个⽤户同时可以建⽴的连接数   grant 授权示例 //没有授权的权限 select user,host,authentication_string from mysql.user; delete from mysql.user where user='wing' and host='%'; GRANT ALL ON *.* TO wing@'%' IDENTIFIED BY 'Wing@123'; mysql> GRANT ALL ON *.* TO wing@'%' IDENTIFIED BY 'Wing@123'; ERROR 1045 (28000): Access denied for user 'wing'@'%' (using password: YES) //有授权权限 delete from mysql.user where user='wing1' and host='%'; delete from mysql.user where user='wing1' and host='localhost'; GRANT ALL ON *.* TO wing1@'%' IDENTIFIED BY 'Wing@123' WITH GRANT OPTION; //授权bbs库给wing2⽤户 GRANT ALL ON bbs.* TO wing2@'%' IDENTIFIED BY 'Wing@123'; create database bbs; //授权bbs库给wing3⽤户@10.1.106.70 GRANT ALL ON bbs.* TO wing3@'10.1.106.70' IDENTIFIED BY 'Wing@123'; mysql -uwing3 -p -h10.1.106.70 // 授权bbs.user表只能 wing4⽤户访问 GRANT ALL ON bbs.user TO wing4@'%' IDENTIFIED BY 'Wing@123'; mysql> create table t1(id int); Query OK, 0 rows affected (0.01 sec) mysql> create table user(id int AUTO_INCREMENT PRIMARY key,name varchar(10),age int); Query OK, 0 rows affected (0.01 sec) mysql> show tables; +---------------+ | Tables_in_bbs | +---------------+ | user | +---------------+ 1 row in set (0.00 sec) //授权wing5对 bbs库user表中id 只能查询, name和age字段可以插⼊ GRANT SELECT(id),INSERT(name,age) ON bbs.user TO wing5@'%' IDENTIFIED BY 'Wing@123'; insert into user(name,age) values('wing',18); mysql> select id from user; +----+ | id | +----+ | 1 | +----+ 1 row in set (0.00 sec) mysql> select id,name,age from user; ERROR 1143 (42000): SELECT command denied to user 'wing5'@'mysql.server' for column 'name' in table 'user' mysql> insert into user(id,name,age) values(2,'wing',18); ERROR 1143 (42000): INSERT command denied to user 'wing5'@'mysql.server' for column 'id' in table 'user' mysql> insert into user(name,age) values('wing',18); Query OK, 1 row affected (0.00 sec) mysql> select id from user; +----+ | id | +----+ | 1 | | 2 | +----+ 2 rows in set (0.00 sec) 访问权限回收 1.查看⽤户权限 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 | +---------------------------------------------------------------------+ 2 rows in set (0.00 sec) mysql> SHOW GRANTS\G *************************** 1. row *************************** Grants for root@localhost: GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' WITH GRANT OPTION *************************** 2. row *************************** Grants for root@localhost: GRANT PROXY ON ''@'' TO 'root'@'localhost' WITH GRANT OPTION 2 rows in set (0.00 sec) #查看所有⽤户 mysql> select user,host,authentication_string from mysql.user; +---------------+-------------+-------------------------------------------+ | user | host | authentication_string | +---------------+-------------+-------------------------------------------+ | root | localhost | *4147336C7F7536D206BD558035D39F064E07A2E3 | | mysql.session | localhost | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE | | mysql.sys | localhost | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE | | root | % | *D9ED32292D8F86849C725F9D9FD2B7B2FC29F2BA | | wing3 | 10.1.106.70 | *D9ED32292D8F86849C725F9D9FD2B7B2FC29F2BA | | wing2 | % | *D9ED32292D8F86849C725F9D9FD2B7B2FC29F2BA | | wing1 | % | *D9ED32292D8F86849C725F9D9FD2B7B2FC29F2BA | | wing4 | % | *D9ED32292D8F86849C725F9D9FD2B7B2FC29F2BA | | wing5 | % | *D9ED32292D8F86849C725F9D9FD2B7B2FC29F2BA | +---------------+-------------+-------------------------------------------+ //查看其它⽤户权限 mysql> show grants for wing1@'%'; +--------------------------------------------------------------+ | Grants for wing1@% | +--------------------------------------------------------------+ | GRANT ALL PRIVILEGES ON *.* TO 'wing1'@'%' WITH GRANT OPTION | +--------------------------------------------------------------+ 1 row in set (0.00 sec)   2.回收权限 语法: REVOKE 权限列表 ON 数据库名 FROM ⽤户名@'客户端主机'; //回收Delete权限 mysql> revoke DELETE on *.* from wing1@'%'; Query OK, 0 rows affected (0.01 sec) mysql> show grants for wing1@'%'\G *************************** 1. row *************************** Grants for wing1@%: GRANT SELECT, INSERT, UPDATE, CREATE, DROP, RELOAD, SHUTDOWN, PROCESS, FILE, REFERENCES, INDEX, ALTER, SHOW DATABASES, SUPER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, REPLICATION SLAVE, REPLICATION CLIENT, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, CREATE USER, EVENT, TRIGGER, CREATE TABLESPACE ON *.* TO 'wing1'@'%' WITH GRANT OPTION 1 row in set (0.00 sec) //回收所有权限 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 1 row in set (0.00 sec) //回收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'@'%' 1 row in set (0.00 sec) 开发⼈员 就是select 权限 或者对某⼀个库有权限 ⼀定不能root权限 业务测java程序 连接都有单独⾃⼰权限 app ⽤户只能访问app库 权限最⼩化