{"id":200142,"date":"2025-05-06T08:50:23","date_gmt":"2025-05-06T00:50:23","guid":{"rendered":"https:\/\/server.hk\/cnblog\/200142\/"},"modified":"2025-05-06T08:50:23","modified_gmt":"2025-05-06T00:50:23","slug":"mysql-%e7%94%a8%e6%88%b7%e6%9d%83%e9%99%90%ef%bc%9a%e5%ae%9e%e7%94%a8%e6%8c%87%e5%8d%97","status":"publish","type":"post","link":"https:\/\/server.hk\/cnblog\/200142\/","title":{"rendered":"MySQL \u7528\u6237\u6743\u9650\uff1a\u5b9e\u7528\u6307\u5357"},"content":{"rendered":"<p><b><\/b> <\/p>\n<h1>MySQL \u7528\u6237\u6743\u9650\uff1a\u5b9e\u7528\u6307\u5357<\/h1>\n<p>\u6765\u5230\u4e3b\u673a\u5b9d\u8d1d\u7684\u5927\u5bb6\uff0c\u76f8\u4fe1\u90fd\u662f\u7f16\u7a0b\u5b66\u4e60\u7231\u597d\u8005\uff0c\u5e0c\u671b\u5728\u8fd9\u91cc\u5b66\u4e60\u6570\u636e\u5e93\u76f8\u5173\u7f16\u7a0b\u77e5\u8bc6\u3002\u4e0b\u9762\u672c\u7bc7\u6587\u7ae0\u5c31\u6765\u5e26\u5927\u5bb6\u804a\u804a\u300aMySQL \u7528\u6237\u6743\u9650\uff1a\u5b9e\u7528\u6307\u5357\u300b\uff0c\u4ecb\u7ecd\u4e00\u4e0b\uff0c\u5e0c\u671b\u5bf9\u5927\u5bb6\u7684\u77e5\u8bc6\u79ef\u7d2f\u6709\u6240\u5e2e\u52a9\uff0c\u52a9\u529b\u5b9e\u6218\u5f00\u53d1\uff01<\/p>\n<p><img decoding=\"async\" src=\"https:\/\/www.17golang.com\/uploads\/20240930\/172767892866fa49d0106a9.jpg\" class=\"aligncenter\"><\/p>\n<p>\u5728\u6570\u636e\u5e93\u7ba1\u7406\u9886\u57df\uff0c\u63a7\u5236\u7528\u6237\u8bbf\u95ee\u5bf9\u4e8e\u7ef4\u62a4\u6570\u636e\u5b8c\u6574\u6027\u548c\u5b89\u5168\u6027\u81f3\u5173\u91cd\u8981\u3002\u8fd9\u7bc7\u535a\u6587\u5c06\u5f15\u5bfc\u60a8\u5b8c\u6210\u8bbe\u7f6e mysql \u7528\u6237\u6743\u9650\u7684\u771f\u5b9e\u573a\u666f\uff0c\u5305\u62ec\u8fc7\u7a0b\u3001\u6f5c\u5728\u9677\u9631\u548c\u8c03\u8bd5\u6b65\u9aa4\u3002<\/p>\n<h2> \u573a\u666f <\/h2>\n<p>\u5047\u8bbe\u60a8\u662f\u4e00\u5bb6\u62e5\u6709\u591a\u4e2a\u6570\u636e\u5e93\u7684\u516c\u53f8\u7684\u6570\u636e\u5e93\u7ba1\u7406\u5458\uff1a<\/p>\n<ul>\n<li>\u539f\u59cb\u6570\u636e\u5e93\uff1a\u4ea7\u54c1\u3001\u5ba2\u6237\u3001\u8ba2\u5355\u3001\u5206\u6790<\/li>\n<li>\u590d\u5236\u6570\u636e\u5e93\uff1aproducts_copy\u3001customers_copy\u3001orders_copy\u3001analytics_copy<\/li>\n<\/ul>\n<p>\u60a8\u7684\u4efb\u52a1\u662f\u4e3a\u540d\u4e3a\u201canalyst\u201d\u7684\u7528\u6237\u8bbe\u7f6e\u6743\u9650\uff0c\u5e76\u6ee1\u8db3\u4ee5\u4e0b\u8981\u6c42\uff1a<\/p>\n<ol>\n<li>\u5206\u6790\u5e08\u5e94\u8be5\u80fd\u591f\u67e5\u770b\u539f\u59cb\u6570\u636e\u5e93\u4e2d\u7684\u6570\u636e\u5e93\u548c\u8868\u3002<\/li>\n<li>\u5206\u6790\u5e08\u4e0d\u5e94\u8be5\u80fd\u591f\u7f16\u8f91\u539f\u59cb\u6570\u636e\u5e93\u3002<\/li>\n<li>\u5206\u6790\u5e08\u4e0d\u5e94\u8be5\u80fd\u591f\u521b\u5efa\u65b0\u7684\u6570\u636e\u5e93\u3002<\/li>\n<li>\u5206\u6790\u5e08\u5e94\u5bf9\u201c_copy\u201d\u6570\u636e\u5e93\u62e5\u6709\u5b8c\u5168\u8bbf\u95ee\u6743\u9650\uff08\u67e5\u770b\u3001\u7f16\u8f91\u3001\u5220\u9664\u3001\u521b\u5efa\u8868\uff09\u3002<\/li>\n<\/ol>\n<p>\u8ba9\u6211\u4eec\u6df1\u5165\u4e86\u89e3\u5982\u4f55\u4f7f\u7528 mysql \u7684 grant \u548c revoke \u8bed\u53e5\u6765\u5b9e\u73b0\u8fd9\u4e00\u70b9\u3002<\/p>\n<h2> \u7b2c 1 \u6b65\uff1a\u521d\u59cb\u8bbe\u7f6e <\/h2>\n<p>\u9996\u5148\uff0c\u6211\u4eec\u9700\u8981\u4f7f\u7528\u7ba1\u7406\u5e10\u6237\u8fde\u63a5\u5230mysql\u670d\u52a1\u5668\uff1a<\/p>\n<pre>mysql -h hostname -p port -u admin -p\n<\/pre>\n<p>\u5c06\u201c\u4e3b\u673a\u540d\u201d\u3001\u201c\u7aef\u53e3\u201d\u548c\u201c\u7ba1\u7406\u5458\u201d\u66ff\u6362\u4e3a\u60a8\u7684\u5b9e\u9645\u670d\u52a1\u5668\u8be6\u7ec6\u4fe1\u606f\u548c\u7ba1\u7406\u5458\u7528\u6237\u540d\u3002<\/p>\n<h2> \u7b2c 2 \u6b65\uff1a\u521b\u5efa\u7528\u6237 <\/h2>\n<p>\u5982\u679c\u7528\u6237\u5c1a\u4e0d\u5b58\u5728\uff0c\u6211\u4eec\u9700\u8981\u521b\u5efa\u5b83\uff1a<\/p>\n<pre>create user 'analyst'@'%' identified by 'password';\n<\/pre>\n<p>\u5c06\u201c\u5bc6\u7801\u201d\u66ff\u6362\u4e3a\u5b89\u5168\u5f3a\u5ea6\u9ad8\u7684\u5bc6\u7801\u3002<\/p>\n<h2> \u7b2c 3 \u6b65\uff1a\u6388\u4e88\u5fc5\u8981\u7684\u6743\u9650 <\/h2>\n<p>\u73b0\u5728\uff0c\u8ba9\u6211\u4eec\u6388\u4e88\u6240\u9700\u7684\u6743\u9650\uff1a<\/p>\n<pre>-- grant select on original databases\ngrant select on products.* to 'analyst'@'%';\ngrant select on customers.* to 'analyst'@'%';\ngrant select on orders.* to 'analyst'@'%';\ngrant select on analytics.* to 'analyst'@'%';\n\n-- grant all privileges on copy databases\ngrant all privileges on products_copy.* to 'analyst'@'%';\ngrant all privileges on customers_copy.* to 'analyst'@'%';\ngrant all privileges on orders_copy.* to 'analyst'@'%';\ngrant all privileges on analytics_copy.* to 'analyst'@'%';\n\n-- grant global privileges\ngrant process, show databases on *.* to 'analyst'@'%';\n\n-- apply the changes\nflush privileges;\n<\/pre>\n<h2> \u7b2c 4 \u6b65\uff1a\u9a8c\u8bc1\u6743\u9650 <\/h2>\n<p>\u8bbe\u7f6e\u6743\u9650\u540e\uff0c\u9a8c\u8bc1\u5b83\u4eec\u81f3\u5173\u91cd\u8981\uff1a<\/p>\n<pre>show grants for 'analyst'@'%';\n<\/pre>\n<h2> \u8c03\u8bd5\u548c\u6545\u969c\u6392\u9664 <\/h2>\n<h3> \u95ee\u9898\u4e00\uff1a\u6743\u9650\u8fc7\u591a <\/h3>\n<p>\u5728\u6211\u4eec\u7684\u573a\u666f\u4e2d\uff0c\u6211\u4eec\u6700\u521d\u9047\u5230\u4e86\u201c\u5206\u6790\u5e08\u201d\u62e5\u6709\u592a\u591a\u6743\u9650\u7684\u95ee\u9898\uff1a<\/p>\n<pre>mysql&gt; show grants for 'analyst'@'%';\n+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+\n| grants for analyst@%                                                                                                                                                                                                                                                                                                              |\n+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+\n| grant select, insert, update, delete, create, drop, reload, process, references, index, alter, show databases, create temporary tables, lock tables, execute, replication slave, replication client, create view, show view, create routine, alter routine, create user, event, trigger on *.* to \"analyst\"@\"%\" with grant option |\n| grant replication_applier,role_admin on *.* to \"analyst\"@\"%\" with grant option                                                                                                                                                                                                                                                    |\n...\n+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+\n<\/pre>\n<h4> \u89e3\u51b3\u65b9\u6848\uff1a <\/h4>\n<p>\u4e3a\u4e86\u89e3\u51b3\u8fd9\u4e2a\u95ee\u9898\uff0c\u6211\u4eec\u64a4\u9500\u4e86\u6240\u6709\u6743\u9650\uff0c\u7136\u540e\u4ec5\u6388\u4e88\u5fc5\u8981\u7684\u6743\u9650\uff1a<\/p>\n<pre>revoke all privileges, grant option from 'analyst'@'%';\ngrant process, show databases on *.* to 'analyst'@'%';\n-- then re-grant the specific permissions as shown in step 3\n<\/pre>\n<h3> \u95ee\u9898 2\uff1a\u7f3a\u5c11\u590d\u5236\u6570\u636e\u5e93\u7684\u6743\u9650 <\/h3>\n<p>\u4fee\u590d\u8fc7\u591a\u7684\u6743\u9650\u540e\uff0c\u6211\u4eec\u6ce8\u610f\u5230\u590d\u5236\u6570\u636e\u5e93\u7684\u6743\u9650\u4e22\u5931\u4e86\uff1a<\/p>\n<pre>mysql&gt; show grants for 'analyst'@'%';\n+-----------------------------------------------------+\n| grants for analyst@%                                |\n+-----------------------------------------------------+\n| grant process, show databases on *.* to \"analyst\"@\"%\"|\n| grant select on \"products\".* to \"analyst\"@\"%\"       |\n| grant select on \"customers\".* to \"analyst\"@\"%\"      |\n| grant select on \"orders\".* to \"analyst\"@\"%\"         |\n| grant select on \"analytics\".* to \"analyst\"@\"%\"      |\n+-----------------------------------------------------+\n<\/pre>\n<h4> \u89e3\u51b3\u65b9\u6848\uff1a <\/h4>\n<p>\u6211\u4eec\u6dfb\u52a0\u4e86\u590d\u5236\u6570\u636e\u5e93\u7f3a\u5c11\u7684\u6388\u6743\uff1a<\/p>\n<pre>grant all privileges on products_copy.* to 'analyst'@'%';\ngrant all privileges on customers_copy.* to 'analyst'@'%';\ngrant all privileges on orders_copy.* to 'analyst'@'%';\ngrant all privileges on analytics_copy.* to 'analyst'@'%';\nflush privileges;\n<\/pre>\n<h2> \u6700\u7ec8\u7ed3\u679c <\/h2>\n<p>\u5e94\u7528\u6240\u6709\u8fd9\u4e9b\u66f4\u6539\u548c\u4fee\u590d\u540e\uff0c\u6700\u7ec8\u7684\u62e8\u6b3e\u5e94\u5982\u4e0b\u6240\u793a\uff1a<\/p>\n<pre>mysql&gt; SHOW GRANTS FOR 'analyst'@'%';\n+-----------------------------------------------------+\n| Grants for analyst@%                                |\n+-----------------------------------------------------+\n| GRANT PROCESS, SHOW DATABASES ON *.* TO \"analyst\"@\"%\"|\n| GRANT SELECT ON \"products\".* TO \"analyst\"@\"%\"       |\n| GRANT SELECT ON \"customers\".* TO \"analyst\"@\"%\"      |\n| GRANT SELECT ON \"orders\".* TO \"analyst\"@\"%\"         |\n| GRANT SELECT ON \"analytics\".* TO \"analyst\"@\"%\"      |\n| GRANT ALL PRIVILEGES ON \"products_copy\".* TO \"analyst\"@\"%\"|\n| GRANT ALL PRIVILEGES ON \"customers_copy\".* TO \"analyst\"@\"%\"|\n| GRANT ALL PRIVILEGES ON \"orders_copy\".* TO \"analyst\"@\"%\"|\n| GRANT ALL PRIVILEGES ON \"analytics_copy\".* TO \"analyst\"@\"%\"|\n+-----------------------------------------------------+\n<\/pre>\n<h2> \u7ed3\u8bba <\/h2>\n<p>\u8bbe\u7f6e\u9002\u5f53\u7684 mysql \u7528\u6237\u6743\u9650\u53ef\u80fd\u5f88\u68d8\u624b\uff0c\u4f46\u8fd9\u662f\u6570\u636e\u5e93\u7ba1\u7406\u7684\u4e00\u4e2a\u91cd\u8981\u65b9\u9762\u3002\u901a\u8fc7\u4ed4\u7ec6\u4f7f\u7528 grant \u548c revoke \u8bed\u53e5\uff0c\u5e76\u59cb\u7ec8\u9a8c\u8bc1\u7ed3\u679c\uff0c\u60a8\u53ef\u4ee5\u4e3a\u60a8\u7684\u7528\u6237\u521b\u5efa\u4e00\u4e2a\u5b89\u5168\u4e14\u529f\u80fd\u9f50\u5168\u7684\u73af\u5883\u3002<\/p>\n<p>\u8bb0\u4f4f\u8fd9\u4e9b\u8981\u70b9\uff1a<\/p>\n<ol>\n<li>\u59cb\u7ec8\u4ece\u6700\u5c0f\u7279\u6743\u539f\u5219\u5f00\u59cb\u3002<\/li>\n<li>\u8fdb\u884c\u66f4\u6539\u540e\u4f7f\u7528 show grants \u9a8c\u8bc1\u6743\u9650\u3002<\/li>\n<li>\u5c0f\u5fc3\u5168\u5c40\u6743\u9650\uff08on <em>.<\/em>\uff09\u3002<\/li>\n<li>\u8fdb\u884c\u66f4\u6539\u540e\u4e0d\u8981\u5fd8\u8bb0\u5237\u65b0\u6743\u9650\u3002<\/li>\n<\/ol>\n<p>\u901a\u8fc7\u9075\u5faa\u8fd9\u4e9b\u6307\u5357\u548c\u672c\u6587\u4e2d\u6982\u8ff0\u7684\u6b65\u9aa4\uff0c\u60a8\u5c06\u80fd\u591f\u6709\u6548\u5730\u7ba1\u7406 mysql \u7528\u6237\u6743\u9650\u3002<\/p>\n<p>\u5230\u8fd9\u91cc\uff0c\u6211\u4eec\u4e5f\u5c31\u8bb2\u5b8c\u4e86\u300aMySQL \u7528\u6237\u6743\u9650\uff1a\u5b9e\u7528\u6307\u5357\u300b\u7684\u5185\u5bb9\u4e86\u3002\u4e2a\u4eba\u8ba4\u4e3a\uff0c\u57fa\u7840\u77e5\u8bc6\u7684\u5b66\u4e60\u548c\u5de9\u56fa\uff0c\u662f\u4e3a\u4e86\u66f4\u597d\u7684\u5c06\u5176\u8fd0\u7528\u5230\u9879\u76ee\u4e2d\uff0c\u6b22\u8fce\u5173\u6ce8\u4e3b\u673a\u5b9d\u8d1d\u516c\u4f17\u53f7\uff0c\u5e26\u4f60\u4e86\u89e3\u66f4\u591a\u5173\u4e8e\u7684\u77e5\u8bc6\u70b9\uff01<\/p>\n<p> \u7248\u672c\u58f0\u660e \u672c\u6587\u8f6c\u8f7d\u4e8e\uff1adev.to \u5982\u6709\u4fb5\u72af\uff0c\u8bf7\u8054\u7cfb \u5220\u9664 <\/p>\n<dl>\n<dt>\n <\/dt>\n<\/dl>\n","protected":false},"excerpt":{"rendered":"<p>MySQL \u7528\u6237\u6743\u9650\uff1a\u5b9e\u7528\u6307\u5357 &#46;&#46;&#46;<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[101],"tags":[],"class_list":["post-200142","post","type-post","status-publish","format-standard","hentry","category-database"],"_links":{"self":[{"href":"https:\/\/server.hk\/cnblog\/wp-json\/wp\/v2\/posts\/200142","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/server.hk\/cnblog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/server.hk\/cnblog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/server.hk\/cnblog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/server.hk\/cnblog\/wp-json\/wp\/v2\/comments?post=200142"}],"version-history":[{"count":0,"href":"https:\/\/server.hk\/cnblog\/wp-json\/wp\/v2\/posts\/200142\/revisions"}],"wp:attachment":[{"href":"https:\/\/server.hk\/cnblog\/wp-json\/wp\/v2\/media?parent=200142"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/server.hk\/cnblog\/wp-json\/wp\/v2\/categories?post=200142"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/server.hk\/cnblog\/wp-json\/wp\/v2\/tags?post=200142"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}