MySQL Database authorization (grant a database to a user)

Set the user to show database permission first

Grant Select,insert,update,delete on redmine1.* to [e-mail protected]"%" identified by "Jira";

Add super permissions and allow remote access:

GRANT all privileges on * * to ' myuser ' @ ' 192.168.1.3 ' identified by ' MyPassword ' with GRANT OPTION;

FLUSH privileges;

The syntax for an authorization command grant statement is as follows:
GRANT Privileges (columns)
On what
To user Identifiedby "password"
With GRANT OPTION
Authorization to users
Mysql>grant Rights on database.* to[email protected]Identified by "Pass";
Example 1:
Add a user test1 password to ABC so that he can log on on any host and have access to queries, insertions, modifications, and deletions to all databases.
Grant Select,insert,update,delete on *.[email protected]"%" identified by "ABC";
The *. * specifier in the ON clause means "all databases, all tables"
Example 2:
Add a user test2 password to ABC, so that he can only login on localhost, and can query, insert, modify, delete the database mydb operation.
Grant Select,insert,update,delete on mydb.* to[email protected]Identified by "ABC";
Example 3
Add a user custom, he can connect from host localhost, Server.domain and whitehouse.gov. He only wants to access the BankAccount database from localhost, access the expenses database from whitehouse.gov, and access the customer database from all 3 hosts. He wants to use the password stupid from all 3 hosts.

To set permissions for a user using the GRANT statement, run these commands:
shell> MySQL--user=root MySQL
Mysql> GRANT Select,insert,update,delete,create,drop
On bankaccount.* to[email protected]Identified by ' stupid ';
Mysql> GRANT Select,insert,update,delete,create,drop
On expenses.* to[email protected]Identified by ' stupid ';
Mysql> GRANT Select,insert,update,delete,create,drop
On customer.* to[email protected]'% ' identified by ' stupid ';
==============================================
The permissions information is stored in the MySQL database (in the database named MySQL) with the user, DB, host, Tables_priv, and Columns_priv tables.
Permissions Column Context
Select Select_priv Table
Insert Insert_priv Table
Update Update_priv Table
Delete Delete_priv table
Index INDEX_PRIV Table
Alter ALTER_PRIV table
Create Create_priv database, table, or index
Drop Drop_priv database or table
Grant Grant_priv database or table
References References_priv database or table
Reload Reload_priv Server Management
Shutdown SHUTDOWN_PRIV Server Management
Process Process_priv Server Management
File File_priv access to files on the server

1.select, INSERT, UPDATE, and delete permissions allow you to implement operations on an existing table on a database, which is a basic permission
The 2.alter permission allows you to use ALTER TABLE
3.create and Drop permissions allow you to create new databases and tables, or discard (delete) existing databases and tables if you grant the drop permission of the MySQL database to a user, the user can discard the database where MySQL access is stored!
The 4.grant permission allows you to grant other users the permissions you own.
You cannot clearly specify that a given user should be denied access. That is, you cannot obviously match a user and then refuse to connect. You cannot specify that a user has permission to create or discard a table in a database, nor can it be created or discarded by the database itself. You can list many individual permissions that are granted at the same time.
For example, if you want users to be able to read and modify the contents of an existing table, but not allow you to create a new table or delete a table, you can authorize it as follows:
GRANT select,insert,delete,update on samp_db.* to ' user ' @ '% ' identifiedby ' pass '

The above is I copied from other places to make a change in the text, below to write some of the things you need to pay attention to.

1, why use the grant all on the db.* to the user identified by "pass", the access to the database on the host will also appear error 1045 (28000): Access denied for user ' user ' @ ' lo Calhost ' (using Password:yes) error message?

The solution is as follows: Run command Grant all on db.* to ' user ' @ ' localhost ' identified by ' pass '

The reason is: when the @ option is not added, the effect is the same as the plus @ '% ', '% ' is nominally included in any host, (% must be quoted, otherwise it may not be identified with the @). However, there are times when (some versions) '% ' does not include localhost and is assigned to @ ' localhost ' separately

2. It seems to be the root user when importing SQL

MySQL Database authorization (grant a database to a user)