The outline

One 、 Preface

Two 、 Create users and authorize

3、 ... and 、GRANT Types of statements

Four 、 Remove rights and delete users

One 、 Preface

As Mysql Database administrators manage user accounts , It's a very important thing , Point out which user can connect to the server , Where to connect , What can you do after connecting .Mysql from 3.22.11 Start by introducing two statements to do this ,GRANT Sentence creation Mysql Users and specify their permissions , and REVOKE Statement to delete permissions .CREATE and REVOKE Sentences affect 4 Tables ,

  • user Users who can connect to the server and any global permissions they have

  • db Database level permissions

  • tables_priv Table level permissions

  • columns_priv Column level permissions

Also the first 5 Authorization table host, But it's not affected by GRANT and REVOKE Influence , Let's take a look mysql All the tables in the database ,

mysql> use mysql; 
Database changed  
mysql> show tables;  
| Tables_in_mysql           |  
| columns_priv              |  
| db                        |  
| event                     |  
| func                      |  
| general_log               |  
| help_category             |  
| help_keyword              |  
| help_relation             |  
| help_topic                |  
| host                      |  
| ndb_binlog_index          |  
| plugin                    |  
| proc                      |  
| procs_priv                |  
| proxies_priv              |  
| servers                   |  
| slow_log                  |  
| tables_priv               |  
| time_zone                 |  
| time_zone_leap_second     |  
| time_zone_name            |  
| time_zone_transition      |  
| time_zone_transition_type |  
| user                      |  
24 rows  in  set  (0.00 sec)

When you send a message to a user GRANT When the sentence is , stay user Create a record for the user in the table . If the statement specifies any global permissions ( Administrative permissions or permissions that apply to all databases ), These are also recorded in user In the table . If you specify a database 、 Table and column level permissions , They are recorded separately in db、tables_priv and columns_priv In the table .

Two 、 Create users and authorize

1.GRANT Usage of sentences

mysql> ? grant 
Name:  '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 The syntax of the statement looks like this ,

GRANT privileges [columns] ON what TO user IDENTIFIED BY "password" WITH GRANT OPTION;

notes : It's marked in red , It's all definable , Now let's make an explanation !

2. Authority classification (privileges)

The first group : Specifier applies to database 、 Tables and columns

  • ALTER Modify tables and indexes

  • CREATE Create databases and tables

  • DELETE Delete the existing records in the table

  • DROP Delete databases and tables

  • INDEX Create or discard indexes

  • INSERT Insert a new row into the table

  • REFERENCE Unused

  • SELECT The records in the search table

  • UPDATE Modify existing table records

The second group : Specify the number of databases to manage permissions

  • FILE Read or write files on the server

  • PROCESS View the thread information executed in the server or kill the thread

  • RELOAD Overload authorization table or clear log 、 Host cache or table cache

  • SHUTDOWN Shut down the server

The third group has special rights :ALL signify “ All permissions ”,UASGE It means no authority , That is to create users , But don't grant permission

  • ALL all ;ALL PRIVILEGES“ All permissions ”

  • USAGE special “ No authority ” jurisdiction

The column of authority application , It's optional , And you can only set column specific permissions . If the command has more than one column , They should be separated by commas .

The level of authority application . Permissions can be global ( For all databases and all tables )、 Specific databases ( For all tables in a database ) Or a specific table . By specifying a columns The word permission is column specific .

Users granted permissions , It consists of a user name and a host name . stay MySQL in , You don't just specify who can connect , And where to connect . This allows you to connect two users with the same name from different places .MySQL Let you distinguish them , And independently of each other .    
MySQL One of the user names in is the user name you specified when you connected to the server , The name doesn't have to be related to your Unix Login or Windows It's connected to . By default , If you don't specify a name , The client program will use your login name as MySQL user name . It's just an agreement . You can change the name to nobody, And then to nobody Connect to perform operations that require superuser privileges .

The password given to the user , It's optional . If you don't specify a new user IDENTIFIED BY Clause , The user is not assigned a password ( unsafe ). For existing users , Any password you specify will replace the old password . If you don't specify a password , The old password remains unchanged , When you use IDENTIFIED BY when , The password string uses the literal meaning of the password instead ,GRANT Will encode your password , Don't use it like you do SET PASSWORD Use like that password() function .

