Mysql database authorization (authorize a user to a database)

Revoke first sets this user to only have the showdatabase permission grantselect, insert, update, deleteonredmine1. * tojira @ % identifiedbyjira. the syntax of the GRANT command is as follows: GRANTprivileges (columns) ONwhat

The http://www.cnblogs.com/jifeng/archive/2011/03/06/1972183.html first sets this user to only grant the show database permission GRANT select, insert, update, delete on redmine1. * to jira @ % identified by jira; the syntax of the grant command statement is as follows: GRANT privileges (columns) ON what

Http://www.cnblogs.com/jifeng/archive/2011/03/06/1972183.html

First, set the show database permission for this user.

Grant select, insert, update, delete on redmine1. * to jira @ "%" identified by "jira ";

The syntax of the GRANT statement is as follows:
GRANT privileges (columns)
ON what
TO user IDENTIFIEDBY "password"
WITH GRANT OPTION
User authorization
Mysql> grant rights on database. * to user @ host identified by "pass ";
Example 1:
Add a user named "test1" with the password "abc" so that he can log on to any host and have the permission to query, insert, modify, and delete all databases.
Grant select, insert, update, delete on *. * to test1 @ "%" Identified by "abc ";
*. * In the ON Clause indicates "all databases, All Tables"
Example 2:
Add a user named test2 "abc" so that the user can only log on to localhost and query, insert, modify, and delete the Database "mydb.
Grant select, insert, update, delete on mydb. * to test2 @ localhost identified by "abc ";
Example 3
Add a User custom to connect from the host localhost, server. domain, and whitehouse.gov. He only wants to access the bankaccount database from localhost, the expenses database from whitehouse.gov, and the customer database from all three hosts. He wants to use the password stupid from all three hosts.

To use the GRANT statement to set permissions for individual users, run these commands:
Shell> mysql -- user = root mysql
Mysql> grant select, INSERT, UPDATE, DELETE, CREATE, DROP
ON bankaccount. * TO custom @ localhost identified by 'topid ';
Mysql> grant select, INSERT, UPDATE, DELETE, CREATE, DROP
ON expenses. * TO custom@whitehouse.gov identified by 'stupid ';
Mysql> grant select, INSERT, UPDATE, DELETE, CREATE, DROP
ON customer. * TO custom @ '%' identified by 'stupid ';
========================================================== ======
Permission information is stored in the mysql database using the user, db, host, tables_priv, and columns_priv tables ).
Permission 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 file access on the server

1. The select, insert, update, and delete permissions allow you to perform operations on an existing table in a database. They are basic permissions.
2. alter permission allows you to use ALTER TABLE
3. the 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, this user can discard the database that stores the MySQL access permission!
4. grant permissions allow you to grant your own permissions to other users.
You cannot explicitly specify that a given user should be denied access. That is, you cannot explicitly match a user and then reject the connection. You cannot specify a user with the right to create or discard a table in a database, nor create or discard the database itself. You can list multiple granted permissions at the same time.
For example, if you want to allow users to read and modify the content of an existing table, but do not allow users to create or delete a new table, perform the following authorization:
Grant select, INSERT, DELETE, update on samp_db. * TO 'user' @ '%' IDENTIFIEDBY "pass"

The above is the text I copied from other places and then slightly modified. Below I will write something worth attention.

Why Grant all on db. * to user identified by "pass", ERROR 1045 (28000): Access denied for user 'user' @ 'localhost' (using password: YES) error message?

The solution is as follows: run the 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 adding @ '%', '%' includes any host in name, (% must be enclosed by quotation marks, otherwise, put together with @ may not be identified .) However, sometimes (in some versions) '%' does not include localhost. You must assign a value to @ 'localhost' separately.