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