Detailed explanation of MySQL database creation, user creation and authorization

1. View user related information through the user table of the mysql database

mysql> use mysql
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> select host,user,password from user ;+-----------+------+-------------------------------------------+
| host      | user | password                                  |
+-----------+------+-------------------------------------------+
| localhost | root | *87F2746835A04895BB77E12AA5054A767******* |
| qxyw      | root |                                           |
| 127.0.0.1 | root |                                           |
| localhost |      |                                           |
| qxyw      |      |                                           |
+-----------+------+-------------------------------------------+
5 rows in set (0.00 sec)

2. Create a database

mysql> create database [databasename] default character set utf8 collate utf8_general_ci;
Query OK, 1 row affected (0.00 sec)

3. Create a user

mysql> create user 'dba'@'%' identified by '*******';
Query OK, 0 rows affected (0.00 sec)

The meaning of the value of the host column in the user table

% Matches all hosts

localhost localhost will not be parsed into an IP address and will be connected directly through UNIX socket

127.0.0.1 will be connected through TCP/IP protocol and can only be accessed locally;

::1 ::1 is compatible with ipv6, which means the same as 127.0.0.1 of ipv4

4. Give the dba user the permission to add, delete, modify and check the specified database

mysql> grant select,insert,update,delete,create on [databasename].* to dba;
Query OK, 0 rows affected (0.00 sec)

Note: After modifying the permissions, you must refresh the service or restart the service. To refresh the service, use: FLUSH PRIVILEGES

5. You can check the permissions through the show grants command. If you want to update the service based on the original To increase the permissions, continue to execute grant

mysql> grant drop on [databasename].* to dba;
Query OK, 0 rows affected (0.00 sec)
mysql> show grants for dba;+----------------------------------------------------------------------------------------------------+
| Grants for dba@%                                                                                   |
+----------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'dba'@'%' IDENTIFIED BY PASSWORD '*****************************************' |
| GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP ON `[databasename]`.* TO 'dba'@'%'              |
+----------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)

6. The user’s relevant permissions can be removed through the revoke command

mysql> revoke drop on [databasename].* from dba;
Query OK, 0 rows affected (0.00 sec)
mysql> show grants for dba;+----------------------------------------------------------------------------------------------------+
| Grants for dba@%                                                                                   |
+----------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'dba'@'%' IDENTIFIED BY PASSWORD '*****************************************' |
| GRANT SELECT, INSERT, UPDATE, DELETE, CREATE ON `[databasename]`.* TO 'dba'@'%'                    |
+----------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)

The above is the detailed content of Detailed explanation of MySQL database creation, user creation and authorization. For more information, please follow other related articles on the PHP Chinese website!