Summary of various methods for adding user and user permissions in mysql

Three methods are commonly used to add user and user permissions in mysql. One is to add users to the user table by using grant or using insert, another simplest way is to use phpmyadmin to add users.

1. grant statement

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

Note:

(1) grant all Permissions

(2) demo. * all tables in the database demo

(3) newuser User Name

(4) @ localhost mysql server on the Local Computer

(5) identpolicied by 'Password' to set the password

Add a user named "test1" with the password "abc" so that he can log on to any host and have the permission to query, insert, modify, and delete all databases. First, use the root user to connect to MYSQL, and then type the following command:

The Code is as follows:	Copy code
Grant select, insert, update, delete on *. * to test1 @ "%" Identified by "abc "; Mysql> grant all privileges on *. * TO monty @ localhost identified by 'Password' with grant option; Mysql> grant select, INSERT, UPDATE, DELETE, CREATE, drop on *. * TO custom@192.168.206. % identified by 'Password ';

2. SQL statements

The Code is as follows:	Copy code
Mysql> insert into user (host, user, password) VALUES ('2017. 192.% ', 'high _ vod', PASSWORD ('high _ vod ')); Mysql> insert into db VALUES ('2017. 168.206.% ', 'high _ vod', 'high _ vod ', 'y ', 'N', 'y ', 'y', 'y', 'y '); Mysql> flush privileges;

Delete a user

The Code is as follows:	Copy code
Use mysql Mysql> Delete FROM user Where User = "xxxxx" and Host = "localhost "; Mysql> flush privileges;

If you do not know the command, we can use a simpler method.Added phpMyadmin users,

1. First, log on to phpMyAdmin.

2. Create a database. For example, in the right-side window of phpMyAdmin, enter the Database Name and click Create.

For example, here we create a database named netshield
If the creation is successful, the following message is displayed:

 

 

3. After the database is created, click "permission" on the right of the main interface to create a database account.

4. On the permission page, click "Add new user"

 

5. On this page, enter the username of the database to be created as netshield.cn, the user's access scope, and password.

 

For example, if the user name is netshield.cn, the database user only allows access from the local machine, and the host is selected as the local user, the password can be automatically generated, click "Generate" below to Generate a random password, and then click "Copy" to automatically fill in the password box, you can also define a password.
The system is more user-friendly. By default, it will check and prompt to grant all the permissions of the Database "netshield", which is exactly what we want.
If none of the following global permissions is selected, a new user can be created by clicking at the bottom of the page.
If the database user is created successfully, the following page is returned:

 

7. After creating a user, you can set the specific access permissions for the user's database.
If you need specific settings, you can continue to set them. If you need to set the permissions for the netshield.cn user separately in the future, you can continue with this operation:

 

For example, select the newly created netshield in the Database List to automatically go to the permission settings page of the database. If you want to grant this user the permissions of other databases, you can continue to set this, but remember that you must never grant any global permissions, which is only what root should have.

In the permission settings, we select all the permissions in the "data" and "structure" columns, and do not select any management permissions. Click to execute.

The permissions above are described below.

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.