Home>

Knowledge induction

Because mysql uses user and host fields to determine the identity of the userThis creates a problem,It is the host to which a client belongs.

If a client matches several hosts at the same time, the determination of the user will be ranked according to the following priorities

The more accurate the basic idea, the higher the priority On the host column, the more certain the host is, the higher the priority, [localhost, 192.168.1.1, wiki.yfang.cn] has priority over [192.168.%,%.yfang.cn], and has priority over [192.%,%.cn] , Takes precedence over [%] On the user column, the explicit username takes precedence over the empty username. (The empty username matches all usernames,Ie anonymous users match all users) host column takes precedence over user column

When you log in to the mysql server,You can use user () and current_user () to check the user you are logged in to.

user () returns the user and host you specified when connecting to the server current_user () returns the users and hosts matched in the mysql.user table,This will determine your permissions in the database

When you log into the server and execute the mysql command,The system will check if your current user (current_user) has permission to perform the current operation.

First check the global permissions in the user table,If the conditions are met,Then perform the operation If the above fails,Then check whether the mysql.db table has the permissions that meet the conditions,If satisfied,Then perform the operation If the above fails,Then check mysql.table_priv and mysql.columns_priv (if stored procedure operation, check mysql.procs_priv), if it meets,Then perform the operation If the above checks fail,The system refuses to perform the operation.

Testing process

Create 3 usernames same,user with different host and permissions

mysql>grant select on *. * to ""@"%" identified by "123";
query ok, 0 rows affected (0.00 sec)
mysql>grant select, createon *. * to "bruce"@"10.20.0.232" identified by "123";
query ok, 0 rows affected (0.01 sec)
mysql>grant select, create, deleteon *. * to "bruce"@"%" identified by "123";
query ok, 0rows affected (0.00 sec)

Log in from another machine

[[email protected] ~] #mysql -ubruce -p -h10.20.0.231
enter password:
welcome to the mariadb monitor. commands end with;or \ g.
your mysql connection id is 5
server version:5.5.20-log mysql community server (gpl)
this software comes with absolutely no warranty. this is free software,and you are welcome tomodify and redistribute it under the gpl v2 license
type "help;" or "\ h" for help. type "\ c" to clear the current inputstatement.
mysql [(none)]>show grants;
+ ------------------------------------------------- -------------------------------------------------- ---------------------- +
| grants for [email protected] |
+ ------------------------------------------------- -------------------------------------------------- ---------------------- +
grant select, createon *. * to "bruce"@"10.20.0.232" identified by password "* 23ae809ddacaf96af0fd78ed04b6a265e05aa257" |
+ ------------------------------------------------- -------------------------------------------------- ---------------------- +
1 row inset (0.00 sec)
mysql [(none)]>select user (), current_user ();
+ ------------------- + ------------------- +
| user () | current_user () |
+ ------------------- + ------------------- +
| [email protected] | [email protected] |
+ ------------------- + ------------------- +
1 row in set (0.03 sec)

Delete this user and log in

mysql>delete from mysql.userwhereuser="bruce" andhost="10.20.0.232";
query ok, 1row affected (0.00 sec)
mysql>flush privileges;
query ok, 0 rows affected (0.00 sec)
[[email protected] ~] #mysql -ubruce -p -h10.20.0.231
enter password:
welcome to the mariadb monitor. commands end with;or \ g.
your mysql connection id is 6
server version:5.5.20-log mysql community server (gpl)
this software comes with absolutely no warranty. this is free software,and you are welcome tomodify and redistribute it under the gpl v2 license
type "help;" or "\ h" for help. type "\ c" to clear the current inputstatement.
mysql [(none)]>show grants;
+ ------------------------------------------------- -------------------------------------------------- -------------------- +
| grants for bruce @%|
+ ------------------------------------------------- -------------------------------------------------- -------------------- +
| grant select, delete, createon *. * to "bruce"@"%" identified bypassword "* 23ae809ddacaf96af0fd78ed04b6a265e05aa257" |
+ ------------------------------------------------- -------------------------------------------------- -------------------- +
1 row inset (0.00 sec)
mysql [(none)]>select user (), current_user ();
+ ------------------- + ---------------- +
| user () | current_user () |
+ ------------------- + ---------------- +
| [email protected] | bruce @%|
+ ------------------- + ---------------- +
1 row in set (0.00 sec)

Then delete this user as well,Login again

[[email protected] ~] #mysql -ubruce -p -h10.20.0.231
enter password:
welcome to the mariadb monitor. commands end with;or \ g.
your mysql connection id is 8
server version:5.5.20-log mysql community server (gpl)
this software comes with absolutely no warranty. this is free software,and you are welcome tomodify and redistribute it under the gpl v2 license
type "help;" or "\ h" for help. type "\ c" to clear the current inputstatement.
mysql [(none)]>show grants;
+ ------------------------------------------------- -------------------------------------------------- -------------------------------------------------- ------------------------------------------------ +
| grants for @%|
+ ------------------------------------------------- -------------------------------------------------- -------------------------------------------------- ------------------------------------------------ +
| grant select on *. * to ""@"%" identified by password "* 23ae809ddacaf96af0fd78ed04b6a265e05aa257" |
| grant select, insert, update, delete, create, drop, references, index, alter, create temporary tables, lock tables, create view, show view, createroutine, event, trigger on `test`. * to" "@"%"|
| grant select, insert, update, delete, create, drop, references, index, alter, createtemporary tables, lock tables, create view, show view, createroutine, event, trigger on `test \ _%`. * to ""@"%" |
+ ------------------------------------------------- -------------------------------------------------- -------------------------------------------------- ------------------------------------------------ +
mysql [(none)]>select user (), current_user ();
+ ------------------- + ---------------- +
| user () | current_user () |
+ ------------------- + ---------------- +
| [email protected] | @%|
+ ------------------- + ---------------- +
1 row in set (0.00 sec)

For empty users,By default, it has permission to test or test database.

  • Previous JS layer effect code for scrolling with a scroll bar on a web page
  • Next C # Cinema Ticketing System Graduation Design (1)