{"id":1628,"date":"2020-09-25T11:44:19","date_gmt":"2020-09-25T03:44:19","guid":{"rendered":"https:\/\/199604.com\/?p=1628"},"modified":"2020-11-17T18:42:59","modified_gmt":"2020-11-17T10:42:59","slug":"gp%e9%9b%86%e7%be%a4%e6%95%b0%e6%8d%ae%e5%ba%93-%e5%88%9b%e5%bb%ba%e5%8f%aa%e8%af%bb%e7%94%a8%e6%88%b7%e5%b9%b6%e8%b5%8b%e6%9f%a5%e8%af%a2%e6%9d%83%e9%99%90","status":"publish","type":"post","link":"https:\/\/199604.com\/1628","title":{"rendered":"gp\u96c6\u7fa4\u6570\u636e\u5e93-\u521b\u5efa\u53ea\u8bfb\u7528\u6237\u5e76\u8d4b\u67e5\u8be2\u6743\u9650"},"content":{"rendered":"<p>CREATE ROLE select_user login password &#8216;Js_gp123!@#&#8217;;<br \/>\n\u6216\u8005\u4e0b\u9762\u90fd\u53ef\u4ee5\uff1a<br \/>\nCREATE USER select_user WITH PASSWORD &#8216;Js_gp123!@#&#8217;;<\/p>\n<p>\u628aUSAGE\u6743\u9650\u7ed9\u5230select_user<br \/>\ngrant usage on schema smartinsight to select_user;<\/p>\n<p>#\u5355\u72ec\u8d4b\u67e5\u8be2\u7684\u6743\u9650<br \/>\nGRANT SELECT ON TABLE zz_pm_nr_nsa_nrcellcu_minute20200923 TO select_user;<\/p>\n<p>GP\u96c6\u7fa4\u867d\u7136\u548cpgsql\u5dee\u4e0d\u591a \u4f46\u662f\u4e5f\u6709\u672c\u8d28\u533a\u522b\u56e0\u6b64\u4e0d\u80fd\u4f7f\u7528\uff1a<br \/>\ngrant select on all tables in schema smartinsight to select_user;\u8fd9\u4e2a\u8bed\u53e5 \u65e0\u6548\u7684<br \/>\n\u8be6\u60c5\uff1ahttps:\/\/blog.csdn.net\/weixin_42658788\/article\/details\/88040209<\/p>\n<p>\u89e3\u51b3\u529e\u6cd5-\u521b\u5efa\u4e00\u4e2a\u51fd\u6570\uff1a<\/p>\n<pre class=\"lang:default decode:true \">create or replace function grant_on_all_tables(schema text, usr text) \r\nreturns setof text as $$ \r\ndeclare \r\n   r record ; \r\n   grantstmt text; \r\nbegin \r\n   for r in select * from pg_class c, pg_namespace nsp \r\n       where c.relnamespace = nsp.oid AND c.relkind='r' AND nspname = schema and (relname like '%JS_MR_SERVICE_CELL_MINUTE_%' or relname like '%JS_PM_LTE_EUTRANCELL_MINUTE_%')\r\n   loop \r\n     grantstmt = 'GRANT SELECT ON \"'|| quote_ident(schema) || '\".\"' || \r\nquote_ident(r.relname) || '\" to \"' || quote_ident(usr) || '\"'; \r\n\r\n     EXECUTE grantstmt; \r\n     return next grantstmt; \r\n   end loop; \r\nend; \r\n\r\n$$ language plpgsql;<\/pre>\n<p>&nbsp;<\/p>\n","protected":false},"excerpt":{"rendered":"<p>CREATE ROLE select_user login password &#8216;Js_gp123! [&hellip;]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[257],"tags":[275],"class_list":["post-1628","post","type-post","status-publish","format-standard","hentry","category-gp","tag-gppgsql"],"_links":{"self":[{"href":"https:\/\/199604.com\/wp-json\/wp\/v2\/posts\/1628","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/199604.com\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/199604.com\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/199604.com\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/199604.com\/wp-json\/wp\/v2\/comments?post=1628"}],"version-history":[{"count":3,"href":"https:\/\/199604.com\/wp-json\/wp\/v2\/posts\/1628\/revisions"}],"predecessor-version":[{"id":1640,"href":"https:\/\/199604.com\/wp-json\/wp\/v2\/posts\/1628\/revisions\/1640"}],"wp:attachment":[{"href":"https:\/\/199604.com\/wp-json\/wp\/v2\/media?parent=1628"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/199604.com\/wp-json\/wp\/v2\/categories?post=1628"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/199604.com\/wp-json\/wp\/v2\/tags?post=1628"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}