7.WITH GRANT OPTION Clauses are optional . If you include it , Users can grant permissions through GRANT Statement to other users . You can use this clause to give other users the ability to authorize .

notes : user name 、 password 、 Database and table names are case sensitive in authorization table records , The host name and column name are not .

3、 ... and 、GRANT Types of statements

In a general way , You can identify... By asking a few simple questions GRANT Types of statements :

  • Who can connect? , Connect from there ?

  • What level of authority should a user have , What do they apply to ?

  • Should users allow administrative rights ?

1. Who can connect? , Connect from there ?

(1). You can allow a user to connect from a specific host or a series of hosts .

GRANT ALL ON db.* TO  free @localhost  IDENTIFIED BY  "123456" ;

explain :db.* intend “db All tables in the database

(2). You may have a user who goes out a lot and needs to be able to connect from any host free. under these circumstances , You can allow him to connect from anywhere :

GRANT ALL ON db.* TO  free @% IDENTIFIED BY  "123456" ;

explain :“%” Characters act as wildcards , And LIKE Pattern matching has the same meaning . In the above statement , It means that the “ Any mainframe ”. therefore free and free@% Equivalent . This is the easiest way to build users , But it's also the most insecure .    
(3). You can allow a user to access from a limited set of hosts . for example , To allow mary from Any host connection to the domain , Use one Host specifier :

GRANT ALL ON db.* TO mary@%. free .net IDENTIFIED BY  "123456" ;

(4). If you like , The host part of the user identifier can be used IP Address instead of a host name . You can designate a IP Address or an address containing pattern characters , and , from MySQL 3.23, You can also specify... With a netmask that indicates the number of digits used for the netnumber IP Number :

GRANT ALL ON db.* TO  free @  IDENTIFIED BY  "123456" ;
GRANT ALL ON db.* TO  free @192.168.12.% IDENTIFIED BY  "123456" ;
GRANT ALL ON db.* TO  free @ /24   IDENTIFIED BY  "123456" ;

explain : The first example shows the specific hosts that users can connect from , The second designation is for C Class subnet 192.168.12 Of IP Pattern , And in the third sentence , To specify a 24 Bit network number and match with 192.168.12 head 24 Bit IP Address .

(5). If the user value you specified is wrong , You may need to use quotation marks ( Just separate the user name and host name parts with quotation marks ).

GRANT ALL ON db.* TO  "free" @ ""   IDENTIFIED BY  "123456" ;

2. What level of permissions users should have and what they should apply to ?

(1). You can delegate different levels of authority , Global permissions are the most powerful , Because they apply to any database . To make free Become a super user who can do anything , Including being able to authorize other users , Issue the following statement :

GRANT ALL ON *.* TO  free @localhost IDENTIFIED BY  "123456"  WITH GRANT OPTION;

explain :ON In Clause *.* signify “ All databases 、 All tables ”. In terms of safety , We specify free Can only connect locally . It's usually wise to limit the number of hosts that a superuser can connect to , Because it limits the host trying to crack the password .    
Some rights (FILE、PROCESS、RELOAD and SHUTDOWN) It's administrative rights and can only be used "ON *.*" Global permission specifier Authorization . If you will , You can delegate these rights , Instead of authorizing database permissions . for example , The following statement sets up a flush user , He can only send out flush sentence . This may be useful when you need to execute administrative scripts such as clearing logs :

GRANT RELOAD ON *.* TO flushl@localhost IDENTIFIED BY  "123456" ;

In a general way , You want to delegate administrative rights , Be stingy , Because the users who own them can influence the operation of your server .

(2). Database level permissions apply to all tables in a specific database , They can be used ON db_name.* Clause grants :

GRANT ALL ON db TO  free @ test . free .net INDETIFIED BY  "123456" ;
GRANT SELECT ON db TO  free @% INDETIFIED BY  "123456" ;

explain : The first sentence to free to grant authorization db Permissions for all tables in the database , Second, create a strictly restricted user free( Read only users ), Only access db All the tables in the database , But only read , That is, users can only send SELECT sentence . You can list a series of permissions granted at the same time . for example , If you want users to be able to read and modify the contents of an existing database , But you can't create new tables or delete tables , These permissions are granted as follows :GRANT SELECT,INSERT,DELETE,UPDATE ON db TO INDETIFIED BY "123456";

