MySQL users and permissions and examples of how to crack the root password

MySQL Users and Privileges

In MySQL, there is a database that comes with the system called MySQL. After the database is installed, the system comes with several databases, and MySQL is one of them. The MySQL database has a table related to user account permissions called the user table, which contains the created users.

The complete user name in MySQL is formed by user + host name. The host name determines on which host the user can log in.

1. User creation and password modification

1. User creation

create user 'USERNAME'@'HOST' identified by 'PASSWORD';

USERNAME: Username
HOST: Host address
PASSWORD: Password

Example:

MariaDB [(none)]> create user [email protected] identified by 'centos';
Query OK, 0 rows affected (0.01 sec)

MariaDB [(none)]> select user,host,password from mysql.user;
+--------+-----------------------+-------------------------------------------+
| user | host | password |
+--------+-----------------------+-------------------------------------------+
| root | localhost | |
| root | localhost.localdomain | |
| root | 127.0.0.1 | |
| root | ::1 | |
| | localhost | |
| | localhost.localdomain | |
| masuri | 192.168.73.133 | *128977E278358FF80A246B5046F51043A2B1FCED |
+--------+-----------------------+-------------------------------------------+
7 rows in set (0.00 sec)

There are anonymous accounts in MySQL, which can be deleted by running the security hardening script mysql_secure_installation, or they can be deleted manually.

To delete a user:

DROP USER 'USERNAME'@'HOST';

Example:

MariaDB [(none)]> select user,host,password from mysql.user;
+--------+-----------------------+-------------------------------------------+
| user | host | password |
+--------+-----------------------+-------------------------------------------+
| root | localhost | |
| root | localhost.localdomain | |
| root | 127.0.0.1 | |
| root | ::1 | |
| | localhost | |
| | localhost.localdomain | |
| masuri | 192.168.73.133 | *128977E278358FF80A246B5046F51043A2B1FCED |
+--------+-----------------------+-------------------------------------------+
7 rows in set (0.00 sec)

MariaDB [(none)]> DROP USER ''@'localhost';
Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> DROP USER ''@'localhost.localdomain';
Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> select user,host,password from mysql.user;
+--------+-----------------------+-------------------------------------------+
| user | host | password |
+--------+-----------------------+-------------------------------------------+
| root | localhost | |
| root | localhost.localdomain | |
| root | 127.0.0.1 | |
| root | ::1 | |
| masuri | 192.168.73.133 | *128977E278358FF80A246B5046F51043A2B1FCED |
+--------+-----------------------+-------------------------------------------+
5 rows in set (0.00 sec)

2. Password Change

Change the mysql password

SET PASSWORD FOR user = PASSWORD('cleartext password')
UPDATE table SET password = password('cleartext password')

Example:

Change the password for the masuri user

MariaDB [(none)]> SET PASSWORD FOR [email protected] = PASSWORD ('magedu');
Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> select user,host,password from mysql.user;
+--------+-----------------------+-------------------------------------------+
| user | host | password |
+--------+-----------------------+-------------------------------------------+
| root | localhost | |
| root | localhost.localdomain | |
| root | 127.0.0.1 | |
| root | ::1 | |
| masuri | 192.168.73.133 | *6B8CCC83799A26CD19D7AD9AEEADBCD30D8A8664 |
+--------+-----------------------+-------------------------------------------+
#The password has been changed at this time

The root account password is empty. Set a password for the root password. Since it is too troublesome to set one by one, you can also use the modify table operation to change the password.

MariaDB [(none)]> update mysql.user set password=password('centos') where user='root';
Query OK, 4 rows affected (0.01 sec)
Rows matched: 4 Changed: 4 Warnings: 0

MariaDB [(none)]> select user,host,password from mysql.user;
+--------+-----------------------+-------------------------------------------+
| user | host | password |
+--------+-----------------------+-------------------------------------------+
| root | localhost | *128977E278358FF80A246B5046F51043A2B1FCED |
| root | localhost.localdomain | *128977E278358FF80A246B5046F51043A2B1FCED |
| root | 127.0.0.1 | *128977E278358FF80A246B5046F51043A2B1FCED |
| root | ::1 | *128977E278358FF80A246B5046F51043A2B1FCED |
| masuri | 192.168.73.133 | *6B8CCC83799A26CD19D7AD9AEEADBCD30D8A8664 |
+--------+-----------------------+-------------------------------------------+
5 rows in set (0.00 sec)

At this time, the password has been changed but you still cannot log in. You need to refresh the permissions

MariaDB [(none)]> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.00 sec)

2. MySQL Permission Management

Permission management involves multiple categories of permissions, such as management, program, database level, table level, and field level.

Management: whether users can be created, whether the database list can be displayed, whether the configuration file can be reloaded, whether the database can be closed, whether functions related to replication can be executed, whether processes can be managed, whether temporary tables can be created, and whether files in the database can be created.

Programs mainly involve three types of programs: functions, stored procedures, and triggers. For example, you can create, modify, delete, and execute these programs. You can also use permissions at the library, table, and field level: for example, you can add, delete, query, and modify operations in libraries, tables, and fields.

1. Authorization GRANT

