MySQL 用户操作之新增、删除、授权、修改等

MySQL 新增用户

创建 test 用户

CREATE USER 'test'@'localhost' IDENTIFIED BY '123456'

创建 test 用户并设置远程访问

CREATE USER 'test'@'%' IDENTIFIED BY '123456'

MySQL 用户授权

授予 test 用户所有表的 select、insert 权限

GRANT SELECT, INSERT ON *.* TO 'test'@'%'

授予 test 用户 table1 表的所有权限

GRANT all ON table1 TO 'test'@'%'

用户的操作权限如 SELECT、INSERT、UPDATE 等,如果要授予所有权限则使用 ALL

创建用户并同时授权

GRANT ALL ON *.* TO 'test'@'localhost' IDENTIFIED BY '123456'

刷新权限

如果希望修改用户权限后在不重启 mysql 的情况下直接生效,使用如下命令

mysql> flush privileges;

修改用户密码

MySQL 5.7推荐使用 ALTER USER 修改密码

// 基本使用
ALTER USER testuser IDENTIFIED BY '123456'

// 修改当前登录用户
ALTER USER USER() IDENTIFIED BY '123456'

// 使密码过期
ALTER USER testuser IDENTIFIED BY '123456' PASSWORD EXPIRE

// 使密码从不过期 
ALTER USER testuser IDENTIFIED BY '123456' PASSWORD EXPIRE NEVER

// 按默认设置过期时间
ALTER USER testuser IDENTIFIED BY '123456' PASSWORD EXPIRE DEFAULT

// 指定过期间隔
ALTER USER testuser IDENTIFIED BY '123456' PASSWORD EXPIRE INTERVAL 90 DAY

// 指定 plugin
ALTER USER testuser IDENTIFIED WITH mysql_native_password BY '123456'

查看当前用户的简要信息

mysql> select host,user,authentication_string,plugin from user;

+-----------+------------------+-------------------------------------------+-----------------------+
| host      | user             | authentication_string                     | plugin                |
+-----------+------------------+-------------------------------------------+-----------------------+
| localhost | root             |                                           | auth_socket           |
| localhost | mysql.session    | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE | mysql_native_password |
| localhost | mysql.sys        | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE | mysql_native_password |
| localhost | debian-sys-maint | *6393E0AA56EC75832112DFBA4CD6A60A659AC14E | mysql_native_password |
+-----------+------------------+-------------------------------------------+-----------------------+
4 rows in set (0.00 sec)

如果要修改的用户 plugin 字段不是 ‘mysql_native_password’,则需要指定 plugin

撤销用户权限

REVOKE all ON *.* FROM 'test'@'%'

删除用户

DROP USER 'test'@'localhost'

查看用户权限

mysql> show grants for 'test'@'%';

+-------------------------------------------------+
| Grants for test@%                               |
+-------------------------------------------------+
| GRANT USAGE ON *.* TO 'test'@'%'                |
| GRANT ALL PRIVILEGES ON `mysql`.* TO 'test'@'%' |
+-------------------------------------------------+
2 rows in set (0.00 sec)
除特殊说明外本人博客均属原创,转载请注明出处:http://blog.johnhan.cn/blog_1017.html
鄂ICP备17018604号-1  鄂公网安备42060702000030号