How the enterprise gives MySQL user rights

MySQL create user and give user permission

Methods commonly used by operations personnel:

Mysql> Grant all on xiaohu.* to ' [e-mail protected] ' identified by ' 123.com ';

Library. Table

Query OK, 0 rows affected (0.01 sec)

Grant syntax

Grant all privileges the dbname.* to [e-mail protected] identified by ' password '

For example, the user who created the boy, the QiQi library has all the permissions, allow to log on from the localhost host management database, password is 123.com

Mysql> Grant all privileges the qiqi.* to [e-mail protected] identified by ' 123.com ';

Query OK, 0 rows Affected (0.00 sec)

mysql> flush Privileges; Refresh Permissions

Query OK, 0 rows Affected (0.00 sec)

Mysql> select User,host from Mysql.user;

+----------------+-----------------+

| user | Host |

+----------------+-----------------+

| [Email protected] | %               |

| Root | 127.0.0.1 |

| | localhost |

| Boy | localhost |

| Root | localhost |

| | master.test.com |

| Root | master.test.com |

+----------------+-----------------+

7 Rows in Set (0.00 sec)

Mysql> Show grants for [email protected]; View Permissions

+------------------------------------------------------------------------------------------------------------+

| Grants for [email protected] |

+------------------------------------------------------------------------------------------------------------+

| GRANT USAGE on *. * to ' boy ' @ ' localhost ' identified by PASSWORD ' *ac241830ffddc8943ab31cbd47d758e79f7953ea ' |

| GRANT all privileges in ' QiQi '. * to ' boy ' @ ' localhost ' has created permissions on the QiQi library |

+------------------------------------------------------------------------------------------------------------+

2 rows in Set (0.00 sec)

The MySQL user can authorize permissions:

The above mentioned that boy has all the authority how to withdraw?

Revoke REVOKE permission syntax

Revoke INSTRT on * * from ' boy ' @ ' localhost ';

Mysql> Revoke insert on qiqi.* Note Be sure to see which library to receive permissions from ' Boy ' @ ' localhost ';

Query OK, 0 rows Affected (0.00 sec)

Mysql> Show grants for [email protected];

+-------------------------------------------------------------------------------------------------------------- --------------------------------------------------------------------------------------------------------------- +

|                                                                                                                                                                                                    Grants for [email protected] |

+-------------------------------------------------------------------------------------------------------------- --------------------------------------------------------------------------------------------------------------- +

|                                                                                                                  GRANT USAGE on * * to ' boy ' @ ' localhost ' identified by PASSWORD ' *ac241830ffddc8943ab31cbd47d758e79f7953ea ' |

| GRANT SELECT, UPDATE, DELETE, create, DROP, REFERENCES, INDEX, ALTER, CREATE temporary TABLES, LOCK TABLES, EXECUTE, creat E view, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, EVENT, TRIGGER on ' QiQi '. * to ' boy ' @ ' localhost ' |

+-------------------------------------------------------------------------------------------------------------- --------------------------------------------------------------------------------------------------------------- +

Above is Grant's permission

2 rows in Set (0.00 sec)

How is the enterprise production environment authorized?

1 blog, CMS and other product database authorization:

If the Web connection user tries to adopt the minimization principle, such as select (query) insert (INSERT) Update (Modify) Delete (delete) also need Create,drop permission

Must have permission to retract after generating the data table

Revoke Insert on * * from ' boy ' @localhost '

How the enterprise gives MySQL user rights