When authorizing a user, if the user does not exist, you can create it. Before authorization, you must first confirm that you are an administrator with authorization authority.

GRANT 
 priv_type [(column_list)]
  [, priv_type [(column_list)]] ...
 ON [object_type] priv_level
 TO user_specification [, user_specification] ...
 [REQUIRE {NONE | ssl_option [[AND] ssl_option] ...}]
 [WITH with_option ...]

Example:

Create a WordPress user and authorize it.

MariaDB [(none)]> CREATE DATABASE wordpress;
Query OK, 1 row affected (0.02 sec)

MariaDB [(none)]> GRANT ALL ON wordpress.* TO wpuser@'192.168.73.%' identified by 'mylinuxops';
Query OK, 0 rows affected (0.00 sec)

2. Check the user's permissions

MariaDB [(none)]> show grants for wpuser@'192.168.73.%';
+----------------------------------------------------------------------------------------------------------------------------------+
| Grants for [email protected].% |
+----------------------------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'wpuser'@'192.168.73.%' IDENTIFIED BY PASSWORD '*EC0DBFB480593BB6ED2EC028A4231A72D8137406' |
| GRANT ALL PRIVILEGES ON `wordpress`.* TO 'wpuser'@'192.168.73.%' |
+----------------------------------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)

3. Other options for authorization

MAX_QUESRIES_PER_HOUR count #Maximum number of queries per hour MAX_UPDATES_PER_HOUR count #Maximum number of changes per hour MAX_CONNECTIONS_PER_HOUR count #Maximum number of connections per hour MAX_USER_CONNECTIONS count #Maximum number of user connections

Revoke permissions

REVOKE
 priv_type [(column_list)]
  [, priv_type [(column_list)]] ...
 ON [object_type] priv_level
 FROM user [, user] ...

Example:

MariaDB [(none)]> revoke delete on wordpress.* from wpuser@'192.168.73.%';
Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> show grants for wpuser@'192.168.73.%';
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Grants for [email protected].% |
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'wpuser'@'192.168.73.%' IDENTIFIED BY PASSWORD '*EC0DBFB480593BB6ED2EC028A4231A72D8137406' |
| GRANT SELECT, INSERT, UPDATE, CREATE, DROP, REFERENCES, INDEX, ALTER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, EVENT, TRIGGER ON `wordpress`.* TO 'wpuser'@'192.168.73.%' |
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)
# At this time, wpuser@'192.168.73.%' no longer has the delete permission

MySQL root password cracking

Sometimes you may lose your root password during work. You can use the following methods to retrieve your root password:

The following is a demonstration of how to crack the root password

1. Unable to log in to MySQL due to unknown password

[root@localhost ~]# mysql
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)

2. Cracking

1. Modify the configuration file /etc/my.cnf and add two lines of parameters

skip_grant_tables: Skip the authorization table information. After this option takes effect, you do not need to use a password to use MySQL again, but other remote users can also log in without a password, which has certain risks.

skip_networking: Turn off the network function. If you only enable the skip_grant_tables option, other users can log in to MySQL without a password, which is very dangerous. Therefore, you need to turn off the network function and only allow local users to operate.

[root@localhost ~]# vim /etc/my.cnf
[mysqld]
skip_networking=on #Do not enable network function skip_grant_tables=on #Skip the authorization table [root@localhost ~]# service mysqld restart #After modifying the location file, you need to restart the service Restarting mysqld (via systemctl): [ OK ]

2. Log in to MySQL and change the password

[root@localhost ~]# mysql #You can log in without entering a password. Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 11
Server version: 10.2.23-MariaDB-log Source distribution

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> UPDATE mysql.user SET password=PASSWORD('123456') where user='root'; #Modify the root password Query OK, 4 rows affected (0.01 sec)
Rows matched: 4 Changed: 4 Warnings: 0

3. After the password is modified, the configuration file needs to be restored

Unregister or delete the two options just enabled, and then restart the service

[root@localhost ~]# vim /etc/my.cnf
[mysqld]
#skip_networking=on   
#skip_grant_tables=on   

[root@localhost ~]# service mysqld restart
Restarting mysqld (via systemctl): [ OK ]

4. Log in to MySQL using the new password

[root@localhost ~]# mysql -uroot -p123456 
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 10
Server version: 10.2.23-MariaDB-log Source distribution

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]>

The above is the full content of this article. I hope it will be helpful for everyone’s study. I also hope that everyone will support 123WORDPRESS.COM.

You may also be interested in:

Summary of methods for querying MySQL user permissions
The easiest way to create a new user and grant permissions to MySQL
Detailed explanation of MySQL user and permission management
Detailed explanation of the process of querying user permissions using mysql statements
In-depth explanation of MySQL user account management and permission management
Detailed explanation of the solution to the error in creating a user and granting permissions in mysql8.0
Detailed explanation of MySQL user rights verification and management methods
Example of how to create a local user in mysql and grant database permissions
Detailed explanation of MySQL user rights management
MySql sets the specified user database view query permissions

<<: Detailed explanation of props and context parameters of SetUp function in Vue3

>>: Alibaba Cloud Ubuntu 16.04 builds IPSec service