参考文献

用户管理

  • 用户信息表:mysql.user

新增用户

1
2
3
4
5
6
CREATE USER [IF NOT EXISTS]'username'@'host' IDENTIFIED BY 'password';
    - 必须拥有mysql数据库的全局CREATE USER权限,或拥有INSERT权限.
    - 只能创建用户,不能赋予权限.
    - 用户名,注意引号:如 'user_name'@'192.168.1.1'
    - 密码也需引号,纯数字密码也要加引号
    - 要在纯文本中指定密码,需忽略PASSWORD关键词.要把密码指定为由PASSWORD()函数返回的混编值,需包含关键字PASSWORD
  • username:你将创建的用户名
  • host:指定该用户在哪个主机上可以登陆,如果是本地用户可用localhost,如果想让该用户可以从任意远程主机登陆,可以使用通配符%
  • password:该用户的登陆密码,密码可以为空,如果为空则该用户可以不需要密码登陆服务器

重命名用户

1
2
-- 重命名用户
RENAME USER old_user TO new_user

设置用户密码

1
2
3
4
5
6
7
-- 设置密码
SET PASSWORD = PASSWORD('新密码')  -- 为当前用户设置密码
SET PASSWORD FOR 用户名 = PASSWORD('新密码') -- 为指定用户设置密码

-- 8.X
SET PASSWORD = '新密码'  -- 为当前用户设置密码
SET PASSWORD FOR 用户名 = '新密码' -- 为指定用户设置密码

设置密码过期

1
2
3
4
5
6
7
8
# 将密码标记为过期，用户在第一次连接到服务器时必须选择一个新密码
CREATE USER 'holelin'@'localhost' PASSWORD EXPIRE;
# 默认过期时间，应用 default_password_lifetime 系统变量指定的全局过期策略
CREATE USER 'holelin'@'localhost' PASSWORD EXPIRE DEFAULT;
# 禁用密码过期，永不过期
CREATE USER 'holelin'@'localhost' PASSWORD EXPIRE NEVER;
# 设定过期时间，每 180 天选择一个新密码
CREATE USER 'holelin'@'localhost' PASSWORD EXPIRE INTERVAL 180 DAY;

删除用户

1
2
-- 删除用户
DROP USER 用户名

锁定用户

1
2
3
4
# 锁定用户
ALTER USER 'developer'@'%' ACCOUNT LOCK;
# 解锁
ALTER USER 'developer'@'%' ACCOUNT UNLOCK;

角色管理

创建角色

1
CREATE ROLE 'dba','app_dev';

给角色赋予权限

1
GRANT SELECT,INSERT,UPDATE,DELETE ON athena.* TO app_dev;

创建用户并授予用户角色

1
2
CREATE USER 'athena_dev1' @'%' IDENTIFIED BY '123456';
GRANT app_dev TO 'athena_dev1' @'%';

激活角色

1
2
3
SET DEFAULT ROLE
    {NONE | ALL | role [, role ] ...}
    TO user [, user ] ...
1
2
3
SET DEFAULT ROLE 'app_dev' to 'athena_dev1'@'%';

-- 若要删除默认角色设置：SET DEFAULT ROLE none TO user_name;

启用登录自动激活

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
-- 修改参数，启用登录自动激活。（该参数不允许会话级修改）
mysql> show global variables like 'activate_all_roles_on_login';
+-----------------------------+-------+
| Variable_name               | Value |
+-----------------------------+-------+
| activate_all_roles_on_login | OFF   |
+-----------------------------+-------+
1 row in set (0.04 sec)

mysql> set global activate_all_roles_on_login = on;
Query OK, 0 rows affected (0.00 sec)

mysql> show global variables like 'activate_all_roles_on_login';
+-----------------------------+-------+
| Variable_name               | Value |
+-----------------------------+-------+
| activate_all_roles_on_login | ON    |
+-----------------------------+-------+
1 row in set (0.01 sec)

查看当前账号角色

1
2
3
4
5
6
7
mysql> select CURRENT_ROLE();
+----------------+
| CURRENT_ROLE() |
+----------------+
| `app_dev`@`%`  |
+----------------+
1 row in set (0.00 sec)

撤销角色/删除角色

1
2
3
4
5
6
7
-- 撤销角色，从用户上拿掉某一角色，即同时的撤销该角色拥有的权限集合
REVOKE role_name FROM user_name;
-- 回收角色上的授权(和旧时的用户回收权限类似)
REVOKE DELETE ON db_name.* FROM role_name;

-- 删除角色
DROP ROLE role_name1, role_name2;

权限管理

