GROUP BYで同一グループ内の文字列カラムを連結する
Oracle の隠し関数 wmsys.wm_concat が非常に強力な件について - drk7jp を参照。
隠し関数なのでサポートを受けられなくてよい範囲でのみ使うこと。でもすごい便利なんだよな。
11gR2 から ListAgg 関数が標準でサポートされる。デリミタも指定できるのでさらに良い感じ。標準機能なのでListAggを使おう。
オブジェクト権限を DBA_TAB_PRIVS から読んで表示させようとしていたのだが、1権限ごとにレコードがあるので、表示上ちと見にくい。
GRANTEE | TABLE_SCHEMA | TABLE_NAME | GRANTOR | PRIVILEGE |
---|---|---|---|---|
user01 | user02 | tabA | user02 | SELECT |
user01 | user02 | tabA | user02 | INSERT |
user01 | user02 | tabA | user02 | DELETE |
user01 | user02 | tabA | user02 | UPDATE |
上表の様な出力を、下記のSQLで下表の様にできる。表は正規化されているのがよいけど、権限の数が少数に限られているので表示上は第一正規形無視のほうが見やすい。
SELECT GRANTEE, TABLE_SCHEMA, TABLE_NAME, GRANTOR, wm_concat(PRIVILEGE) AS "PRIVS" FROM DBA_TAB_PRIVS GROUP BY GRANTEE, TABLE_SCHEMA, TABLE_NAME, GRANTOR ;
GRANTEE | TABLE_SCHEMA | TABLE_NAME | GRANTOR | PRIVS |
---|---|---|---|---|
user01 | user02 | tabA | user02 | SELECT,INSERT,DELETE,UPDATE |