User and Rights Management for MySQL

1. User Columns
The user column of the user table includes Host, User, and password, representing the host name, user name, and password, respectively.User and Host are the joint masters of the user table.When a connection is established between a user and a server, the user name, host name and password in the account information entered must match the corresponding field in the user table. Connection establishment is only allowed if all three values match.The values of these three fields are the account information that was saved when the account was created.When you modify a user's password, you are actually modifying the password field value of the user table.

2. Permission Columns
The fields in the permission column determine the user's permissions and describe the operations that are allowed on the data and database globally.This includes common privileges such as query privileges, modify privileges, and advanced privileges such as shutting down the server, super privileges, and loading users.General privileges are used to operate databases; advanced privileges are used for database management.The corresponding permissions in the user table are for all user databases.These field values are of type ENUM and can only be taken as Y and N, Y means that the user has the corresponding permissions, N means that the user does not have the corresponding permissions.Looking at the structure of the user table, you can see that the values of these fields are all N by default.If you want to modify permissions, you can use GRANT or UPDATE statements to change these fields of the user table to modify the user's corresponding permissions.

3. Security Columns
The security column has only six fields, two of which are SSI related, two of which are x509 related, and two of which are authorization plug-in related.SSI is used for encryption; X509 standard can be used to identify users: Plugin field identifies a plug-in that can be used to authenticate users, and if the field is empty, the server uses the built-in authorization authentication mechanism to authenticate users.The SHOW VARIABLES LIKE'have_openssl'statement can be used to query whether the server supports SSI functionality.
4. Resource Control Columns

The fields of the resource control column are used to limit the resources used by users and contain four fields:
1) Max_questions - Number of query operations the user is allowed to perform per hour.
2) Number of update operations that a Max_updates user is allowed to perform per hour.
3) Max_connections - Number of connection operations allowed per hour by the user.
4) Max_user_connections - Number of simultaneous connections allowed by the user.

If the number of user queries or connections exceeds the resource control limit within one hour, the user will be locked until the next hour before the corresponding operation can be performed here.You can use the GRANT statement to update the values of these fields.
Note: If the newly created user cannot log on to the database, he or she may try to refresh the permissions, excluding the permissions error. The command is as follows:

mysql> flush privileges;

When using grant to authorize a user, you can use the following instructions to see which permissions can be authorized to the user:

mysql> SHOW PRIVILEGES;

1. Create a new user using the create user statement
1) Method 1:

mysql> create user 'tom'@'localhost' identified by '123.com';

The above statement creates a tom user, allows only local login, and has a password of 123.com.

However, in the above way, the password is written in plain text (although stored in cipher), but there are some insecurities. The above instructions will generate tom information in the mysq.user table, as follows:

mysql> select * from user where User='tom'\G # Query columns whose User field is tom *************************** 1. row *************************** Host: localhost # Which host is allowed to log in from User: tom # User name # Below are permissions,'N'means no corresponding permissions,'Y' means having corresponding permissions Select_priv: N Insert_priv: N Update_priv: N Delete_priv: N Create_priv: N Drop_priv: N Reload_priv: N Shutdown_priv: N Process_priv: N File_priv: N Grant_priv: N References_priv: N Index_priv: N Alter_priv: N Show_db_priv: N Super_priv: N Create_tmp_table_priv: N Lock_tables_priv: N Execute_priv: N Repl_slave_priv: N Repl_client_priv: N Create_view_priv: N Show_view_priv: N Create_routine_priv: N Alter_routine_priv: N Create_user_priv: N Event_priv: N Trigger_priv: N Create_tablespace_priv: N ssl_type: ssl_cipher: x509_issuer: x509_subject: max_questions: 0 max_updates: 0 max_connections: 0 max_user_connections: 0 plugin: mysql_native_password authentication_string: *AC241830FFDDC8943AB31CBD47D758E79F7953EA # This line is a hashed password for 123.com password_expired: N password_last_changed: 2020-05-16 22:05:58 password_lifetime: NULL account_locked: N 1 row in set (0.00 sec)

