一、mysql修改用户密码方法:
方法一:mysqladmin -u username -h host -p password 'new_password';
方法二:mysql>set password for 'username'@'host'=password('new_password');
方法三:mysql>update mysql.user set password=password('new_password')
where User='username';
mysql>flush privileges;
二、mysql创建用户
mysql>create user 'username'@'host' identified by 'password';
三、mysql删除用户
mysql>drop user 'username'@'host';
四、mysql用户重命名
mysql>RENAME USER old_user TO new_user;
五、授权(grant,revoke)
1、查看用户授权信息
mysql>show grants for 'username'@'host';
2、授权
官方文档:http://dev.mysql.com/doc/refman/5.7/en/grant.html
GRANT priv_type [(column_list)]
[, priv_type [(column_list)]] ...
ON [object_type] priv_level
TO user_specification [, user_specification] ...
[REQUIRE {NONE | tls_option [[AND] tls_option] ...}]
[WITH {GRANT OPTION | resource_option} ...]
object_type: {
TABLE
| FUNCTION
| PROCEDURE
}priv_level: {
*
| *.*
| db_name.*
| db_name.tbl_name
| tbl_name
| db_name.routine_name}user_specification: user [ auth_option ]auth_option: { # Before MySQL 5.7.6
IDENTIFIED BY 'auth_string'
| IDENTIFIED BY PASSWORD 'hash_string'
| IDENTIFIED WITH auth_plugin
| IDENTIFIED WITH auth_plugin AS 'hash_string'
}auth_option: { # As of MySQL 5.7.6
IDENTIFIED BY 'auth_string'
| IDENTIFIED BY PASSWORD 'hash_string'
| IDENTIFIED WITH auth_plugin
| IDENTIFIED WITH auth_plugin BY 'auth_string'
| IDENTIFIED WITH auth_plugin AS 'hash_string'
}tls_option: {
SSL
| X509
| CIPHER 'cipher'
| ISSUER 'issuer'
| SUBJECT 'subject'
}resource_option: {
| MAX_QUERIES_PER_HOUR count
| MAX_UPDATES_PER_HOUR count
| MAX_CONNECTIONS_PER_HOUR count
| MAX_USER_CONNECTIONS count}
Privilege | Meaning and Grantable Levels |
---|---|
ALL [PRIVILEGES] | Grant all privileges at specified access level except GRANT OPTION |
ALTER | Enable use of ALTER TABLE . Levels: Global, database, table. |
ALTER ROUTINE | Enable stored routines to be altered or dropped. Levels: Global, database, procedure. |
CREATE | Enable database and table creation. Levels: Global, database, table. |
CREATE ROUTINE | Enable stored routine creation. Levels: Global, database. |
CREATE TABLESPACE | Enable tablespaces and log file groups to be created, altered, or dropped. Level: Global. |
CREATE TEMPORARY TABLES | Enable use of CREATE TEMPORARY TABLE . Levels: Global, database. |
CREATE USER | Enable use of CREATE USER , DROP USER , RENAME USER , and REVOKE ALL PRIVILEGES . Level: Global. |
CREATE VIEW | Enable views to be created or altered. Levels: Global, database, table. |
DELETE | Enable use of DELETE . Level: Global, database, table. |
DROP | Enable databases, tables, and views to be dropped. Levels: Global, database, table. |
EVENT | Enable use of events for the Event Scheduler. Levels: Global, database. |
EXECUTE | Enable the user to execute stored routines. Levels: Global, database, table. |
FILE | Enable the user to cause the server to read or write files. Level: Global. |
GRANT OPTION | Enable privileges to be granted to or removed from other accounts. Levels: Global, database, table, procedure, proxy. |
INDEX | Enable indexes to be created or dropped. Levels: Global, database, table. |
INSERT | Enable use of INSERT . Levels: Global, database, table, column. |
LOCK TABLES | Enable use of LOCK TABLES on tables for which you have the SELECT privilege. Levels: Global, database. |
PROCESS | Enable the user to see all processes with SHOW PROCESSLIST . Level: Global. |
PROXY | Enable user proxying. Level: From user to user. |
REFERENCES | Enable foreign key creation. Levels: Global, database, table, column. |
RELOAD | Enable use of FLUSH operations. Level: Global. |
REPLICATION CLIENT | Enable the user to ask where master or slave servers are. Level: Global. |
REPLICATION SLAVE | Enable replication slaves to read binary log events from the master. Level: Global. |
SELECT | Enable use of SELECT . Levels: Global, database, table, column. |
SHOW DATABASES | Enable SHOW DATABASES to show all databases. Level: Global. |
SHOW VIEW | Enable use of SHOW CREATE VIEW . Levels: Global, database, table. |
SHUTDOWN | Enable use of mysqladmin shutdown. Level: Global. |
SUPER | Enable use of other administrative operations such as CHANGE MASTER TO , KILL , PURGE BINARY LOGS , SET GLOBAL , and mysqladmin debugcommand. Level: Global. |
TRIGGER | Enable trigger operations. Levels: Global, database, table. |
UPDATE | Enable use of UPDATE . Levels: Global, database, table, column. |
USAGE | Synonym for “no privileges” |
eg:
GRANT priv_type ON *.* TO 'someuser'@'somehost' WITH GRANT OPTION;
3、取消授权(revoke)
REVOKE priv_type [(column_list)]
[, priv_type [(column_list)]] ...
ON [object_type] priv_level
FROM user [, user] ...
REVOKE ALL PRIVILEGES, GRANT OPTION
FROM user [, user] ...
eg:
REVOKE INSERT ON *.* FROM 'jeffrey'@'localhost';
REVOKE ALL PRIVILEGES, GRANT OPTION FROM user [, user] ...