MySQL User management

Transferred from: http://www.cnblogs.com/kissdodog/p/4173337.html

MySQL is divided into regular users and root users. These two types of users have different permissions.

First, the Authority table

When you install MySQL, a database named MySQL is automatically installed. The MySQL database is stored in the permissions table.

　　1. User table

The user table has 39 fields. These fields can be divided into 4 categories:

• User columns;
• permission columns;
• Safety column;
• Resource control columns;

　　1. User List

The user column includes host, user, Password, which represents the hostname, username, and password, respectively. The login is the user name and password;

　　2. Permissions column

The Permissions column for the user table includes fields that end in Priv, such as Select_priv, Insert_priv, and so on.

The values for these fields are only Y and N. Y indicates that the permission can be used on all databases; n indicates that the permission cannot be used on all databases;

In general, you can use the GRANT statement to Wie a user with some permissions or to update the user table by updating the statement to set permissions;

However, after modifying the user table, be sure to execute the flush privileges, otherwise the following error may occur:

　　Error (1133): Can ' t find any matching row in the user table

Column	Description
Select_priv	Determines whether the user can select data with the SELECT command
Insert_priv	Determines whether the user can insert data through the Insert command
Update_priv	Determines whether users can modify existing data with the update command
Delete_priv	Determines whether users can delete existing data by using the Delete command
Create_priv	Determine whether a user can create a new database and table
Drop_priv	Determine whether users can delete existing databases and tables
Reload_priv	Determines whether a user can perform a specific command that refreshes and reloads various internal caches used by MySQL, including logs, permissions, hosts, queries, and tables
Shutdown_priv	Determines whether the user can shut down the MySQL server. You should be very cautious when giving this permission to any user other than the root account
Process_priv	Determines whether users can view other users ' processes through the show processlist command
File_priv	Determine if the user can execute the SELECT INTO outfile and load DATA infile commands
Grant_priv	Determines whether a user can grant another user the privileges already granted to the user
References_priv	Currently just a placeholder for some future features; it's not going to work.
Index_priv	Determine whether users can create and delete table indexes
Alter_priv	Determine whether users can rename and modify table structures
Show_db_priv	Determines whether the user can view the names of all databases on the server, including databases with sufficient access to the user
Super_priv	Determine whether users can perform some powerful administrative functions, such as removing user processes through the KILL command, modifying global MySQL variables using set global, and executing various commands on replication and logs
Create_tmp_table_priv	To determine whether a user can create a temporary table
Lock_tables_priv	Determine if a user can use the Lock Tables command to block access/modification to a table
Execute_priv	Determine whether a user can execute a stored procedure
Repl_slave_priv	Determines whether the user can read the binary log files used to maintain the replicated database environment. This user is located in the primary system and facilitates communication between the host and the client
Repl_client_priv	Determine whether users can determine the location of replication from the server and the primary server
Create_view_priv	Determine whether a user can create a view
Show_view_priv	Determine whether the user can view the view or understand how the view performs
Create_routine_priv	Determine whether a user can change or discard stored procedures and functions
Alter_routine_priv	Determine if a user can modify or delete stored functions and functions
Create_user_priv	Determines whether the user can execute the Create user command, which creates a new MySQL account
Event_priv	Determine whether users can create, modify, and delete events
Trigger_priv	Determine whether a user can create and delete triggers

Update Modify Permissions:

UPDATE user SET Select_priv = ' N ' WHERE Host = '% '

Grant grants permissions

　　3. Safety column

The user table has a security column with 4 fields:

• Ssl_type;
• Ssl_cipher;
• X509_issuer;
• X509_subject;

SSL is used for encryption; The X509 standard can be used to identify users. There is no encryption feature in the normal distribution. You can use the show VARIABLES like ' Have_openssl ' statement to see if you have SSL capabilities. If the value is disabled, then there is no SSL encryption feature.

　　4. Resource Control column

The 4 resource control columns for the user table are:

• Max_questions: How many queries can be allowed to execute per hour;
• Max_updates: How many updates can be allowed per hour;
• Max_connections: How many connections can be established per hour;
• Max_user_connections: The number of connections that a single user can have at the same time.

The default value is 0, which means no limit.

　　2. DB table and Host table

The DB table stores a user's permissions to a database.

The host table stores the operation permissions of a database on a given host, and the DB table provides more granular control over the database-level operation permissions on the specified hosts; but rarely, the new version has canceled the host table;

　　1. User List

The user column for the DB table has 3 fields:

• Host: hostname;
• DB: Database name;
• User: username;

　　2. Permissions column

DB table:

• Create_routine_priv: Whether you have permission to create stored procedures;
• Alter_routine_priv: Whether you have permission to modify stored procedures;

　　The permissions in the user table are for all databases , and if the Select_priv field in the user table has a value of Y, the user can query the tables in all databases;

If you set permissions on the query test table for a user value, the value of the Select_priv field for the Users table is n. This select permission is recorded in the DB table. The value of the Select_priv field in the DB table will be Y.

　　The user obtains permissions based on the contents of the user table, and then obtains the permissions based on the contents of the DB table.

