MySQL用户管理
Mysql用户的作用：
1. 登录mysql
2. 管理mysql
创建用户
语法：create user [email protected]’host’ identified by 'password';
说明：
username：你将创建的用户名
host：指定该用户从哪个主机上可以登陆，如果是本地用户可用localhost，如果想让该用户可以从任意远程主机登陆，可以使用通配符%
password：该用户的登陆密码
mysql> create user [email protected]'localhost' identified by 'test123';
注：mysql 8.0以后必须先创建用户再授权
查看刚创建的用户
select user,host from mysql.user;
修改用户密码
mysql> set password for [email protected]'localhost'=password('hello');
mysql> flush privileges;
如果想修改当前用户密码
mysql> SET PASSWORD = PASSWORD("hello123");
删除用户
drop user [email protected]’localhost’;
用户权限管理
查看用户权限
show grants for [email protected]’localhost’;
注：usage 表示没有任何权限
给用户授权
GRANT privileges ON databasename.tablename TO 'username'@'host';
说明：
privileges：权限列表，all 表示所有权限，常用权限select,update,delete,create,insert
databasename.tablename：权限作用于哪个库哪个表，*.* 表示作用于所有的库和表，databasename.* 表示作用于某个库下的所有表，也可以对表的某列授权
'username'@'host'：权限赋予的用户
注：mysql 8.0已经不支持授权时直接创建用户
查看权限列表
show privileges;
说明：Privilege：权限，Context：权限作用对象，Comment：说明
给test用户test库的select和insert权限
mysql> grant select,insert on test.* to [email protected]'localhost';
查看用户权限
mysql> show grants for [email protected]'localhost';
+--------------------------------------------------------+
| Grants for [email protected] |
+--------------------------------------------------------+
| GRANT USAGE ON *.* TO 'test'@'localhost' |
| GRANT SELECT, INSERT ON `test`.* TO 'test'@'localhost' |
+--------------------------------------------------------+
查看root用户权限
mysql> show grants for [email protected]'localhost';
+---------------------------------------------------------------------+
| Grants for [email protected] |
+---------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' WITH GRANT OPTION |
| GRANT PROXY ON ''@'' TO 'root'@'localhost' WITH GRANT OPTION |
+---------------------------------------------------------------------+
WITH GRANT OPTION：表示该用户可以对其他用户进行授权，此权限一般只给管理员
回收用户权限
REVOKE privileges ON databasename.tablename FROM 'username'@'host';
查看用户当前权限
mysql> show grants for [email protected]'localhost';
+----------------------------------------------------------------+
| Grants for [email protected] |
+----------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'test'@'localhost' |
| GRANT SELECT, INSERT, UPDATE ON `test`.* TO 'test'@'localhost' |
+----------------------------------------------------------------+
回收[email protected]’localhost’用户对test库的select权限
mysql> revoke select on test.* from [email protected]'localhost';
mysql> show grants for [email protected]'localhost';
+--------------------------------------------------------+
| Grants for [email protected] |
+--------------------------------------------------------+
| GRANT USAGE ON *.* TO 'test'@'localhost' |
| GRANT INSERT, UPDATE ON `test`.* TO 'test'@'localhost' |
+--------------------------------------------------------+
回收[email protected]’localhost’用户对test库的所有权限
mysql> revoke all on test.* from [email protected]'localhost';
mysql> show grants for [email protected]'localhost';
+------------------------------------------+
| Grants for [email protected] |
+------------------------------------------+
| GRANT USAGE ON *.* TO 'test'@'localhost' |
+------------------------------------------+
现在test用户只能登录和查看information_schema库
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
+--------------------+
注：为了数据库的安全，用户的权限应该进行严格管理
管理员忘记密码
1. 关闭mysql，/etc/init.d/mysqld stop
2. 使用mysql_safe 安全登录 并跳过授权验证和远程连接
mysqld_safe --skip-grant-tables --skip-networking &
3. 登录mysql，修改密码
mysql> alter user [email protected]'localhost' identified by '123';
ERROR 1290 (HY000): The MySQL server is running with the --skip-grant-tables option so it cannot execute this statement
mysql>flush privileges; #先刷新权限表
mysql> alter user [email protected]'localhost' identified by '123';
4. 修改密码之后重启mysql，并用新密码登录
关闭mysqld_safe启动的mysql实例
mysqladmin -uroot -pxxx shutdown
或者直接kill mysql进程
正常启动mysql并登录
/etc/init.d/mysqld start