MySQL Grant user Rights summary

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

mysql> mysqladmin -uroot -p123456 password 1234abcd

Note: mysqladmin is located in the bin directory of the mysql installation directory

8. Forgot password
1> Add login skip permission check configuration
Modify my.cnf, add skip-grant-tables configuration in mysqld configuration node

[mysqld]
skip-grant-tables

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 ’;

[mysql @ mydev ~] $ mysql -h localhost -u p1 -p pyt

mysql> create temporary table tt1 (id int);

6. create view

You must have create view permission to use create view

mysql> grant create view on pyt. * to ‘p1 ′ @’ localhost ’;

mysql> create view v_shop as select price from shop;

7. create user

To use CREATE USER, you must have global CREATE USER permissions for the mysql database, or have INSERT permissions.

mysql> grant create user on *. * to ‘p1’ @ ’localhost’;

Or: mysql> grant insert on *. * To p1 @ localhost;

8. insert

You must have insert permission before you can use insert into… .. values….

9. alter

You must have alter permission before you can use alter table

alter table shop modify dealer char (15);

10. alter routine

You must have permission to alter routine before you can use {alter | drop} {pro
cedure | function}

mysql> grant alter routine on pyt. * to ‘p1’ @ ’localhost‘;

mysql> drop procedure pro_shop;

Query OK, 0 rows affected (0.00 sec)

 

mysql> revoke alter routine on pyt. * from ‘p1’ @ ’localhost’;

[mysql @ mydev ~] $ mysql -h localhost -u p1 -p pyt

mysql> drop procedure pro_shop;

ERROR 1370 (42000): alter routine command denied to user ‘p1’ @ ’localhost’ for routine ‘pyt.pro_shop’

11. update

You must have update permission before you can use update table

mysql> update shop set price = 3.5 where article = 0001 and dealer = ’A‘;

12. delete

You must have delete permission before you can use delete from… .where….

13. drop

You must have drop permission before you can use drop database db_name; drop table tab_name;

drop view vi_name; drop index in_name;

14. show database

You can only see the database with certain permissions you have through show database unless you have global SHOW DATABASES permissions.

For the user p1 @ localhost, there is no permission to the mysql database, so when logging in as a query, you cannot see the mysql database:

mysql> show databases;

+ ——————– +

| Database |

+ ——————– +

| information_schema |

| pyt |

| test |

+ ——————– +

15. show view

You must have show view permission to execute show create view.

mysql> grant show view on pyt. * to p1 @ localhost;

mysql> show create view v_shop;

16. index

You must have the index permission to execute [create | drop] index

mysql> grant index on pyt. * to p1 @ localhost;

mysql> create index ix_shop on shop (article);

mysql> drop index ix_shop on shop;

17. excute

Perform existing functions, procedures

mysql> call pro_shop1 (0001, @ a);

+ ——— +

| article |

+ ——— +

| 0001 |

| 0001 |

+ ——— +

mysql> select @a;

+ —— +

| @a |

+ —— +

| 2 |

+ —— +

18. lock tables

You must have lock tables permission to use lock tables

mysql> grant lock tables on pyt. * to p1 @ localhost;

mysql> lock tables a1 read;

mysql> unlock tables;

19. references

With REFERENCES permission, users can use a field of another table as a foreign key constraint of a certain table.

20. reload

You must have the reload permission to execute flush [tables | logs | privileges]

mysql> grant reload on pyt. * to p1 @ localhost;

ERROR 1221 (HY000): Incorrect usage of DB GRANT and GLOBAL PRIVILEGES

mysql> grant reload on *. * to ‘p1’ @ ’localhost’;

Query OK, 0 rows affected (0.00 sec)

mysql> flush tables;

21. replication client

With this permission, you can query the status of master server and slave server.

mysql> show master status;

ERROR 1227 (42000): Access denied; you need the SUPER, REPLICATION CLIENT privilege for this operation

mysql> grant Replication client on *. * to p1 @ localhost;

Or: mysql> grant super on *. * To p1 @ localhost;

mysql> show master status;

+ —————— + ———- + ————– + —————— +

| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |

+ —————— + ———- + ————– + —————— +

| mysql-bin.000006 | 2111 | | |

+ —————— + ———- + ————– + —————— +

mysql> show slave status;

22. replication slave

With this permission, you can view the slave server and read the binary log from the master server.

mysql> show slave hosts;

ERROR 1227 (42000): Access denied; you need the REPLICATION SLAVE privilege for this operation

mysql> show binlog events;

ERROR 1227 (42000): Access denied; you need the REPLICATION SLAVE privilege for this operation

mysql> grant replication slave on *. * to p1 @ localhost;

mysql> show slave hosts;

Empty set (0.00 sec)

mysql> show binlog events;

+ ————— + ——- + ——————- + ———– + ————- + ————– +

| Log_name | Pos | Event_type | Server_id | End_log_pos | Info |

+ ————— + ——- + ————– + ———– + ————- + ————— +

| mysql-bin.000005 | 4 | Format_desc | 1 | 98 | Server ver: 5.0.77-log, Binlog ver: 4 | | mysql-bin.000005 | 98 | Query | 1 | 197 | use `mysql`; create table a1 (i int) engine = myisam |

……………………………………

23. Shutdown

Close MySQL:

[mysql @ mydev ~] $ mysqladmin shutdown

reconnect:

[mysql @ mydev ~] $ mysql

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

mysql> grant super on *. * to p1 @ localhost;

Query OK, 0 rows affected (0.01 sec)

mysql grant user permissions summary

Tag: share picture err bash permission information csdn option variable lock

Original address: https://www.cnblogs.com/albertYe/p/8856006.html