User rights management mainly has the following functions:
1. You can restrict which libraries and tables users access
2. You can restrict which tables users perform SELECT, CREATE, DELETE, DELETE, ALTER and other operations
3. You can restrict the user's login IP or domain name
4. Can limit the user's own permissions can be authorized to other users
1. User authorization
mysql> grant all privileges on *. * to ‘yangxin‘ @ ‘%‘ identified by ‘yangxin123456’ with grant option;
1
1
all privileges: indicates that all privileges are granted to users. You can also specify specific permissions, such as: SELECT, CREATE, DROP, etc.
on: indicates to which databases and tables these permissions take effect, the format: database name. table name, write "*" here to indicate all databases, all tables. If I want to specify to apply permissions to the user table of the test library, I can write: test.user
to: Which user is granted the permission. Format: "user name" @ "login IP or domain name". % Means there is no limit, and you can log in on any host. For example: "yangxin" @ "192.168.0.%" Means that the user yangxin can only log in at the 192.168.0IP segment
identified by: Specifies the user's login password
with grant option: indicates that users are allowed to authorize their permissions to other users
You can use GRANT to add permissions to the user, the permissions will be automatically superimposed, and will not override the previously granted permissions. For example, if you first add a SELECT permission to the user, and then add an INSERT permission to the user, then the user has both SELECT and INSERT permission.
For the user-specific permission list, please refer to the MySQL official website description: http://dev.mysql.com/doc/refman/5.7/en/privileges-provided.html
Second, refresh permissions
After making permission changes to the user, be sure to reload the permissions and write the permission information from the memory to the database.
mysql> flush privileges;
Three, view user permissions
mysql> grant select, create, drop, update, alter on *. * to ‘yangxin‘ @ ‘localhost‘ identified by ‘yangxin0917’ with grant option;
mysql> show grants for ‘yangxin‘ @ ‘localhost’;
Technology to share pictures
4. Recycling authority
Delete the create permission of user yangxin, the user will not be able to create databases and tables.
mysql> revoke create on *. * from ‘yangxin @ localhost’;
mysql> flush privileges;
1
2
1
2
Five, delete users
mysql> select host, user from user;
+ --------------- + --------- +
| host | user |
+ --------------- + --------- +
|% | root |
|% | test3 |
|% | yx |
| 192.168.0.% | Root |
| 192.168.0.% | Test2 |
| 192.168.0.109 | test |
| :: 1 | yangxin |
| localhost | yangxin |
+ --------------- + --------- +
8 rows in set (0.00 sec)
mysql> drop user ‘yangxin‘ @ ‘localhost’;
Six, user rename
shell> rename user ‘test3’ @ ‘%‘ to ‘test1’ @ ‘%’;
1
1
Seven, modify the password
1> Update mysql.user table
mysql> use mysql;
# before mysql5.7
mysql> update user set password = password (‘123456’) where user = ‘root’;
# mysql5.7 after
mysql> update user set authentication_string = password (‘123456’) where user = ‘root’;
mysql> flush privileges;
2> Use the set password command
Syntax: set password for ‘username’ @ ’login address’ = password (‘password’)
mysql> set password for ‘root‘ @ ‘localhost’ = password (‘123456’);
3> mysqladmin
Syntax: mysqladmin -u username -p old password password new password
2> Restart the mysql service
shell> service mysqld restart
3> Change password
At this time, the user password is not required when logging in with the mysql command in the terminal, and then the password can be changed according to the first way of changing the password.
Technology to share pictures
Note: In the user table of the mysql library, the password field below version 5.7 is password, and the password field above version 5.7 is authentication_string
4> Restore login permission and skip checking configuration
Delete the skip-grant-tables configuration of the mysqld node in my.cnf, and then restart the service.
Mysql has multiple permissions? I often ca n’t remember it. To sum up today, I can remember it firmly in my heart after reading it! ! Technology to share pictures
Obviously a total of 28 permissions: The following is a specific permission introduction: reproduced, record:
One. Permissions table
Three permission tables in the mysql database: user, db, host
The access process of the permission table is:
1) First determine whether the connected IP, user name, and password exist in the table from the three fields of host, user, and password in the user table. If they exist, they will pass the identity verification;
2) When the authority is assigned through authority verification, it is assigned in the order of useràdbàtables_privàcolumns_priv. That is, first check the global permission table user, if the corresponding permission in user is Y, then the user's permissions on all databases are Y, will no longer check db, tables_priv, columns_priv; if N, then check this in the db table The specific database corresponding to the user, and get the permission of Y in db; if it is N in db, check the specific table corresponding to this database in tables_priv, get the permission Y in the table, and so on.
two. MySQL various permissions (a total of 27)
(The following operations are all logged in as root for grant authorization, and logged in as p1 @ localhost to execute various commands.)
1. usage
Connect (login) permissions, create a user, it will automatically grant its usage permissions (default grant).
mysql> grant usage on *. * to ‘p1 ′ @’ localhost ’identified by‘ 123 ’;
This permission can only be used to log in to the database, and cannot perform any operation; and the usage permission cannot be recovered, that is, the REVOKE user cannot delete the user.
2. select
You must have select permission to use select table
mysql> grant select on pyt. * to ‘p1’ @ ’localhost’;
mysql> select * from shop;
3. create
You must have create permission before you can use create table
mysql> grant create on pyt. * to ‘p1’ @ ’localhost’;
4. create routine
You must have permission to create routine before you can use {create | alter | drop} {procedure | function}
mysql> grant create routine on pyt. * to ‘p1 ′ @’ localhost ’;
When the create routine is granted, EXECUTE, ALTER ROUTINE permissions are automatically granted to its creator:
mysql> show grants for ‘p1’ @ ’localhost’;
+ —————————————————————————— +
Grants for p1 @ localhost
+ —————————————————————————— +
| GRANT USAGE ON *. * TO ‘p1’ @ ’localhost’ IDENTIFIED BY PASSWORD ‘* 23AE809DDACAF96AF0FD78ED04B6A265E05AA257’ |
| GRANT SELECT, CREATE, CREATE ROUTINE ON `pyt`. * TO‘ p1 ’@’ localhost ’|
| GRANT EXECUTE, ALTER ROUTINE ON PROCEDURE `pyt`.`pro_shop1` TO‘ p1 ’@’ localhost ’|
+ ——————————————————————————————- +
5. create temporary tables (note that this is tables, not tables)
You must have permission to create temporary tables before you can use create temporary tables.
mysql> grant create temporary tables on pyt. * to ‘p1 ′ @’ localhost ’;
ERROR 2002 (HY000): Ca n’t connect to local MySQL server through socket ‘/tmp/mysql.sock’ (2)
[mysql @ mydev ~] $ cd / u01 / mysql / bin
[mysql @ mydev bin] $ ./mysqld_safe &
[mysql @ mydev bin] $ mysql
24. grant option
With the grant option, you can grant your own permissions to other users (limited to the permissions you already have)
mysql> grant Grant option on pyt. * to p1 @ localhost;
mysql> grant select on pyt. * to p2 @ localhost;
25. file
You can only perform select ..into outfile and load data infile ... operations with file permissions, but do not grant file, process, super permissions to accounts other than administrators. This poses serious security risks.
mysql> grant file on *. * to p1 @ localhost;
mysql> load data infile ‘/home/mysql/pet.txt’ into table pet;
26. super
This permission allows users to terminate any query; modify the SET statement of global variables; use CHANGE MASTER, PURGE MASTER LOGS.
mysql> grant super on *. * to p1 @ localhost;
mysql> purge master logs before ‘mysql-bin.000006’;
27. process
With this permission, users can execute SHOW PROCESSLIST and KILL commands. By default, each user can execute the SHOW PROCESSLIST command, but can only query the process of the user.
mysql> show processlist;
+ —- + —— + ———– + —— + ——— + —— + ——- + —————— +
| Id | User | Host | db | Command | Time | State | Info |
+ —- + —— + ———– + —— + ——— + —— + ——- + —————— +
| 12 | p1 | localhost | pyt | Query | 0 | NULL |
+ —- + —— + ———– + —— + ——— + —— + ——- + —————— +
In addition,
Administrative rights (such as super, process, file, etc.) cannot specify a database, on must be followed by *. *
mysql> grant super on pyt. * to p1 @ localhost;
ERROR 1221 (HY000): Incorrect usage of DB GRANT and GLOBAL PRIVILEGES
The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion;
products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the
content of the page makes you feel confusing, please write us an email, we will handle the problem
within 5 days after receiving your email.
If you find any instances of plagiarism from the community, please send an email to:
info-contact@alibabacloud.com
and provide relevant evidence. A staff member will contact you within 5 working days.