MySQL/mariadb知识点——操作篇(1)用户管理

在了解mysql的用户管理语句之前，先来了解一下mysql的用户账号格式，即：

username@host　

username对应的用户能够通过那个host登录mysql。

host：次mysql用户能够通过哪些客户端主机ip登录当前服务器上的mysql服务。

注：可以使用通配符表示，%和_；192.168.%即表示这个网段的所有主机

1、查询用户

MariaDB [(none)]> use mysql
Database changed

MariaDB [mysql]> select user,host,password from user;
+------+-----------------------+----------+
| user | host                  | password |
+------+-----------------------+----------+
| root | localhost             |          |
| root | localhost.localdomain |          |
| root | 127.0.0.1             |          |
| root | ::1                   |          |
|      | localhost             |          |
|      | localhost.localdomain |          |
+------+-----------------------+----------+

2、创建用户

方法一：使用create user命令创建mysql用户；

MariaDB [mysql]> create user 'Batman' identified by 'Robin';
Query OK, 0 rows affected (0.00 sec)

方法二：直接在mysql库的user表中插入记录；这种方法较少使用，而且这种方法在设置了严格的sql-mode以后便无法使用；

MariaDB [mysql]> insert into mysql.user(user,host,password) values('superman','192.168.%.%',password('Super'));

注意：如果当前mysql数据库的sql-mode设置为严格检查(TRADITIONAL)，则无法使用上述方法插入数据；显示为

ERROR 1364 (HY000): Field 'ssl_cipher' doesn't have a default value

方法三：在授权数据库的同时，如果对应用户不存在，namemysql将会自动创建对应的用户；

MariaDB [mysql]> grant all on testdb.* to superman identified by 'super';
Query OK, 0 rows affected (0.00 sec)

3、删除用户

通常使用drop命令删除用户或者直接删除mysql.user表中对应的用户记录

方法一：使用delete语句删除mysql.user表中对应的记录，但是会有相关信息残留 

MariaDB [mysql]> DELETE FROM mysql.user WHERE user='superman' and host='%';
Query OK, 1 row affected (0.01 sec)

上例中，使用了delete语句删除了user表中的用户对应的记录，但是如果某些数据库的权限以赋给该用户；那么，在mysql.db表中，该用户相应的权限将不会被删除，当管路员再次创建superman用户时则会出现如下错误：

MariaDB [mysql]> CREATE USER 'superman';
ERROR 1396 (HY000): Operation CREATE USER failed for 'superman'@'%'

 解决上述问题；

我们可以删除mysql.db表中源“superman@%”用户残留的权限数据后，使用

flush privileges　

进行刷新，之后正常创建；

方法二：使用drop user命令删除对应用户；用户与用户对应的所有权限将被删除。

MariaDB [mysql]> DROP USER 'superman';
Query OK, 1 row affected (0.01 sec)

4、重命名用户

可用如下语句重命名账户；

rename user OldName to NewName;

实例：

MariaDB [mysql]> select user,host,password from user;
+--------+-----------------------+-------------------------------------------+
| user   | host                  | password                                  |
+--------+-----------------------+-------------------------------------------+
| root   | localhost             |                                           |
| root   | localhost.localdomain |                                           |
| root   | 127.0.0.1             |                                           |
| root   | ::1                   |                                           |
|        | localhost             |                                           |
|        | localhost.localdomain |                                           |
| Batman | %                     | *5CEE5F1426974EA6CD9708CB85FF206C98ADF749 |
+--------+-----------------------+-------------------------------------------+
7 rows in set (0.00 sec)

MariaDB [mysql]> rename user Batman to Robin;
Query OK, 0 rows affected (0.00 sec)

MariaDB [mysql]> select user,host,password from user;
+-------+-----------------------+-------------------------------------------+
| user  | host                  | password                                  |
+-------+-----------------------+-------------------------------------------+
| root  | localhost             |                                           |
| root  | localhost.localdomain |                                           |
| root  | 127.0.0.1             |                                           |
| root  | ::1                   |                                           |
|       | localhost             |                                           |
|       | localhost.localdomain |                                           |
| Robin | %                     | *5CEE5F1426974EA6CD9708CB85FF206C98ADF749 |
+-------+-----------------------+-------------------------------------------+
7 rows in set (0.00 sec)

 5、用户密码管理

方法一：管理员在系统命令中使用mysqladmin命令设置mysql密码。

mysqladmin -u用户名 -p旧密码 password 新密码

实例：

[root@localhost ~]# mysqladmin -uRobin -pRobin password 123456

如果数据库刚刚被初始化 用户还没有密码，则可用不指定旧密码；

[root@localhost ~]# mysqladmin -uroot password 123456

方法二：使用root用户登入以后，使用SET PASSWORD命令设置用户密码

MariaDB [(none)]> SET PASSWORD for Robin = password('123456');
Query OK, 0 rows affected (0.00 sec)

方法三：忘记root密码，破解root账号密码

