How to add new user permissions in MySQL

There are two different ways to add users: using the GRANT statement or directly operating the MySQL authorization table. The better way is to use the GRANT statement, because they are more concise and seem to have fewer errors.

The following example shows how to use a MySQL client to install a new user. These examples assume that permissions are installed by default according to the previous chapter. This means that, in order to change, you must be on the same machine that MySQL is running, you must be connected as the MySQL root user, and the root user must have the insert permission and reload management permission on the MySQL database. In addition, if you change the root user password, you must specify it using the following MySQL command.

You can add new users by issuing the GRANT statement:

Copy codeThe Code is as follows:
Shell> mysql -- user = root mysql
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;



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 GRANT statement to monty @ localhost and monty @ "%. If we add a localhost entry, the entry created by mysql_install_db for the anonymous user entry of localhost takes priority when we connect from the local Host, because it has a more specific Host field value, therefore, the user table is arranged in the order of users.

Admin: a user who can connect from localhost without a password and is granted reload and process management permissions. This allows you to run the mysqladmin reload, mysqladmin refresh, mysqladmin flush-* commands, and mysqladmin processlist commands. 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:


Copy codeThe Code is as follows:
Shell> mysql -- user = root mysql
Mysql> insert into user VALUES ('localhost', 'monty ', PASSWORD ('something '),
'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;