加入收藏 | 设为首页 | 会员中心 | 我要投稿 李大同 （https://www.lidatong.com.cn/）- 科技、建站、经验、云计算、5G、大数据,站长网!
当前位置： 首页 > 综合聚焦 > 服务器 > 安全 > 正文

MySQL如何查看用户授予的权限

发布时间：2020-12-15 04:54:00 所属栏目：安全 来源：网络整理
导读：《MySQL如何查看用户授予的权限》要点： 本文介绍了MySQL如何查看用户授予的权限，希望对您有用。如果有疑问，可以联系我们。 导读：在MySQL中,如何查看一个用户被授予了那些权限呢? 授予用户的权限可能分全局层级权限、数据库层级权限、表层级别权限、列层

《MySQL如何查看用户授予的权限》要点：
本文介绍了MySQL如何查看用户授予的权限，希望对您有用。如果有疑问，可以联系我们。

导读：在MySQL中,如何查看一个用户被授予了那些权限呢? 授予用户的权限可能分全局层级权限、数据库层级权限、表层级别权限、列层级别权限、子...
在MySQL中,如何查看一个用户被授予了那些权限呢? 授予用户的权限可能分全局层级权限、数据库层级权限、表层级别权限、列层级别权限、子程序层级权限.具体分类如下：


全局层级

全局权限适用于一个给定服务器中的所有数据库.这些权限存储在mysql.user表中.GRANT ALL ON *.*和REVOKE ALL ON *.*只授予和撤销全局权限.

数据库层级

数据库权限适用于一个给定数据库中的所有目标.这些权限存储在mysql.db和mysql.host表中.GRANT ALL ON db_name.*和REVOKE ALL ON db_name.*只授予和撤销数据库权限.
???
表层级

表权限适用于一个给定表中的所有列.这些权限存储在mysql.tables_priv表中.GRANT ALL ON db_name.tbl_name和REVOKE ALL ON db_name.tbl_name只授予和撤销表权限.

列层级

列权限适用于一个给定表中的单一列.这些权限存储在mysql.columns_priv表中.当使用REVOKE时,您必须指定与被授权列相同的列.

子程序层级

CREATE ROUTINE,ALTER ROUTINE,EXECUTE和GRANT权限适用于已存储的子程序.这些权限可以被授予为全局层级和数据库层级.而且,除了CREATE ROUTINE外,这些权限可以被授予为子程序层级,并存储在mysql.procs_priv表中.

1：那么我们来创建一个测试账号test,授予全局层级的权限.如下所示：

mysql> grant select,insert on *.* to test@'%' identified by 'test';
Query OK,0 rows affected (0.01 sec)

mysql> flush privileges;
Query OK,0 rows affected (0.00 sec)

mysql>?

