MySQL User and Rights management

The main function of the MySQL privilege system is to verify the user connected to a given host and give the user relevant DML,DQL permissions on the database. MySQL access control contains 2 stages, one is that the server checks whether you are allowed to connect, and the other is that you can connect and the server checks every request you make. See if you have enough permissions to implement it. This article mainly describes the MySQL privilege system related to user creation, authorization, revoke permissions and so on.

1. Get help with Rights management

[Email protected] [(None)]> Help account managementfor more information, type ' help <item> ', where <item> is one of the Follo Wingtopics:you asked for help on Help category: ' Account Management '   CREATE user   DROP user   GRANT   RENAME USER   REVOKE   SET PASSWORD

--Create user's syntax [email protected][(none)]> help create user; Name: ' CREATE user ' Description:Syntax:CREATE user user_specification [, user_specification] ... user_specification:use R [| Identified with Auth_plugin [as ' auth_string '] identified by [PASSWORD] ' PASSWORD ']create the user command creates a new account and can also be considered Its specified password. The command adds a record to the user table. This command grants only the usage permission. Further authorization is required using the grant command. You can also use the grant command to create an account directly to see subsequent demos. The following is an explanation of usage in the official MySQL manual. The USAGE privilege specifier stands for "no privileges." It is used at the global level withgrant to modify account attributes such as resource limits or SSL characteristics Witho UT affectingexisting account privileges.--Current demo environment [email protected][(none)]> show variables like ' version '; +--- ------------+------------+| variable_name | Value |+---------------+------------+| Version | 5.5.39-log |+---------------+------------+--Create new User (no password specified) [email protected][(none)]> create user ' fred ' @ ' localhost '; Query OK, 0 rows Affected (0.00 sec)--Specify password to create a new user,% indicatesAny, that is, Frank can access the database from any host [email protected][(none)]> create user ' frank ' @ '% ' identified by ' Frank '; Query OK, 0 rows Affected (0.00 sec)--View the account you just added [email protected][(none)]> Select Host,user,password from Mysql.user where user like ' fr% '; +-----------+-------+-------------------------------------------+| Host | user | Password |+-----------+-------+-------------------------------------------+| %         | Frank | *63daa25989c7e01eb96570fa4dbe154711beb361 | | localhost |                                           Fred | |+-----------+-------+-------------------------------------------+

