Use MySQL GRANT statements to add new user practices

Today, we will mainly discuss how to use the MySQL GRANT statement to add new users to the actual operation scheme and illustrate the actual installation steps and specific operations of the MySQL database, if you are interested, you can click the following articles to view them.

How to back up and restore a MySQL database (the best combination with PHP)

VC Implementation and Application of MySQL (best combination with PHP) database interface (2)

How to back up a MySQL database (the best combination with PHP)

MySQL (the best combination with PHP): use Java to test the best combination of MySQL (and PHP) Databases

MySQL (best combination with PHP) database technology 01)

How to connect to MySQL (the best combination with PHP) database in ASP

The easiest way to back up a MySQL database (the best combination with PHP)

MySQL (best combination with PHP) database backup and recovery

MySQL (the best combination with PHP) has too many database connections.

The following example shows how to use MySQL (the best combination with PHP) to install new users. These examples assume that the permission has been installed by default.

This means that to change, you must connect to MySQL (the best combination of PHP and MySQL) on the same machine that is running. You must connect to the root user as MySQL (the best combination of PHP and MySQL, in addition, the root user must have the insert permission and reload management permission on the MySQL (the best combination with PHP) database. In addition, if you change the root user password, you must specify it using the following MySQL (best combination with PHP) command.

You can add new users by issuing the MySQL GRANT statement:

 
 

  
  
Shell> MySQL (the best combination with PHP) -- user = root MySQL (the best combination with PHP)
MySQL (best combination with PHP)> grant all privileges on *.*
TO monty @ localhost identified by 'something' with grant option;
MySQL (best combination with php)> grant all privileges on *. * TO monty @ "%" identified by 'something' with grant option;
MySQL (the best combination with PHP)> MySQL grant reload, process on *. * TO admin @ localhost; MySQL (the best combination with PHP)> MySQL grant usage on *. * TO dummy @ localhost;
 
 

These GRANT statements install three new users:

Monty: A full super user who can connect to the server from anywhere, but must use a password ('something' to do this. Note: You must issue a MySQL GRANT statement for monty @ localhost and monty @ "%.

If we add a localhost entry, the entry created by MySQL (the best combination with PHP) _ install_db will take precedence over the anonymous user entry of localhost when we connect from the local host, because it has a more specific Host field value, it comes earlier in the order of user table arrangement.

Admin: a user who can connect from localhost without a password and is granted reload and process management permissions. This allows you to execute MySQL (the best combination with PHP) admin reload, MySQL (the best combination with PHP) admin refresh and MySQL (the best combination with PHP) admin flush-* command, and MySQL (the best combination with PHP) admin processlist.

No database-related permissions are granted. They can GRANT permissions in the future by issuing another GRANT statement.

Dummy: you can connect to a user without a password, but only from the local host. The global permission is set to 'n' -- the USAGE permission type allows you to set a user without permission. It assumes that you will grant database-related permissions in the future.

You can also directly add the same user access information by issuing an INSERT statement, and then tell the server to load the authorization table again:

 
 

  
  
Shell> MySQL (the best combination with PHP) -- user = root MySQL (the best combination with PHP)
MySQL (best combination with PHP)> insert into user
VALUES ('localhost', 'monty ', PASSWORD ('something '),
'Y ', 'y', 'y ')
MySQL (best combination with PHP)> insert into user
VALUES ('%', 'monty ', PASSWORD ('something'), 'y ', 'y', 'y ')
MySQL (best combination with PHP)> insert into user SET Host = 'localhost', User = 'admin', Reload_priv = 'y', Process_priv = 'y ';
MySQL (best combination with PHP)> insert into user (Host, User, Password) VALUES ('localhost', 'dummy ','');
MySQL (the best combination with PHP)> flush privileges;
 
 

Depends on your MySQL (the best combination with PHP) version, you may have to use a different number of 'y' values (versions earlier than 3.22.11 have fewer permission columns ). For admin users, only the INSERT extension syntax that is more readable in version 3.22.11.

Note: To set a Super user, you only need to create a user table entry with the permission field set to 'y '. No db or host table entries are required.

The permission columns in the user table are not explicitly set by the last INSERT statement (for dummy users), so those columns are assigned the default value 'n '. This is the same thing MySQL grant usage does.

In the following example, add a User custom, which can be connected from the host localhost, server. domain, and whitehouse.gov. He only wants to access the bankaccount database from localhost, the expenses database from whitehouse.gov, and the customer database from all three hosts. He wants to use the password stupid from all three hosts.

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

 
 

  
  
Shell> MySQL (the best combination with PHP) -- user = root MySQL (the best combination with PHP)> MySQL grant select,
INSERT, UPDATE, DELETE, CREATE, drop on bankaccount. * TO custom @ localhost identified by 'stupid ';
MySQL (best combination with PHP)> grant select, INSERT, UPDATE, DELETE, CREATE, drop on expenses .*
TO custom@whitehouse.gov identified by 'stupid ';
MySQL (best combination with PHP)> 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 ):

 
 

  
  
Shell> MySQL (the best combination with PHP) -- user = root MySQL (the best combination with PHP)
MySQL (best combination with PHP)> insert into user (Host, User, Password) VALUES ('localhost', 'custom', PASSWORD ('stupid '));
MySQL (best combination with PHP)> insert into user (Host, User, Password) VALUES ('server. domain ', 'custom', PASSWORD ('stupid '));
MySQL (best combination with PHP)> insert into user (Host, User, Password) VALUES ('whitehouse. gov ', 'custom', PASSWORD ('stupid '));
MySQL (best combination with PHP)> 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 (best combination with PHP)> 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 (best combination with PHP)> insert into db (Host, Db, User, Select_priv, Insert_priv,
Update_priv, Delete_priv, Create_priv, Drop_priv) VALUES ('%', 'customer', 'custom', 'y ', 'y', 'y ');
MySQL (the best combination with PHP)> flush privileges;