那么可以用下面两种方式查询授予test的权限.如下所示：

  1. mysql>?show?grants?for?test;?
  2. +--------------------------------------------------------------------------------------------------------------+?
  3. |?Grants?for?test@%????????????????????????????????????????????????????????????????????????????????????????????|?
  4. +--------------------------------------------------------------------------------------------------------------+?
  5. |?GRANT?SELECT,?INSERT?ON?*.*?TO?'test'@'%'?IDENTIFIED?BY?PASSWORD?'*94BDCEBE19083CE2A1F959FD02F964C7AF4CFC29'?|?
  6. +--------------------------------------------------------------------------------------------------------------+?
  7. 1?row?in?set?(0.00?sec)?
  8. ??
  9. mysql>?select?*?from?mysql.user?where?user='test'G;?
  10. ***************************?1.?row?***************************?
  11. ??????????????????Host:?%?
  12. ??????????????????User:?test?
  13. ??????????????Password:?*94BDCEBE19083CE2A1F959FD02F964C7AF4CFC29?
  14. ???????????Select_priv:?Y?
  15. ???????????Insert_priv:?Y?
  16. ???????????Update_priv:?N?
  17. ???????????Delete_priv:?N?
  18. ???????????Create_priv:?N?
  19. ?????????????Drop_priv:?N?
  20. ???????????Reload_priv:?N?
  21. ?????????Shutdown_priv:?N?
  22. ??????????Process_priv:?N?
  23. ?????????????File_priv:?N?
  24. ????????????Grant_priv:?N?
  25. ???????References_priv:?N?
  26. ????????????Index_priv:?N?
  27. ????????????Alter_priv:?N?
  28. ??????????Show_db_priv:?N?
  29. ????????????Super_priv:?N?
  30. ?Create_tmp_table_priv:?N?
  31. ??????Lock_tables_priv:?N?
  32. ??????????Execute_priv:?N?
  33. ???????Repl_slave_priv:?N?
  34. ??????Repl_client_priv:?N?
  35. ??????Create_view_priv:?N?
  36. ????????Show_view_priv:?N?
  37. ???Create_routine_priv:?N?
  38. ????Alter_routine_priv:?N?
  39. ??????Create_user_priv:?N?
  40. ????????????Event_priv:?N?
  41. ??????????Trigger_priv:?N?
  42. Create_tablespace_priv:?N?
  43. ??????????????ssl_type:??
  44. ????????????ssl_cipher:??
  45. ???????????x509_issuer:??
  46. ??????????x509_subject:??
  47. ?????????max_questions:?0?
  48. ???????????max_updates:?0?
  49. ???????max_connections:?0?
  50. ??max_user_connections:?0?
  51. ????????????????plugin:?mysql_native_password?
  52. ?authentication_string:??
  53. ??????password_expired:?N?
  54. 1?row?in?set?(0.04?sec)?
  55. ??
  56. ERROR:??
  57. No?query?specified?
  58. ??
  59. mysql>??

MySQL如何查看用户授予的权限




2：那么我们来创建一个测试账号test,授予数据库层级的权限.如下所示：
?

  1. mysql>?drop?user?test;?
  2. Query?OK,?0?rows?affected?(0.00?sec)?
  3. ??
  4. mysql>?grant?select,insert,update,delete?on?MyDB.*?to?test@'%'?identified?by?'test';?
  5. Query?OK,?0?rows?affected?(0.01?sec)?
  6. ??
  7. mysql>??
  8. ??
  9. mysql>?select?*?from?mysql.user?where?user='test'G;?--可以看到无任何授权.?
  10. mysql>?select?*?from?mysql.db?where?user='test'G;?
  11. ***************************?1.?row?***************************?
  12. ?????????????????Host:?%?
  13. ???????????????????Db:?MyDB?
  14. ?????????????????User:?test?
  15. ??????????Select_priv:?Y?
  16. ??????????Insert_priv:?Y?
  17. ??????????Update_priv:?Y?
  18. ??????????Delete_priv:?Y?
  19. ??????????Create_priv:?N?
  20. ????????????Drop_priv:?N?
  21. ???????????Grant_priv:?N?
  22. ??????References_priv:?N?
  23. ???????????Index_priv:?N?
  24. ???????????Alter_priv:?N?
  25. Create_tmp_table_priv:?N?
  26. ?????Lock_tables_priv:?N?
  27. ?????Create_view_priv:?N?
  28. ???????Show_view_priv:?N?
  29. ??Create_routine_priv:?N?
  30. ???Alter_routine_priv:?N?
  31. ?????????Execute_priv:?N?
  32. ???????????Event_priv:?N?
  33. ?????????Trigger_priv:?N?
  34. 1?row?in?set?(0.04?sec)?
  35. ??
  36. ERROR:??
  37. No?query?specified?
  38. ??
  39. mysql>??
  40. mysql>?show?grants?for?test;?
  41. +-----------------------------------------------------------------------------------------------------+?
  42. |?Grants?for?test@%???????????????????????????????????????????????????????????????????????????????????|?
  43. +-----------------------------------------------------------------------------------------------------+?
  44. |?GRANT?USAGE?ON?*.*?TO?'test'@'%'?IDENTIFIED?BY?PASSWORD?'*94BDCEBE19083CE2A1F959FD02F964C7AF4CFC29'?|?
  45. |?GRANT?SELECT,?INSERT,?UPDATE,?DELETE?ON?`MyDB`.*?TO?'test'@'%'??????????????????????????????????????|?
  46. +-----------------------------------------------------------------------------------------------------+?
  47. 2?rows?in?set?(0.00?sec)?
  48. ??
  49. mysql>??