--grant command Syntax [email protected][mysql]> help grantname: ' GRANT ' Description:Syntax:GRANT priv_type [(column_    list)] [, Priv_type [(column_list)]] ...    On [object_type] priv_level to User_specification [, user_specification] ...    [REQUIRE {NONE | ssl_option [[and] ssl_option] ...}] [With With_option ...]    GRANT PROXY on User_specification to User_specification [, user_specification] ... [With GRANT option]object_type:table | FUNCTION | Procedurepriv_level: * | *.*  | db_name.* | Db_name.tbl_name | Tbl_name | Db_name.routine_nameuser_specification:user [| Identified with Auth_plugin [as ' auth_string '] identified by [PASSWORD] ' PASSWORD '] how to authorize a, need to specify which permissions to grant B, permissions to apply on that (Global, specific objects, etc.) C, to which account D, you can specify a password (optionally, automatically create a user in this way) the scope of the authorization permission: On *. db_name.* on Db_name.table_name on db_name.t Able_name.column_name on db_name.routine_name--permissions list, we directly query the root account for all permissions, the following--mysql permissions relative to Oracle, relatively simple, And there is no relation to the definition and configuration of roles [Email protected][(nOne)]> select * from Mysql.user where user= ' root ' and host= ' localhost ' \g *************************** 1.            Row *************************** host:localhost user:root Password:             Select_priv:y insert_priv:y update_priv:y delete_priv:y create_priv:y             Drop_priv:y reload_priv:y shutdown_priv:y process_priv:y file_priv:y Grant_priv:y references_priv:y index_priv:y alter_priv:y Show_db_priv : Y super_priv:y create_tmp_table_priv:y lock_tables_priv:y execute_priv:y repl_slave_p Riv:y repl_client_priv:y create_view_priv:y show_view_priv:y create_routine_priv:y Alter_routin              E_priv:y create_user_priv:y event_priv:y trigger_priv:ycreate_tablespace_priv:y     Ssl_type:ssl_cipher:       x509_issuer:x509_subject:max_questions:0 max_updates:0 max_connections:0 m ax_user_connections:0 plugin:authentication_string:1 Row in Set (0.00 sec)--description, the MySQL prompt described in this article is [EMAIL&NB sp;protected][(dbname)], different accounts, different host logins will be displayed differently. --second, the user identity and permissions that are represented under different prompts. --View current connected user [email protected][(none)]> select Current_User (); +----------------+| Current_User () |+----------------+|  [email protected] |+----------------+--View the permissions of the current account [email protected][(none)]> show grants; --This account is for the highest privilege with the with GRANT option+---------------------------------------------------------------------+| Grants for [email protected] |+-------------------------------------------- -------------------------+| GRANT all privileges on * * to ' root ' @ ' localhost ' with GRANT OPTION | | Grant PROXY on "@" to "root" @ ' localhost ' with GRANT OPTION |+---------------------------------------------------- -----------------+suse11b:~ # mysql-ufred-penter Password: [email protected][(None)]> show grants;+------------------------- -----------------+| Grants for [email protected] |+------------------------------------------+| Grant Usage on * * to ' fred ' @ ' localhost ' |+------------------------------------------+--use the root account to give Fred permission all privileges[email protected][(None)]> grant all privileges on * * to ' fred ' @ ' localhost '; Query OK, 0 rows affected (0.01 sec) [email protected][(none)]> flush privileges; Query OK, 0 rows Affected (0.00 sec) [email protected][(None)]> show grants;+---------------------------------- -----------------+| Grants for [email protected] |+---------------------------------------------------+| GRANT all privileges on * * to ' fred ' @ ' localhost ' |+---------------------------------------------------+[email  protected][(none)]> use tempdb[email protected][tempdb]> CREATE TABLE Tb_isam (ID int,value VARchar ()) Engine=myisam; Query OK, 0 rows affected (0.10 sec) [email protected][tempdb]> insert into Tb_isam values (1, ' Jack '), (2, ' Robin '); Query OK, 2 rows Affected (0.00 sec) Records:2 duplicates:0 warnings:0[email protected][tempdb]> commit;--the following grant Right received an error prompt and cannot authorize [email protected][tempdb]> grant Select on tempdb.* to ' Frank ' at '% '; ERROR 1044 (42000): Access denied for user ' Fred ' @ ' localhost ' to database ' tempdb '--the following from root session to the previously created Frank authorization-Grant Fran K the SELECT permission for all objects on the database tempdb [email protected][(none)]> Grant Select on tempdb.* to ' Frank ' at '% '; Query OK, 0 rows Affected (0.00 sec)--Update the permissions in the cache [email protected][(none)]> flush privileges; Query OK, 0 rows Affected (0.00 sec)-Sign in with Frank account from another host suse11a:~ # mysql-ufrank-p-h172.16.6.89enter Password:-At this time Frank , you have access to table tb_isam[email protected][(none)]> select * from tempdb.tb_isam;+------+-------+| on tempdb ID |    Value |+------+-------+| 1 |    Jack | | 2 | Robin |+------+-------+[EMAIL PRotected][(none)]> show grants;+------------------------------------------------------------------------------ ------------------------+| Grants for [email protected]% |+--- ---------------------------------------------------------------------------------------------------+| GRANT USAGE on * * to ' frank ' @ '% ' identified by PASSWORD ' *63daa25989c7e01eb96570fa4dbe154711beb361 ' | | GRANT select on ' tempdb '. * to ' frank '% '--you can see the extra SELECT permission |+--------------------------- ---------------------------------------------------------------------------+--The following is an example of granting the maximum permission, and the user is automatically created at the time of the grant.   Because we did not set the password, the password column query result is empty [email protected][(none)]> grant all privileges on *. * to ' jack ' @ ' localhost '; Query OK, 0 rows Affected (0.00 sec)--the first * number represents any database, the second number represents any object on the database [email protected][(None)]> Select User,host , Password from Mysql.user where user= ' Jack '; +------+-----------+----------+| user | Host | Password |+------+-----------+----------+| Jack |          localhost | |+------+-----------+----------+suse11b:~ # mysql-ujack-p-H localhostenter Password: [email protected][(none)] > show grants for Current_User; --this method is equivalent to show grants, viewing its own permissions +---------------------------------------------------+| Grants for [email protected] |+---------------------------------------------------+| GRANT all privileges on * * to ' jack ' @ ' localhost ' |+---------------------------------------------------+-- Under the current session to view the permissions of other users, note that the current session login user also need to have permission to view other user rights [email protected][(None)]> show grants for ' Frank '% '; +------------------------------------------------------------------------------------------------------+| Grants for [email protected]% |+--- ---------------------------------------------------------------------------------------------------+|GRANT USAGE on * * to ' frank ' @ '% ' identified by PASSWORD ' *63daa25989c7e01eb96570fa4dbe154711beb361 ' | | GRANT SELECT on ' tempdb '. * to ' frank ' @ '% ' |+------------------- -----------------------------------------------------------------------------------+--below demonstrates authorization based on the object column level--First REVOKE SELECT permission before [Email protected] [(None)]> revoke select on tempdb.* from ' Frank ' @ '% '; Query OK, 0 rows Affected (0.00 sec) [email protected][tempdb]> CREATE table Tb_user as SELECT * from Mysql.user; Query OK, 9 rows affected (0.15 sec) records:9 duplicates:0 warnings:0[email protected][tempdb]> Grant Select ( user,host), update (host) on the Tempdb.tb_user to ' Frank ' @ '% '; ERROR 1142 (42000): Grant command denied to the user ' Fred ' @ ' localhost ' for table ' tb_user '--Authorization failed--use root to authorize [Email pro tected][(None)]> Grant Select (user,host), update (host) on Tempdb.tb_user to ' Frank ' at '% '; Query OK, 0 rows Affected (0.00 sec) [Email protected][(None)]> flush privileges; Query OK, 0 rows Affected (0.00 sec)--Check the permissions that Frank has [email protected][(none)]> show grants for ' Frank '; +----- -------------------------------------------------------------------------------------------------+| Grants for [email protected]% |+--- ---------------------------------------------------------------------------------------------------+| GRANT USAGE on * * to ' frank ' @ '% ' identified by PASSWORD ' *63daa25989c7e01eb96570fa4dbe154711beb361 ' | | GRANT SELECT (user, host), UPDATE (host) on ' tempdb '. ' Tb_user ' to ' Frank ' @ '% ' |+------------------- -----------------------------------------------------------------------------------+--Use the Frank identity to verify the permissions granted [ email protected][(none)]> desc tempdb.tb_user;+-------+----------+------+-----+---------+-------+| Field | Type | Null | Key | Default | Extra |+-------+----------+------+-----+---------+-------+| Host | char (60) |     NO |         |       | || User | char (16) |     NO |         |       | |+-------+----------+------+-----+---------+-------+[email protected][(none)]> select * from Tempdb.tb_   User --wildcard characters are not supported on Access, column name must be specified ERROR 1142 (42000): SELECT command denied to user ' frank ' @ ' suse11a.site ' for table ' Tb_user ' [email&nbsp ;p rotected][(None)]> Select Host,user from Tempdb.tb_user where user= ' Frank '; +------+-------+| Host | User |+------+-------+| %    | Frank |+------+-------+--It is important to note that if your object is created under the test-related database, the permission limit may be invalidated. --The following query is used to view the authorization table for DB [email protected][(None)]> Select Host,db,user from mysql.db;+------+---------+------+| Host | db | User |+------+---------+------+| %    |      Test | || %    |      test\_% | |+------+---------+------+--based on the previous permission grants, the column host can be updated, and the column user does not work, as the following 2 SQL statement results [email protected][(none)]> Update tempdb.tb_user set host= ' localhost ' where user= ' Frank '; Query OK, 1 row affected (0.12 sec) Rows matched:1 changed:1 Warnings:0[email protected][(none)]> update tempdb.tb_user set user= ' Jason ' where user= ' Jack '; ERROR 1143 (42000): UPDATE command denied to user ' frank ' @ ' suse11a.site ' for column ' user ' in table ' Tb_user '-about with GRAN   T option[email protected][(none)]> show grants; -Note that Root has the with GRANT option+---------------------------------------------------------------------+| Grants for [email protected] |+-------------------------------------------- -------------------------+| GRANT all privileges on * * to ' root ' @ ' localhost ' with GRANT OPTION | | Grant PROXY on "@" to "root" @ ' localhost ' with GRANT OPTION |+---------------------------------------------------- -----------------+[email protected][(none)]> show grants for ' Jack ' @ ' localhost '; --Note that Jack does not have a with GRANT option+---------------------------------------------------+-This is the question why the object created by the user itself cannot be authorized |       Grants for [email protected] | +---------------------------------------------------+| GRANT all privileges on * * to ' jack ' @ ' localhost ' |+---------------------------------------------------+         

