Details of how to set the password and access restrictions in MySQL
- 2020-05-06 11:47:09
- OfStack
Because of its open source and stability, and with the site popular compilation americium PHP perfect combination, many sites now use it as a back-end database, so that it has been widely used. For security reasons, each user needs to be given access restrictions to different databases to meet the requirements of different users. The following are discussed separately for your reference.
Summary of password modification method
The first thing to note is that, in general, changing the MySQL password requires root permission in mysql, so the average user cannot change the password unless he asks the administrator for help.
method -
Use phpMyAdmin
(graphical MySql database management tools), this is the simplest, directly using the SQL statement to modify the user database mysql table, but do not forget to use the PASSWORD function, insert the user with Insert command, modify the user with Update command, delete with Delete command. The data table user field is described in detail later in this section.
method 2
Use mysqladmin. Enter
mysqladmin -u root -p oldpassword newpasswd
After executing this command, you need to enter the original password of root, so that the password of root will be changed to newpasswd. Also, change the root command to your username and you can change your own password.
Of course, if your mysqladmin is not connected to mysql
server, or you can't execute mysqladmin, then this method is invalid, and mysqladmin can't clear the password.
The following methods are used at the mysql prompt and must have root permission for mysql:
method 3
mysql> INSERT INTO mysql.user (Host,User,Password) VALUES
(' % ', 'system, PASSWORD (' manager'));
mysql> FLUSH PRIVILEGES
To be exact, this is adding a user with the username system and the password manager. Note that you use the PASSWORD function, and then FLUSH
PRIVILEGES to perform the validation.
method 4
As with method 3, the REPLACE statement
is used mysql> REPLACE INTO mysql.user (Host,User,Password)
VALUES (' % ', 'system, PASSWORD (' manager'));
mysql> FLUSH PRIVILEGES
method 5
Use SET PASSWORD statement
mysql> 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 6
Use GRANT ... IDENTIFIED BY statement to authorize.
mysql> * TO system@"%" IDENTIFIED BY BY 'manager';
Here the PASSWORD() function is not necessary, and there is no need to use FLUSH PRIVILEGES to perform the validation.
Note: the PASSWORD() function encrypts the password, and MySql automatically interprets it in the program.
The method for setting access restrictions in ii and MySql is
We set up the user in two ways.
Go to the Mysql execution directory (usually c:\mysql\bin). Enter mysqld-shareware.exe, mysql
--user=root mysql , otherwise no new user can be added. Enter the mysql> Operate at the prompt.
Suppose we want to create a superuser with the username system and the user password manager.
method --
Authorize with the Grant command, enter the following code:
mysql> GRANT ALL PRIVILEGES ON *.* TO system@localhost IDENTIFIED BY
'manager' WITH GRANT OPTION;
Query OK, 0 affected (0.38 sec)
method 2
Set each of the user's permissions:
mysql> INSERT INTO user
VALUES (' localhost ', 'system PASSWORD (' manager),
'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y');
For version 3.22.34 of MySQL, there are 14 "Y" with the following permissions (in field order):
Permission table column name corresponding to
select Select_priv requires select permission table
only if it is actually retrieved from a table insert Insert_priv allows you to insert new rows into an existing table update Update_priv allows you to update the columns of existing tables with new values delete Delete_priv allows you to delete rows that meet the criteria table
create Create_priv allows you to create new databases and tables databases, tables or indexes
drop Drop_priv discard (delete) existing databases and tables databases or tables
reload Reload_priv allows you to tell the server to read in the authorization table server management
shutdown Shutdown_priv can be abused (by terminating the server and refusing to serve other users) server manages
process Process_priv allows you to view the plain text of the currently executed query, including setting or changing the password query server management
file File_priv permission can be abused to read any readable file on the server to the file access on the database table server
grant Grant_priv allows you to delegate those privileges you have to other users of the database or
table references References_priv allows you to open and close the record file database or the table
index Index_priv allows you to create or drop an index
alter Alter_priv allows you to change the table, which can be used to override the permission system table
by renaming the table If the user is created with only select, insert, update, and delete permissions, the user is allowed to operate only on existing tables in a database Now we can create the database we want to use. for example, we want to create the database name XinXiKu, you can use the following code:
mysql> create database XinXiKu;
Query OK, 1 row (0.00 sec)
Summary of password modification method
The first thing to note is that, in general, changing the MySQL password requires root permission in mysql, so the average user cannot change the password unless he asks the administrator for help.
method -
Use phpMyAdmin
(graphical MySql database management tools), this is the simplest, directly using the SQL statement to modify the user database mysql table, but do not forget to use the PASSWORD function, insert the user with Insert command, modify the user with Update command, delete with Delete command. The data table user field is described in detail later in this section.
method 2
Use mysqladmin. Enter
mysqladmin -u root -p oldpassword newpasswd
After executing this command, you need to enter the original password of root, so that the password of root will be changed to newpasswd. Also, change the root command to your username and you can change your own password.
Of course, if your mysqladmin is not connected to mysql
server, or you can't execute mysqladmin, then this method is invalid, and mysqladmin can't clear the password.
The following methods are used at the mysql prompt and must have root permission for mysql:
method 3
mysql> INSERT INTO mysql.user (Host,User,Password) VALUES
(' % ', 'system, PASSWORD (' manager'));
mysql> FLUSH PRIVILEGES
To be exact, this is adding a user with the username system and the password manager. Note that you use the PASSWORD function, and then FLUSH
PRIVILEGES to perform the validation.
method 4
As with method 3, the REPLACE statement
is used mysql> REPLACE INTO mysql.user (Host,User,Password)
VALUES (' % ', 'system, PASSWORD (' manager'));
mysql> FLUSH PRIVILEGES
method 5
Use SET PASSWORD statement
mysql> 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 6
Use GRANT ... IDENTIFIED BY statement to authorize.
mysql> * TO system@"%" IDENTIFIED BY BY 'manager';
Here the PASSWORD() function is not necessary, and there is no need to use FLUSH PRIVILEGES to perform the validation.
Note: the PASSWORD() function encrypts the password, and MySql automatically interprets it in the program.
The method for setting access restrictions in ii and MySql is
We set up the user in two ways.
Go to the Mysql execution directory (usually c:\mysql\bin). Enter mysqld-shareware.exe, mysql
--user=root mysql , otherwise no new user can be added. Enter the mysql> Operate at the prompt.
Suppose we want to create a superuser with the username system and the user password manager.
method --
Authorize with the Grant command, enter the following code:
mysql> GRANT ALL PRIVILEGES ON *.* TO system@localhost IDENTIFIED BY
'manager' WITH GRANT OPTION;
Query OK, 0 affected (0.38 sec)
method 2
Set each of the user's permissions:
mysql> INSERT INTO user
VALUES (' localhost ', 'system PASSWORD (' manager),
'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y');
For version 3.22.34 of MySQL, there are 14 "Y" with the following permissions (in field order):
Permission table column name corresponding to
select Select_priv requires select permission table
only if it is actually retrieved from a table insert Insert_priv allows you to insert new rows into an existing table update Update_priv allows you to update the columns of existing tables with new values delete Delete_priv allows you to delete rows that meet the criteria table
create Create_priv allows you to create new databases and tables databases, tables or indexes
drop Drop_priv discard (delete) existing databases and tables databases or tables
reload Reload_priv allows you to tell the server to read in the authorization table server management
shutdown Shutdown_priv can be abused (by terminating the server and refusing to serve other users) server manages
process Process_priv allows you to view the plain text of the currently executed query, including setting or changing the password query server management
file File_priv permission can be abused to read any readable file on the server to the file access on the database table server
grant Grant_priv allows you to delegate those privileges you have to other users of the database or
table references References_priv allows you to open and close the record file database or the table
index Index_priv allows you to create or drop an index
alter Alter_priv allows you to change the table, which can be used to override the permission system table
by renaming the table If the user is created with only select, insert, update, and delete permissions, the user is allowed to operate only on existing tables in a database Now we can create the database we want to use. for example, we want to create the database name XinXiKu, you can use the following code:
mysql> create database XinXiKu;
Query OK, 1 row (0.00 sec)