• 空数据库的情况下恢复密码

# systemctl stop mariadb
# rm -rf /var/lib/mysql/*  #删库跑路
# systemctl start mariadb

• 有数据的情况下恢复密码

  1）在/etc/my.cnf配置文件的[mydqld]下添加skip-grant-tables和skip-networking参数
  2）# systemctl restart mariadb 重启服务
  3）执行mysql登录到数据库
  4）MariaDB [(none)]> UPDATE mysql.user SET password=PASSWORD('newpassword') WHERE user='root' AND host='localhost';  #更新密码
  5）MariaDB [(none)]> FLUSH PRIVILEGES;  #刷新授权表
  6）退出，修改配置文件，删除skip-grant-tables和skip-networking参数，重启服务

　　注：也可以在启动mysqld进程时，使用如下选项：

--skip-grant-tables
--skip-networking

6、授权管理

1)授权

使用授权语句的语法：

GRANT priv_type ON [object_type] priv_level TO user@'%' [IDENTIFIED BY 'password'] [WITH GRANT OPTION];

如同创建用户方法三；授权时如果用户不存在则创建，所以我们一般不会单独去创建一个用户，而是授权创建一块完成。

priv_type 授权类型
    SELECT
​    INSERT
    ​UPDATE
​    DELETE
​    CREATE
​    DROP
    INDEX
    ALTER
    ​SHOW DATABASES
    ​CREATE TEMPORARY TABLES
    ​LOCK TABLES
    ​CREATE VIEW
    SHOW VIEW
    CREATE USER
    ALL PRIVILEGES 或 ALL
object_type 授权对象
    TABLE
    FUNCTION
    PROCEDURE
priv_level 授权级别
    *或*.* 表示所有库
    db_name.* 表示指定库中的所有表
    db_name.tbl_name 指定库中的指定表
    tbl_name 表示当前库的表
    db_name.routine_name 表示指定库的函数,存储过程,触发器
WITH GRANT OPTION
    MAX_QUERIES_PER_HOUR count
    MAX_UPDATES_PER_HOUR count
    MAX_CONNECTIONS_PER_HOUR count
    MAX_USER_CONNECTIONS count

实例：

MariaDB [school]> GRANT SELECT(stuid,name) ON TABLE school.students TO admin@'%' IDENTIFIED BY 'admin';  
#把students表的stuid和name字段的查询权限授权于admin@'%'用户
MariaDB [school]> FLUSH PRIVILEGES;  
#刷新授权表

授权用户某个数据库多个权限：

MariaDB [school]> GRANT INSERT,DELETE,UPDATE,SELECT ON school.* TO superman'192.168.%.%';
MariaDB [school]> FLUSH PRIVILEGES;

同时授权多个用户：

MariaDB [school]> GRANT INSERT,DELETE,UPDATE,SELECT ON school.* TO superman,batman;
MariaDB [school]> FLUSH PRIVILEGES;

2)查询授权

查询指定用户的权限

MariaDB [school]> SHOW GRANTS FOR admin@'%'\G  #查看指定用户的权限
*************************** 1. row ***************************
Grants for admin@%: GRANT USAGE ON *.* TO 'admin'@'%' IDENTIFIED BY PASSWORD '*4ACFE3202A5FF5CF467898FC58AAB1D615029441'
*************************** 2. row ***************************
Grants for admin@%: GRANT SELECT (stuid, name) ON `school`.`students` TO 'admin'@'%'

查询自己的权限

[root@working ~]# mysql -uadmin -padmin -h192.168.0.7
MariaDB [(none)]> SHOW GRANTS FOR CURRENT_USER()\G  #查询自己的权限
*************************** 1. row ***************************
Grants for admin@%: GRANT USAGE ON *.* TO 'admin'@'%' IDENTIFIED BY PASSWORD '*4ACFE3202A5FF5CF467898FC58AAB1D615029441'
*************************** 2. row ***************************
Grants for admin@%: GRANT SELECT (stuid, name) ON `school`.`students` TO 'admin'@'%'

3)收回授权

MariaDB [school]> REVOKE SELECT(stuid) ON school.students FROM admin@'%';  
#收回admin@'%'用户对stuid字段的查询权限

注：当一个用户被创建时，mysql会自动授予usage权限；usage权限只能用于登录数据，不能执行其他操作。

如果用户有可能会跨越不安全的网络连接到数据库，我们可以强制用户使用ssl建立会话：

grant usage on *.* to 'superman'@'222.222.222.222' require ssl;

如果取消上述ssl连接，使用如下命令撤销：

grant usage on *.* to 'superman'@'222.222.222.222' require none;

如果，root用户授权了superman用户权限时，搭配了grant选项，那么superman有权将已拥有的权限授予其他用户。这样做很危险；请勿随意使用此选项；示例如下

grant select on *.* to superman@'192.168.%.%'  with grant option;

在上面提到了WITH GRANT OPTION这一项，管理员爸爸还可以通过这些选项对用户进行一些其他限制。