4. Revoke Permissions

Revoke permissions using the REVOKE keyword, revocation and authorization is basically similar to the permissions, followed by what permissions can be granted, corresponding to what permissions can be revoked, the original to clause becomes the FROM clause.  As the following example mysql> revoke SELECT (user, host), UPDATE (host) on ' tempdb '. ' Tb_user ' from ' Frank ' @ '% '; Mysql> Revoke all privileges, grant option from ' Frank ' @ '% '; [Email protected] [(none)]> revoke SELECT (user, host), UPDATE (host) on ' tempdb '. ' Tb_user ' from ' Frank ' @ '% '; Query OK, 0 rows Affected (0.00 sec)--author:leshami--blog:http://blog.csdn.net/leshami[email protected][(non e)]> Revoke all privileges, grant option from ' Frank ' @ '% '; Query OK, 0 rows affected (0.01 sec) [email protected][(none)]> flush privileges;  Query OK, 0 rows Affected (0.00 sec) [email protected][(none)]> show grants for ' Frank '; --Only the most basic permissions +------------------------------------------------------------------------------------------after viewing revoke ------------+| Grants for [email protected]% |+--- ---------------------------------------------------------------------------------------------------+| GRANT USAGE on * * to ' frank ' @ '% ' identified by PASSWORD ' *63daa25989c7e01eb96570fa4dbe154711beb361 ' |+----------------- -------------------------------------------------------------------------------------+