　　3. tables_priv Table and Columns_priv table

　　　Tables_priv: You can set permissions on a single table:

• The Tables_priv table consists of 8 fields:
• Host: hostname;
• DB: Database name;
• User: username;
• TABLE_NAME: Table name
• TABLE_PRIV: Permission to operate on a table (Select,insert,update,delete,create,drop,grant,references,index,alter)
• Column_priv: Permission to manipulate data columns in a table (select,insert,update,rederences);
• Timestamp: Event to modify permissions
• Grantor: Set of permissions

Columns_priv: You can set permissions on a single data column, with 7 columns, as above:

Host, Db, User, TABLE_NAME, COLUMN_NAME, Column_priv, Timestamp.

The MySQL rights assignment is assigned in the order of the table, Columns_priv, table, TABLE_PRIV, DB table, user table.

In the database system, first determine whether the value in the user table is ' y ', and if the value in the user table is ' y ', you do not need to check the subsequent table. If the user table is n, the subsequent table is checked one time.

　　4. Procs_priv table

• The Procs_priv table allows you to set permissions on stored procedures and stored functions.
• The Procs_priv table contains 8 fields, namely:
• Host: hostname;
• DB: Database name;
• User: username;
• Routine_name: Stored procedure or function name;
• Routine_type: Type (value: function or procedure);
• Proc_priv: Permissions owned (execute: EXECUTE; ALTER Routine: Modify; Grant: Permission given);
• Timestamp: The time that the field stores the update;
• Grantor: field set by;

Second, account management

Account management is the most basic content of MySQL user management. This includes logging in, exiting the MySQL server, creating users, deleting users, password management, and Rights management.

The login method is very simple, at this address: http://www.cnblogs.com/kissdodog/p/4154068.html

　　1. New Normal User

There are 3 ways to build a user in the MySQL database:

• Use the Create USER statement to make new users;
• Insert the user directly in the Mysql.user table;
• Use the GRANT statement to create a new user;

　　1. CREATE USER

Creating users with the Create USER statement must have the CREATE user permission. The format is as follows:

