Create a MySQL user to assign permissions to a specified library table flush privileges to take effect!!!

update

ERROR 1364 (HY000): Field ‘ssl_cipher‘ doesn‘t have a default value

建议使用GRANT语句进行授权，语句如下：

grant all privileges on *.* to [email protected]‘%‘ identified by "root";

---------------------------------------------------

GRANT SELECT,INSERT,UPDATE,DELETE,CREATE,DROP

ON maildb.*

TO ‘mail‘@‘localhost‘

IDENTIFIED by ‘mailPASSWORD ‘;

Create a snort database, create snort users, and give users snort all the permissions of all the tables in the Snort library.
mysql> CREATE database snort;
Query OK, 1 row affected (0.06 sec)
Create a database

mysql> use MySQL;
Go to MySQL Library
mysql> INSERT INTO User (Host,user,password) VALUES ("localhost", "snort", Password ("112233445566"));
Create user, set initial password
mysql> flush Privileges;
Query OK, 0 rows Affected (0.00 sec)
make the change effective;Note: If you do not execute the instruction, subsequent instructions cannot be executed properly.
Mysql> Grant all on snort.* to ' snort ' @ ' localhost ';
Query OK, 0 rows Affected (0.00 sec)
Give snort users all the permissions to snort library
mysql> flush Privileges;
Query OK, 0 rows Affected (0.00 sec)
Make changes effective
Mysql> Show grants for [email protected];
+-------------------------------------------------------------------------------------+
| Grants for [email protected] |
+-------------------------------------------------------------------------------------+
| GRANT USAGE on * * to ' snort ' @ ' localhost ' identified by PASSWORD ' 1e6b29186dd45e97 ' |
| GRANT all Privileges "snort". * to ' snort ' @ ' localhost ' |
+-------------------------------------------------------------------------------------+
2 rows in Set (0.00 sec)

The simple format that MySQL gives to user rights commands can be summarized as: Grant permissions on database objects to users

A grant general data user, the right to query, insert, UPDATE, delete all table data in the database.
Grant SELECT on testdb.* to [email protected] '% '
Grant insert on testdb.* to [email protected] '% '
Grant update on testdb.* to [email protected] '% '
Grant Delete on testdb.* to [email protected] '% '

Alternatively, replace it with a MySQL command:
Grant SELECT, INSERT, UPDATE, delete on testdb.* to [email protected] '% '

II. Grant Database Developer, creating tables, indexes, views, stored procedures, functions ... and other permissions.
Grant creates, modifies, and deletes MySQL data table structure permissions.

Grant create on testdb.* to [email protected] ' 192.168.0.% ' ;
Grant alter on testdb.* to [email protected] ' 192.168.0.% ' ;
Grant drop on testdb.* to [email protected] ' 192.168.0.% ' ;

Grant operates MySQL foreign key permissions.

Grant references on testdb.* to [email protected] ' 192.168.0.% ' ;

Grant operates MySQL temp table permissions.

Grant create temporary tables on testdb.* to [email protected] ' 192.168.0.% ' ;

Grant operates MySQL index permissions.

Grant index on testdb.* to [email protected] ' 192.168.0.% ' ;

Grant operates the MySQL view, viewing the view source code permissions.

Grant CREATE view on testdb.* to [email protected] ' 192.168.0.% ' ;
Grant Show view on testdb.* to [email protected] ' 192.168.0.% ' ;

Grant operates MySQL stored procedures, function permissions.

grant create routine on testdb.* to [email  Protected] ' 192.168.0.% ' ; --now, can show procedure status
Grant alter  Routine on testdb.* to [email protected] ' 192.168.0.% ' ; --now, can drop a procedure
Grant execute     & nbsp;  on testdb.* to [email protected] ' 192.168.0.% ' where the keyword "privileges" can be omitted.

Grant Advanced DBA manages permissions for all databases in MySQL.
Grant all on * * to [email protected] ' localhost '

MySQL grant permissions can be used on multiple levels, respectively.
1. Grant acts on the entire MySQL server:

Grant SELECT On * * to [email protected] ;--DBAs can query tables in all databases in MySQL.
Grant All on * * to [email protected] ;--DBA can manage all databases in MySQL
2. Grant acts on a single database:

Grant Select on testdb.* to [email protected] ;--DBAs can query tables in TestDB.

3. Grant acts on a single data table:

Grant SELECT, INSERT, UPDATE, delete on testdb.orders to [email protected] ;

4. Grant acts on the columns in the table:

Grant Select (ID, SE, rank) on Testdb.apache_log to [email protected] ;

5. Grant acts on stored procedures, functions:

Grant execute on procedure testdb.pr_add to ' dba ' @ ' localhost '
Grant execute on function testdb.fn_add to ' dba ' @ ' localhost '
Vi. Viewing MySQL user rights
To view the current user (own) permissions:

Show grants;
To view additional MySQL user rights:

Show grants for [email protected];
Vii. revoke permissions that have been given to MySQL users.
Revoke is similar to Grant's syntax, just replace the keyword "to" with "from":

Grant all on * * to [email protected];
Revoke all on * * from [email protected];
Viii. MySQL Grant, REVOKE user rights considerations
1. Grant, after revoke user rights, the user has to reconnect to the MySQL database for the permission to take effect.

2. If you want to allow authorized users, you can also grant these permissions to other users, you need the option "grant option"

Grant Select on testdb.* to [e-mail protected] with GRANT option;
This feature is generally not available. In practice, database permissions are best managed centrally by DBAs.

Create a MySQL user to assign permissions to a specified library table flush privileges to take effect!!!