2) Method 2:

mysql> select password('123.com'); # Encrypt 123.com +-------------------------------------------+ | password('123.com') | +-------------------------------------------+ | *AC241830FFDDC8943AB31CBD47D758E79F7953EA | +-------------------------------------------+ 1 row in set, 1 warning (0.00 sec) mysql> create user 'zhangyi'@'localhost' # Use the encrypted password to create the user -> identified by password '*AC241830FFDDC8943AB31CBD47D758E79F7953EA';

The above command encrypts the 123.com string first, then when creating the user, write the encrypted password directly, but add the password keyword
Once created, Zhangngyi users can log in to the database using the password 123.com locally.
2. Create a new user using the grant statement

mysql> grant select on test1.* to 'zhanger'@'localhost' identified by '123.com'; Query OK, 0 rows affected, 1 warning (0.00 sec)

The above directive, while creating user zhanger, specifies that it only allows local login with a password of 123.com and that the user has select privileges on all tables in the test01 library.
Verification:

[root@mysql~]# mysql -u zhanger -p123.com mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | test1 | +--------------------+ 2 rows in set (0.00 sec) mysql> use test1; # Cut in to a privileged Library Database changed mysql> show tables; # View all tables +-----------------+ | Tables_in_test1 | +-----------------+ | tb1 | +-----------------+ 1 row in set (0.00 sec) mysql> select * from tb1; # View a table's contents +------+ | id | +------+ | 1 | +------+ 1 row in set (0.00 sec) mysql> update tb1 set id=2 ; # An attempt was made to update the table contents, but they were rejected because they had only select privileges ERROR 1142 (42000): UPDATE command denied to user 'zhanger'@'localhost' for table 'tb1'

1. Delete using DROP USER statement
mysql> drop user zhangyi@localhost;
2. Use delete statement to delete user

mysql> delete from mysql.user where Host='localhost' and user='tom';

1. Use the mysqladmin command at the command line to change the user password

[root@mysql ~]# mysqladmin -uroot -p password '2020.com' # Change root user's password to 2008.com Enter password: # Note, fill in the old password here [root@mysql ~]# mysql -u root -p123 # The old password can no longer be logged in at this time mysql: [Warning] Using a password on the command line interface can be insecure. ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES) [root@mysql ~]# mysql -u root -p2020.com # Sign in with a new password

2. Modify the user table of MySQL database

# Modify root password to 123.com mysql> update mysql.user set authentication_string=password('123.com') where User='root' and Host='localhost'; mysql> flush privileges;

3. Use the set statement to modify the current user's password

mysql> set password=password('2020.com');

4. Root User Modifies Ordinary User Password
1) Use set statements to modify normal users

# Modify zhanger user password to 123 456 mysql> set password for 'zhanger'@'localhost'=password('123456');

2) Use update statements to modify common users

#Modify the zhanger user password to 123.com mysql> update mysql.user set authentication_string=password('123.com') -> where User='zhanger' and Host='localhost'; mysql> flush privileges; # A refresh is required to take effect

3) Use grant statements to modify ordinary user passwords

#Modify the password of the zhanger user to 123 123 and grant select privileges mysql> grant select on *.* to 'zhanger'@'localhost' identified by '123123';

5. Ordinary users change their passwords

[root@mysql ~]# mysql -uzhanger -p123123 # Sign in mysql> set password=password('123.com'); # Modify password to 123.com

6. Revoke the rights of ordinary users
Now that you have written how to authorize the user, here you will write how to revoke the user's existing privileges.The following:

mysql> show grants for zhanger@localhost\G # View user-owned permissions mysql> revoke select on *.* from 'zhanger'@'localhost'; #Revoke the user's select privilege mysql> flush privileges; # Refresh Permissions

16 May 2020, 15:40 | Views: 3330