(3). For more refined access control , You can authorize... On various tables , Or even on every column of the table . When you want to hide a part of a table from users , Or when you want a user to modify only specific columns , Special permissions are very useful . Such as :

GRANT SELECT ON db.member TO  free @localhost INDETIFIED BY  "123456" ;
GRANT UPDATE (expiration) ON db. member TO  free @localhost;

explain : The first statement grants to the whole member Table and set a password , The second sentence adds UPDATE jurisdiction , When only right expiration Column . There's no need to specify a password , Because the first statement has specified .

(4). If you want to grant permissions to multiple columns , Specify a comma separated list . for example , Yes free Users increase member The address field of the table UPDATE jurisdiction , Use the following statement , The new permissions will be added to the user's existing permissions :

GRANT UPDATE (street,city,state,zip) ON db TO  free @localhost ;

explain : Usually , You don't want to grant any more permissions than the user really needs . However , When you want users to be able to create a temporary table to hold intermediate results , But when you don't want them to do this in a database that contains content they shouldn't modify , Something happened to grant relatively loose permissions on a database . You can build a separate database ( Such as tmp) And grant all permissions on the open database to . for example , If you want people from Any user of the host in the domain uses tmp database , You can send out messages like this GRANT sentence :

GRANT ALL ON tmp.* TO  "" @ test .net;

After you're done , Users can create and use tmp.tb_name Formal quotation tmp In the table ( In the user specifier "" Create an anonymous user , Any user matches a blank user name ).

3 Should users be allowed to manage permissions ?

You can allow the owner of a database to control access to the database by granting permissions to all the owners on the database , When authorizing , Appoint WITH GRANT OPTION. for example : If you want to make free Can from Any host of the domain is connected and has sales Administrator rights for all tables in the database , You can use the following GRANT sentence :

GRANT ALL ON sales.* TO  free @%.big. free .com INDETIFIED BY  "123456"  WITH GRANT OPTION;

In effect WITH GRANT OPTION Clause allows you to grant access authorization to another user . it is to be noted that , Have GRANT Two users with permissions can authorize each other . If you only give it to the first user SELECT jurisdiction , And another user has GRANT add SELECT jurisdiction , So the second user can be the first user more “ Powerful ”.

Four 、 Remove rights and delete users   
To revoke a user's rights , Use REVOKE sentence .REVOKE The grammar of is very similar to GRANT sentence , except TO use FROM Instead of and without INDETIFED BY and WITH GRANT OPTION Clause :

mysql> ? REVOKE 
Name:  'REVOKE'  
     priv_type [(column_list)]  
       [, priv_type [(column_list)]] ...  
     ON [object_type] priv_level  
     FROM user [, user] ...

REVOKE The syntax of the statement looks like this ,   
REVOKE privileges (columns) ON what FROM user;

Now let's make a detailed description of the red part ,

1.user The part must match the original GRANT You want to withdraw the authority of the user's user part .

2.privileges Parts don't need to match , You can use it. GRANT Statement Authorization , And then use REVOKE Statement to revoke only part of the permission .   
3.REVOKE Statement to delete only permissions , Without deleting users . Even if you revoke all permissions , stay user The user records in the table remain , This means that users can still connect to the server . To completely delete a user , You have to use a DELETE The statement clearly starts from user Delete user records from the table , The specific operation is as follows :

mysql -u root -p 123456 mysql
mysql>DELETE FROM user WHERE User= "user_name"  and Host= "host_name" ;

