MySQL User management

Source: Internet
Author: User

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

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.