MySQL 用户管理

--MySQL用户管理

-- 查看MYSQL数据库中所有用户
SELECT DISTINCT CONCAT('User: ''',user,'''@''',host,''';') AS QueryUser FROM mysql.user;

--创建MySQL用户
--创建MySQL用户有三种方法

--1.通过CREATE USER命令进行创建用户
--语法
CREATE USER 'username'@'host' [IDENTIFIED BY 'PASSWORD'];
--例
mysql> create user www@'localhost' identified by '123456';
mysql> create user www@'%' identified by '123456';
mysql> create user www@'192.168.1.10';
--说明:该方法创建出来的用户只有连接数据库的权限,需要后续继续授权

--2.通过 GRANT 命令创建用户(常用方法)
--语法
GRANT <权限> ON <数据库.表名> TO <用户名@登录主机> IDENTIFIED BY '密码'[WITH GRANT OPTION];
GRANT priv_type[(column_list)] ON [object_type] priv_level TO username@'%' [IDENTIFIED BY [PASSWORD] 'password'][WITH GRANT OPTION];
--例
--1.>创建用户www,并分配对所有数据库和表有所有的权限,允许所有主机登录，设定口令为123456
GRANT ALL PRIVILEGES ON *.* TO www@'%'IDENTIFIED BY '123456' WITH GRANT OPTION;
--2.>给主机为192.168.10.1的用户www分配可对数据库testdb所有表进行所有操作的权限，并设定口令为123456
GRANT ALL PRIVILEGES ON testdb.* TO www@'192.168.10.1' IDENTIFIED BY '123456';
--3.>创建只读账户
grant select on pms.* to pmsbi@'%' IDENTIFIED BY 's86j3z47995';
--说明：
--priv代表权限 select,insert,update,delete,create,drop,index,alter,grant,references,reload,shutdown,process,file等14个权限
--ON：表示这些权限对哪些数据库和表生效，格式：数据库名.表名,写‘*.*’表示所有数据库所有表。
--TO：将权限授予哪个用户.格式：”用户名”@”登录IP或域名”.%表示没有限制,在任何主机都可以登录。
--IDENTIFIED BY：指定用户的登录密码.
--WITH GRANT OPTION：表示允许用户将自己的权限授权给其它用户,为可选项
--priv_type：ALL或上面的权限表格中的权限
--priv_level：*| *.*| db_name.*| db_name.tbl_name| tbl_name| db_name.routine_name

-- 直接向mysql.user表插入记录（该方法个人很少用)
INSERT INTO mysql.user(Host,User,Password) VALUES('%','www',password('123456'));
flush privileges;

-- 创建用户及赋权(app业务用户)(5.6.*需要本地和%分别赋权)
create user 'yllp'@'%' identified by 'yllp@1119';
grant select, insert, update, delete, create on yllp.* to 'yllp'@'%' identified by 'yllp@1119';
grant select, insert, update, delete, create on yllp.* to 'yllp'@'localhost' identified by 'yllp@1119';
flush privileges;

-- 创建app用户(例)
create user 'qms'@'%' identified by 'qms@1219';
grant select, insert, update, delete, create, drop, create view, create routine on hk.* to 'qms'@'%' identified by 'qmspasswd';
flush privileges;

-- 创建复制用户：
grant replication client,replication slave on *.* to rep@'%' identified by 'rep';

 

--查看用户权限
-- 查看mysql当前登录用户:
select user();
-- 查看某MySQL用户的使用权限
show grants for dbuser;
--例
show grants for root@localhost;
-- 查看创建数据库时用到那些参数
show create database dbname;
-- 查看创建表时用到的那些参数
show create table 表名;

--权限查询
-- A little query to write the wide privilege table out in narrower form:
SELECT host, user,
CONCAT(Select_priv, Lock_tables_priv) AS selock,
CONCAT(Insert_priv, Update_priv, Delete_priv, Create_priv, Drop_priv) AS modif,
CONCAT(Grant_priv, References_priv, Index_priv, Alter_priv) AS meta,
CONCAT(Create_tmp_table_priv, Create_view_priv, Show_view_priv) AS views,
CONCAT(Create_routine_priv, Alter_routine_priv, Execute_priv) AS funcs,
CONCAT(Repl_slave_priv, Repl_client_priv) AS replic,
CONCAT(Super_priv, Shutdown_priv, Process_priv, File_priv, Show_db_priv, Reload_priv) AS admin
FROM USER ORDER BY user, host;

-- MySQL用户加密插件(new:caching_sha2_password old:mysql_native_password)
use mysql;
select user, host, plugin, authentication_string from user\G

-- 回收用户权限
revoke create on *.* from 'www@localhost';
flush privileges;

--管理用户
-- 删除用户
delete FROM user Where User='www' and Host='localhost';
flush privileges;
-- 删除账户及权限
drop user www@localhost;

-- 用户重命名
rename user 'www'@'localhost' to 'user1'@'localhost';

--修改用户密码
--1.用set password命令
--语法：
SET PASSWORD FOR 'user_name'@'host' = PASSWORD('new_password');
--例
mysql> set password for 'www'@'localhost' = password('123');
mysql> SET PASSWORD FOR 'barlow'@'%' = PASSWORD('987654');
--2.直接update mysql.user表的password字段实现修改密码
--语法：
mysql> use mysql
mysql> UPDATE user SET PASSWORD = PASSWORD('new_password') WHERE User='user_name' AND Host='host';
mysql> FLUSH PRIVILEGES;
--例
mysql> use mysql
mysql> update user set authentication_string=password('123456') where user='www';
mysql> UPDATE user SET PASSWORD = PASSWORD('redhat') WHERE USER='barlow' AND HOST='%';
mysql> FLUSH PRIVILEGES;
--3.用mysqladmin命令
--语法：
mysqladmin -u用户名 -p旧的密码 password 新密码
--例
mysql> mysqladmin -uroot -p123456 password 1234abcd

--mysql5.6.*修改密码必须密文
--通过如下方式可获取密文
(root@localhost) [(none)]> select password('hking');
+-------------------------------------------+
| password('hking')                         |
+-------------------------------------------+
| *D2B3FF3048468079EDD2FA4FD21CBC478B0BF8A9 |
+-------------------------------------------+

--忘记root密码
--第一种方法
--1.添加登录跳过权限检查配置
--修改my.cnf,在mysqld配置节点添加skip-grant-tables配置
#vi my.cnf
[mysqld]
skip-grant-tables
--2.重新启动mysql服务
#service mysqld restart
--3.修改密码
FLUSH PRIVILEGES;
--此时在终端用mysql命令登录时不需要用户密码，然后按照修改密码的第一种方式将密码修改即可
--注意：mysql库的user表，5.7以下版本密码字段为password，5.7以上版本密码字段为authentication_string
--4.还原登录权限跳过检查配置
--将my.cnf中mysqld节点的skip-grant-tables配置删除,然后重新启动服务即可
#vi my.cnf
[mysqld]
skip-grant-tables
#service mysqld restart
--第二种方法
--1.kill掉mysql的进程
kill -9 mysql-PID
--2.用--skip-grant-tables & 启动数据库
/usr/local/mysql/bin/mysqld_safe --defaults-file=/etc/my.cnf --skip-grant-tables --datadir=/data/mysql/mysql/ --user=mysql &
--3.mysql -uroot 空密码登陆
#mysql -uroot
--4.修改root用户的密码
-- FLUSH PRIVILEGES;
MySQL> update MySQL.user set password=PASSWORD('新密码') where User='root';
MySQL> flush privileges;
MySQL> quit
--重新启动MySQL，就可以使用新密码登录了

--第三种方法
--root密码丢失-修改
--软件目录
mysqldir='/mysql/mysoft/mysql8.0'
--密码
mysqlpassword='mysql'
--执行文件
cd $mysqldir
touch init.sql
cat >>$mysqldir <<EOF
alter user 'root'@'localhost' identified by '$mysqlpassword';
EOF
--执行命令
service mysqld stop
service mysqld start --init-file=$mysqldir/init.sql