mysql option=3,mysql基础知识-3

一、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、授权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}PrivilegeMeaning and Grantable LevelsGrant all privileges at specified access level except GRANT OPTION

Enable use of ALTER TABLE. Levels: Global, database, table.

Enable stored routines to be altered or dropped. Levels: Global, database, procedure.

Enable database and table creation. Levels: Global, database, table.

Enable stored routine creation. Levels: Global, database.

Enable tablespaces and log file groups to be created, altered, or dropped. Level: Global.

Enable views to be created or altered. Levels: Global, database, table.

Enable use of DELETE. Level: Global, database, table.

Enable databases, tables, and views to be dropped. Levels: Global, database, table.

Enable use of events for the Event Scheduler. Levels: Global, database.

Enable the user to execute stored routines. Levels: Global, database, table.

Enable the user to cause the server to read or write files. Level: Global.

Enable privileges to be granted to or removed from other accounts. Levels: Global, database, table, procedure, proxy.

Enable indexes to be created or dropped. Levels: Global, database, table.

Enable use of INSERT. Levels: Global, database, table, column.

Enable use of LOCK TABLES on tables for which you have the SELECT privilege. Levels: Global, database.

Enable the user to see all processes with SHOW PROCESSLIST. Level: Global.

Enable user proxying. Level: From user to user.

Enable foreign key creation. Levels: Global, database, table, column.

Enable use of FLUSH operations. Level: Global.

Enable the user to ask where master or slave servers are. Level: Global.

Enable replication slaves to read binary log events from the master. Level: Global.

Enable use of SELECT. Levels: Global, database, table, column.

Enable SHOW DATABASES to show all databases. Level: Global.

Enable use of SHOW CREATE VIEW. Levels: Global, database, table.

Enable use of mysqladmin shutdown. Level: Global.

Enable use of other administrative operations such as CHANGE MASTER TO, KILL, PURGE BINARY LOGS, SET GLOBAL, and mysqladmin debugcommand. Level: Global.

Enable trigger operations. Levels: Global, database, table.

Enable use of UPDATE. Levels: Global, database, table, column.

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] ...