Mysql user password modification, user addition, deletion, and permission setting, mysql user password Modification

Mysql user password modification, user addition, deletion, and permission setting, mysql user password Modification

The username and password used in the following example are: test, 111111

 

MysqlPassword modification:

Mysql requires the root permission to change the password. Run mysql-uroot-p (password) first );

1) use the set password method to change the account password:

Set password for 'hzd' @ 'localhost' = password ('newpassword ');

2) use phpMyAdmin to modify the user table of the mysql database directly using SQL statements, but do not forget to use the PASSWORD function to Insert the user using the Insert command and modify the user using the Update command, delete: Use the Delete command.

 

MysqlUser addition and authorization:

1) insert:

Log on to mysql with the root permission and create a test user.

Mysql> Insert into mysql. user (Host, User, Password) values ('localhost', 'test', password ('123 '));

Refresh the system permission table mysql> flush privileges;

If an error is reported

#1364-Field 'ssl _ cipher 'doesn' t have a default value

Change the MySQL configuration file to my. cnf in Linux and my. ini in Windows.

SQL _mode = NO_ENGINE_SUBSTITUTION, STRICT_TRANS_TABLES

Change

SQL _mode = NO_ENGINE_SUBSTITUTION

Restart MySQL Service

2) grant:

Log on to mysql with the root permission and create a test user.

Mysql> grant usage on *. * to 'test' @ 'localhost' identified by '123 ';

Authorize the test user

Mysql> grant all on 'hzd'. * to 'test' @ 'localhost' identified by "111111 ";

It means that the authorized user 'test' (whose password is 111111) can log on to any host and perform all operations on all tables in the database 'hzd.

 

The following are some SQL statements about authorization:

The syntax of the grant statement is as follows:

Grant privileges (columns) on what to user identified by "password" with grant option

 

1) grant select, insert on '*' to test @ 'local' identified by '123 ';

This command means that the authorized user "test" (whose password is "111111") can only log on to the local localhost, and can access any table ("*. * "the first" * "represents any database, and the last" * "represents any table under the constraints of the previous" * ") performs select and insert operations.

 

2) grant update, delete on scut. student to test @ "%" Identified by "123 ";

This command means that the authorized user "test" (whose password is "123") can log on to any host and update the table "student" under the Database "scut, delete operation;

 

3) grant all on len. * to ca @ "%" Identified by "123456 ";

This command means that the authorized user "ca" (with the password "123456") can log on to any host and perform all operations on all tables under the Database "len;

 

User deletion:

Drop user test @ localhost

 

MysqlView authorization:
Select user from mysql. db where db = "youdb"
Instance: View authorized users in the len Database
Select user from mysql. db where db = "len"

View User Permissions:

Show grants for your users

For example:
Show grants for test @ 'localhost ';

 

Database/Data Table/Data column permission:Alter: Modify existing data tables (such as adding/deleting columns) and indexes.
Create: Create a new database or data table.
Delete: Delete table records.
Drop: delete a data table or database.
INDEX: Create or delete an INDEX.
Insert: Add Table records.
Select: displays/searches for table records.
Update: Modify existing records in the table.

Global Management permissions:

File: read and write files on the MySQL server.
PROCESS: displays or kills service threads of other users.
RELOAD: RELOAD Access Control tables and refresh logs.
SHUTDOWN: Shut down the MySQL service.

Special permissions:

ALL: allow anything (same as root ).
USAGE: Only logon is allowed. Other operations are not allowed.

Appendix: Permission table column name explanation Scope
Select Select_priv the select permission table is required only when a table is actually retrieved.
Insert Insert_priv allows you to insert a new row into an existing table.
Update Update_priv allows you to use the new value to update the columns in the row of an existing table.
Delete Delete_priv allows you to delete row tables that meet the conditions.
Create Create_priv allows you to create new databases and table databases, tables, or indexes.
Drop Drop_priv discard (delete) existing database and table database or table
Reload Reload_priv allows you to tell the server to read the authorization table again. Server Management
Shutdown Shutdown_priv may be abused (by terminating the server and rejecting other user services) Server Management
Process Process_priv allows you to view the common 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 files from the server to the database table server for file Access
Grant Grant_priv allows you to grant your own permissions to other user databases or tables.
References References_priv allows you to open and close the record file database or table
Index Index_priv allows you to create or discard (delete) index tables.
Alter Alter_priv allows you to change the table, which can be used to overturn the permission system table by renaming the table.