MySQL中用户分为root用户和普通用户。root用户为超级管理员,具有所有权限(创建用户、删除用户、管理用户)。
二、用户管理
2.1 User表
-
新建MySQL时,会自动安装一个mysql的数据库,该数据库下面的表都是权限表。
-
其中:
user
表时最重要权限表。记录了允许连接到服务器的账号信息以及一些全局权限信息。 -
user
表有42个字段,大致分为4类。
1. 用户列
- 包括
Host
、User
、Password
,分别代表主机名、用户名和密码。 Host
、User
:为联合主键。Host
、User
、Password
:三个字段都匹配时,才会允许建立连接。- 修改密码:只需要修改User表中
Password
即可。
2. 权限列
- 包括
Select_priv
、Insert_priv
、Update_priv
等以priv
结尾的字段,这些字段决定了用户的权限。 Select_priv
:查询权限。Insert_priv
:插入权限。Update_priv
:更新权限。- 数据类型都是枚举类型
Enum
。 - 默认值为:N(没有权限)。
3. 安全列
- 用户管理用户的安全信息,其中包含6个字段。
ssl_type
和ssl_cipher
:用户加密。x509_issuer
和x509_subject
标准:用来标识用户。plugin
和authentication_string
:用于存储于授权相关的插件。
4. 资源控制列
- 用于限制用户使用的资源。其中包括4个字段。
max_questions
:每小时允许用户执行查询操作的次数。max_updates
:每小时允许用户执行更新操作的次数。max_connection
:每小时允许用户建立连接的次数。max_user_connection
:每小时允许用户同时建立连接的次数。
所属分类 | 字段名 | 含义 |
---|---|---|
1.用户列 | Host | 主机名 |
1.用户列 | User | 用户名 |
1.用户列 | Password | 密码 |
2. 权限列 | Select_priv | 确定用户是否可以通过SELECT命令选择数据 |
2. 权限列 | Insert_priv | 确定用户是否可以通过INSERT命令插入数据 |
2. 权限列 | Update_priv | 确定用户是否可以通过UPDATE命令修改现有数据 |
2. 权限列 | Delete_priv | 确定用户是否可以通过DELETE命令删除现有数据 |
2. 权限列 | Create_priv | 确定用户是否可以创建新的数据库和表 |
2. 权限列 | Drop_priv | 确定用户是否可以删除现有数据库和表 |
2. 权限列 | Reload_priv | 确定用户是否可以执行刷新和重新加载MySQL所用各种内部缓存的特定命令。 包括日志、权限、主机、查询和表 |
2. 权限列 | Shutdown_priv | 确定用户是否可以关闭MySQL服务器。 在将此权限提供给root账户之外的任何用户时,都应当非常谨慎 |
3. 安全列 | ssl_type | 支持ssl标准加密安全字段 |
3. 安全列 | ssl_cipher | 支持ssl标准加密安全字段 |
3. 安全列 | x509_issuer | 支持x509标准字段 |
3. 安全列 | x509_subject | 支持x509标准字段 |
3. 安全列 | plugin | 5.5.7开始,mysql引入plugins以进行用户连接时的密码验证,plugin创建外部/代理用户 |
3. 安全列 | authentication_string | 通过authentication_string可以控制两者的映射关系。 (PAM plugin等,PAM可以支持多个服务名)尤其是在使用代理用户时,并须声明这一点 |
4. 资源控制列 | max_questions | 每小时允许用户执行查询操作的次数 |
4. 资源控制列 | max_updates | 每小时允许用户执行更新操作的次数 |
4. 资源控制列 | max_connection | 每小时允许用户建立连接的次数 |
4. 资源控制列 | max_user_connection | 每小时允许用户同时建立连接的次数 |
2.2 创建普通用户
有中方式创建:
- 使用
GRANT
语句创建用户 - 使用
CREATE USER
语句创建用户 - 使用
Insert
语句创建用户
1. 使用GRANT
语句创建用户
-
GRANT
语句不仅可以创建新用户,还可以对用户进行授权。 -
该语句会自动加载权限表,不需要手动刷新。
-
而且安全、准确、错误少。
-
使用
GRANT
语句创建用户是最常用的方法。 -
创建用户时,MySQL会对用户的密码自动加密,以提高安全性。
语法:
GRANT privileges ON database.table
TO `username`@`hostname` [IDENTIFIED BY[PASSWORD]`password`]
privileges
:表示该用户具有的权限信息。database.table
:表示新用户的权限范围表,可以在指定的库、表上使用权限。username
:新用户名称。hostname
:主机名。Password
:新用户的密码。
创建新用户:用户名user01
,密码1581145
,授予对test.student
有查询权限。
GRANT SELECT ON test.student TO `user01`@`1581145` IDENTIFIED BY `123`
2. 使用CREATE USER
语句创建用户
- 使用
CREATE USER
语句创建新用户,服务器会自动修改授权表。不需要手动刷新。 - 但,新建的用户是没有权限的。
CREATE USER `username`@`hostname` [IDENTIFIED BY [PASSWORD]`password`]
username
:表示新创建的用户名。hostname
:表示主机名。password
:表示用户的密码。[PASSWORD]
:该参数是可选的,假如密码为普通字符串就不需要使用。
语句:
CREATE USER `user02`@`127.0.0.1` IDENTIFIED BU `123`
3. 使用Insert
语句创建用户
- 在
User
表添加数据。 - 通常只需要添加
Host
、User
、Password
这三个字段即可,其它字段取默认值。 ssl_cipher
、x509_issuer
、x509_subject
字段没有默认值,也需要添加。- 需要手动刷新权限表或者重启MySQL服务。
新建用户
INSERT INTO mysql.`user` ( `host`, `user`, `Password`, ssl_cipher, x509_issuer, x509_subject )
VALUES( 'localhost', 'user03', PASSWORD ( '123' ), '', '', '' );
刷新权限:
FLUSH PRIVILEGES;
2.3 删除普通用户
1. 使用DROP User
语句删除用户
语法
DROP USER `username`@`hostname` [,`username`@`hostname`];
DROP USER `user01`@`localhost`;
2. 使用DELETE
语句删除用户
DELETE
不仅可以删除普通表数据,还可以删除user
表数据。
语法
DELETE FROM mysql.user WHERE Host='hostname' AND User='username';
sql
DELETE FROM mysql.user WHERE Host = 'localhost' AND User = 'user02';
2.4 修改用户密码
1. 使用mysqladmin
修改密码
dd
mysqladmin -u username [-h hostname] -p password new_password
username
:要修改的用户名。-h hostname
:可以不写,默认localhost。-p password
:为关键字。new_password
:新设置的密码。
修改密码:
mysqladmin -u root -p password 1581145
上述语句执行完成后,会提示输入密码。这个密码是旧密码。
2. 使用Update
语句修改密码
语法:
UPDATE mysql.user set Password=PASSWORD('new_password')
WHERE User = 'username' AND Host='hostname';
sql
UPDATE mysql.user set Password=PASSWORD('1581145')
WHERE User = 'root' AND Host='localhost';
刷新权限:
FLUSH PRIVILEGES;
3. 使用SET
语句修改密码
- 用户登录MySQL服务器以后,可以使用
set
语句,修改密码。 - 需使用
PASSWORD('new_password')
进行密码加密。
语法:
SET PASSWORD FOR `username`@'hostname' = PASSWORD('new_password');
SET PASSWORD = PASSWORD('new_password');
SET PASSWORD = PASSWORD('1581145');
4. root使用GRANT
修改普通用户密码
语法:
GRANT USAGE ON *.* TO 'usernmae'@'localhost' IDENTIFIED BY [PASSWORD]‘new_password';
2.5 解决root用户密码丢失
--skip-grant-tables
:可以停止MySQL的权限判断。也就是任何用户都可以登录MySQL。
1、停止MySQL服务
net stop mysql;
2、使用--skip-grant-tables
启动MySQL服务
mysqld --skip-grant-tables
3、登录MySQL服务器
mysql -u -root
4、使用Update
语句设置root用户密码
UPDATE mysql.user SET PASSWORD = PASSWORD ( '158145' )
WHERE User = 'root' AND Host = 'localhot';
5、加载权限表
FLUSH PRIVILEGES;
三、权限管理
3.1 MySQL的权限
MySQL的权限信息,被存储在user、db、host、tables、priv、cloumn_priv和procs_priv中。当MySQL启动时,会加载这些表,并将权限信息读取到内存中。
Create
和Drop
权限:可以创建数据库、表、索引,或者删除已有的数据库、表、索引。Insert
、Delete
、Update
和Select
权限:对数据库表进行增删改查。Index
权限:可以创建和删除索引。适用于所有表。Alter
权限:用于修改表的结构或者重命名表。Grant
权限:允许用户为其它用户授权。可用户数据库和表。File
权限:能读写MySQL服务器上的所有文件。
3.2 授予权限
语法:
GRANT privileges [(columns)][,privileges [(columns)]...] ON database.table
TO `username`@`hostname` [IDENTIFIED BY[PASSWORD]`password`]
[,`username`@`hostname` [IDENTIFIED BY[PASSWORD]`password`]]...
[WITH with_option [with_option]...]
privileges
:表示权限类型。columns
:表示权限作用域某一列。IDENTIFIED BY
:为用户设置密码。
其中WITH
关键字有5个参数取值:
GRANT OPTION
:将自己权限授予其它用户。MAX_QUERIED_PER_HOUR count
:设置每小时最多可执行多少次count
查询。MAX_UPDATES_PER_HOUR count
:设置每小时最多可执行多少次更新。MAX_CONNECTIONS_PER_HOUR count
:设置每小时最大连接数。MAX_USER_CONNECTIONS
:设置每个用户最多可以同时建立的连接数量。s
授予权限:
GRANT INSERT,SELECT ON *.* TO 'user04'@'localhost' IDENTIFIED BY '123'
WITH GRANT OPTION;
3.3 查看权限
SHOW GRANTS
:用来查看用户的权限
SHOW GRANTS FOR 'username'@'hostname';
SHOW GRANTS FOR 'root'@'1581145';
结果:
root用户
GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost'
IDENTIFIED BY PASSWORD '*B105526BD04216F24C8DC48AF956866AA539F35C' WITH GRANT OPTION
普通用户( SELECT,INSERT
)
GRANT SELECT,INSERT ON *.* TO 'user03'@'localhost' IDENTIFIED BY PASSWORD '*23AE809DDACAF96AF0FD78ED04B6A265E05AA257'
3.4 收回权限
REVOKE
:用于收回用户的权限。
REVOKE privileges [columns] [,privileges [columns]] ON database.table
FROM 'username'@'hostname'[,username'@'hostname']...
privileges
:表示参数列表。columns
:表示作用在哪一列。
收回Insert
权限
REVOKE INSERT ON *.* FROM 'user04'@'localhost';
收回全部权限
REVOKE ALL PRIVILEGES,GRANT OPTION ON *.* FROM 'user04'@'localhost';