3：那么我们来创建一个测试账号test,授予表层级的权限.如下所示：

?

  1. mysql>?drop?user?test;?
  2. Query?OK,?0?rows?affected?(0.00?sec)?
  3. ??
  4. mysql>?flush?privileges;?
  5. Query?OK,?0?rows?affected?(0.00?sec)?
  6. ??
  7. mysql>?grant?all?on?MyDB.kkk?to?test@'%'?identified?by?'test';?
  8. Query?OK,?0?rows?affected?(0.01?sec)?
  9. ??
  10. mysql>??
  11. ??
  12. mysql>?show?grants?for?test;?
  13. +-----------------------------------------------------------------------------------------------------+?
  14. |?Grants?for?test@%???????????????????????????????????????????????????????????????????????????????????|?
  15. +-----------------------------------------------------------------------------------------------------+?
  16. |?GRANT?USAGE?ON?*.*?TO?'test'@'%'?IDENTIFIED?BY?PASSWORD?'*94BDCEBE19083CE2A1F959FD02F964C7AF4CFC29'?|?
  17. |?GRANT?ALL?PRIVILEGES?ON?`MyDB`.`kkk`?TO?'test'@'%'??????????????????????????????????????????????????|?
  18. +-----------------------------------------------------------------------------------------------------+?
  19. 2?rows?in?set?(0.00?sec)?
  20. ??
  21. mysql>?select?*?from?mysql.tables_privG;?
  22. ***************************?1.?row?***************************?
  23. ???????Host:?%?
  24. ?????????Db:?MyDB?
  25. ???????User:?test?
  26. ?Table_name:?kkk?
  27. ????Grantor:?root@localhost?
  28. ??Timestamp:?0000-00-00?00:00:00?
  29. ?Table_priv:?Select,Insert,Update,Delete,Create,Drop,References,Index,Alter,Create?View,Show?view,Trigger?
  30. Column_priv:??
  31. 1?row?in?set?(0.01?sec)?
  32. ??
  33. ERROR:??
  34. No?query?specified?
  35. ??
  36. mysql>??

MySQL如何查看用户授予的权限



4：那么我们来创建一个测试账号test,授予列层级的权限.如下所示：

?

  1. mysql>?drop?user?test;?
  2. Query?OK,?0?rows?affected?(0.00?sec)?
  3. ??
  4. mysql>?grant?select?(id,?col1)?on?MyDB.TEST1?to?test@'%'?identified?by?'test';?
  5. Query?OK,?0?rows?affected?(0.01?sec)?
  6. ??
  7. mysql>?flush?privileges;?
  8. Query?OK,?0?rows?affected?(0.00?sec)?
  9. ??
  10. mysql>??
  11. ??
  12. ??
  13. mysql>?select?*?from?mysql.columns_priv;?
  14. +------+------+------+------------+-------------+---------------------+-------------+?
  15. |?Host?|?Db???|?User?|?Table_name?|?Column_name?|?Timestamp???????????|?Column_priv?|?
  16. +------+------+------+------------+-------------+---------------------+-------------+?
  17. |?%????|?MyDB?|?test?|?TEST1??????|?id??????????|?0000-00-00?00:00:00?|?Select??????|?
  18. |?%????|?MyDB?|?test?|?TEST1??????|?col1????????|?0000-00-00?00:00:00?|?Select??????|?
  19. +------+------+------+------------+-------------+---------------------+-------------+?
  20. 2?rows?in?set?(0.00?sec)?
  21. ??
  22. mysql>?show?grants?for?test;?
  23. +-----------------------------------------------------------------------------------------------------+?
  24. |?Grants?for?test@%???????????????????????????????????????????????????????????????????????????????????|?
  25. +-----------------------------------------------------------------------------------------------------+?
  26. |?GRANT?USAGE?ON?*.*?TO?'test'@'%'?IDENTIFIED?BY?PASSWORD?'*94BDCEBE19083CE2A1F959FD02F964C7AF4CFC29'?|?
  27. |?GRANT?SELECT?(id,?col1)?ON?`MyDB`.`TEST1`?TO?'test'@'%'?????????????????????????????????????????????|?
  28. +-----------------------------------------------------------------------------------------------------+?
  29. 2?rows?in?set?(0.00?sec)?
  30. ??
  31. mysql>??

