数据库基础
Source
Edit
History

undefined

一、创建用户

1. create user语句

  1. 使用 create user 语句来创建 mysql 用户，并设置相应的密码

    1
    create user <用户> [identified by [password] 'password']
    • 用户：指定创建用户账号，格式为 user_name@’host_name’, user_name 是用户名，host_name 是主机名。
      如果只给出了用户名，那么主机名默认是’%’,表示对所有主机开放权限
    • identified by 子句：用于指定密码，新用户可以没有初始密码，此子句可省略
    • password ‘password’ : password 表示使用哈希值设置密码，参数可选。如果密码是一个普通的字符串，则不需要使用 password 关键字

    注：create user 语句可以同时创建多个用户，多个用户用逗号隔开

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    # 例子
    # 1. 用户名是 test1，密码是 test1，主机名是 localhost
    create user 'test1'@'localhost' identified by 'test1';
    # 2. 避免使用明文指定密码，可以通过password 关键字使用密码的哈希值设置密码
    mysql> select password('test1');
    +-------------------------------------------+
    | password('test1')                         |
    +-------------------------------------------+
    | *06C0BF5B64ECE2F648B5F048A71903906BA08E5C |
    +-------------------------------------------+
    create user 'test1'@'localhost' identified by password '*06C0BF5B64ECE2F648B5F048A71903906BA08E5C';

2. insert 语句

通常insert 语句只添加 Host、User、authentication_string/password 这三个字段的值即可

1
2
insert into mysql.user(Host,User,authentication_string,ssl_cipher,x509_issuser,x509_subject) values('hostname', 'username', password('password'), '', '', ''); 
# 由于user表中，ssl_cipher、x509_issuer和x509_subject 这3个字段没有默认值，所以需要设置

例子：

1
2
3
INSERT INTO mysql.user(Host, User, authentication_string, ssl_cipher, x509_issuer, x509_subject) VALUES ('localhost', 'test2', PASSWORD('test2'), '', '', '');
flush privileges;
# 注意需要刷新系统权限相关表，执行 flush 命令需要 reload 权限

3. grant 语句

1
grant priv_type on database.table to user [identified by [password] 'password']
  • priv_type：新用户的权限
  • database.table：新用户的权限范围，即只能在指定的数据库和表上使用自己的权限
  • user：指定新用户的账户，由用户名和主机名构成
  • identified by：用来设置密码
  • password：表示新用户的密码

例子：

1
2
grant select on *.* to 'test3'@localhost identified by 'test3';
grant all privileges on *.* to 'test3'@'localhost' identified by password '*06C0BF5B64ECE2F648B5F048A71903906BA08E5C';

二、修改用户

1
rename user <旧用户> to <新用户>
  • 旧用户：系统中已经存在的 mysql 用户账号
  • 新用户：新的 mysql 用户账号

注：使用 rename user 语句，必须拥有 mysql 数据库的 update 权限或全局 create user 权限

1
rename user 'test1'@'localhost' to 'test2'@'localhost';

三、删除用户

1. 使用 drop 语句

1
drop user <用户1> [,<用户2>]...
  • 使用 drop user 语句必须拥有 mysql 数据库的 delete 权限或全局 create user 权限
  • 在 drop user 语句的使用中，若没有明确给出账户的主机名，则该主机名默认为 ‘%’
  • 用户的删除不会影响他们之前所创建的表、索引或其他数据库对象，因为 mysql 并不会记录是谁创建了这些对象
1
drop user 'test1'@'localhost';

2. 使用 delete 语句

1
delete from mysql.user where Host = 'hostname' and User = 'username';
  • 必须拥有 mysql.user 表的 delete 权限

四、权限管理

1. 查看用户管理

  1. 使用 select * from mysql.user; 要执行该语句，必须拥有对 user 表的查询权限
  2. show grants for 'username'@'hostname';

2. 用户授权

1
grant priv_type [(column_list)] on database.table to user [IDENTIFIED BY [PASSWORD] 'password'] [, user[IDENTIFIED BY [PASSWORD] 'password']] ... [WITH with_option [with_option]...]
  • priv_type：权限类型
  • column_list：权限作用于哪些列上，省略该参数时，表示作用于整个表
  • database.table：用于指定权限的级别
  • user：表示用户账户，由用户名和主机名构成，格式是 ‘username‘@’hostname’
  • identified by ：用来为用户设置密码
  • password：用户的新密码

