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

This article mainly gives you a brief introduction to the management of Mysql users and the allocation of permissions. You can check the relevant professional terms on the Internet or find some related books to supplement them. We will not dabble here. Let's go straight to the topic. I hope this article on the management of Mysql users and the allocation of permissions can bring you some practical help.

one。 Create a user

1. Log in

The password of the root user is initially empty, after the initial installation of mysql and the configuration of the environment variable.

In the command window, enter:

Mysql-u root-p

Enter directly when prompted for the password.

Mysql >

User table

The users of Mysql are in the user table of the mysql database.

1) Select the database:

Mysql > use mysql

2) after selecting the database, you can execute sql query, update, etc.

Mysql > selecthost,user,password from user

Host is the host address that users can log in to, and only hosts with host can log in with this user. Mysql,password displays the encrypted password.

The meaning of Host:

192.168.1.23 fixed IP

192.168.1% represents the

% match all hosts

Localhost native, localhost will not be resolved to IP address, directly connected through UNIXsocket

127.0.0.1 Native IP, which is connected through the TCP/IP protocol and can only be accessed locally

:: 1 is compatible with ipv6, which represents 127.0.0.1 with ipv4.

3) create a user

Mysql > create user username IDENTIFIED by 'password'

Username is the user name to be created, and password is the password. Enter the clear code here and it will be encrypted in the database.

4) modify the user name

Mysql > rename user username to new_username

Username is the original user name, and new_username is the new user name.

5) Delete a user

Mysql > drop user new_username

6) modify the user's password

Mysql > set password forusername@hostname=password ('XXXX')

You can also operate directly with SQL statements.

Mysql > update user set password='XXXX' where user='username'and host=' hostname'

two。 Assign permissions

Assign permission grant, take back permission revoke

Assign all mysql permissions to the user

Grant all on*.* to username @ localhost

Description: @ followed by hostname'

Assign database permissions to users

Grant ordinary data users, the right to query, insert, update and delete all table data in the database.

Grant select on mydb.* to username @ localhost

Grant insert on mydb.* to username @ localhost

Grant update on mydb.* to username @ localhost

Grant delete on mydb.* to username @ localhost

Or, with a MySQL command:

Grant select, insert, update, delete on mydb.* to username @ localhost

Description: mydb is the name of the database, and mydb.* represents all objects in the mydb database. A single table or view is mydb.log. Log is a log table.

Other commands:

Grant create on

Grant alter on

Grant drop on

Grant references (foreign key) on

Grant create temporary (temporary table) tables on

Grant index on

Grant create view on

Grant show view on

Grant create routine (stored procedures, functions) on

Grant execute on

Note: after modifying permissions, be sure to refresh the service, or restart the service, refresh the service with: FLUSH PRIVILEGES.

The introduction of managing Mysql users and assigning permissions will stop here. If you want to know about other related issues, you can continue to pay attention to our industry information. Our section will capture some industry news and professional knowledge to share with you every day.

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.