查詢使用者對物件的權限

舉凡用過Oracle的使用者, 再在使用PostgreSQL時, 都會用力尋找的一件事, 就是我的Data Dictionary呢? 想要知道使用者有多少Table的權限, 要怎麼查呢?

在參拜谷哥大神後, 綜合一個用法, 看來很複雜, 只能用function包起來, 才能方便使用.

1. create function


CREATE OR REPLACE FUNCTION userPrivilege(text) RETURNS table(username text, relname regclass, privileges text[])AS$$SELECT  $1,c.oid::regclass, array(select privileges from unnest(ARRAY [( CASE WHEN has_table_privilege($1,c.oid,'SELECT') THEN 'SELECT' ELSE NULL END),(CASE WHEN has_table_privilege($1,c.oid,'INSERT') THEN 'INSERT' ELSE NULL END),(CASE WHEN has_table_privilege($1,c.oid,'UPDATE') THEN 'UPDATE' ELSE NULL END),(CASE WHEN has_table_privilege($1,c.oid,'DELETE') THEN 'DELETE' ELSE NULL END),(CASE WHEN has_table_privilege($1,c.oid,'TRUNCATE') THEN 'TRUNCATE' ELSE NULL END),(CASE WHEN has_table_privilege($1,c.oid,'REFERENCES') THEN 'REFERENCES' ELSE NULL END),(CASE WHEN has_table_privilege($1,c.oid,'TRIGGER') THEN 'TRIGGER' ELSE NULL END)]) foo(privileges ) where privileges  s not null) FROM pg_class c JOIN pg_namespace n on c.relnamespace=n.oid where n.nspname not in ('information_schema','pg_catalog','sys') and nspparent=0 and c.relkind='r' andhas_table_privilege($1,c.oid,'SELECT, INSERT,UPDATE,DELETE,TRUNCATE,REFERENCES,TRIGGER') AND has_schema_privilege($1,c.relnamespace,'USAGE')$$ language sql;

2. 查詢

edb=# select * from userPrivilege(‘enterprisedb’);