查看存在哪些用户
mysql> select user,host from mysql.user;
±-----±----------+
| user | host |
±-----±----------+
| ducc | % |
| rep | % |
| chen | localhost |
| ducc | localhost |
| root | localhost |
±-----±----------+
5 rows in set (0.00 sec)
具体的用户具有哪些权限
mysql> show grants for rep@’%’;
±---------------------------------------------------------------------------------------------------------------+
| Grants for rep@% |
±---------------------------------------------------------------------------------------------------------------+
| GRANT REPLICATION SLAVE ON . TO ‘rep’@’%’ IDENTIFIED BY PASSWORD ‘*9FF2C222F44C7BBA5CC7E3BE8573AA4E1776278C’ |
±---------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql.user 存放用户
mysql.db 存下权限
mysql的用户名不能超过16个字符
在mysql创建完成之后
安全:
delete from mysql.user where user!=‘root’ or host!=‘localhost’;
如果没有root用户,则手工添加root用户
mysql>INSERT INTO user VALUES(’%’,‘root’,password(‘qaz@wsx’),‘Y’,‘Y’,‘Y’,‘Y’,‘Y’,‘Y’,‘Y’,‘Y’,‘Y’,‘Y’,‘Y’,‘Y’,‘Y’,‘Y’,‘Y’,‘Y’,‘Y’,‘Y’,‘Y’,‘Y’,‘Y’,‘Y’,‘Y’,‘Y’,‘Y’,‘Y’,‘Y’,‘Y’,‘Y’,’’,’’,’’,’’,0,0,0,0,‘mysql_native_password’,’’,‘N’);
mysql>flush privileges;
创建用户:
1.
grant all privileges on ducc.* to ducc@’%’ identified by ‘ducc’;
赋权 数据库。表 用户名@ip 密码
CREATE USER ‘jeffrey’@‘localhost’ IDENTIFIED BY ‘mypass’;
GRANT ALL ON db1.* TO ‘jeffrey’@‘localhost’;
GRANT SELECT ON db2.invoice TO ‘jeffrey’@‘localhost’;
GRANT USAGE ON . TO ‘jeffrey’@‘localhost’ WITH MAX_QUERIES_PER_HOUR 90;
修改密码:
- SET PASSWORD = PASSWORD(‘ducc1’); --当前用户
root用户: - grant all privileges on xxx.* to ducc@‘localhost’ identified by ‘xxx’;
- 直接修改权限表:
update mysql.user set password=password(‘123’) where user=‘root’ and host=‘localhost’;
flush privileges; - set password for ‘ducc’@‘localhost’= password(‘123’);
Table 13.1. Permissible Privileges for GRANT and REVOKE