Shulou(Shulou.com)05/31 Report--

How to carry out MySQL user management, many novices are not very clear about this, in order to help you solve this problem, the following editor will explain for you in detail, people with this need can come to learn, I hope you can gain something.

User Management of 1.MySQL

The user information of MySQL is stored in the user table, so the user action of MySQL is actually the operation of adding, deleting, querying and modifying the mysql.user table. The following figure shows the table structure of mysql.user.

In this table, you can set all the information about the user, including user name, password, permissions, and so on.

The Notice:password field is generated by the PASSWORD ('') function.

In addition to the way you modify the user table to manipulate MySQL users, MySQL has some additional commands for creating or deleting MySQL users.

two。 Create a user

2.1.CREATE USER creates an unauthorized user

Syntax: CREATE USER user [IDENTIFIED BY [PASSWORD] 'password'] [, user [IDENTIFIED BY [PASSWORD]' password']]...

Example: create user test@localhost identified by 'test';// creates a user with a user name of test and a password of test, which does not have any permissions

Create users with permissions in 2.2.GRANT mode

Syntax: GRANT [ALL | [SELECT,DELETE...]] PRIVILEGES ON {databasename | *}. {tablename | *}... TO username IDENTIFIED BY password

Example: GRANT ALL PRIVILEGES ON *. * TO test@% IDENTIFIED BY 'test'

3. Delete user

Syntax: DROP USER username;//username refers to username with host, that is, test@localhost. If host,host is not specified, it is%.

Example: DROP USER test@localhost

4.MySQL permission description

4.1 View a user's permissions

Mysql > show grants for root@localhost

4.2 interpretation of each authority

Authority

Column

Description

CREATE

Create_priv

Create databases and tables

DROP

Drop_priv

Discard (delete) databases and tables

GRANT OPTION

Grant_priv

A database, table, or saved program

REFERENCES

References_priv

Not used

ALTER

Alter_priv

Modify tables and indexes

DELETE

Delete_priv

Table

INDEX

Index_priv

Create or discard indexes

INSERT

Insert_priv

Insert a new row into the table

SELECT

Select_priv

Records in the key table

UPDATE

Update_priv

Modify existing table records

CREATE VIEW

Create_view_priv

View

SHOW VIEW

Show_view_priv

View

ALTER ROUTINE

Alter_routine_priv

Saved program

CREATE ROUTINE

Www.2cto.com

Create_routine_priv

Saved program

EXECUTE

Execute_priv

Saved program

FILE

File_priv

Read or write files on the server

CREATE TEMPORARY TABLES

Create_tmp_table_priv

Server management

LOCK TABLES

Lock_tables_priv

Server management

CREATE USER

Create_user_priv

Server management

PROCESS

Www.2cto.com

Process_priv

View thread information executed on the server or kill threads

RELOAD

Reload_priv

Reload the authorization table or empty the log, host cache, or table cache

REPLICATION CLIENT

Repl_client_priv

Server management

REPLICATION SLAVE

Repl_slave_priv

Server management

SHOW DATABASES

Show_db_priv

Server management

SHUTDOWN

Shutdown_priv

Shut down the server

SUPER

Super_priv

Is it helpful for you to read the above content? If you want to know more about the relevant knowledge or read more related articles, please follow the industry information channel, thank you for your support.

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.