5, delete and rename accounts

Use the drop user command to delete users--view existing users on the current system [email protected][(None)]> Select User,host,password from mysql.user;+----- --+-----------+-------------------------------------------+| user | Host | Password |+-------+-----------+-------------------------------------------+| Root |                                           localhost | || Root |                                           suse11b | || Root |                                           127.0.0.1 | || Root |                                           :: 1 |       || |                                           localhost |       || |                                           suse11b | || Fred |                                           localhost | || Frank | %         | *63daa25989c7e01eb96570fa4dbe154711beb361 | | Jack |                                           localhost | |+-------+-----------+-------------------------------------------+--use the drop user command to delete the users [email protected][( None)]> drop user ' frank ' @ '% '; Query OK, 0 rows Affected (0.00 sec) [email protected][(none)]> drop user ' fred ' @ ' localhost '; Query OK, 0 rows Affected (0.00 sec) [email protected][(None)]> Select User,host,password from Mysql.user where use R like ' fr% '; Empty Set (0.00 sec)--How to rename an account, using the Rename user command [email protected][(none)]> rename user ' jack ' @ ' localhost ' to ' Jason ' @ ' localhost '; Query OK, 0 rows Affected (0.00 sec) [email protected][(None)]> Select User,host,password from Mysql.user where use R like ' j% '; +-------+-----------+----------+| user | Host | Password |+-------+-----------+----------+| Jason |          localhost | |+-------+-----------+----------+--Delete the corresponding records directly from Mysql.user, but it is not recommended to operate the MySQL system table directly.

MySQL User and Rights management