权限层级

  • 要使用GRANT或REVOKE,您必须拥有GRANT OPTION权限,并且您必须用于您正在授予或撤销的权限.
    • 全局层级:全局权限适用于一个给定服务器中的所有数据库,mysql.user
      • GRANT ALL ON *.*和REVOKE ALL ON *.*只授予和撤销全局权限.
    • 数据库层级:数据库权限适用于一个给定数据库中的所有目标,mysql.db, mysql.host
      • GRANT ALL ON db_name.*和REVOKE ALL ON db_name.*只授予和撤销数据库权限.
    • 表层级:表权限适用于一个给定表中的所有列,mysql.talbes_priv
      • GRANT ALL ON db_name.tbl_name和REVOKE ALL ON db_name.tbl_name只授予和撤销表权限.
    • 列层级:列权限适用于一个给定表中的单一列,mysql.columns_priv
      • 当使用REVOKE时,您必须指定与被授权列相同的列.

分配权限

1
2
3
4
5
6
-- 分配权限/添加用户
GRANT 权限列表 ON 表名 TO 用户名 [IDENTIFIED BY [PASSWORD] 'password'] [WITH GRANT OPTION]
    - all privileges 表示所有权限
    - *.* 表示所有库的所有表
    - 库名.表名 表示某库下面的某表
    GRANT ALL PRIVILEGES ON `pms`.* TO 'pms'@'%' IDENTIFIED BY 'pms0817';

  • 程序账号一般给开发人员使用,给定权限推荐:create routine、alter routine、 execute、select、delete、insert、update

    1
    GRANT CREATE ROUTINE, ALTER ROUTINE, EXECUTE, SELECT, DELETE, INSERT, UPDATE ON mydatabase.* TO 'developer_user'@'host';

查看用户权限

1
2
3
4
5
6
7
-- 查看权限
SHOW GRANTS FOR 用户名

-- 查看当前用户权限
SHOW GRANTS; 
SHOW GRANTS FOR CURRENT_USER;  
SHOW GRANTS FOR CURRENT_USER();

撤销权限

1
2
3
-- 撤消权限
REVOKE 权限列表 ON 表名 FROM 用户名
REVOKE ALL PRIVILEGES, GRANT OPTION FROM 用户名   -- 撤销所有权限

刷新权限

1
2
-- 刷新权限
FLUSH PRIVILEGES;

权限列表

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
-- 权限列表
ALL [PRIVILEGES]    -- 设置除GRANT OPTION之外的所有简单权限
ALTER   -- 允许使用ALTER TABLE
ALTER ROUTINE   -- 更改或取消已存储的子程序

CREATE  -- 允许使用CREATE TABLE
CREATE ROUTINE  -- 创建已存储的子程序
CREATE TEMPORARY TABLES     -- 允许使用CREATE TEMPORARY TABLE
CREATE USER     -- 允许使用CREATE USER, DROP USER, RENAME USER和REVOKE ALL PRIVILEGES.
CREATE VIEW     -- 允许使用CREATE VIEW

DELETE  -- 允许使用DELETE
DROP    -- 允许使用DROP TABLE
EXECUTE     -- 允许用户运行已存储的子程序
FILE    -- 允许使用SELECT...INTO OUTFILE和LOAD DATA INFILE
INDEX   -- 允许使用CREATE INDEX和DROP INDEX
INSERT  -- 允许使用INSERT
LOCK TABLES     -- 允许对您拥有SELECT权限的表使用LOCK TABLES
PROCESS     -- 允许使用SHOW FULL PROCESSLIST
REFERENCES  -- 未被实施
RELOAD  -- 允许使用FLUSH
REPLICATION CLIENT  -- 允许用户询问从属服务器或主服务器的地址
REPLICATION SLAVE   -- 用于复制型从属服务器（从主服务器中读取二进制日志事件）
SELECT  -- 允许使用SELECT
SHOW DATABASES  -- 显示所有数据库
SHOW VIEW   -- 允许使用SHOW CREATE VIEW
SHUTDOWN    -- 允许使用mysqladmin shutdown
SUPER   -- 允许使用CHANGE MASTER, KILL, PURGE MASTER LOGS和SET GLOBAL语句,mysqladmin debug命令；允许您连接（一次）,即使已达到max_connections.
UPDATE  -- 允许使用UPDATE
USAGE   -- “无权限”的同义词
GRANT OPTION    -- 允许授予权限

MySQL有关权限的表

MySQL服务器通过权限表来控制用户对数据库的访问,权限表存放在mysql数据库里,由mysql_install_db脚本初始化.这些权限表分别user,db,table_priv,columns_priv和host.

  • user权限表:记录允许连接到服务器的用户帐号信息,里面的权限是全局级的.
  • db权限表:记录各个帐号在各个数据库上的操作权限.
  • table_priv权限表:记录数据表级的操作权限.
  • columns_priv权限表:记录数据列级的操作权限.

示例

root密码重置

1
2
3
4
5
6
1. 停止MySQL服务
2.  [Linux] /usr/local/mysql/bin/safe_mysqld --skip-grant-tables &
    [Windows] mysqld --skip-grant-tables
3. use mysql;
4. UPDATE `user` SET PASSWORD=PASSWORD("密码") WHERE `user` = "root";
5. FLUSH PRIVILEGES;