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 consists of 2 stages, one is the server check whether you are connected, and the other is assuming you can connect, and the server checks every request you make. See if you have enough permissions to implement it.

This paper mainly describes the user creation, authorization, revoke permission and so on, which are related to MySQL privilege system.

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

2. Create a MySQL database user

--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 The password is sufficient for its designation. 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 directly to create an account to see a related demonstration. 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 (not specified password) [email protected][(none)]> create user ' Fred ' @ ' localhost '; Query OK, 0 rows Affected (0.00 sec)--Specify password to create a new user,% means casual, that is, Frank can access the database from a random host [email protected][(none)]> Create user ' frank ' @ '% ' identified by ' Frank '; Query OK, 0 rows Affected (0.00 sec)--View the account you just joined [email protected][(None)]> Select Host,user,password from Mysql.user where user like ' fr% '; +-----------+-------+-------------------------------------------+| Host | user | Password |+-----------+-------+-------------------------------------------+| %         | Frank | *63daa25989c7e01eb96570fa4dbe154711beb361 | | localhost |                                           Fred | |+-----------+-------+-------------------------------------------+

3, grant permissions with Grant

--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 applied to that (Global, specific objects, etc.) C, to which account D, the ability to specify password (optionally, the user will be actively creating users) the scope of the authorization permission: On * * on db_name.* on Db_name.table_name on D B_name.table_name.column_name on db_name.routine_name--permissions list, we directly query the root account for all permissions, such as 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_connect ions:0 max_user_connections:0 plugin:authentication_string:1 Row in Set (0.00 sec)--description, described in this article, describes the MySQL mention The indicator is [email 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 (IDInt,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 grants Right received an error prompt. 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 the 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 are now able to access the 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 '% '--can see more SELECT permission |+--------------------------- ---------------------------------------------------------------------------+--The following is a sample that grants maximum permissions. Granted at the same time will be self-initiated to create the user, because we did not set the password, so 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 a random database. The second * number represents a random 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 ' |+---------------------------------------------------+-- View the permissions of other users under the current session. Note, the current session login user also need to have the ability 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 ' @ '% ' |+------------------- -----------------------------------------------------------------------------------+--The following shows the authorization based on the object column level--First revoke the previous SELECT permission [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--the following uses 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)--The following checks 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 Frank as the following 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 --The wildcard character is not supported when visiting, you must specify a column name 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 |+------+-------+--Note that it is assumed that your object is created under test-related databases. Permission restrictions 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 preceding permissions, the column host can be updated, and the column user does not. As the following 2 SQL statements run the result [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

Revoking permissions is using Revokekeyword, which is basically similar to how permissions are revoked and authorized. Second, what permissions can be granted. This corresponds to what permissions can be revoked. The original to clause becomes the FROM clause.  Example mysql> revoke SELECT (user, host), UPDATE (host) on ' tempdb ', as shown below. ' Tb_user ' from ' Frank ' at '% '; 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 | |+-------+-----------+----------+--Deletion of the user is also able to delete the corresponding record directly from Mysql.user. But it is not recommended to operate the MySQL system table directly

MySQL User and Rights management