MySQL permission management

Jack088 2020-11-06 21:19:47
mysql permission management

User and rights management :


grant  jurisdiction  on  database . Data sheet  to ' user ' @ ' Host name ';

example : to  xiaogang  Assign all permissions

grant all on *.* to 'xiaogang'@'%';

This is the time  xiaogang  You have   All rights are granted

Permission list



give an example


Connect ( land ) jurisdiction , Create a user , It's automatically granted to them usage jurisdiction ( By default ).

mysql>  grant usage on *.* to 'root′@'localhost' identified by '123';

This permission can only be used for database login , You can't do anything ; And usage Permissions cannot be reclaimed , That is to say REVOKE Users can't delete users .


Have file Only permissions can be executed   select ..into outfile and load data infile… operation , But don't put file, process,  super Permissions are granted to accounts other than administrators , There are serious security risks .

mysql>  grant file on *.* to root@localhost;

mysql> load data infile '/home/mysql/pet.txt' into table pet;


This permission allows the user to terminate any query ; Modify global variables SET sentence ; Use CHANGE  MASTER,PURGE MASTER LOGS.

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

mysql> purge master logs before 'mysql-bin.000006′;


There has to be select Authority , Can be used select  table

mysql>  grant select on pyt.* to 'root′@'localhost';

mysql> select * from shop;


There has to be insert Authority , Can be used insert  into ….. values….

mysql>  grant insert on pyt.* to 'root′@'localhost';

mysql> insert into shop(name) values('aa');


There has to be update Authority , Can be used update  table

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


There has to be delete Authority , Can be used delete  from ….where….( Delete the records in the table )

mysql>  grant delete on pyt.* to 'root′@'localhost';

mysql> delete from table where id=1;


There has to be alter Authority , Can be used alter  table

mysql>  alter table shop modify dealer char(15);

alter routine

Must possess alter  routine Authority , Can be used {alter |drop} {procedure|function}

mysql>grant  alter routine on pyt.* to 'root′@' localhost ‘;

mysql> drop procedure pro_shop;

Query OK, 0 rows affected (0.00 sec)


There has to be create Authority , Can be used create  table

mysql>  grant create on pyt.* to 'root′@'localhost';


There has to be drop Authority , To delete the library 、 surface 、 Indexes 、 View etc.

mysql>  drop database db_name;

mysql> drop table tab_name;

mysql> drop view vi_name;

mysql> drop index in_name;

create routine

Must possess create  routine Authority , Can be used {create |alter|drop} {procedure|function}

mysql>  grant create routine on pyt.* to 'root′@'localhost';

When granted create routine when , Automatically Grant EXECUTE, ALTER ROUTINE Permission to its Creator :

create temporary tables

( Notice that this is tables, No table)

There has to be create  temporary tables Authority , Can be used create temporary tables.

mysql> grant create temporary tables on pyt.* to  'root′@'localhost';

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

mysql> create temporary table tt1(id int);

create view

There has to be create  view Authority , Can be used create view

mysql>  grant create view on pyt.* to 'root′@'localhost';

mysql> create view v_shop as select price from shop;

create user

To use CREATE  USER, Must have mysql The whole database CREATE USER jurisdiction , Or have INSERT jurisdiction .

mysql>  grant create user on *.* to 'root′@'localhost';

or :mysql> grant insert on *.* to root@localhost;

show database

adopt show  database You can only see the database with certain permissions you have , Unless you have the whole picture SHOW DATABASES jurisdiction .

mysql>  show databases;

about [email protected] Users , No, right mysql Database permissions , So when you log in and query with this identity , Can't see mysql database :

show view

Must have show  view jurisdiction , To perform show create view

mysql>  show create view name;


Must have index jurisdiction , To perform [create  |drop] index

mysql>  grant index on pyt.* to [email protected];

mysql> create index ix_shop on shop(article);

mysql> drop index ix_shop on shop;


Execute what exists Functions,Procedures

mysql>  call pro_shoroot(0001,@a);


event It is recommended to use root Users create and maintain .

mysql>  show global variables like 'event_scheduler';

