MySQL用户管理 - 实践

1.用户信息

MySQL中的用户，都存储在数据库的user表中。

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
|//......            |
| mysql              |
+--------------------+
10 rows in set (0.00 sec)
mysql> show tables;
+------------------------------------------------------+
| Tables_in_mysql                                      |
+------------------------------------------------------+
|//......                                              |
| user                                                 |
+------------------------------------------------------+
38 rows in set (0.00 sec)

查看user表内容

mysql> select * from user\G;
//自行进行查看

mysql> select USER, HOST, authentication_string from user;
+------------------+-----------+------------------------------------------------------------------------+
| USER             | HOST      | authentication_string                                                  |
+------------------+-----------+------------------------------------------------------------------------+
| mysql.infoschema | localhost | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED |
| mysql.session    | localhost | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED |
| mysql.sys        | localhost | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED |
| root             | localhost | *E092E64E0BD5C6924B0E56A4F4D155051CF2FD                                |
+------------------+-----------+------------------------------------------------------------------------+
4 rows in set (0.00 sec)

host字段：表示用户可以从哪一个主机登录，localhost表示只能本机登录

user：用户名

authentication_string：用户通过password函数加密后的值

_priv：用户拥有的权限

MySQL允许创建多个用户，这些用户都为普通用户，root可以创建账号，创建账号需要设置用户名，从那里登录，密码。然后就会把这些信息作为数据插入到user表中。

创建用户

create user '用户名'@'登陆主机/ip' identified by '密码';

创建本地用户

-- 创建只允许本地登录的用户
CREATE USER 'localuser'@'localhost' IDENTIFIED BY 'password';

-- 创建允许本地socket和TCP/IP连接的用户
CREATE USER 'localuser'@'127.0.0.1' IDENTIFIED BY 'password';

本地是mysql机器内连接

权限刷新

mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

登录创建的账号

u后面是用户名，p就是要密码

这个账号不能远程登录，host的值是localhost，只能本地登录，在MySQL上登录，不能跨网络登录。

删除用户

第一种

不安全，可能留下权限残留

delete from user where USER='zymm'

第二种

自动清理相关权限

-- 删除所有主机上的 lfz 用户
DROP USER IF EXISTS 'zymm'@'localhost', 'zymm'@'%';

创建远程登录的账号

使用  % 符号代表可以从任意主机进行登入

mysql> create user 'zymm'@'%' identified by '123456';
Query OK, 0 rows affected (0.00 sec)
mysql> select USER, HOST, authentication_string from user;
+------------------+-----------+------------------------------------------------------------------------+
| USER             | HOST      | authentication_string                                                  |
+------------------+-----------+------------------------------------------------------------------------+
| zymm              | %         | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9                              |
| mysql.infoschema | localhost | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED |
| mysql.session    | localhost | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED |
| mysql.sys        | localhost | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED |
| root             | localhost | *E092E64E0BD5C6924B0E56A4F4D155051DFCF2FD                              |
+------------------+-----------+------------------------------------------------------------------------+
5 rows in set (0.01 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

window系统装了mysql就可以在命令提示符里打开mysql。

修改用户密码

用户自己修改密码

set password = password('新的密码');

root修改密码

set password for '用户名'@'主机名' = password('新的密码');

数据库权限

权限	例	上下文
CREATE	Create_priv	数据库、表或索引
DROP	Drop_priv	数据库或表
GRANT OPTION	Grant_priv	数据库、表或存储的程序
REFERENCES	References_priv	数据库或表
ALTER	Alter_priv	表
DELETE	Delete_priv	表
INDEX	Index_priv	表
INSERT	Insert_priv	表
SELECT	Select_priv	表
UPDATE	Update_priv	表
CREATE VIEW	Create_view_priv	视图
SHOW VIEW	Show_view_priv	视图
ALTER ROUTINE	Alter_routine_priv	保存的程序
CREATE ROUTINE	Create_routine_priv	保存的程序
EXECUTE	Execute_priv	保存的程序
FILE	File_priv	服务器主机上的文件访问
CREATE TEMPORARY TABLES	Create_tmp_table_priv	服务器管理
LOCK TABLES	Lock_tables_priv	服务器管理
CREATE USER	Create_user_priv	服务器管理
PROCESS	Process_priv	服务器管理
RELOAD	Reload_priv	服务器管理
REPLICATION CLIENT	Repl_client_priv	服务器管理
REPLICATION SLAVE	Repl_slave_priv	服务器管理
SHOW DATABASES	Show_db_priv	服务器管理
SHUTDOWN	Shutdown_priv	服务器管理
SUPER	Super_priv	服务器管理

授权操作

grant 权限列表 on 库.对象名 to '用户名'@'登陆位置' [identified by '密码'];

示例

-- 授予所有数据库的所有权限（类似root）
GRANT ALL PRIVILEGES ON *.* TO 'superuser'@'localhost' IDENTIFIED BY 'password';

-- 授予所有权限并允许远程连接
GRANT ALL PRIVILEGES ON *.* TO 'admin'@'%' IDENTIFIED BY 'Admin@123';

回收权限

revoke 权限列表 on 库.对象名 from '用户名'@'登陆位置';

示例

all和all privileges是一个意思，后者规范

-- 回收某个用户的所有数据库权限
REVOKE ALL PRIVILEGES, GRANT OPTION FROM 'olduser'@'localhost';

-- 回收特定数据库的所有权限
REVOKE ALL PRIVILEGES ON `mydb`.* FROM 'dbuser'@'%';