Account management for MySQL

Account management in MySQL:

1 View all Users:
All user and permission information is stored in the user table in the MySQL database
View the structure of the user table
Desc user\g;

Main fields:
Host: Indicates which hosts are allowed to access
User: Indicates username
Authentication_string: Password after encryption
View All Users:
Select host,user,authentication_string from user;

2 Create an account and grant permissions:
Need to use account login after operation such as using root
Common permissions: Create alter drop INSERT UPDATE Delete select
Assign all permissions: All privileges

Create an account and grant permissions: Grant permission list on database to "user name" @ "Access host" identified by "password";

Example:
1 Login root:mysql-u Root-p
2 Grant all privileges to python.* "py" @ "%" identified by "123";
--All tables in the Python database have all permissions
--user name py,% means can use any IP login
--123 for password
3 Exit Root:quit;
4 Login Py:mysql-u Py-p

3 Authorization: Grant permissions on database to account 1, account 2 ... with GRANT option;


4 See what permissions a user has: Show grants for user name;

5 Back-up permissions: Log in with Root after operation
Revoke a user's permissions: The REVOKE permission list on the database name. * FROM "username" @ "host";

6 Change Password:
Method 1 do not log in, in Terminal: Mysqladmin-u User name-p password "New password"
Method 2 Use root login to modify the user table in the MySQL database
Use the password () function to encrypt the database
Refreshing permissions after modifying a database is only valid

In the root login state:
Change user table:
Update user Set authentication_string = password ("New password") where user = "username";
Refresh permissions:
Flush privileges;

7 Deleting an account:
Method 1 After using root login:
Drop user "username" @ "host";

Method 2 Delete the data in the user table of the MySQL library by using root login
Delete from user where user = "user name";
Permissions must be refreshed after operation: Flush privileges

It is recommended to use Method 1, if the host's letter capitalization, Method 1 will not be valid, and then use Method 2.

Account management for MySQL