Shulou(Shulou.com)06/01 Report--

I. user authorization

1. Points for attention:

-after the MySQL database server is built, it is placed in a separate room or in the IDC server room. By default, MySQL database service only allows database administrator root to log in locally, and authorization is required to log in to the management database on other clients.

Programmers (website running data) programming using databases are all on the client side and require authorization.

-by default, only the database administrator is authorized to log in locally on the database server.

2. Authorization library mysql, the main tables:

-user table: record the user's information

Host: client user: authorized user name password: authorized user password * _ priv: permission

-db table: records the authorization information of authorized users to the library

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

Host: client Db: library name user: user name * _ priv: permission

-tables_ private table: records the authorization information of authorized users to the table

Records are only available when there is authorization to the watch.

Host db user table_name grantor timestamp table_priv column_priv

-columns_ privilege table: records the authorization information of the authorized user to the field

Record only if there is authorization to the field (grant select,update (name,sex) on studb.usertab tolucy;)

3. Authorization order:

Format: mysql > grant permission list on database name. Table name to username @; / / all hosts in the network can log in with this user without a password

Mysql > grant permission list on database name. Table name to user name @ "client address"; / / only one client is allowed to log in with this user name without a password

Mysql > grant permission list on database name. Table name to user name @ "client address" identified by "password"; / / only one client is allowed to log in with this user name, which requires a password

Mysql > grant permission list on database name. Table name to user name @ "client address" identified by "password" withgrant option;// allows only one client to log in with this user name, which requires a password, and the user has authorization to other users

1) permission list: what kind of permissions do users have to operate on the database after connecting?

-all: have full permissions on the specified library

-select: only have permission to view the specified library

-insert: only if you specify the permission to insert records in the library

-update:updtae (field 1, field 2.) update permissions, change permissions for a certain field of a table

-delete:

-USAGE: users can only log in without any permissions on libraries and tables

-multiple permission commas, separated by: select,insert...

(permission '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: customized during authorization, must be identifiable, and saved in the mysql.user table

4) client address:

-@ "%" or not set: represents all hosts in the network

-@ "192.168.1%": all hosts on this network segment

-@ "ip address": only hosts at this ip address are allowed to log in

-@ "hostname": make sure that the hostname can be resolved, such as @ "pc1.tarena.com" / / not frequently used.

-@ "% .tarena.com": allows login of all hosts in the tarena.com area to be resolved / / rarely used

5) identified by "password": optional, password used when authorizing users to log in, custom

6) with grant option: optional to allow authorized users to have authorization rights

Example: mysql > grant select on bbsdb.usertab to jim@ "192.168.4.1" identifiedby "123" with grant option

Mysql > grant all on *. * to root@ "192.168.4.205" identified by "123" with grant option; / / authorized database administrator root can log in to the database on 192.168.4.205

4. Login users to view their own permissions: mysql > show grants

Database administrator to view the permission information of authorized users: mysql > show grants fortom@192.168.4.205

Check which authorized users are: mysql > select user,host from mysql.user

5. Check who the current login user is: mysql > select user ()

6. The authorization information is stored in the table in the mysql library in the database server.

7. Server side and client side turn off firewall and SELinux (disable)

Client login: # mysql-h database server IP-u authorized username-p password

8. Authorization permissions:

A) Authorization requires full permissions on the mysql library

B) Authorization permissions can only give permissions to others (libraries / tables to which they have permissions)

C) for example, authorization is given to the manager of the development department, and he authorizes the programmer of the department

Grant all on webdb.*to webuser10@ "" identified by "123" with grant option

Grant all on mysql.*to webuser10@ "" identified by "123"

9. Authorized users to change their login passwords

Set password=password ("New password")

10. The administrator can reset the user's password

Set password for user name @ "client address" = password ("New password")

2. Revoke the user's rights

1. The target library / table must be authorized to revoke successfully.

2. Users with authorized rights can also revoke permissions when logging in on the client.

3. Revoke the order:

Format: revoke permission list on database name (. Table name) from user name @ "client address"

Database name (. Table name): this location must be authorized

Example: revoke delet on *. * from root@ "192.168.4.205"

Revoke all on *. * from root@ "192.168.4.205"; / / the permission is revoked, and the user can still log in. All is the right to manipulate the library and table, not including the authorization right.

Revoke grant option on *. * from root@ "192.168.4.205"; / / revoke authorization

Revoke revokes the permissions and will not delete the authorized user information (still saved in the mysql.user table). 4. To completely delete the authorized user, delete the record of the authorized user in the mysql.user table:

Delete from mysql.user where user= "user name"

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.