with 关键字后面带有一个或多个 with_option 参数，这个参数有5个选项，详细介绍如下：

  • GRANT OPTION：被授权的用户可以将这些权限赋予给别的用户
  • MAX_QUERIES_PER_HOUR count：设置每小时可以允许执行 count 次查询
  • MAX_UPDATES_PER_HOUR count：设置每小时可以允许执行 count 次更新
  • MAX_CONNECTIONS_PER_HOUR count：设置每小时可以建立 count 个连接
  • MAX_USER_CONNECTIONS count：设置单个用户可以同时具有的 count 个连接

grant 语句中可用于指定权限级别的值有以下几类格式：

  • *：表示当前数据库中的所有表
  • *.*：表示所有数据库中所有表
  • db_name.*：某个数据库中的所有表，db_name 指定数据库名
  • db_name.tbl_name：表示某个数据库中某个表或视图，db_name 指定数据库名，tbl_name 指定表名或视图名
  • db_name.routine_name：表示某个数据库中的某个存储过程或函数，routine_name 指定存储过程名或函数名
  • TO子句：如果权限被授予给一个不存在的用户，Mysql 会自动执行一条 create user 语句来创建这个用户，但同时必须为该用户设置密码
1
2
grant select, insert on *.* to 'test'@'localhost' identified by 'test' with grant option;
# 使用 grant 创建一个新用户 test，密码为 test，用户对所有的数据有查询、插入权限，并授予 grant 权限

3. 删除用户权限

使用 revoke 语句删除某个用户的某些权限(此用户不会被删除)

1
2
# 1. 删除用户某些特定的权限
revoke priv_type [(column_list)]... on database.table from user[, user]...
  • priv_type：表示权限的类型
  • column_list：表示权限作用于哪些列上，没有该参数时作用于整个表上
  • user：由用户名和主机名构成，格式为 ‘username‘@’localhost’
1
2
# 2. 删除特定用户的所有权限
revoke all privileges, grant option from user [, user] ...
  • 使用 revoke 语句，必须拥有mysql 数据库的全局 create user 权限或 update 权限
1
2
# 例子
revoke insert on *.* from 'test'@'localhost';

五、修改密码

修改普通用户密码

1. 使用 set 语句修改普通用户密码

1
set password for 'username'@'localhost' = password('newpwd');
  • 新密码必须使用 password() 函数来加密，如果不使用 password() 加密，也会执行成功，但用户无法登录
  • 如果普通用户修改密码，可省略 for 子句来更改自己的密码。 set password = password('newpwd');

2. 使用 update 语句修改普通用户的密码

可以使用 update 语句修改 mysql.user 的 authentication_string 字段，从而修改普通用户的密码

1
2
update mysql.user set authentication_string = password('newpwd') where User='username' and Host = 'hostname';
flush privileges;

3. 使用 grant 语句修改普通用户密码

1
grant usage on *.* to 'user'@'hostname' identified by 'newpwd';

使用 grant 语句修改密码，必须拥有 grant 权限

修改root密码

1. 使用 mysqladmin 命令在命令行指定 root 密码

1
mysqladmin -u username -h hostname -p password "newpwd"
  • password 是关键字
  • “newpwd” 为新设置的密码，必须用双引号括起来。如果使用单引号会引发错误，可能会造成修改后的密码不是你想要的
1
2
mysqladmin -u root -p password "rootpwd"
回车后输入 root 用户原来的密码，即可修改成功

2. 修改 mysql.user 表

1
2
update mysql.user set authentication_string = password("rootpwd") where User = "root" and Host = "localhost";
flush privileges;

新密码必须使用 password() 函数来加密。

3. 使用 set 语句修改 root 用户密码

1
set password = password("rootpwd");

set password 语句可以用来重新设置其他用户的登录密码或者自己使用的账户的密码

六、重置mysql的root密码

  1. 关闭当前 mysql 服务
  2. 使用 --skip-grant-tables 方式会让 mysql 服务器跳过验证步骤，允许所有用户以匿名、无需密码验证就可以直接登录 mysql 服务器，并且拥有所有的操作权限。此方式登录 mysql 服务器
  3. 设置 root 新密码 update mysql.user set euthentication_string=password('root') where User='root' and Host='localhost';
  4. 刷新权限 flush privileges;
  5. 去掉 --skip-grant-tables 重启 mysql 服务