Detailed explanation of examples of adding new user permissions in MySQL

There are 2 different ways to add users: by using the GRANT statement or by directly manipulating the MySQL authorization table. A better approach is to use GRANT statements, since they are more concise and appear to be less error-prone.

The following example shows how to install a new user using MySQL client. These examples assume that permissions are installed by default as described in the previous section. This means that in order to make changes, you must be on the same machine as MySQL is running, you must be connected as the MySQL root user, and the root user must have insert permissions and reload administrative permissions on the MySQL database. Additionally, if you change the root user password, you must specify it with the following MySQL command.
You can add new users by issuing a GRANT statement:
The 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 3 new users:

monty: A full superuser who can connect to the server from anywhere, but must use a password ('something') to do this. Note that we must issue a GRANT statement for monty@localhost and monty@"%". If we add the localhost entry , the anonymous user entry for localhost will be given priority when we connect from the local host created by mysql_install_db, because it has a more specific Host field value, so it comes earlier in the user table sort order

admin: A user who can connect from localhost without a password and is granted reload and process administrative rights. This allows the user to execute the mysqladmin reload, mysqladmin refresh and mysqladmin
flush-* commands, as well as mysqladmin process##. #list. No database-related permissions are granted. They can be granted later by issuing another GRANT statement. dummy: A user can connect without a password, but only from the local host. Permissions are set to 'N' - the USAGE permission type allows you to set up a user without permissions. It assumes that you will grant database-related permissions later. You can also add the same directly by issuing an INSERT statement. User access information, and then tell the server to load the authorization table again:

The code is as follows:

shell> mysql --user=root mysql   
mysql> INSERT INTO user VALUES('localhost','monty',PASSW
ORD
('something'),   
        'Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','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;

[Related recommendations]

1.

Mysql free video tutorial

2. Detailed explanation of examples of php storing pictures in mysql

3. Use mysqldump to back up and restore tables Detailed explanation of examples

4. Detailed explanation of examples of replacing content in the database with regular expressions

5. Instances of changing passwords and access restrictions in MySQL Detailed explanation

The above is the detailed content of Detailed explanation of examples of adding new user permissions in MySQL. For more information, please follow other related articles on the PHP Chinese website!