MySQL Database Grant command Add new user tutorials

Grant statement adds new users:

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

Format: Grant Select on database. * To User name @ login host identified by "password"

Example 1

The code is as follows	Copy Code
shell> mysql--user=root mysql mysql> GRANT all privileges in *.* to monty@localhost identified by ' something ' With GRANT OPTION; Mysql> GRANT all privileges in *.* to monty@ "%" identified by ' something ' with GRANT OPTION; Mysql> GRANT reload,process on *.* to Admin@localhost; Mysql> GRANT USAGE on *.* to Dummy@localhost;

Add the same user access information directly by issuing an INSERT statement, and then tell the server to reload the authorization table again:

code is as follows

copy code

shell> mysql--user=root mysql mysql> INSERT into user VALUES (' localhost ', ' Monty ', PASSWORD (' Something '), ' Y ' , ' y ', ' y ', ' y ', ' y ', ' y ', ' y ', ' y ', ' y ', ' y ', ' y ', ' y ', ' y-axis ', ' Y ' mysql> INSERT into user VALUES ('% ', ' Monty ', PASSWORD (' Something '), ' y ', ' y ', ' y ', ' y ', ' y ', ' y ', ' y ', ' y ', ' y ', ', ', ', ' y ', ' y ', ' 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 a user, run these commands:

The code is as follows	Copy Code
shell> mysql--user=root mysql mysql> GRANT select,insert,update,delete,create,drop on bankaccount.* to Custom@localhost identified by ' stupid '; Mysql> GRANT Select,insert,update,delete,create,drop on expenses.* to custom@whitehouse.gov Identifie D by ' stupid '; Mysql> GRANT Select,insert,update,delete,create,drop on customer.* to custom@ '% ' identified by ' Stupi d ';

To run these commands by directly modifying the authorization table to set user permissions (note that flush privileges at the end):

The code is as follows

Copy Code

shell> mysql--user=root mysql 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 ', ' y ', ' y ', ' 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 ', ' y ', ' y ', ' y ', ' y ');     Mysql> INSERT into db (Host,db,user,select_priv,insert_priv,update_priv,delete_priv, Create_priv,drop_priv) VALUES ('% ', ' customer ', ' Custom ', ' y ', ' y ', ' y ', ' y ', ' y ', ' y '); mysql> FLUSH privileges;

If the above code makes you not see clearly or look dizzy, we can use phpMyAdmin to modify the password.

1. New database:

After entering the phpMyAdmin management interface, find the following text "Create a new database," and then fill in the name of the database to be created, select the encoding of the database in the following drop-down list, generally "Gb2312_chinese_bin", click the "Create" button, This creates a new database.

2. Create a new account for this database

Click "Permissions" in the phpMyAdmin Admin interface, open the link, click "Add New User", in the open page will see "Login Information", enter the user name, host (generally local: localhost), password, because we are now to a single user to configure a single database permissions, So we don't select global permissions here, and then click the "Execute" button below to create a new username.

3. Add this database permission for the new user

After the user is added, the top of the admin interface prompts you that you have added a new user. We will now add permissions to the newly created user, find "Specify Permissions by Database" below, and then click the Drop-down list after "Add permissions in the following database", select the database, select all of the data below, Then select the structure in addition to the bottom three of the check box, all the others are not selected, and then click the "Execute" button, so that the user has configured the full management of the database permissions.

Note: Because we just want to give this user all the permissions to manage the database, but there is no other database management permissions, so when configuring permissions must pay attention to.

Attached: The user rights shown in the phpMyAdmin are in English, we enclose the Chinese translation:

Data:

SELECT: Allows reading of data.

Insert: Allows inserting and replacing data.

Updata: Allows data to be changed.

Delete: Allows data to be deleted.

File: Allows you to import data from a file and export the data to a file.

Structure:

Create: Allows creation of new databases and tables.

Alter: Allows you to modify the structure of an existing table.

Index: Allows indexes to be created and deleted.

Drop: Allows database and tables to be deleted.

Create temporary tables: Allows temporary tables to be created.

CREATE VIEW: Allows new comments to be created.

Show view: Displays the views created.

Create routine: Allows the creation of stored procedures.

Alter routine: Allows change and descent of stored procedures.

Execute: Allow execution of stored procedures.

Management:

Grant: Allows adding users and permissions, and does not allow reload of permission tables.

SUPER: Allows the connection to be made when the maximum allowable number is reached.

Process: Allows you to view the full query in the process list.

RELOAD: Allow reload of server settings and Refresh Server cache.

SHUTDOWN: Allow shutdown of server.

Show DATABASES: Allows access to the complete list of databases.

Lock tables: A table that allows the current thread to be locked.

REFERENCES: Not valid in this version of MySQL.

REPLICATION client: The user has the right to ask where the Affiliate/controller is.

REPLICATION SLAVE: Need to reply to the accessory.

Create User: Allows creation, descent, and renaming of user accounts.