To make event Work ,MySQL The constant GLOBAL event_scheduler It has to be for on Or is it 1

lock tables

Must have lock  tables jurisdiction , Can be used lock tables

mysql>  grant lock tables on pyt.* to [email protected];

mysql> lock tables a1 read;

mysql> unlock tables;


With REFERENCES jurisdiction , Users can use a field in other tables as a foreign key constraint for a table .



Must have reload jurisdiction , To execute flush  [tables | logs | privileges]

mysql>  grant reload on pyt.* to [email protected];

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

mysql> grant reload on *.* to 'root′@'localhost';

Query OK, 0 rows affected (0.00 sec)

mysql> flush tables;

replication client

With this permission, you can query master  server、slave server state .

mysql>  grant Replication client on *.* to [email protected];

or :mysql> grant super on *.* to [email protected];

mysql> show master status;

replication slave

With this permission, you can view the slave server , Read binary logs from the primary server .

mysql>  grant replication slave on *.* to [email protected];

mysql> show slave hosts;

Empty set (0.00 sec)

mysql>show binlog events;


close mysql jurisdiction

[[email protected]  ~]$ mysqladmin shutdown

grant option

Have grant  option, You can give your own permissions to other users ( Limited to the rights you already have )

mysql>  grant Grant option on pyt.* to [email protected];

mysql> grant select on pyt.* to [email protected];


Through this authority , User can execute SHOW  PROCESSLIST and KILL command . By default , Every user can execute SHOW PROCESSLIST command , But you can only query the process of the user .

mysql>  show processlist;

all privileges

All permissions .with  grant option  It can be authorized jointly and severally

mysql>  grant all privileges on pyt.* to [email protected] with grant option;

·   Administrative authority ( Such as  super, process, file etc. ) You can't specify one database ,on It has to be followed by  *.*

·    Someone will ask. truncate Authority , Actually truncate Authority is create+drop, This is something to be aware of

View user authorization information

mysql> show grants for bzfys;


| Grants for [email protected]%                                                                                   |


| GRANT USAGE ON *.* TO [email protected]'%' IDENTIFIED BY PASSWORD '*A399693A49F7EC7C548D0FC376FA52AD293A552F' |


1 row in set (0.00 sec)

The authority granted by general circumstances

User management

mysql>use mysql;

One 、 see

mysql> select host,user,password from user ;

Two 、 establish

mysql> create user  bzfys   IDENTIFIED by 'xxxxx';   //identified by  The plain text password is encrypted and stored as a hash value

3、 ... and 、 modify

mysql>rename   user  bzfys to   buzaifengyaosha;//mysql 5 Can be used later , You need to use update  to update user surface

Four 、 Delete

mysql>drop user buzaifengyaosha;   //mysql5 Before deleting a user, you must first use revoke  Delete user privileges , Then delete the user ,mysql5 after drop  The command can delete the user's related permissions at the same time

5、 ... and 、 Change password ( If you find it back root The password has to be in this way )

mysql> set password for bzfys =password('xxxxxx');

 mysql> update  mysql.user  set  password=password('xxxx')  where user=’bzfys’;5.8 The columns that need to be modified in the future are authentication_string Column update  mysql.user  set  authentication_string=password('xxxx')  where user=’bzfys’

6、 ... and 、 View user permissions

mysql> show grants for bzfys;

7、 ... and 、 To give permission

mysql> grant select on bzfys_db.*  to bzfys;

Recycling permissions

mysql> revoke  select on bzfys_db.*  from  bzfys;  // If the permission does not exist, an error will be reported

  The above command can also use multiple permissions to grant and reclaim at the same time , Permissions are separated by commas

mysql> grant select,update,delete  ,insert  on bzfys_db.*  to  bzfys;

If you want to see the results immediately, use

flush  privileges ;

The command to update  

The following information must be given when setting permissions

1, Permission to be granted

2, A database or table to which access is granted

3, user name

grant and revoke Access can be controlled at several levels

1, Entire server , Use  grant ALL   and revoke  ALL

2, Entire database , Use on  database.*