DELETE Statement to delete a user record , and FLUSH Statement tells the server to overload the authorization table .( When you use GRANT and REVOKE When the sentence is , Table auto overload , When you modify the authorization form directly, it's not ).

5、 ... and 、 summary

After the above explanation, I think you should be very clear about user authorization , Hey ! ^_^……

This article from the “Share your knowledge …” Blog , Please make sure to keep this source

MySQL More related articles about user and authorization management

  1. linux User and user group management details

    ############# User and user group management ###################linux Know only UID and GID # Can be found in /etc/passwd and /etc/group Find ##/etc/passwd ...

  2. linux User and user group management details

    Linux  User and user group management Linux The system is a multi-user and multi task time-sharing operating system , Any user who wants to use system resources , You must first apply to the system administrator for an account , Then enter the system as this account . On the one hand, a user's account can help ...

  3. MySQL Authorization authentication details

    MySQL Authorization authentication details author : Yin Zhengjie Copyright notice : Original works , Declined reprint ! Otherwise, the legal liability will be investigated . One .MySQL Authority system introduction 1>. The role of the permission system is to grant a user from a host to query . Insert . modify . Delete ...

  4. MySQL User password expiration function details

    MySQL User password expiration function details author :chszs, No reprint without the permission of the blogger . The author and the blog's home page should be indicated in the permission reprint : Let's start with two terms . Payment Ca ...

  5. Probably the most beautiful Spring Details of business management project

    Wechat read the address link : Probably the most beautiful Spring Details of business management Review of transaction concept What is business ? A transaction is a logical set of operations , Or both , Either not . The nature of things (ACID): Atomicity : Transactions are the smallest unit of execution , Not allow ...

  6. MySQL Cluster The configuration file (config.ini) Detailed explanation

    MySQL Cluster The configuration file (config.ini) Detailed explanation ################################################################### ...

  7. Oracle Access management details

    Oracle Access management details Reprint --CzmMiao My blog life Oracle jurisdiction Permissions allow users to access objects or execute programs belonging to other users ,ORACLE The system provides three kinds of permissions :Object Object level .System The system level . ...

  8. Shrio Authorization verification details

    Authorization , It's about controlling whether you can access a resource , for instance , You can get directions page Under folder jsp page , But it's not accessible page Under folder admin Under folder jsp page . In Authorization , There are three core elements : jurisdiction , role , user . ...

  9. The product manager - Demand analysis - A user story - agile development Detailed explanation A picture to help you understand Scrum Agile processes

    The product manager - Demand analysis - A user story - agile development Detailed explanation User stories describe the functions that users desire from the perspective of users . A good user story consists of three elements :1. role : Who wants to use this feature .2. Activities : What functions need to be completed .3. Commercial price ...

Random recommendation

  1. iOS Empty string error

    * Reference resources : * Reference resources : ...

  2. Use Arduino and LM35 The temperature sensor monitors the temperature

    I played with the last one Arduino introduction , This time, go further , Use it. LM35 Temperature sensor to monitor the current temperature .LM35 The temperature sensor is already in Arduino The starter kit contains , It's the little black guy with three feet . Let's connect these things first . ...

  3. socket Programming --socket Module introduction

    socket Also known as ' Socket , Used to describe IP Address and port , It's the end of a communication . socket Come of Unix, and Unix/Linux One of the basic philosophies is " Everything is a document ", For files, use [ open ][ Reading and writing ] ...

  4. Android UI Drawing process analysis ( One )LayoutInflater brief introduction

    Preface This article blog Yes, I read csdn Daniel Guo Lin's < Take you step by step to learn more about View> After a series of articles , Practice and make a summary . As an aspiring front-end development engineer , How can we not understand View The basic principle of drawing —— It's just ...

  5. javax.mail.MessagingException: 501 Syntax: HELO hostname Linux End exception resolution

    Use... In projects javamail stay window The environment is normal , Throw an exception when placing it on the server javax.mail.MessagingException: 501 Syntax: HELO hostname , primary ...

  6. MFC The rules of DLL And expansion DLL

    One .MFC The rules DLL     MFC The rules DLL Can be in this dll For internal use MFC, But the interface to the application cannot be MFC Of . Can be supported by all dll The programming language written by the application uses , Of course, it also includes the use of MFC Created applications . stay ...

  7. ( turn )Maven actual combat ( Two ) Simple to build Maven project

    Last section maven Installation and configuration , In this section, let's learn how to create a simple Maven project 1. use Maven Command to create a simple Maven project stay cmd Run the following command : mvn archetype:gene ...

  8. oracle 12c Third, study pdb Pluggable test

    DECLARE l_result BOOLEAN; BEGIN l_result := DBMS_PDB.check_plug_compatibility( pdb_descr_file => ...

  9. Linux Install Sogou input method tutorial

    Recently started to learn linux Some problems encountered in installing input method , Finally successfully installed , It also benefits from the articles written by the predecessors on the Internet , Now the whole installation steps and some problems encountered are summarized as follows :   There are basically three steps 1, add to fcitx Keyboard input method system ...

  10. matplotlib Data visualization

    A piece of matplotlib Ku's learning blog .matplotlib It's very important for data visualization , It completely encapsulates MatLab All of the API, stay python And Python The use of the same grammar complements each other . One . Library installation and ...