mysql用户与权限管理

查看存在哪些用户
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 [email protected]’%’;
±---------------------------------------------------------------------------------------------------------------+
| Grants for [email protected]% |
±---------------------------------------------------------------------------------------------------------------+
| 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(‘[email protected]’),‘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 [email protected]’%’ 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;

修改密码：

1. SET PASSWORD = PASSWORD(‘ducc1’); --当前用户
   root用户：
2. grant all privileges on xxx.* to [email protected]‘localhost’ identified by ‘xxx’;
3. 直接修改权限表：
   update mysql.user set password=password(‘123’) where user=‘root’ and host=‘localhost’;
   flush privileges;
4. set password for ‘ducc’@‘localhost’= password(‘123’);

Table 13.1. Permissible Privileges for GRANT and REVOKE