Mysql之用户管理
1. 权限表
1. user表
+------------------------+-----------------------------------+------+-----+-----------------------+-------+
| Field | Type | Null | Key | Default | Extra |
+------------------------+-----------------------------------+------+-----+-----------------------+-------+
| Host | char(60) | NO | PRI | | |
| User | char(32) | NO | PRI | | |
| Select_priv | enum('N','Y') | NO | | N | |
| Insert_priv | enum('N','Y') | NO | | N | |
| Update_priv | enum('N','Y') | NO | | N | |
| Delete_priv | enum('N','Y') | NO | | N | |
| Create_priv | enum('N','Y') | NO | | N | |
| Drop_priv | enum('N','Y') | NO | | N | |
| Reload_priv | enum('N','Y') | NO | | N | |
| Shutdown_priv | enum('N','Y') | NO | | N | |
| Process_priv | enum('N','Y') | NO | | N | |
| File_priv | enum('N','Y') | NO | | N | |
| Grant_priv | enum('N','Y') | NO | | N | |
| References_priv | enum('N','Y') | NO | | N | |
| Index_priv | enum('N','Y') | NO | | N | |
| Alter_priv | enum('N','Y') | NO | | N | |
| Show_db_priv | enum('N','Y') | NO | | N | |
| Super_priv | enum('N','Y') | NO | | N | |
| Create_tmp_table_priv | enum('N','Y') | NO | | N | |
| Lock_tables_priv | enum('N','Y') | NO | | N | |
| Execute_priv | enum('N','Y') | NO | | N | |
| Repl_slave_priv | enum('N','Y') | NO | | N | |
| Repl_client_priv | enum('N','Y') | NO | | N | |
| Create_view_priv | enum('N','Y') | NO | | N | |
| Show_view_priv | enum('N','Y') | NO | | N | |
| Create_routine_priv | enum('N','Y') | NO | | N | |
| Alter_routine_priv | enum('N','Y') | NO | | N | |
| Create_user_priv | enum('N','Y') | NO | | N | |
| Event_priv | enum('N','Y') | NO | | N | |
| Trigger_priv | enum('N','Y') | NO | | N | |
| Create_tablespace_priv | enum('N','Y') | NO | | N | |
| ssl_type | enum('','ANY','X509','SPECIFIED') | NO | | | |
| ssl_cipher | blob | NO | | NULL | |
| x509_issuer | blob | NO | | NULL | |
| x509_subject | blob | NO | | NULL | |
| max_questions | int(11) unsigned | NO | | 0 | |
| max_updates | int(11) unsigned | NO | | 0 | |
| max_connections | int(11) unsigned | NO | | 0 | |
| max_user_connections | int(11) unsigned | NO | | 0 | |
| plugin | char(64) | NO | | mysql_native_password | |
| authentication_string | text | YES | | NULL | |
| password_expired | enum('N','Y') | NO | | N | |
| password_last_changed | timestamp | YES | | NULL | |
| password_lifetime | smallint(5) unsigned | YES | | NULL | |
| account_locked | enum('N','Y') | NO | | N | |
+------------------------+-----------------------------------+------+-----+-----------------------+-------+
user表有45个字段，分为4个分类，分别是用户列，权限列，安全列和资源控制列
1. 用户列
Host,User,authentication_string 分别是主机名，用户名和密码
User,Host是user表的联合主键。当用户与服务器建立连接时，输入的账户信息中的用户名，主机名和密码必须与User表中的对应的字段，都匹配，才能建立连接。
修改用户密码，改的就是authentication_string字段的值
password_expired
password_last_changed
password_lifetime 该用户密码的生存时间，默认值为 NULL
2. 权限列
权限列的字段决定了用户的权限
这些字段的类型为ENUM，可以取的值为Y和N,Y表示该用户有对应的权限；N表示用户没有对应的权限
3. 安全列
两个关于ssl的，两个是x509相关的，还有一个关于插件的
4. 资源控制列
max_questions 用户每小时允许执行的查询操作次数
max_updates 用户每小时允许执行的更新操作次数
max_connections 用户每小时允许执行的连接操作次数
max_user_connections 用户允许同时建立的连接次数
2. db表
+-----------------------+---------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------------------+---------------+------+-----+---------+-------+
| Host | char(60) | NO | PRI | | |
| Db | char(64) | NO | PRI | | |
| User | char(32) | NO | PRI | | |
| Select_priv | enum('N','Y') | NO | | N | |
| Insert_priv | enum('N','Y') | NO | | N | |
| Update_priv | enum('N','Y') | NO | | N | |
| Delete_priv | enum('N','Y') | NO | | N | |
| Create_priv | enum('N','Y') | NO | | N | |
| Drop_priv | enum('N','Y') | NO | | N | |
| Grant_priv | enum('N','Y') | NO | | N | |
| References_priv | enum('N','Y') | NO | | N | |
| Index_priv | enum('N','Y') | NO | | N | |
| Alter_priv | enum('N','Y') | NO | | N | |
| Create_tmp_table_priv | enum('N','Y') | NO | | N | |
| Lock_tables_priv | enum('N','Y') | NO | | N | |
| Create_view_priv | enum('N','Y') | NO | | N | |
| Show_view_priv | enum('N','Y') | NO | | N | |
| Create_routine_priv | enum('N','Y') | NO | | N | |
| Alter_routine_priv | enum('N','Y') | NO | | N | |
| Execute_priv | enum('N','Y') | NO | | N | |
| Event_priv | enum('N','Y') | NO | | N | |
| Trigger_priv | enum('N','Y') | NO | | N | |
+-----------------------+---------------+------+-----+---------+-------+
1. 用户列
host,db,user
2. 权限列
3. tables_priv表
4. columns_priv表
5. procs_priv表
2. 账户管理
1. mysql命令使用
-h 主机名
-u 用户名
-p密码 密码字符串可以用单引号，但是与-p之间不能有空格
数据库名
-e 执行sql语句
2. 新建普通用户
1. 使用CREATE USER语句创建新用户
create user gaozhongming@"localhost" identified by '123'; 会在mysql.user中添加一条记录，但是新创建的用户没有任何权限
create user yichangkun@'localhost' identified by password '*23AE809DDACAF96AF0FD78ED04B6A265E05AA257'; 通过哈希值创建用户
2. 使用GRANT语句创建新用户
GRANT语句不仅可以创建用户，还可以在创建的同时对用户授权
GRANT privileges ON db.table TO user@host IDENTIFIED BY 'password' [WITH GRANT OPTION]; WITH GRANT OPTION表示对新建立的用户赋予GRANT权限
grant select,delete,update,insert on *.* to username@ 'localhost' identified by '123456';
flush privileges;
例子：
grant select,insert,delete,update on test.* to yangjianbo@'192.168.1.%' identified by '123';
因为是针对的具体的某个数据库test进行的授权，所以在mysql.user中显示的权限为N.
但是在mysql.db中显示的权限为Y.
3. 直接操作mysql用户表（基本不用该方法）
3. 删除普通用户
1. 使用DROP USER语句删除用户
drop user 'yichangkun'@'localhost'; 删除某个一个用户
drop user; 删除授权表中所有用户
2. 使用DELETE语句删除用户
delete from user where user='gaozhongming';
4. root用户修改自己的密码
1. 使用mysqladmin命令在命令行指定新密码
mysqladmin -uroot -p123.com password '123.com'; password为关键字
2. 修改mysql库的user表
5.5
update user set password=password(456) where user='root' and host='localhost'; password()函数用来加密用户密码
flush privileges;
5.7
update mysql.user set authentication_string=password('*******') where user='root' and host='localhost';
flush privileges;
3. 使用password函数
set password=password('654321');
5. root用户修改普通用户的密码
1. 使用SET语句修改普通用户的密码
set password for 'user'@'host' =password('newpassword')
2. 使用UPDATE修改普通用户的密码
update mysql.user set authentication_string=password('*******') where user='yangjianbo' and host='localhost';
3. 使用GRANT语句修改普通用户密码
GRANT USAGE on *.* TO 'username'@'%' IDENTIFIED BY 'newpassword';
6. 普通用户修改密码
自己登陆mysql服务器，执行sql语句 set password=password('123');
7. root用户密码丢失的解决方法
先停止数据库
在配置文件中，[mysqld]下面，添加一条内容：skip-grant-tables
然后启动mysql。
以空密码进去，设置root密码。
或者启动mysql添加选项--skip-grant-tables
修改密码完成后，必须使用FLUSH PRIVILEGES语句加载权限表
3. 权限管理
1. grant语句详解
1. 语法
grant priv_type [(column_list)] on [object_type] priv_level to user [identified by 'password']
例子：
grant all on *.* to admin1@'192.168.1.%' identified by '123'; admin1会出现在mysql.user表中，不会出现在mysql.db中。
grant all on test.* to admin2@'192.168.1.%' identified by '123'; admin2会出现在mysql.user表中，但是权限显示为N;admin2的权限会在mysql.db中体现。
grant all on test.student to admin3@'192.168.1.%' identified by '123'; admin3会出现在mysql.user表中，但是权限显示为N; admin3不会出现在mysql.db中，但是会出现在mysql.tables_priv表中。
grant select(id),insert(name) on test.student to admin4@'192.168.1.%' identified by '123'; admin4会出现在mysql.user表中，但是权限显示为N;admin4不会出现在mysql.db中，但是会出现在mysql.tables_priv表中，还会出现在mysql.columns_priv表中。注意：如果需要多个权限针对某个字段，多次执行sql语句。
2. revoke语句详解
1. 语法
revoke priv_type[(column_list)] on [object_type] priv_level from user
例子：
revoke all on *.* from admin1@'192.168.1.%';
revoke all on test.* from admin2@'192.168.1.%';
revoke all on test.student to admin3@'192.168.1.%'
revoke select(id),insert(name) on test.student from admin4@'192.168.1.%'
revoke all,grant option on test.* to admin2@'192.168.1.%' 解除所有权限和授权权限
5.6之前，先revoke，再删除用户
5.7之后，直接删除用户，权限也会被撤销
3. 查看权限
SHOW GRANTS FOR 'user'@'host';
4. 访问控制
浙公网安备 33010602011771号