MySQL创建用户与授权

MySQL创建用户与授权

创建用户

SQL语句

create user 'username'@'host' identified by 'password';

说明

• username：新建的用户名。
• host：该用户登录的主机地址，统配符为%，表示任意主机。
• password：该用户的登录密码，可以为空

例子

CREATE USER 'mongo'@'localhost' identified by 'abcd1234';
CREATE USER 'mongo'@'192.168.3.1' identified by 'abcd1234';
CREATE USER 'mongo'@'%' identified by 'abcd1234';
CREATE USER 'mongo'@'%' identified by '';
CREATE USER 'mongo'@'%';

地址的表示实例

• ‘%’
• ‘localhost’
• ‘127.0.0.1’
• ‘192.168.3.%’
• ‘192.168.%’

查看所有的用户

命令

SELECT * FROM mysql.user;
SELECT user,host,password FROM mysql.user;

为用户授权

SQL语句

GRANT privileges ON databaseName.tableName TO 'username'@'host'

说明

• privileges：用户的操作权限，详见附录.权限列表
• databaseName：数据库名
• tableName：表名，通配符*

例子

GRANT SELECT,INSERT ON test.user TO 'mongo'@'%';
GRANT ALL ON *.* TO 'mongo'@'%';

收回用户权限

# 查看用户权限
show grants for username@'host';

# 收回用户权限
revoke 权限列表 on databaseName.tableName from username@'host';

修改用户密码

方法一

SQL语句

UPDATE user set password=password('new_password') where host='curr_host' and user='curr_user';
# 刷新权限
flush privileges;

实例

use mysql;
update user set password=password('123') where user='mongo';
flush privileges;

方法二

注：这种方法无需刷新权限

SQL语句

SET password for username@'host' =password('new_password');

实例

SET password for mongo@'localhost' =password('abc123');

附录

权限列表

ALL ,SELECT,INSERT, UPDATE, DELETE, CREATE, DROP, RELOAD, SHUTDOWN, PROCESS, FILE, REFERENCES, INDEX, ALTER, SHOW DATABASES, SUPER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, REPLICATION SLAVE, REPLICATION CLIENT, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, CREATE USER, EVENT, TRIGGER, CREATE TABLESPACE