MySQL user permission settings

 

MySQL groups and mysql users have been set in MySQL.

When MySQL is used, it is found that mysql-H 127.0.0.1-u mysql-P cannot access the database.

Root # mysql-H 127.0.0.1-u mysql-P
Enter Password :******
Error 1045: Access denied for user: 'mysql @ 127.0.01 '(using password: Yes)

Cause:
MySQL on 127.0.0.1 does not have the permission to connect to MySQL on localhost. You can confirm the permission by using the following method:

Root # mysql-H localhost-u mysql-P
Enter Password :******

Welcome to the MySQL monitor. commands end with; or/g.
Your MySQL connection ID is 4 to server version: 4.0.20a-Debug

Type 'help; 'or'/H' for help. type'/C' to clear the buffer.

Mysql> use MySQL; (this dB stores various MySQL configuration information)
Database changed
Mysql> select host, user from user; (view User Permissions)
+ ------------- + ------- +
| Host | user |
+ ------------- + ------- +
| Localhost |
| Localhost | root |
| Localhost |
| Localhost | MySQL |
+ ------------- + ------- +
6 rows in SET (0.02 Sec)

From this we can see that it can only be accessed as a localhost.

Solution:

Mysql> grant all privileges on *. * To 'root' @ '%' identified by ****** with grant option;
Query OK, 0 rows affected (0.02 Sec) (% indicates all external machines. If you specify a machine, change % to the corresponding machine name)
Mysql> grant all privileges on *. * To 'mysql' @ '%' identified by ****** with grant option;
Query OK, 0 rows affected (0.02 Sec) (% indicates all external machines. If you specify a machine, change % to the corresponding machine name)

Mysql> flush privileges)
Query OK, 0 rows affected (0.03 Sec)

Mysql> select host, user from user; (view User Permissions again)
+ ------------- + ------- +
| Host | user |
+ ------------- + ------- +

| % | MySQL |

| % | Root |
| Localhost |
| Localhost | root |
| Localhost |
| Localhost | MySQL |
+ ------------- + ------- +

Mysql> exit

Try again now:
Root # mysql-H mysql-u root-P
Enter Password :******
Welcome to the MySQL monitor. commands end with; or/g.
Your MySQL connection ID is 9 to server version: 4.0.20a-Debug

Type 'help; 'or'/H' for help. type'/C' to clear the buffer.

Mysql>

The connection is successful.

Note:

The above settings are not limited to changes to the user's permission on the local machine. You can use the specified user login connection on all external machines. When you use MySQL-front to manage databases in windows, some programs may not support passwords due to version issues. The following prompt appears:

1251-client does not support Authentication Protocol requested by server; consider upgrading MYSQL client

You can use mymanager to manage MySQL.

 

Cancel permissions and delete users.

To revoke the permissions of a user, you can use the revoke statement. Except for replacing to with from without the identifiedby or with grant option clause, the revoke syntax is very similar to that of the grant statement :.

Revoke privileges (columns) on what from user.

The user part must match the user part of the original grant statement of the user whose permissions are to be revoked. Priv I l e g e s does not need to be matched. You can use the grant statement to grant permissions, and then use the revoke statement to cancel some of them. The revoke statement only deletes permissions and does not delete users. The user's items are still in the User table, even if you cancel all permissions of the user. This means that the user can still connect to the server. To delete the entire user, you must use the delete statement to directly Delete the user's records from the user table :.

The delete statement deletes the user's items. The flush statement tells the server to reload the authorization table (these tables are automatically reloaded when the grant or revoke statement is used instead of directly modifying the authorization table ). .

//////////////////////////////////////// //////////////////////////////////////// ////////////

The only hosts recognized after MySQL is installed are localhost and 127.0.0.1. If you want to allow the IP address on your machine to log on normally, there are two methods:
First, authorize your IP address 172.16.1.10
Grant privileges on dbname. * to "user" @ "172.16.1.10" identified by "password ";
If it's just for your own use, you can be "lazy ".
Grant all on *. * to "user" @ "172.16.1.10" identified by "password ";
For more information about Grant, see the MySQL user manual.
Mysql> use MySQL;
Database changed
Mysql> insert into user values ('2017. 168.13.21 ', 'root', password ('qqq'), 'y ', 'y ', 'y ', 'y', 'y, you can check the number of fields in MySQL query browser.

The second is to set the root user to be able to log on remotely, that is, to log on from any machine.
Mysql> use MySQL;
Database changed
Mysql> Update user set host = "%" where user = "root ";
**************************************** *
 
 
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 mysqld 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:
Shell> MySQL -- user = root MySQL
Mysql> grant all privileges on *. * to [email = Monty @ localhost

] Monty @ localhost [/Email]
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 [email = admin @ localhost ] Admin @ localhost [/Email];
Mysql> grant usage on *. * to [email = dummy @ localhost ] Dummy @ localhost [/Email];

These grant statements install three new users:
Monty
A complete super user who can connect to the server from anywhere, but must use a password ('something' to do this. Note that we must pair [email = Monty @ localhost] Monty @ localhost [/Email] And Monty @ "%"Issue the grant statement. 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
Users who can connect from localhost without a password and are 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 do not need a password to connect to a user, but you can only access the user 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 -- 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;

Depending on your MySQL version, for the above, 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 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 -- user = root MySQL
Mysql> grant select, insert, update, delete, create, drop
On bankaccount .*
To [email = custom @ localhost] Custom @ localhost [/Email]
Identified by 'stupid ';
Mysql> grant select, insert, update, delete, create, drop
On expenses .*
To [email = custom@whitehouse.gov] Custom@whitehouse.gov [/Email]
Identified by 'stupid ';
Mysql> grant select, insert, update, delete, create, drop
On customer .*
To [email = custom @ '%'] Custom @ [/Email]
Identified by 'stupid ';

Run these commands by directly modifying the authorization table to set user permissions (Note: flush privileges at the end ):
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 ');
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 ');
Mysql> insert into DB
(Host, DB, user, select_priv, insert_priv, update_priv, delete_priv,
Create_priv, drop_priv)
Values ('%', 'customer', 'custom', 'y', 'y ');
Mysql> flush privileges;

The first three insert statements Add User table entries, allowing custom to connect from different hosts with a given password, but no permission is granted (all permissions are set to the default value 'n '). Add dB table entries in the last three insert statements, and grant custom database permissions to the bankaccount, expenses, and customer databases. However, the database can only be accessed from the correct host. Generally, when the authorization table is directly modified, the server must be notified to mount them again (use flush privileges) to make the permission modification take effect.
If you want to give a specific user access to any machine in a given domain, you can issue the following grant statement:
Mysql> Grant...
On *.*
To myusername @ "% .mydomainname.com"
Identified by 'mypassword ';

To do the same thing by directly modifying the authorization table:
Mysql> insert into user values ('% .mydomainname.com', 'myusername ',
Password ('mypassword '),...);
Mysql> flush privileges;