3, Characteristics table , Use on  database.table

4, Specific columns

5, Specific stored procedures

user In the table host The meaning of the value of the column

%               Match all hosts

localhost    localhost It won't be resolved into IP Address , Directly through UNIXsocket Connect       Will pass TCP/IP Protocol connection , And can only be accessed locally ;

::1                 ::1 It's compatibility support ipv6 Of , It means the same as ipv4 Of

grant  Ordinary data users , Inquire about 、 Insert 、 to update 、 Delete   Rights to all table data in the database .

grant select on testdb.* to [email protected]’%’

grant insert on testdb.* to [email protected]’%’

grant update on testdb.* to [email protected]’%’

grant delete on testdb.* to [email protected]’%’

perhaps , Use one  MySQL  Order to replace :

grant select, insert, update, delete on testdb.* to [email protected]’%’

9>.grant  Database developers , Create table 、 Indexes 、 View 、 stored procedure 、 function ... Such as permissions .

grant  establish 、 modify 、 Delete  MySQL  Data table structure permission .

grant create on testdb.* to [email protected]’192.168.0.%’;

grant alter on testdb.* to [email protected]’192.168.0.%’;

grant drop on testdb.* to [email protected]’192.168.0.%’;

grant  operation  MySQL  Foreign key permissions .

grant references on testdb.* to [email protected]’192.168.0.%’;

grant  operation  MySQL  Temporary table permission .

grant create temporary tables on testdb.* to [email protected]’192.168.0.%’;

grant  operation  MySQL  Index permission .

grant index on testdb.* to [email protected]’192.168.0.%’;

grant  operation  MySQL  View 、 View view source code   jurisdiction .

grant create view on testdb.* to [email protected]’192.168.0.%’;

grant show view on testdb.* to [email protected]’192.168.0.%’;

grant  operation  MySQL  stored procedure 、 function   jurisdiction .

grant create routine on testdb.* to [email protected]’192.168.0.%’; -- now, can show procedure status

grant alter routine on testdb.* to [email protected]’192.168.0.%’; -- now, you can drop a procedure

grant execute on testdb.* to [email protected]’192.168.0.%’;

10>.grant  Ordinary  DBA  Manage someone  MySQL  Database permissions .

grant all privileges on testdb to [email protected]’localhost’

among , keyword  “privileges”  It can be omitted .

11>.grant  senior  DBA  management  MySQL  Permissions for all databases in .

grant all on *.* to [email protected]’localhost’

12>.MySQL grant  jurisdiction , They can work at multiple levels .

1. grant  It works on the whole  MySQL  Server :

grant select on *.* to [email protected]; -- dba  You can query  MySQL  All the tables in the database in .

grant all on *.* to [email protected]; -- dba  Can manage  MySQL  All databases in

2. grant  Acting on a single database :

grant select on testdb.* to [email protected]; -- dba  You can query  testdb  In the table .

3. grant  Acting on a single data table :

grant select, insert, update, delete on testdb.orders to [email protected];

4. grant  Acting on columns in a table :

grant select(id, se, rank) on testdb.apache_log to [email protected];

5. grant  It works on stored procedures 、 On the function :

grant execute on procedure testdb.pr_add to ’dba’@’localhost’

grant execute on function testdb.fn_add to ’dba’@’localhost’

Be careful : After modifying the permissions   Be sure to refresh the service , Or restart the service , Refresh the service with :FLUSH PRIVILEGES.

grant create routine, alter routine, execute ON `blacklist`.* TO 'blacklist'@'%';

create routine Create stored procedure

alter routine,  Modify stored procedure

execute: Execute stored procedures