CREATE USER user[identified by [PASSWORD] ' PASSWORD '],
[User[identified by [PASSWORD] ' PASSWORD '] ...

Where the user parameter represents the user's account, which is composed of the username and host name (host), the identified by keyword is used to set the user's password, the password parameter represents the user's password, and if the password is a normal string, You do not need to use the password keyword. You can have no initial password.

Example:

CREATE USER ' admin ' @ '% ' identified by ' admin '

After execution, the user table adds a row of records, but the permissions are temporarily all ' N '.

　　2. Create a new normal user with INSERT statement

You can use the INSERT statement to add user information directly to the Mysql.user table. However, you must have insert permissions for the Mysql.user table.

In addition, Ssl_cipher, X509_issuer, x509_subject have no value, you must set the value, otherwise the INSERT statement cannot be executed.

Example:

INSERT into Mysql.user (host,user,password,ssl_cipher,x509_issuer,x509_subject) VALUES ('% ', ' newuser1 ', Password (' 123456 '), ', ', ', ')

After you execute the INSERT, you use the command:

FLUSH Privileges

command to make the user effective.

　　3. Create a new normal user with GRANT statement

When you create a new user with Grant, you are able to authorize the user when the user is created. However, you need to have grant permissions.

The syntax is as follows:

GRANT Priv_type on Database.tableto user[identified by [PASSWORD] ' PASSWORD '][,user [identified by [PASSWORD] ' PASSWORD '] ...]

• Priv_type: The parameter indicates the permission of the new yoghurt;
• Databse.table: The parameter indicates the permission scope of the new user;
• User: Parameter The account of the new user, composed by the username and the host;
• Identified by keyword to set the password;
• Password: new user password;

The GRANT statement can create multiple users at the same time.

GRANT SELECT On * * to [email protected] '% '

The difference between * * and db.* is that. *. * is valid for all databases, so select of the user table becomes Y. The Db.*user table is ' N ' and the DB table is changed.

　　2, delete the ordinary user

　　1. Drop USER statement Delete ordinary users

You need to have drop user permissions. The syntax is as follows:

DROP USER User[,user] ...

User is the one that needs to be deleted, and is comprised of the user name and hostname (host).

DROP USER ' newuser1 ' @ '% '

　　2. Delete statement Remove normal user

You can use the DELETE statement to remove the user's information directly from the Mysql.user table. However, you must have delete permission on the Mysql.user table.

DELETE from mysql.user WHERE Host = '% ' and user = ' admin '

After the deletion is complete, the flush privileges only takes effect.

　　3. Change the password

　　1. Use the mysqladmin command to modify the root user's password

Grammar:

mysqladmin-u-username-p Password "New_password"

The new password (new_password) must be enclosed in parentheses and the single quotation mark will cause an error.

Example:

(Note: This is to be verified, how I do not succeed!) ）

　　2. Modify the user table

The value of the Passwor field in the UPDATE user table can also be used to modify the password;

UPDATE user SET Password = Password (' 123 ') WHERE user = ' MyUser '

FLUSH privileges After the entry into force.

　　3. Use the Set statement to modify the password

After you log on to the MySQL server with the root user, you can use the SET statement to modify the password:

Change your password without a user name

SET PASSWORD = PASSWORD ("123");

To modify another user's password:

SET PASSWORD for ' myuser ' @ '% ' =password ("123456")

For user name @ host name

　　4, Grant statement to modify the password of ordinary users

You must have grant permission to modify the password for a normal user using the GRANT statement.

GRANT Priv_type on database.table to user [identified by [PASSWORD] ' PASSWORD ']

Example:

GRANT SELECT on * * to ' test3 ' @ '% ' identified by ' 123 '

　　4, forget the password solution

If the root user password is lost, it can cause a lot of trouble to the user. However, you can log in to the root user in a special way, and then change the password.

　　1. Start the MySQL service using the--skip-grant-tables option

This option will cause the MySQL server to stop the permissions to judge and any user can access the database.

Under Windows operating system, use Mysqlid or mysqlid-nt to start the MySQL service.

MYSQLD command:

　　Mysqld--skip-grant-tables

MYSQLD-NT command:

　　Mysqld-nt--skip-grant-tables

net start mysql command:

　　net start MySQL--skip-grant-tables

　　2. Log in to the root user and set a new password

　　Mysql-u Root

You must update the user table under the MySQL database with the UPDATE statement, and you cannot use the SET statement because--skip-grant-tables cannot use the SET statement.

After the password is modified, the FLUSH privileges statement loads the permissions table and the new password takes effect.

Third, Rights management

　　1, MySQL's various permissions

Permission Name	Corresponds to a column in the user table	Scope of permissions
CREATE	Create_priv	Database, table, or index
DROP	Drop_priv	Database or table
GRANT OPTION	Grant_priv	database, table, stored procedure, or function
REFERENCES	References_priv	Database or table
Alter	Alter_priv	Modify Table
DELETE	Delete_priv	Delete a table
INDEX	Index_priv	Querying tables with indexes
INSERT	Insert_priv	Insert Table
SELECT	Select_priv	Query table
UPDATE	Update_priv	Update table
CREATE VIEW	Create_view_priv	Create a View
SHOW VIEW	Show_view_priv	View View
ALTER ROUTINE	Alter_routine	Modifying a stored procedure or stored function
CREATE ROUTINE	Create_routine_priv	Create a stored procedure or stored function
EXECUTE	Execute_priv	Executing a stored procedure or stored function
FILE	File_priv	Load a file on the server host
CREATE Temporary TABLES	Create_temp_table_priv	Create a temporary table
LOCK TABLES	Lock_tables_priv	Lock table
CREATE USER	Create_user_priv	Create user
PROCESS	Process_priv	Server Management
RELOAD	Reload_priv	Reload Permissions Table
REPLICATION CLIENT	Repl_client_priv	Server Management
REPLICATION SLAVE	Repl_slave_priv	Server Management
SHOW DATABASES	Show_db_priv	View Database
SHUTDOWN	Shutdown_priv	Shutting down the server
SUPER	Super_priv	Super privilege

　　2. Authorization

The grant syntax is as follows:

GRANT Priv_type [(column_list)] on Database.tableto user [identified by [PASSWORD] ' PASSWORD '][,user [identified by [PASSW] ORD] [password]] ... With With_option[with_option]

• The Priv_type parameter represents the permission type;
• Column_list: The parameter indicates which columns the permission acts on, and is not set on the entire table;
• The user parameter consists of a username and hostname, and the form is "' username ' @ ' hostname '";
• The identified by parameter is used to set the password for the user;
• Password: User new password;

The WITH keyword is followed by one or more with_option parameters. There are 5 options:

• Grant OPTION: Authorized users can assign these permissions to other users;
• Max_queries_per_hour count: Setting does not disappear to allow the execution of count queries;
• Max_updates_per_hour count: Setting each vanishing can allow the execution of count updates;
• Max_connections_per_hour Count: Sets the count of connections that can be established per hour;
• Max_user_connections count: Sets the number of count connections that an individual user can have at the same time;

Example:

GRANT select,update on *    . * to ' myuser ' @ '% ' with GRANT OPTION;

　　3. Revoke Permissions

To revoke a permission is to cancel certain permissions for a user. MySQL uses the REVOKE keyword to set permissions for the user.

The syntax is as follows:

REVOKE priv_type[(column_list)]on Database.tablefrom User[,user]

The basic syntax for a revoke statement that reclaims full permissions is as follows:

REVOKE all privileges,grant OPTION from User[,user] ...

Example: Reclaim Select permissions for user MyUser

REVOKE SELECT on *.*from ' myuser ' @ '% '

Recover all permissions for MyUser:

REVOKE all privileges,grant OPTION from ' myuser ' @ '% '

　　4. View Permissions

The show grants statement is used to view permissions. The user table in the MySQL database also stores the users ' basic permissions.

SELECT * from Mysql.usershow GRANTS

　　

MySQL User management