Create and authorize MySQL database

Create and authorize MySQL database

1. view user information in the user table of the mysql database

mysql> use mysqlReading table information for completion of table and column namesYou can turn off this feature to get a quicker startup with -ADatabase changedmysql> 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 in the host column in the user table % matching all hosts localhost will not be resolved to an IP address. Connecting 127.0.0.1 directly through UNIXsocket will be connected through TCP/IP protocol and can only be accessed on the local machine ;:: 1: 1 is compatible with ipv6, indicating 127.0.0.1 of the same ipv4

4. Grant dba users the permission to add, delete, modify, and query specified databases.

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 use flush privileges.

5. You can run the show grants command to view the permissions. If you want to add permissions to the original 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 revoke command can be used to remove related permissions of users.

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)