To modify the password and access restrictions in Mysql settings method detailed _mysql

Source: Internet
Author: User
Tags flush require create database
Due to the openness and stability of its source code, and the perfect combination of the popularity of the site with the "Americium" PHP, many sites now use it as a back-end database, so that it has been widely used. Security considerations require that each user be assigned access restrictions to different databases to meet the requirements of different users. The following is discussed separately for your reference.
A, MySQL modified password method summary
The first thing to say is: Under normal circumstances, to modify the MySQL password is required to have the root of MySQL, so the average user can not change the password, unless the administrator to help modify.
Method One
Using phpMyAdmin
(graphical management of MySQL database Tools), this is the simplest, directly with SQL statements to modify the MySQL Database Library user table, but do not forget to use the password function, insert the user with the Insert command, modify the user with the update command, delete the deletion with the delete command. A detailed description of the data table User field is available later in this section.
  Method Two
Use Mysqladmin. Input
Mysqladmin-u root-p OldPassword newpasswd
After executing this command, you need to enter the original password of root, so that the root password will be changed to NEWPASSWD. Again, change the command root to your username, and you can change your password.
Of course, if your mysqladmin doesn't connect to MySQL,
Server, or you have no way to execute mysqladmin, this method is invalid, and mysqladmin cannot empty the password.
The following methods are used at the MySQL prompt and must have the root permissions of MySQL:
  Method Three
Mysql> INSERT into Mysql.user (Host,user,password) VALUES
('% ', ' system ', PASSWORD (' manager '));
Mysql> FLUSH Privileges
Specifically, this is adding a user named system with a password of manager. Note that you want to use the password function, and then use the flush
Privileges to perform the confirmation.
method Four
And method Three, just use the Replace statement
Mysql> REPLACE into Mysql.user (Host,user,password)
VALUES ('% ', ' system ', PASSWORD (' manager '));
Mysql> FLUSH Privileges
  Method Five
Using the Set Password statement
Mysql> SET PASSWORD for system@ "%" = PASSWORD (' manager ');
You must also use the password () function, but you do not need to use flush privileges to perform the validation.
   Method Six
Use Grant ... Identified by statement to authorize.
Mysql> GRANT USAGE on *.* to system@ "%" identified by ' manager ';
This password () function is unnecessary and does not require the use of flush privileges to perform validation.
Note: PASSWORD () function is to encrypt password, in the program MySQL automatic interpretation.
  the method of setting access restrictions in MySQL
We use two methods to set up the user.
Enter the MySQL execution directory (usually C:\mysql\bin). Enter Mysqld-shareware.exe, enter MySQL
--user=root MySQL, otherwise you cannot add new users. Go to the mysql> prompt to do the operation.
Let's say we're going to build a superuser, the username is system, and the user password is manager.
  Method One
With the grant command authorization, enter the following code:
Mysql>grant all privileges in *.* to system@localhost identified by
' manager ' with GRANT OPTION;
Should be shown: Query OK, 0 rows affected (0.38 sec)
Method Two
To set each permission for a user:
Mysql>insert into user
VALUES (' localhost ', ' system ', PASSWORD (' manager '),
' Y ', ' y ', ' y ', ' y ', ' y ', ' y ', ' y ', ' y ', ' y ', y ', ' y ', ' y ', ' y ', y ';
For 3.22.34 version of MySQL, here are 14 "Y" with the appropriate permissions (in order of field):
The Permission table column name explains the use scope appropriately
Select Select_priv requires a SELECT permission table only when it is actually retrieved from a table
Insert Insert_priv allows you to insert new rows into a table in an existing table
The update Update_priv allows you to update the list of rows in an existing table with a new value
Delete Delete_priv allows you to delete a row table that satisfies a condition
Create Create_priv allows you to build new database and table databases, tables, or indexes
Drop Drop_priv Discard (delete) existing database and table databases or tables
Reload Reload_priv allows you to tell the server to read back into the Authorization table server administration
Shutdown Shutdown_priv may be abused (denied to other users by terminating the server) server management
Process Process_priv allows you to view the normal text of the currently executing query, including setting or changing password Query server Management
File File_priv permissions can be misused to read any readable files on the server to access files on the database table server
Grant Grant_priv allows you to delegate those permissions you own to other user databases or tables
References References_priv allows you to open and close a log file database or table
Index INDEX_PRIV allows you to create or discard (delete) an indexed table
Alter ALTER_PRIV allows you to change the table, which can be used to override the permission system table by renaming the table
If you create a user with only SELECT, INSERT, UPDATE, and delete permissions, the user is allowed to perform operations only on existing tables in one database.
Here we can create the database we want to use, we directly enter. For example: To create a database named Xinxiku, you can use the following code:
Mysql>create database Xinxiku;
Should be shown: Query OK, 1 row Affected (0.00 sec)
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: 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.