mysql用户操作

1. 创建用户

CREATE USER 'username'@'192.168.1.101_' IDENDIFIED BY '123456'

 

 

 

2. 授权

GRANT privileges ON databasename.tablename TO 'username'@'host'
　　--> GRANT SELECT, INSERT ON test.user TO 'username'@'%';
　　--> GRANT ALL ON *.* TO 'username'@'%';

 

 

 

3. 回收用户权限

REVOKE privilege ON databasename.tablename FROM 'username'@'host';
　　--> REVOKE SELECT ON *.* FROM 'username'@'%';

 

 

4. 设置与更改用户密码

 4.1. SET PASSWORD FOR 'username'@'host' = PASSWORD('newpassword');
      　　--> SET PASSWORD FOR 'username'@'%' = PASSWORD("123456");
 4.2. UPDATE mysql.user SET password=PASSWORD(‘newpassword’) where user=’username’ and host=’host’;

 

 

 

5. 删除用户

 5.1. DROP USER 'username'@'host';
 5.2. DELETE FROM mysql.user where user=’username’ and host=’host’

 

 

 

附表：在MySQL中的操作权限

ALTER	Allows use of ALTER TABLE.
ALTER ROUTINE	Alters or drops stored routines.
CREATE	Allows use of CREATE TABLE.
CREATE ROUTINE	Creates stored routines.
CREATE TEMPORARY TABLE	Allows use of CREATE TEMPORARY TABLE.
CREATE USER	Allows use of CREATE USER, DROP USER, RENAME USER, and REVOKE ALL PRIVILEGES.
CREATE VIEW	Allows use of CREATE VIEW.
DELETE	Allows use of DELETE.
DROP	Allows use of DROP TABLE.
EXECUTE	Allows the user to run stored routines.
FILE	Allows use of SELECT... INTO OUTFILE and LOAD DATA INFILE.
INDEX	Allows use of CREATE INDEX and DROP INDEX.
INSERT	Allows use of INSERT.
LOCK TABLES	Allows use of LOCK TABLES on tables for which the user also has SELECT privileges.
PROCESS	Allows use of SHOW FULL PROCESSLIST.
RELOAD	Allows use of FLUSH.
REPLICATION	Allows the user to ask where slave or master
CLIENT	servers are.
REPLICATION SLAVE	Needed for replication slaves.
SELECT	Allows use of SELECT.
SHOW DATABASES	Allows use of SHOW DATABASES.
SHOW VIEW	Allows use of SHOW CREATE VIEW.
SHUTDOWN	Allows use of mysqladmin shutdown.
SUPER	Allows use of CHANGE MASTER, KILL, PURGE MASTER LOGS, and SET GLOBAL SQL statements. Allows mysqladmin debug command. Allows one extra connection to be made if maximum connections are reached.
UPDATE	Allows use of UPDATE.
USAGE	Allows connection without any specific privileges.