MySQL database GRANT command Add new user tutorial

In mysql, there are many methods for adding users other than root users. The simplest way for us to understand commands is to directly operate on the GUI, for example, phpmyadmin database management tool. If you know the command, you can directly use the GRANT command to add new users.

Add a new user to the GRANT statement:

(Note: Unlike the above, the following commands in the MYSQL environment are followed by a semicolon as the command Terminator)

Format: grant select on database. * to username @ login host identified by "password"

Example 1

 

The Code is as follows:	Copy code
Shell> mysql -- user = root mysql> grant all privileges on *. * TO monty @ localhost identified by 'something' with grant option; Mysql> grant all privileges on *. * TO monty @ "%" identified by 'something' with grant option; mysql> grant reload, process on *. * TO admin @ localhost; Mysql> grant usage on *. * TO dummy @ localhost;

Directly add the same user access information by issuing the INSERT statement, and then tell the server to load the authorization table again:

 

The Code is as follows:

Copy code

Shell> mysql -- user = root mysql> insert into user VALUES ('localhost', 'monty ', PASSWORD ('something'), 'y', 'y ', 'y ', 'y', 'y ') Mysql> insert into user VALUES ('%', 'monty ', PASSWORD ('something'), 'y ', 'y', 'y ') Mysql> insert into user SET Host = 'localhost', User = 'admin', Reload_priv = 'y', Process_priv = 'y '; Mysql> insert into user (Host, User, Password) VALUES ('localhost', 'dummy', ''); mysql> flush privileges;

To use the GRANT statement to set permissions for individual users, run these commands:

 

The Code is as follows:	Copy code
Shell> mysql -- user = root mysql> grant select, INSERT, UPDATE, DELETE, CREATE, drop on bankaccount. * TO custom @ localhost identified by 'topid'; mysql> grant select, INSERT, UPDATE, DELETE, CREATE, drop on expenses. * TO custom@whitehouse.gov identified by 'stupid '; mysql> grant select, INSERT, UPDATE, DELETE, CREATE, drop on customer. * TO custom @ '%' identified by 'stupid ';

Run these commands by directly modifying the authorization table to set user permissions (Note: flush privileges at the end ):

 

The Code is as follows:

Copy code

Shell> mysql -- user = root mysql> insert into user (Host, User, Password) VALUES ('localhost', 'custom', PASSWORD ('stupid ')); Mysql> insert into user (Host, User, Password) VALUES ('server. domain ', 'custom', PASSWORD ('stupid'); mysql> insert into user (Host, User, Password) VALUES ('whitehouse. gov ', 'custom', PASSWORD ('stupid ')); Mysql> insert into db (Host, Db, User, Select_priv, Insert_priv, Update_priv, Delete_priv, Create_priv, Drop_priv) VALUES ('localhost', 'bankaccount', 'custom ', 'y', 'y'); mysql> insert into db (Host, Db, User, Select_priv, insert_priv, Update_priv, Delete_priv, Create_priv, Drop_priv) VALUES ('whitehouse. gov ', 'expenses', 'custom', 'y', 'y '); mysql> insert into db (Host, Db, User, Select_priv, Insert_priv, Update_priv, Delete_priv, Create_priv, Drop_priv) VALUES ('%', 'custom', 'custom ', 'y', 'y '); Mysql> flush privileges;

 
If the above Code makes you confused or dizzy, we can use phpmyadmin to change the password.

1. Create a database:

Go to the phpmyadmin Management page, find the text "Create a new database", enter the name of the database to be created below, and select the database encoding from the drop-down list, generally, it is "gb2312_chinese_bin". Click "CREATE" to create a database.

2. Create an account for this database

On the phpmyadmin Management page, click "Permissions" to open the link and click "Add new user". On the displayed page, you will see "Logon Information" and enter the user name and host (generally local: localhost), password, because we want to assign permissions for a single database to a single user, so we do not select "Global Permissions" here ", click the "execute" button at the bottom to create a new user name.

3. Add the database permission to the new user

After a user is added, the system prompts "you have added a new user" at the top of the management interface. Now, we can add permissions to the new user, find "specify permissions by Database" below, click the drop-down list after "add permissions to the following Database", select a database, and select all the data below, then, select the check boxes except the bottom three for the structure. Do not select any other check boxes. Then, click the "execute" button to configure the permissions for the user to fully manage the database.

Note: we only need to grant this user all the permissions to manage this database, but we do not have the management permissions for other databases. Therefore, you must pay attention to the configuration permissions.

Note: The user permissions displayed in phpmyadmin are in English. We will attach a Chinese translation here:

Data:

SELECT: allows reading data.

INSERT: data can be inserted or replaced.

UPDATA: allows you to change data.

DELETE: allows you to DELETE data.

FILE: allows you to import data from a FILE and export data to a FILE.

Structure:

CREATE: allows the creation of new databases and tables.

ALTER: allows you to modify the structure of an existing table.

INDEX: allows you to create and delete indexes.

DROP: allows you to delete databases and tables.

Create temporary tables: allows the creation of temporary tables.

Create view: allows the creation of new comments.

Show view: displays the created VIEW.

Create routine: allows the creation of stored procedures.

Alter routine: allows changing and dropping stored procedures.

EXECUTE: allows execution of stored procedures.

Management:

GRANT: Users and permissions can be added, but the permission table cannot be reloaded.

SUPER: connection is allowed when the maximum allowed number is reached.

PROCESS: allows you to view complete queries in the PROCESS list.

RELOAD: allows you to RELOAD server settings and refresh the server cache.

SHUTDOWN: Allows server SHUTDOWN.

Show databases: allows access to the complete database list.

Lock tables: The table that allows the current clue to be locked.

REFERENCES: Invalid in MySQL of this version.

Replication client: the user has the right to ask where the submitter/controller is.

Replication slave: required to reply to the submitter.

Create user: allows you to CREATE, drop, and rename USER accounts.