欧美97色伦欧美一区二区日韩,国产福利片在线观看,freexxx性欧美vide0高清,西西亚洲,日本欧美国产精品第一页久久,成人18免费软件

當前位置： 維易PHP培訓學院 > 服務器運維 > 內容正文

MySQL如何查看用戶授予的權限

作者：VEPHP   時間 2017-09-27

《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_priv\G;?
  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>?flush?privileges;?
  5. Query?OK,?0?rows?affected?(0.00?sec)?
  6. ??
  7. mysql>?grant?select?(id,?col1)?on?MyDB.TEST1?to?test@'%'?identified?by?'test';?
  8. Query?OK,?0?rows?affected?(0.01?sec)?
  9. ??
  10. mysql>?flush?privileges;?
  11. Query?OK,?0?rows?affected?(0.00?sec)?
  12. ??
  13. mysql>??
  14. ??
  15. ??
  16. mysql>?select?*?from?mysql.columns_priv;?
  17. +------+------+------+------------+-------------+---------------------+-------------+?
  18. |?Host?|?Db???|?User?|?Table_name?|?Column_name?|?Timestamp???????????|?Column_priv?|?
  19. +------+------+------+------------+-------------+---------------------+-------------+?
  20. |?%????|?MyDB?|?test?|?TEST1??????|?id??????????|?0000-00-00?00:00:00?|?Select??????|?
  21. |?%????|?MyDB?|?test?|?TEST1??????|?col1????????|?0000-00-00?00:00:00?|?Select??????|?
  22. +------+------+------+------------+-------------+---------------------+-------------+?
  23. 2?rows?in?set?(0.00?sec)?
  24. ??
  25. mysql>?show?grants?for?test;?
  26. +-----------------------------------------------------------------------------------------------------+?
  27. |?Grants?for?test@%???????????????????????????????????????????????????????????????????????????????????|?
  28. +-----------------------------------------------------------------------------------------------------+?
  29. |?GRANT?USAGE?ON?*.*?TO?'test'@'%'?IDENTIFIED?BY?PASSWORD?'*94BDCEBE19083CE2A1F959FD02F964C7AF4CFC29'?|?
  30. |?GRANT?SELECT?(id,?col1)?ON?`MyDB`.`TEST1`?TO?'test'@'%'?????????????????????????????????????????????|?
  31. +-----------------------------------------------------------------------------------------------------+?
  32. 2?rows?in?set?(0.00?sec)?
  33. ??
  34. 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.01?sec)?
  15. ??
  16. mysql>?flush?privileges;?
  17. Query?OK,?0?rows?affected?(0.00?sec)?
  18. ??
  19. mysql>??
  20. ??
  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?EXECUTE?ON?PROCEDURE?`MyDB`.`prc_test`?TO?'test'@'%'??????????????????????????????????????????|?
  28. +-----------------------------------------------------------------------------------------------------+?
  29. 2?rows?in?set?(0.00?sec)?
  30. ??
  31. mysql>?select?*?from?mysql.procs_priv?where?User='test';?
  32. +------+------+------+--------------+--------------+----------------+-----------+---------------------+?
  33. |?Host?|?Db???|?User?|?Routine_name?|?Routine_type?|?Grantor????????|?Proc_priv?|?Timestamp???????????|?
  34. +------+------+------+--------------+--------------+----------------+-----------+---------------------+?
  35. |?%????|?MyDB?|?test?|?PRC_TEST?????|?PROCEDURE????|?root@localhost?|?Execute???|?0000-00-00?00:00:00?|?
  36. +------+------+------+--------------+--------------+----------------+-----------+---------------------+?
  37. 1?row?in?set?(0.00?sec)?
  38. ??
  39. mysql>??

MySQL如何查看用戶授予的權限

所以,如果需要查看用戶被授予的權限,就需要從這五個層級來查看被授予的權限.從上到下或從小到上,逐一檢查各個層級被授予的權限.

轉載請注明本頁網址：
http://www.snjht.com/jiaocheng/5531.html


    標簽：