mysql用户权限时间_Mysql用户权限管理

1. 查看mysql用户

select User,Host,authentication_string from mysql.user;

+------+--------------+-----------------------+

| User | Host | authentication_string |

+------+--------------+-----------------------+

| root | localhost | |

| root | b7d0cf7b6e2f | |

| root | 127.0.0.1 | |

| root | ::1 | |

| | localhost | NULL |

| | b7d0cf7b6e2f | NULL |

+------+--------------+-----------------------+

可以先查看user表中的字段，select自己想要的字段信息，一般有user，host，password(有些版本是authentication_string)

2. 增加用户

create user ‘新用户名‘@‘localhost‘ identified by ‘密码‘;

# 允许所有ip连接

create user ‘新用户名‘@‘%‘ identified by ‘密码‘;

3. 给新用户赋予权限

# 基本格式如下

grant all privileges on 数据库名.表名 to ‘新用户名‘@‘指定ip‘ identified by ‘新用户密码‘ ;

# 允许访问所有数据库下的所有表

grant all privileges on *.* to ‘新用户名‘@‘指定ip‘ identified by ‘新用户密码‘ ;

# 指定数据库下的指定表

grant all privileges on test.test to ‘新用户名‘@‘指定ip‘ identified by ‘新用户密码‘ ;

4. 删除用户

DROP USER username@localhost;

5. 修改用户权限

#设置用户拥有所有权限也就是管理员

grant all privileges on *.* to ‘用户名‘@‘指定ip‘ identified by ‘用户密码‘ WITH GRANT OPTION;

#拥有查询权限

grant select on *.* to ‘用户名‘@‘指定ip‘ identified by ‘用户密码‘ WITH GRANT OPTION;

#其它操作权限说明,select查询 insert插入 delete删除 update修改

#设置用户拥有查询插入的权限

grant select,insert on *.* to ‘用户名‘@‘指定ip‘ identified by ‘用户密码‘ WITH GRANT OPTION;

#取消用户查询的查询权限

REVOKE select ON what FROM ‘用户名‘;

6. 修改后刷新权限

flush privileges;

7. mysql启动命令及连接

# 启动mysql

mysqld

或

mysqld --initialize-insecure

service mysql start

# 连接mysql

mysql -uroot -p