MySQL Database user authorization management

First, user authorization

1. Precautions:

-MySQL Database server is set up, placed in a separate room or IDC room,mysql database service By default only allows the database administrator root to log on locally, Authorization is required for other clients to log on to the management database.

- Programmers (Web site run data) programmed to use the database are all on the client and need to be authorized.

- By default only the database administrator has permission to log on to the database server

2, the authorization library MySQL, the main several tables:

- User table : Record users ' information

Host: Client user: Authorized user name password: Authorized user password *_priv: Permissions

- db table : Record authorized user's authorization information to library

Delete the full permission record of test and table (Delete from mysql.db where user= "") at work

Host: Client Db: library name User: Username *_priv: Permissions

- tables_priv table : Record authorized user's authorization information to the table

There will be records when there is authorization to the table.

Host DB User table_name grantor timestamp table_priv Column_priv

- columns_priv table : Record authorized user authorization information for a field

There is authorization for the field to be logged ( Grant Select,update (name,sex) on Studb.usertab Tolucy; )

3. Authorization Order:

format: mysql> grant permission list on database name to user name @ //

  mysql> grant permission list on database name to user name @ " client address " //

  mysql> grant permission list on database name to user name @ " client address "  Identified by " password " ; //

&NBSP;MYSQL> grant permission list on database name to user name @ " client address "  Identified by " password "withgrant option ; //< Span style= "font-family: ' The song Body '; > Only one client is allowed to log on with the user name, and a password is required, and the user has permission to grant other users

1 ) permissions List : What permissions do you have to the database after the user connects ?

-All: Have full permissions on the specified library

-Select: Only View permissions for the specified library

-Insert: Only the inserted record permission in the specified library

-Update:updtae(field 1, field 2 ...). Update permissions, change permissions for a field in a table

-Delete:

-USAGE: No permissions on libraries and tables, users can only log on

- multiple permissions comma, delimited:select,Insert ...

( permissions ' Select ', ' Insert ', ' Update ', ' Delete ', ' Create ', ' Drop ', ' Grant ', ' References ', ' Index ', ' Alter ', ' CreateView ', ' Show View ', ' Trigger ' )

2 ) database name:

-*. *: All libraries

- Data name . *: All tables in a library have permissions

- database name . table name: A table in a library has permissions

3 ) User name : Customizable when authorized, to be marked, save in mysql.user table

4 ) Client address:

-@ "%" or not set: Represents all hosts on the network

-@ "192.168.1.%": All hosts of the network segment

-@ "IP address ": Allow only hosts on that IP address to log on

-@ " hostname ": Ensure that the hostname can be parsed as:@ "pc1.tarena.com"// infrequently used

[Email protected] "%.tarena.com": Allow all host logins in tarena.com zone to be resolved / not used

5)identified by " password ": Optional, password to be used when the user is authorized to log on, custom

6)with GRANT option : Optional to allow authorized users to have authorized rights

Example:mysql>Grant Select on Bbsdb.usertab to [e-mail protected] "192.168.4.1" Identifiedby "123" with Grant option;

Mysql>grant all on * * to [email protected] ' 192.168.4.205 ' identified by ' 123 ' with GRANT option; Authorize database administrator root to log on to the database on 192.168.4.205

4, login users to view their rights:Mysql>show grants;

The database administrator views the authorized user's permissions information:mysql>Show grants [email protected];

See what authorized users are:mysql>Select User,host from Mysql.user;

5.see who is currently logged on:mysql>Select User ();

6. Theauthorization information is saved in the table in the MySQL library in the database server

7. Server side and client shutdown firewall and SELinux(disable)

Client Login:#mysql-h database server ip-u Authorized user name - p password

8. Authorized Rights:

A) Authorization permissions must have full access to MySQL library

b) grant permission only to others (the Library/table that grants you permission )

c) such as: Grant authority to the manager of the development Department, he authorized the department programmer

Grant all on webdb.*to [email protected] '% ' identified by ' 123 ' with Grant option ;

Grant all on mysql.*to [email protected] "%" identified by "123" ;

9, authorized users to modify their login password

Set Password=password (" New password " );

Admin can reset the user's password

Set Password for User name @ " Client Address "=password (" New password " );

Second, REVOKE user rights

1, the target library / table has been authorized to revoke success

2.users with authorized rights can also perform revocation permissions on client login

3. Cancellation Order:

format: Revoke permission list on database name (. table name ) from User name @ " Client Address " ;

Database name (. Table name ): This location has to be authorized.

Example: revoke delet on * * FROM [email protected] "192.168.4.205" ;

revoke all on * * from [email protected] "192.168.4.205" ; // Revoke permissions, users can still log on, All is the manipulation of the library and table permissions do not include authorization rights

revoke GRANT OPTION on * * FROM [email protected] "192.168.4.205" ;// Revoke authorization rights

Revoke Revoke the permissions, do not delete the authorized user information (still saved in the mysql.user table), can still log in 4, to completely remove authorized users, delete mysql.user Record of the authorized user in the table:

Delete from Mysql.user where user= " user name " ;

This article is from the IT Technology Learning blog, so be sure to keep this source http://learningit.blog.51cto.com/9666723/1788317

MySQL Database user authorization management