MySQL
MySQL用户和权限管理
元数据数据库：mysql
系统授权表：
db, host, user 存放授权的相关指令
columns_priv(列的授权), tables_priv(表的授权), procs_priv(存储触发器的授权), proxies_priv
select user,host,password,authenticationn_string from user;
authenticationn_string 验证的字符串
不同的版本可能将密码记录在此处
用户账号：
'USERNAME'@'HOST'
@'HOST':
主机名
IP地址或Network
通配符： % _
示例：172.16.%.%
用户管理
创建用户：CREATE USER
CREATE USER 'USERNAME'@'HOST' [IDENTIFIED BY 'password']；
默认权限：USAGE
用户重命名：RENAME USER
RENAME USER old_user_name TO new_user_name;
删除用户：
DROP USER 'USERNAME'@'HOST‘
示例：删除默认的空用户
DROP USER ''@'localhost';
修改密码：
mysql>SET PASSWORD FOR 'user'@'host' = PASSWORD(‘password');
mysql>UPDATE mysql.user SET password=PASSWORD('password') WHERE clause;
此方法需要执行下面指令才能生效：
mysql> FLUSH PRIVILEGES;
#mysqladmin -u root -poldpass password ‘newpass’
忘记管理员密码的解决办法：
启动mysqld进程时，为其使用如下选项：
--skip-grant-tables --skip-networking
使用UPDATE命令修改管理员密码
关闭mysqld进程，移除上述两个选项，重启mysqld
实验: 用户管理
1> 查看创建用户语法
> help create user
2> create user test@'192.168.38.%' identified by '123456';
创建用户test允许192.168.37网段访问,密码为123456
3> 删除用户
> drop user 'test'@'192.168.38.%';
4> 修改密码
password()函数,用于加密口令
> select password("123456");
+-------------------------------------------+
| password("123456") |
+-------------------------------------------+
| *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
+-------------------------------------------+
> set password for root@'localhost'=password('centos');
5> 使用update修改密码
> update user set password=passwprd('centos') where host='centos7.localdomain';
> flush privilees; 生效
6> 修改多个用户密码
> uodate mysql.user set password=password('centos') where user = 'root';
7> 忘记root密码解决办法
在配置文件中添加参数
vim /etc/my.cnf
--skip-grant-tables --skip-networking
systemctl restart mysqld #重启服务
添加参数后无法使用set password 修改密码
可以使用update修改密码
update mysql.user set password=password('mageedu') where host='localhost';
在配置文件中添加 --skip-grant-tables参数后,远程也可以使用直接登录修改密码,非常危险
--skip-networking 参数可以关闭3306端口,禁止远程用户登录,避免此类风险
MySQL权限管理
权限类别：
管理类
程序类
数据库级别
表级别
字段级别
MySQL用户和权限管理
管理类：
CREATE TEMPORARY TABLES
CREATE USER
FILE
SUPER
SHOW DATABASES
RELOAD
SHUTDOWN
REPLICATION SLAVE
REPLICATION CLIENT
LOCK TABLES
PROCESS
程序类： FUNCTION、PROCEDURE、TRIGGER
CREATE
ALTER
DROP
EXCUTE
库和表级别：DATABASE、TABLE
ALTER
CREATE
CREATE VIEW
DROP
INDEX
SHOW VIEW
GRANT OPTION：能将自己获得的权限转赠给其他用户
数据操作
SELECT
INSERT
DELETE
UPDATE
字段级别
SELECT(col1,col2,...)
UPDATE(col1,col2,...)
INSERT(col1,col2,...)
所有权限
ALL PRIVILEGES 或 ALL
授权
参考：https://dev.mysql.com/doc/refman/5.7/en/grant.html
GRANT priv_type [(column_list)],... ON [object_type] priv_level TO 'user'@'host' [IDENTIFIED BY 'password'] [WITH GRANT OPTION];
1> priv_type: ALL [PRIVILEGES]
2> object_type: TABLE | FUNCTION | PROCEDURE
3> priv_level: * 所有库
*.* 所有库的所有表
db_name.* 库里的所有资源
db_name.tbl_name 库里的某个表
tbl_name 当前库的表
db_name.routine_name 指定库的函数,存储过程,触发器
with_option: GRANT OPTION 限定访问频率
| MAX_QUERIES_PER_HOUR count 每小时最多查询次数
| MAX_UPDATES_PER_HOUR count 最多更新次数
| MAX_CONNECTIONS_PER_HOUR count 最多连接次数
| MAX_USER_CONNECTIONS count 最大并发用户连接数
示例：GRANT SELECT (col1), INSERT (col1,col2) ON mydb.mytbl TO 'someuser'@'somehost‘;
回收授权
REVOKE priv_type [(column_list)] [, priv_type [(column_list)]] ... ON [object_type] priv_level FROM user [, user] ...
示例：REVOKE DELETE ON testdb.* FROM 'testuser'@‘172.16.0.%’;
查看指定用户获得的授权
Help SHOW GRANTS
SHOW GRANTS FOR 'user'@'host';
SHOW GRANTS FOR CURRENT_USER[()];
注意：MariaDB服务进程启动时会读取mysql库中所有授权表至内存
(1) GRANT或REVOKE等执行权限操作会保存于系统表中，MariaDB的服务进程通常会自动重读授权表，使之生效
(2) 对于不能够或不能及时重读授权表的命令，可手动让MariaDB的服务进程重读授权表：mysql> FLUSH PRIVILEGES;
实验: 授权
grant 的额外作用: 可以创建用户
1> 授权用户查看teachers表中的stuid和name 字段
> grant select (tid,name) on hellodb.teacher to test@'192.168.38.%';
授权给test@'192.168.38.%'用户查询hellodb库teacher表的tid,name字段
test@'192.168.38.%' 用户只能访问(tid,name)两个字段
> select tid,name from teachers;
2> 查看用户被授权的权限
> show grants for test@'192.168.38.%';
3> 给用户所有权限,相当于管理员
> grant all on *.* to test2@'192.168.38.%';
4> 取消授权,刷新权限
> revoke select on *.* from test2@'192.168.38.%';
> flush privileges;
5> 授权和创建用户同时进行
> grant all on *.* to test3@'192.168.38.%' identified by '123456';
实验: 数据库多实例
环境: 创建对应文件夹 mkdir /mysql/{3306,3307,3308}/{data,etc,socket,log,bin,pid} -pv
yum 安装 mariadb 数据库 yum install mariadb-server
1> 更改mysql的所属者,所属组
chown -R mysql.mysql /mysql/
2> 生成数据库
mysql_install_db --datadir=/mysql/3306/data --user=mysql
mysql_install_db --datadir=/mysql/3307/data --user=mysql
mysql_install_db --datadir=/mysql/3308/data --user=mysql
3> 拷贝配置到对应目录,并做相应修改
cp /etc/my.cnf /mysql/3306/etc
修改配置文件
vim /mysql/3306/etc/my.cnf
添加端口
port=3306
数据库路径
datadir=/mysql/3306/data
socket文件路径
socket=/mysql/3306/socket/mysql.sock
日志文件路径
log-error=/mysql/3306/log/mariadb.log
pid文件路径
pid-file=/mysql/3306/pid/mariadb.pid
删除!includedir /etc/my.cnf.d
4> 拷贝配置文件到另外两个目录中,并做相应修改
sed -i 's/3306/3307' /mysql/3307/etc/my.cnf
sed -i 's/3306/3308' /mysql/3308/etc/my.cnf
5> 导入启动脚本mysqld到/mysql/3306/bin,并加以修改
vim /mysql/3306/bin/mysqld
prot=3306
mysql_user="root"
mysql_pwd=""
cmd_path="/usr/bin"
mysql_basedir="/mysql"
拷贝到其他对应目录修改端口号
6> 添加执行权限
chmod +x /mysql/3306/bin/mysqld
chmod +x /mysql/3307/bin/mysqld
chmod +x /mysql/3308/bin/mysqld
7> 启动服务
/mysql/3306/bin/mysqld start
/mysql/3307/bin/mysqld start
/mysql/3308/bin/mysqld start
8> 查看端口状态
ss -ntl
9> 连接数据库,指定mysql.sock
mysql -S /mysql/3306/socket/mysql.sock
10> 更改数据库口令
mysqladmin -S /mysql/3306/socket/mysql.sock password 123456
11> 设为开机启动
vim /etc/rc.local
/mysql/3306/bin/mysqld start
/mysql/3307/bin/mysqld start
/mysql/3308/bin/mysqld start
添加执行权限
chmod +x /etc/rc.d/rc.local