MySQL如何查看用户授予的权限



5：那么我们来创建一个测试账号test,授子程序层级的权限.如下所示：

?

  1. mysql>?DROP?PROCEDURE?IF?EXISTS?PRC_TEST;?
  2. Query?OK,?0?rows?affected?(0.00?sec)?
  3. ??
  4. mysql>?DELIMITER?//?
  5. mysql>?CREATE?PROCEDURE?PRC_TEST()?
  6. ????->?BEGIN?
  7. ????->????SELECT?*?FROM?kkk;?
  8. ????->?END?//?
  9. Query?OK,?0?rows?affected?(0.00?sec)?
  10. ??
  11. mysql>?DELIMITER?;?
  12. ??
  13. mysql>?grant?execute?on?procedure?MyDB.PRC_TEST?to?test@'%'?identified?by?'test';?
  14. Query?OK,?0?rows?affected?(0.00?sec)?
  15. ??
  16. mysql>??
  17. ??
  18. ??
  19. mysql>?show?grants?for?test;?
  20. +-----------------------------------------------------------------------------------------------------+?
  21. |?Grants?for?test@%???????????????????????????????????????????????????????????????????????????????????|?
  22. +-----------------------------------------------------------------------------------------------------+?
  23. |?GRANT?USAGE?ON?*.*?TO?'test'@'%'?IDENTIFIED?BY?PASSWORD?'*94BDCEBE19083CE2A1F959FD02F964C7AF4CFC29'?|?
  24. |?GRANT?EXECUTE?ON?PROCEDURE?`MyDB`.`prc_test`?TO?'test'@'%'??????????????????????????????????????????|?
  25. +-----------------------------------------------------------------------------------------------------+?
  26. 2?rows?in?set?(0.00?sec)?
  27. ??
  28. mysql>?select?*?from?mysql.procs_priv?where?User='test';?
  29. +------+------+------+--------------+--------------+----------------+-----------+---------------------+?
  30. |?Host?|?Db???|?User?|?Routine_name?|?Routine_type?|?Grantor????????|?Proc_priv?|?Timestamp???????????|?
  31. +------+------+------+--------------+--------------+----------------+-----------+---------------------+?
  32. |?%????|?MyDB?|?test?|?PRC_TEST?????|?PROCEDURE????|?root@localhost?|?Execute???|?0000-00-00?00:00:00?|?
  33. +------+------+------+--------------+--------------+----------------+-----------+---------------------+?
  34. 1?row?in?set?(0.00?sec)?
  35. ??
  36. mysql>??

MySQL如何查看用户授予的权限



所以,如果需要查看用户被授予的权限,就需要从这五个层级来查看被授予的权限.从上到下或从小到上,逐一检查各个层级被授予的权限.

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!
    相关内容
      推荐文章
        站长推荐
        热点阅读

          【免责声明】本站内容转载自互联网，其发布内容言论不代表本站观点，如果其链接、内容的侵犯您的权益，烦请提交相关链接至邮箱bqsm@foxmail.com我们将及时予以处理。

          建议您使用1920×1080分辨率、谷歌浏览器Google Chrome、Microsoft Edge以获得本站的最佳浏览效果

          Copygight © 2008-2022 https://www.lidatong.com.cn/ All Rights Reserved. 李大同