(adsbygoogle = window.adsbygoogle || []).push({});


  1. 【计算机网络 12(1),尚学堂马士兵Java视频教程
  2. 【程序猿历程,史上最全的Java面试题集锦在这里
  3. 【程序猿历程(1),Javaweb视频教程百度云
  4. Notes on MySQL 45 lectures (1-7)
  5. [computer network 12 (1), Shang Xuetang Ma soldier java video tutorial
  6. The most complete collection of Java interview questions in history is here
  7. [process of program ape (1), JavaWeb video tutorial, baidu cloud
  8. Notes on MySQL 45 lectures (1-7)
  9. 精进 Spring Boot 03:Spring Boot 的配置文件和配置管理,以及用三种方式读取配置文件
  10. Refined spring boot 03: spring boot configuration files and configuration management, and reading configuration files in three ways
  11. 精进 Spring Boot 03:Spring Boot 的配置文件和配置管理,以及用三种方式读取配置文件
  12. Refined spring boot 03: spring boot configuration files and configuration management, and reading configuration files in three ways
  13. 【递归,Java传智播客笔记
  14. [recursion, Java intelligence podcast notes
  15. [adhere to painting for 386 days] the beginning of spring of 24 solar terms
  16. K8S系列第八篇(Service、EndPoints以及高可用kubeadm部署)
  17. K8s Series Part 8 (service, endpoints and high availability kubeadm deployment)
  18. 【重识 HTML (3),350道Java面试真题分享
  19. 【重识 HTML (2),Java并发编程必会的多线程你竟然还不会
  20. 【重识 HTML (1),二本Java小菜鸟4面字节跳动被秒成渣渣
  21. [re recognize HTML (3) and share 350 real Java interview questions
  22. [re recognize HTML (2). Multithreading is a must for Java Concurrent Programming. How dare you not
  23. [re recognize HTML (1), two Java rookies' 4-sided bytes beat and become slag in seconds
  24. 造轮子系列之RPC 1:如何从零开始开发RPC框架
  25. RPC 1: how to develop RPC framework from scratch
  26. 造轮子系列之RPC 1:如何从零开始开发RPC框架
  27. RPC 1: how to develop RPC framework from scratch
  28. 一次性捋清楚吧,对乱糟糟的,Spring事务扩展机制
  29. 一文彻底弄懂如何选择抽象类还是接口,连续四年百度Java岗必问面试题
  30. Redis常用命令
  31. 一双拖鞋引发的血案,狂神说Java系列笔记
  32. 一、mysql基础安装
  33. 一位程序员的独白:尽管我一生坎坷,Java框架面试基础
  34. Clear it all at once. For the messy, spring transaction extension mechanism
  35. A thorough understanding of how to choose abstract classes or interfaces, baidu Java post must ask interview questions for four consecutive years
  36. Redis common commands
  37. A pair of slippers triggered the murder, crazy God said java series notes
  38. 1、 MySQL basic installation
  39. Monologue of a programmer: despite my ups and downs in my life, Java framework is the foundation of interview
  40. 【大厂面试】三面三问Spring循环依赖,请一定要把这篇看完(建议收藏)
  41. 一线互联网企业中,springboot入门项目
  42. 一篇文带你入门SSM框架Spring开发,帮你快速拿Offer
  43. 【面试资料】Java全集、微服务、大数据、数据结构与算法、机器学习知识最全总结,283页pdf
  44. 【leetcode刷题】24.数组中重复的数字——Java版
  45. 【leetcode刷题】23.对称二叉树——Java版
  46. 【leetcode刷题】22.二叉树的中序遍历——Java版
  47. 【leetcode刷题】21.三数之和——Java版
  48. 【leetcode刷题】20.最长回文子串——Java版
  49. 【leetcode刷题】19.回文链表——Java版
  50. 【leetcode刷题】18.反转链表——Java版
  51. 【leetcode刷题】17.相交链表——Java&python版
  52. 【leetcode刷题】16.环形链表——Java版
  53. 【leetcode刷题】15.汉明距离——Java版
  54. 【leetcode刷题】14.找到所有数组中消失的数字——Java版
  55. 【leetcode刷题】13.比特位计数——Java版
  56. oracle控制用户权限命令
  57. 三年Java开发,继阿里,鲁班二期Java架构师
  58. Oracle必须要启动的服务
  59. 万字长文!深入剖析HashMap,Java基础笔试题大全带答案
  60. 一问Kafka就心慌?我却凭着这份,图灵学院vip课程百度云