この記事は約135分50秒で読むことができます。

ロール関連のテーブルいじった

まえがき

私はDBAではありませんが、権限関連に興味を持ったので、いろいろいじってみました。

データディクシヨナリビューの分類

こういうところからあまり知らないw。基本が大事ってやつだな。

prefix desc
user_ ログインユーザが所有しているオブジェクトのみが確認できるビュー
all_ ログインユーザが参照できるオブジェクトのみが確認できるビュー
dba_ データベースに存在するすべてのオブジェクトが確認できるビュー。デフォルトで
はSELECT ANY DICTIONARYシステム権限を持つユーザーのみがアクセス可能。

item_lizパッケージでコレクション操作系のファンクションそろえた。

随時増やしていきたいな。層を厚くしたい。

コード表示

CREATE OR REPLACE TYPE liz AS TABLE OF VARCHAR2(4000);
/

CREATE OR REPLACE PACKAGE item_liz
IS
    FUNCTION segregate(p_liz IN VARCHAR2) RETURN liz;
    FUNCTION ecl_dupli_liz(p_liz IN liz) RETURN liz;
    FUNCTION cnt_liz(p_liz IN liz) RETURN NUMBER;
END;
/
CREATE OR REPLACE PACKAGE BODY item_liz
IS
    FUNCTION segregate(p_liz IN VARCHAR2) RETURN liz
    IS
        rt liz;
    BEGIN
        rt := NULL;
		WITH sub AS(
			SELECT
				LEVEL AS rn
				,p_liz AS liz
			FROM
				dual
			CONNECT BY
				LEVEL <= LENGTH(p_liz) - LENGTH(REPLACE(p_liz,',','')) + 1
		)
		SELECT
			CAST(COLLECT(
				decode(rn,1,substr(liz,1,instr(liz,',',1,rn) - 1),substr(liz,instr(liz,',',1,rn -1 ) + 1,nvl(nullif(instr(liz,',',1,rn),0),4000)- instr(liz,',',1,rn-1)-1))
			)AS liz) AS ele
		INTO rt
		FROM
			sub;
        RETURN rt;
    END;

    FUNCTION ecl_dupli_liz(p_liz IN liz) RETURN liz
    IS
        rt liz;
    BEGIN
        rt := NULL;
		SELECT
			CAST(COLLECT(ele) AS liz)
		INTO rt
		FROM
			(
				SELECT DISTINCT
					COLUMN_VALUE AS ele
				FROM
					TABLE ( p_liz )
			);
        RETURN rt;
    END;

    FUNCTION cnt_liz(p_liz IN liz) RETURN NUMBER
	IS
        rt NUMBER;
	BEGIN
        rt := NULL;
		SELECT
			COUNT(COLUMN_VALUE)
		INTO rt
		FROM TABLE ( p_liz );
		RETURN rt;
	END;
END;
/

DBAロールって結局どんな権限持っているの。オブジェクト権限やシステム権限のうち。

dba_role_privsテーブルに管理されているぽい。見たところ、ロールのロールみたいにヒエラルキーがある。

コード表示

SET PAGESIZE 50000
SET LINESIZE 1000
SET TAB OFF
SET TRIMSPOOL ON
COL root_role FOR a10
COL related_role FOR a50

WITH liz___source___ AS(
SELECT
	SUM(CASE WHEN LEVEL <> 1 THEN 0 ELSE 1 END) OVER (ORDER BY ROWNUM) AS grp
	,LEVEL AS lv
	,CONNECT_BY_ROOT grantee AS root_role
	,grantee
	,granted_role
	,CONNECT_BY_ISLEAF AS isleaf
	,sys_connect_by_path(granted_role,',') AS liz
FROM
	dba_role_privs
START WITH
	grantee = 'DBA'
CONNECT BY
	PRIOR granted_role = grantee
)
,liz___create___ AS(
SELECT
	root_role
	,item_liz.ecl_dupli_liz(item_liz.segregate(substr(LISTAGG(liz)WITHIN GROUP (ORDER BY grp+grpseq),2))) AS liz
FROM
	(
	SELECT
		s1.grp
		,row_number()OVER(PARTITION BY s1.grp ORDER BY lv) AS grpseq
		,s1.lv
		,s1.root_role
		,s1.liz
	FROM
		liz___source___ s1
	WHERE
		s1.isleaf = 1
	)
GROUP BY
	root_role
)SELECT root_role AS root_role,COLUMN_VALUE AS related_role FROM liz___create___,TABLE(liz)
;

実行例

コード表示

ROOT_ROLE  RELATED_ROLE                                      
---------- --------------------------------------------------
DBA        HS_ADMIN_EXECUTE_ROLE                             
DBA        DATAPUMP_IMP_FULL_DATABASE                        
DBA        DATAPUMP_EXP_FULL_DATABASE                        
DBA        GATHER_SYSTEM_STATISTICS                          
DBA        OLAP_XS_ADMIN                                     
DBA        WM_ADMIN_ROLE                                     
DBA        EXP_FULL_DATABASE                                 
DBA        EXECUTE_CATALOG_ROLE                              
DBA        SELECT_CATALOG_ROLE                               
DBA        EM_EXPRESS_BASIC                                  
DBA        OLAP_DBA                                          
DBA        JAVA_ADMIN                                        
DBA        CAPTURE_ADMIN                                     
DBA        IMP_FULL_DATABASE                                 
DBA        HS_ADMIN_SELECT_ROLE                              
DBA        EM_EXPRESS_ALL                                    
DBA        XDBADMIN                                          
DBA        OPTIMIZER_PROCESSING_RATE                         
DBA        SCHEDULER_ADMIN                                   
DBA        XDB_SET_INVOKER                                   

20行が選択されました。 

重複排除して件数見ると、20件ぐらいDBAロールに関連しているロールがありそう。。おおいな

関連したロールに紐付く、システム権限を調べる。下のクエリとの取得結果と当てれば、OKそう。基本はleft outer joinでおそるおそる調べる。見失いたくないので。

コード表示

SELECT
	grantee
	,RTRIM(XMLAGG(XMLELEMENT(E,PRIVILEGE,',').EXTRACT('//text()')).getclobval(),',') AS liz
FROM
	dba_sys_privs
GROUP BY
	grantee
;

見失うところでした。nullはどのテーブルで管理しているんだろ。

コード表示

SET PAGESIZE 50000
SET LINESIZE 1000
SET TAB OFF
SET TRIMSPOOL ON
COL root_role FOR a10
COL related_role FOR a50

WITH liz___source___ AS(
SELECT
	SUM(CASE WHEN LEVEL <> 1 THEN 0 ELSE 1 END) OVER (ORDER BY ROWNUM) AS grp
	,LEVEL AS lv
	,CONNECT_BY_ROOT grantee AS root_role
	,grantee
	,granted_role
	,CONNECT_BY_ISLEAF AS isleaf
	,sys_connect_by_path(granted_role,',') AS liz
FROM
	dba_role_privs
START WITH
	grantee = 'DBA'
CONNECT BY
	PRIOR granted_role = grantee
)
,liz___create___ AS(
SELECT
	root_role
	,item_liz.ecl_dupli_liz(item_liz.segregate(substr(LISTAGG(liz)WITHIN GROUP (ORDER BY grp+grpseq),2))) AS liz
FROM
	(
	SELECT
		s1.grp
		,row_number()OVER(PARTITION BY s1.grp ORDER BY lv) AS grpseq
		,s1.lv
		,s1.root_role
		,s1.liz
	FROM
		liz___source___ s1
	WHERE
		s1.isleaf = 1
	)
GROUP BY
	root_role
)
,role___relation___ AS(
SELECT root_role AS root_role,COLUMN_VALUE AS related_role FROM liz___create___,TABLE(liz)
)
,role___sys_prv_mst___ AS(
SELECT
	grantee
	,RTRIM(XMLAGG(XMLELEMENT(E,PRIVILEGE,',').EXTRACT('//text()')).getclobval(),',') AS liz
FROM
	dba_sys_privs
GROUP BY
	grantee
)SELECT
	s1.root_role
	,s1.related_role
	,s2.liz
FROM
	role___relation___ s1
		LEFT OUTER JOIN role___sys_prv_mst___ s2
			ON
				s1.related_role = s2.grantee
;

全文検索とかで調べんのかな。。。やってみるか。。なんか調べてみると以下のような文章が出てきた。PUBLICロールってすべてのユーザーが前提として持っているようなロールぽい。このロールに権限強いやつallocateするととんでもないことになるね。

SYSスキーマ内のオブジェクトへのアクセスの許可

SELECT_CATALOG_ROLE,EXECUTE_CATALOG_ROLE,DELETE_CATALOG_ROLEこの3つのロールはSYSユーザーのオブジェクトもはや何でもどうにでもできるようなやばいやつなのか。DBAロールにはそんなロールまでついているのか。。。すごい。

もうチョイ調べたら、いい感じのリストでてきた!!!

ユーザー権限とロールに関する情報の検索  

さっきわりとがんばって書いたやつROLE_ROLE_PRIVSテーブルで管理されていそうで、萎え。nullのやつたぶんシステム全体として定義しているもので、割り当てる権限とかもう定数で固定されているから、nullでいいんじゃないか??個別でグぐることにした。

HS_ADMIN_EXECUTE_ROLE

Provides the EXECUTE privilege for users who want to use the Heterogeneous Services (HS) PL/SQL packages.

GATHER_SYSTEM_STATISTICS

Provides privileges to update system statistics, which are collected using the DBMS_STATS.GATHER_SYSTEM_STATISTICS procedure.これはシステム統計情報取得パッケージを実行するのに必要なロールってことだな!!さっきのは???だったけど。

GATHER_SYSTEM_STATISTICS

Provides privileges to administer security for Oracle OLAP.分析関数いじるのに必要なロールなんだろうキット。

WM_ADMIN_ROLE

DBMS_WMプロシージャいじるのに必要なロールなんだろな。用途が解せぬ。

JAVA_ADMIN

Provides administrative permissions to update policy tables for Oracle Database Java applications.用途が解せぬ。

CAPTURE_ADMIN

Provides the privileges necessary to create and manage privilege analysis policies.用途が解せぬ。

HS_ADMIN_SELECT_ROLE

Provides privileges to query the Heterogeneous Services data dictionary views.用途が解せぬ。

XDBADMIN

Allows the grantee to register an XML schema globally, as opposed to registering it for use or access only by its owner. It also lets the grantee bypass access control list (ACL) checks when accessing Oracle XML DB Repository.用途が解せぬ。

OPTIMIZER_PROCESSING_RATE

Provides privileges to execute the GATHER_PROCESSING_RATE, SET_PROCESSING_RATE, and DELETE_PROCESSING_RATE procedures in the DBMS_STATS package. These procedures manage the processing rate of a system for automatic degree of parallelism (Auto DOP). Auto DOP uses these processing rates to determine the optimal degree of parallelism for a SQL statement.チューニング関連ってことはなんとなく分かる。

コード表示

ROOT_ROLE  RELATED_ROLE                                       LIZ                                                                             
---------- -------------------------------------------------- --------------------------------------------------------------------------------
DBA        HS_ADMIN_EXECUTE_ROLE                              NULL                                                                            
DBA        DATAPUMP_IMP_FULL_DATABASE                         GRANT ANY ROLE,ALTER RESOURCE COST,EXECUTE ANY OPERATOR,GRANT ANY PRIVILEGE,CREA
DBA        DATAPUMP_EXP_FULL_DATABASE                         CREATE SESSION,CREATE TABLE                                                     
DBA        GATHER_SYSTEM_STATISTICS                           NULL                                                                            
DBA        OLAP_XS_ADMIN                                      NULL                                                                            
DBA        WM_ADMIN_ROLE                                      NULL                                                                            
DBA        EXP_FULL_DATABASE                                  READ ANY FILE GROUP,FLASHBACK ANY TABLE,BACKUP ANY TABLE,EXEMPT REDACTION POLICY
DBA        EXECUTE_CATALOG_ROLE                               NULL                                                                            
DBA        SELECT_CATALOG_ROLE                                NULL                                                                            
DBA        EM_EXPRESS_BASIC                                   CREATE SESSION,EM EXPRESS CONNECT                                               
DBA        OLAP_DBA                                           SELECT ANY TABLE,CREATE ANY VIEW,CREATE ANY CUBE,INSERT ANY TABLE,CREATE ANY TAB
DBA        JAVA_ADMIN                                         NULL                                                                            
DBA        CAPTURE_ADMIN                                      NULL                                                                            
DBA        IMP_FULL_DATABASE                                  CREATE ANY TABLE,DROP ANY DIMENSION,DROP ANY ROLE,DROP PUBLIC DATABASE LINK,DROP
DBA        HS_ADMIN_SELECT_ROLE                               NULL                                                                            
DBA        EM_EXPRESS_ALL                                     CREATE JOB,ALTER TABLESPACE,ADMINISTER ANY SQL TUNING SET,ADMINISTER SQL TUNING 
DBA        XDBADMIN                                           NULL                                                                            
DBA        OPTIMIZER_PROCESSING_RATE                          NULL                                                                            
DBA        SCHEDULER_ADMIN                                    MANAGE SCHEDULER,CREATE ANY CREDENTIAL,CREATE EXTERNAL JOB,EXECUTE ANY CLASS,CRE
DBA        XDB_SET_INVOKER                                    NULL                                                                            

20行が選択されました。 

とりあえず、個々のロールに割りついている権限の数、調べる

IMP_FULL_DATABASEおおいな。

コード表示

SET PAGESIZE 50000
SET LINESIZE 1000
SET TAB OFF
SET TRIMSPOOL ON
COL root_role FOR a10
COL related_role FOR a50

WITH liz___source___ AS(
SELECT
	SUM(CASE WHEN LEVEL <> 1 THEN 0 ELSE 1 END) OVER (ORDER BY ROWNUM) AS grp
	,LEVEL AS lv
	,CONNECT_BY_ROOT grantee AS root_role
	,grantee
	,granted_role
	,CONNECT_BY_ISLEAF AS isleaf
	,sys_connect_by_path(granted_role,',') AS liz
FROM
	dba_role_privs
START WITH
	grantee = 'DBA'
CONNECT BY
	PRIOR granted_role = grantee
)
,liz___create___ AS(
SELECT
	root_role
	,item_liz.ecl_dupli_liz(item_liz.segregate(substr(LISTAGG(liz)WITHIN GROUP (ORDER BY grp+grpseq),2))) AS liz
FROM
	(
	SELECT
		s1.grp
		,row_number()OVER(PARTITION BY s1.grp ORDER BY lv) AS grpseq
		,s1.lv
		,s1.root_role
		,s1.liz
	FROM
		liz___source___ s1
	WHERE
		s1.isleaf = 1
	)
GROUP BY
	root_role
)
,role___relation___ AS(
SELECT root_role AS root_role,COLUMN_VALUE AS related_role FROM liz___create___,TABLE(liz)
)
,role___sys_prv_mst___ AS(
SELECT
	grantee
	,RTRIM(XMLAGG(XMLELEMENT(E,PRIVILEGE,',').EXTRACT('//text()')).getclobval(),',') AS liz
FROM
	dba_sys_privs
GROUP BY
	grantee
)
,list___role_prv___ AS (
SELECT
	s1.root_role
	,s1.related_role
	,s2.liz
FROM
	role___relation___ s1
		LEFT OUTER JOIN role___sys_prv_mst___ s2
			ON
				s1.related_role = s2.grantee
)
SELECT
	root_role
	,related_role
	,item_liz.cnt_liz(item_liz.segregate(liz)) AS cnt
--	,column_value as related_prv
FROM
	list___role_prv___
--	,table(item_liz.segregate(liz))
ORDER BY
	item_liz.cnt_liz(item_liz.segregate(liz)) DESC
;

ROOT_ROLE  RELATED_ROLE                                              CNT
---------- -------------------------------------------------- ----------
DBA        IMP_FULL_DATABASE                                          81
DBA        OLAP_DBA                                                   27
DBA        EM_EXPRESS_ALL                                             23
DBA        DATAPUMP_IMP_FULL_DATABASE                                 14
DBA        EXP_FULL_DATABASE                                          14
DBA        SCHEDULER_ADMIN                                             8
DBA        EM_EXPRESS_BASIC                                            2
DBA        DATAPUMP_EXP_FULL_DATABASE                                  2
DBA        SELECT_CATALOG_ROLE                                         0
DBA        GATHER_SYSTEM_STATISTICS                                    0
DBA        JAVA_ADMIN                                                  0
DBA        WM_ADMIN_ROLE                                               0
DBA        HS_ADMIN_SELECT_ROLE                                        0
DBA        HS_ADMIN_EXECUTE_ROLE                                       0
DBA        XDBADMIN                                                    0
DBA        OPTIMIZER_PROCESSING_RATE                                   0
DBA        XDB_SET_INVOKER                                             0
DBA        EXECUTE_CATALOG_ROLE                                        0
DBA        OLAP_XS_ADMIN                                               0
DBA        CAPTURE_ADMIN                                               0

20行が選択されました。 


明細展開してみる

コード表示

SET PAGESIZE 50000
SET LINESIZE 1000
SET TAB OFF
SET TRIMSPOOL ON
COL root_role FOR a10
COL related_role FOR a50
COL related_prv FOR a50

WITH liz___source___ AS(
SELECT
	SUM(CASE WHEN LEVEL <> 1 THEN 0 ELSE 1 END) OVER (ORDER BY ROWNUM) AS grp
	,LEVEL AS lv
	,CONNECT_BY_ROOT grantee AS root_role
	,grantee
	,granted_role
	,CONNECT_BY_ISLEAF AS isleaf
	,sys_connect_by_path(granted_role,',') AS liz
FROM
	dba_role_privs
START WITH
	grantee = 'DBA'
CONNECT BY
	PRIOR granted_role = grantee
)
,liz___create___ AS(
SELECT
	root_role
	,item_liz.ecl_dupli_liz(item_liz.segregate(substr(LISTAGG(liz)WITHIN GROUP (ORDER BY grp+grpseq),2))) AS liz
FROM
	(
	SELECT
		s1.grp
		,row_number()OVER(PARTITION BY s1.grp ORDER BY lv) AS grpseq
		,s1.lv
		,s1.root_role
		,s1.liz
	FROM
		liz___source___ s1
	WHERE
		s1.isleaf = 1
	)
GROUP BY
	root_role
)
,role___relation___ AS(
SELECT root_role AS root_role,COLUMN_VALUE AS related_role FROM liz___create___,TABLE(liz)
)
,role___sys_prv_mst___ AS(
SELECT
	grantee
	,RTRIM(XMLAGG(XMLELEMENT(E,PRIVILEGE,',').EXTRACT('//text()')).getclobval(),',') AS liz
FROM
	dba_sys_privs
GROUP BY
	grantee
)
,list___role_prv___ AS (
SELECT
	s1.root_role
	,s1.related_role
	,s2.liz
FROM
	role___relation___ s1
		LEFT OUTER JOIN role___sys_prv_mst___ s2
			ON
				s1.related_role = s2.grantee
)
SELECT
	root_role
	,related_role
	,item_liz.cnt_liz(item_liz.segregate(liz)) AS cnt
	,column_value as related_prv
FROM
	list___role_prv___
	,table(item_liz.segregate(liz))
ORDER BY
	item_liz.cnt_liz(item_liz.segregate(liz)) DESC
	,column_value
;

ROOT_ROLE  RELATED_ROLE                                              CNT RELATED_PRV                                       
---------- -------------------------------------------------- ---------- --------------------------------------------------
DBA        IMP_FULL_DATABASE                                          81 ADMINISTER DATABASE TRIGGER                       
DBA        IMP_FULL_DATABASE                                          81 ADMINISTER RESOURCE MANAGER                       
DBA        IMP_FULL_DATABASE                                          81 ADMINISTER SQL MANAGEMENT OBJECT                  
DBA        IMP_FULL_DATABASE                                          81 ALTER ANY PROCEDURE                               
DBA        IMP_FULL_DATABASE                                          81 ALTER ANY TABLE                                   
DBA        IMP_FULL_DATABASE                                          81 ALTER ANY TRIGGER                                 
DBA        IMP_FULL_DATABASE                                          81 ALTER ANY TYPE                                    
DBA        IMP_FULL_DATABASE                                          81 ALTER DATABASE                                    
DBA        IMP_FULL_DATABASE                                          81 ALTER PROFILE                                     
DBA        IMP_FULL_DATABASE                                          81 ALTER RESOURCE COST                               
DBA        IMP_FULL_DATABASE                                          81 ALTER TABLESPACE                                  
DBA        IMP_FULL_DATABASE                                          81 ALTER USER                                        
DBA        IMP_FULL_DATABASE                                          81 ANALYZE ANY                                       
DBA        IMP_FULL_DATABASE                                          81 AUDIT ANY                                         
DBA        IMP_FULL_DATABASE                                          81 AUDIT SYSTEM                                      
DBA        IMP_FULL_DATABASE                                          81 BECOME USER                                       
DBA        IMP_FULL_DATABASE                                          81 COMMENT ANY TABLE                                 
DBA        IMP_FULL_DATABASE                                          81 CREATE ANY CLUSTER                                
DBA        IMP_FULL_DATABASE                                          81 CREATE ANY CONTEXT                                
DBA        IMP_FULL_DATABASE                                          81 CREATE ANY DIMENSION                              
DBA        IMP_FULL_DATABASE                                          81 CREATE ANY DIRECTORY                              
DBA        IMP_FULL_DATABASE                                          81 CREATE ANY INDEX                                  
DBA        IMP_FULL_DATABASE                                          81 CREATE ANY INDEXTYPE                              
DBA        IMP_FULL_DATABASE                                          81 CREATE ANY LIBRARY                                
DBA        IMP_FULL_DATABASE                                          81 CREATE ANY MATERIALIZED VIEW                      
DBA        IMP_FULL_DATABASE                                          81 CREATE ANY OPERATOR                               
DBA        IMP_FULL_DATABASE                                          81 CREATE ANY PROCEDURE                              
DBA        IMP_FULL_DATABASE                                          81 CREATE ANY SEQUENCE                               
DBA        IMP_FULL_DATABASE                                          81 CREATE ANY SQL PROFILE                            
DBA        IMP_FULL_DATABASE                                          81 CREATE ANY SYNONYM                                
DBA        IMP_FULL_DATABASE                                          81 CREATE ANY TABLE                                  
DBA        IMP_FULL_DATABASE                                          81 CREATE ANY TRIGGER                                
DBA        IMP_FULL_DATABASE                                          81 CREATE ANY TYPE                                   
DBA        IMP_FULL_DATABASE                                          81 CREATE ANY VIEW                                   
DBA        IMP_FULL_DATABASE                                          81 CREATE DATABASE LINK                              
DBA        IMP_FULL_DATABASE                                          81 CREATE PROFILE                                    
DBA        IMP_FULL_DATABASE                                          81 CREATE PUBLIC DATABASE LINK                       
DBA        IMP_FULL_DATABASE                                          81 CREATE PUBLIC SYNONYM                             
DBA        IMP_FULL_DATABASE                                          81 CREATE ROLE                                       
DBA        IMP_FULL_DATABASE                                          81 CREATE ROLLBACK SEGMENT                           
DBA        IMP_FULL_DATABASE                                          81 CREATE SESSION                                    
DBA        IMP_FULL_DATABASE                                          81 CREATE TABLESPACE                                 
DBA        IMP_FULL_DATABASE                                          81 CREATE USER                                       
DBA        IMP_FULL_DATABASE                                          81 DELETE ANY TABLE                                  
DBA        IMP_FULL_DATABASE                                          81 DROP ANY CLUSTER                                  
DBA        IMP_FULL_DATABASE                                          81 DROP ANY CONTEXT                                  
DBA        IMP_FULL_DATABASE                                          81 DROP ANY DIMENSION                                
DBA        IMP_FULL_DATABASE                                          81 DROP ANY DIRECTORY                                
DBA        IMP_FULL_DATABASE                                          81 DROP ANY INDEX                                    
DBA        IMP_FULL_DATABASE                                          81 DROP ANY INDEXTYPE                                
DBA        IMP_FULL_DATABASE                                          81 DROP ANY LIBRARY                                  
DBA        IMP_FULL_DATABASE                                          81 DROP ANY MATERIALIZED VIEW                        
DBA        IMP_FULL_DATABASE                                          81 DROP ANY OPERATOR                                 
DBA        IMP_FULL_DATABASE                                          81 DROP ANY OUTLINE                                  
DBA        IMP_FULL_DATABASE                                          81 DROP ANY PROCEDURE                                
DBA        IMP_FULL_DATABASE                                          81 DROP ANY ROLE                                     
DBA        IMP_FULL_DATABASE                                          81 DROP ANY SEQUENCE                                 
DBA        IMP_FULL_DATABASE                                          81 DROP ANY SQL PROFILE                              
DBA        IMP_FULL_DATABASE                                          81 DROP ANY SYNONYM                                  
DBA        IMP_FULL_DATABASE                                          81 DROP ANY TABLE                                    
DBA        IMP_FULL_DATABASE                                          81 DROP ANY TRIGGER                                  
DBA        IMP_FULL_DATABASE                                          81 DROP ANY TYPE                                     
DBA        IMP_FULL_DATABASE                                          81 DROP ANY VIEW                                     
DBA        IMP_FULL_DATABASE                                          81 DROP PROFILE                                      
DBA        IMP_FULL_DATABASE                                          81 DROP PUBLIC DATABASE LINK                         
DBA        IMP_FULL_DATABASE                                          81 DROP PUBLIC SYNONYM                               
DBA        IMP_FULL_DATABASE                                          81 DROP ROLLBACK SEGMENT                             
DBA        IMP_FULL_DATABASE                                          81 DROP TABLESPACE                                   
DBA        IMP_FULL_DATABASE                                          81 DROP USER                                         
DBA        IMP_FULL_DATABASE                                          81 EXECUTE ANY OPERATOR                              
DBA        IMP_FULL_DATABASE                                          81 EXECUTE ANY PROCEDURE                             
DBA        IMP_FULL_DATABASE                                          81 EXECUTE ANY TYPE                                  
DBA        IMP_FULL_DATABASE                                          81 GLOBAL QUERY REWRITE                              
DBA        IMP_FULL_DATABASE                                          81 GRANT ANY OBJECT PRIVILEGE                        
DBA        IMP_FULL_DATABASE                                          81 GRANT ANY PRIVILEGE                               
DBA        IMP_FULL_DATABASE                                          81 GRANT ANY ROLE                                    
DBA        IMP_FULL_DATABASE                                          81 INSERT ANY TABLE                                  
DBA        IMP_FULL_DATABASE                                          81 MANAGE ANY QUEUE                                  
DBA        IMP_FULL_DATABASE                                          81 RESUMABLE                                         
DBA        IMP_FULL_DATABASE                                          81 SELECT ANY TABLE                                  
DBA        IMP_FULL_DATABASE                                          81 UPDATE ANY TABLE                                  
DBA        OLAP_DBA                                                   27 CREATE ANY CUBE                                   
DBA        OLAP_DBA                                                   27 CREATE ANY CUBE BUILD PROCESS                     
DBA        OLAP_DBA                                                   27 CREATE ANY CUBE DIMENSION                         
DBA        OLAP_DBA                                                   27 CREATE ANY MEASURE FOLDER                         
DBA        OLAP_DBA                                                   27 CREATE ANY TABLE                                  
DBA        OLAP_DBA                                                   27 CREATE ANY VIEW                                   
DBA        OLAP_DBA                                                   27 CREATE JOB                                        
DBA        OLAP_DBA                                                   27 CREATE SEQUENCE                                   
DBA        OLAP_DBA                                                   27 DELETE ANY CUBE DIMENSION                         
DBA        OLAP_DBA                                                   27 DELETE ANY MEASURE FOLDER                         
DBA        OLAP_DBA                                                   27 DELETE ANY TABLE                                  
DBA        OLAP_DBA                                                   27 DROP ANY CUBE                                     
DBA        OLAP_DBA                                                   27 DROP ANY CUBE BUILD PROCESS                       
DBA        OLAP_DBA                                                   27 DROP ANY CUBE DIMENSION                           
DBA        OLAP_DBA                                                   27 DROP ANY MEASURE FOLDER                           
DBA        OLAP_DBA                                                   27 DROP ANY TABLE                                    
DBA        OLAP_DBA                                                   27 DROP ANY VIEW                                     
DBA        OLAP_DBA                                                   27 INSERT ANY CUBE DIMENSION                         
DBA        OLAP_DBA                                                   27 INSERT ANY MEASURE FOLDER                         
DBA        OLAP_DBA                                                   27 INSERT ANY TABLE                                  
DBA        OLAP_DBA                                                   27 SELECT ANY CUBE                                   
DBA        OLAP_DBA                                                   27 SELECT ANY CUBE DIMENSION                         
DBA        OLAP_DBA                                                   27 SELECT ANY TABLE                                  
DBA        OLAP_DBA                                                   27 UPDATE ANY CUBE                                   
DBA        OLAP_DBA                                                   27 UPDATE ANY CUBE BUILD PROCESS                     
DBA        OLAP_DBA                                                   27 UPDATE ANY CUBE DIMENSION                         
DBA        OLAP_DBA                                                   27 UPDATE ANY TABLE                                  
DBA        EM_EXPRESS_ALL                                             23 ADMINISTER ANY SQL TUNING SET                     
DBA        EM_EXPRESS_ALL                                             23 ADMINISTER RESOURCE MANAGER                       
DBA        EM_EXPRESS_ALL                                             23 ADMINISTER SQL MANAGEMENT OBJECT                  
DBA        EM_EXPRESS_ALL                                             23 ADMINISTER SQL TUNING SET                         
DBA        EM_EXPRESS_ALL                                             23 ADVISOR                                           
DBA        EM_EXPRESS_ALL                                             23 ALTER ANY ROLE                                    
DBA        EM_EXPRESS_ALL                                             23 ALTER PROFILE                                     
DBA        EM_EXPRESS_ALL                                             23 ALTER SYSTEM                                      
DBA        EM_EXPRESS_ALL                                             23 ALTER TABLESPACE                                  
DBA        EM_EXPRESS_ALL                                             23 ALTER USER                                        
DBA        EM_EXPRESS_ALL                                             23 CREATE JOB                                        
DBA        EM_EXPRESS_ALL                                             23 CREATE PROFILE                                    
DBA        EM_EXPRESS_ALL                                             23 CREATE ROLE                                       
DBA        EM_EXPRESS_ALL                                             23 CREATE TABLESPACE                                 
DBA        EM_EXPRESS_ALL                                             23 CREATE USER                                       
DBA        EM_EXPRESS_ALL                                             23 DROP ANY ROLE                                     
DBA        EM_EXPRESS_ALL                                             23 DROP PROFILE                                      
DBA        EM_EXPRESS_ALL                                             23 DROP TABLESPACE                                   
DBA        EM_EXPRESS_ALL                                             23 DROP USER                                         
DBA        EM_EXPRESS_ALL                                             23 GRANT ANY OBJECT PRIVILEGE                        
DBA        EM_EXPRESS_ALL                                             23 GRANT ANY PRIVILEGE                               
DBA        EM_EXPRESS_ALL                                             23 GRANT ANY ROLE                                    
DBA        EM_EXPRESS_ALL                                             23 SET CONTAINER                                     
DBA        EXP_FULL_DATABASE                                          14 ADMINISTER RESOURCE MANAGER                       
DBA        EXP_FULL_DATABASE                                          14 ADMINISTER SQL MANAGEMENT OBJECT                  
DBA        DATAPUMP_IMP_FULL_DATABASE                                 14 ALTER DATABASE                                    
DBA        DATAPUMP_IMP_FULL_DATABASE                                 14 ALTER PROFILE                                     
DBA        DATAPUMP_IMP_FULL_DATABASE                                 14 ALTER RESOURCE COST                               
DBA        DATAPUMP_IMP_FULL_DATABASE                                 14 ALTER USER                                        
DBA        EXP_FULL_DATABASE                                          14 ANALYZE ANY                                       
DBA        DATAPUMP_IMP_FULL_DATABASE                                 14 AUDIT ANY                                         
DBA        DATAPUMP_IMP_FULL_DATABASE                                 14 AUDIT SYSTEM                                      
DBA        EXP_FULL_DATABASE                                          14 BACKUP ANY TABLE                                  
DBA        DATAPUMP_IMP_FULL_DATABASE                                 14 CREATE PROFILE                                    
DBA        EXP_FULL_DATABASE                                          14 CREATE SESSION                                    
DBA        DATAPUMP_IMP_FULL_DATABASE                                 14 CREATE SESSION                                    
DBA        EXP_FULL_DATABASE                                          14 CREATE TABLE                                      
DBA        DATAPUMP_IMP_FULL_DATABASE                                 14 DELETE ANY TABLE                                  
DBA        DATAPUMP_IMP_FULL_DATABASE                                 14 EXECUTE ANY OPERATOR                              
DBA        EXP_FULL_DATABASE                                          14 EXECUTE ANY PROCEDURE                             
DBA        EXP_FULL_DATABASE                                          14 EXECUTE ANY TYPE                                  
DBA        EXP_FULL_DATABASE                                          14 EXEMPT REDACTION POLICY                           
DBA        EXP_FULL_DATABASE                                          14 FLASHBACK ANY TABLE                               
DBA        DATAPUMP_IMP_FULL_DATABASE                                 14 GRANT ANY OBJECT PRIVILEGE                        
DBA        DATAPUMP_IMP_FULL_DATABASE                                 14 GRANT ANY PRIVILEGE                               
DBA        DATAPUMP_IMP_FULL_DATABASE                                 14 GRANT ANY ROLE                                    
DBA        EXP_FULL_DATABASE                                          14 READ ANY FILE GROUP                               
DBA        EXP_FULL_DATABASE                                          14 RESUMABLE                                         
DBA        EXP_FULL_DATABASE                                          14 SELECT ANY SEQUENCE                               
DBA        DATAPUMP_IMP_FULL_DATABASE                                 14 SELECT ANY TABLE                                  
DBA        EXP_FULL_DATABASE                                          14 SELECT ANY TABLE                                  
DBA        SCHEDULER_ADMIN                                             8 CREATE ANY CREDENTIAL                             
DBA        SCHEDULER_ADMIN                                             8 CREATE ANY JOB                                    
DBA        SCHEDULER_ADMIN                                             8 CREATE CREDENTIAL                                 
DBA        SCHEDULER_ADMIN                                             8 CREATE EXTERNAL JOB                               
DBA        SCHEDULER_ADMIN                                             8 CREATE JOB                                        
DBA        SCHEDULER_ADMIN                                             8 EXECUTE ANY CLASS                                 
DBA        SCHEDULER_ADMIN                                             8 EXECUTE ANY PROGRAM                               
DBA        SCHEDULER_ADMIN                                             8 MANAGE SCHEDULER                                  
DBA        DATAPUMP_EXP_FULL_DATABASE                                  2 CREATE SESSION                                    
DBA        EM_EXPRESS_BASIC                                            2 CREATE SESSION                                    
DBA        DATAPUMP_EXP_FULL_DATABASE                                  2 CREATE TABLE                                      
DBA        EM_EXPRESS_BASIC                                            2 EM EXPRESS CONNECT                                

171行が選択されました。 

operatiton単位でprivilegeをさまって見る

listagg2を使わせてもらっています。。ありがとございます。自分でも南下作ってみたくなるような記事でした!!脱線するけど、そもそもディクショナリの分類意識していないから、ここでまとめておく。

ユーザ定義「集計」関数でDISTINCTや分析関数のウインドウが使えるLISTAGGを作る  

role_sys_privsテーブルではロールに付与されている権限を確認できる。表示される情報は、ユーザーがアクセス可能なロールに関するもののみ。

コード表示

SET PAGESIZE 50000
SET LINESIZE 1000
SET TAB OFF
SET TRIMSPOOL ON
COL ope FOR a15
COL cnt FOR 99
COL prv FOR a155

SELECT
	ope
	,COUNT(DISTINCT PRIVILEGE) AS cnt
	,listagg2(DISTINCT PRIVILEGE) AS prv
FROM
	(
		SELECT
			s1.*
			, row_number() OVER(
				PARTITION BY s1.ROLE, s1.PRIVILEGE
				ORDER BY
					ROWNUM
			) AS rn
			, COLUMN_VALUE AS ope
		FROM
			(
				SELECT
					ROLE
					, PRIVILEGE
					, item_liz.segregate(REPLACE(PRIVILEGE, ' ', ',')
					              || ',') AS liz
				FROM
					role_sys_privs
			) s1
			, TABLE ( liz ) s2
	)
WHERE
	rn = 1
GROUP BY
	ope
ORDER BY
	COUNT(DISTINCT PRIVILEGE) DESC
	,ope
;

ちなみにDEBUGはplsqlのprocedureのデバッグ機能を使用するときに必要な権限たち。DEBUG ANY PROCEDURE,DEBUG CONNECT ANY,DEBUG CONNECT SESSION。
create drop alter execute select は充実しているんだな。ここら辺から覚えていくのかな。おおくね??

コード表示

OPE             CNT PRV                                                                                                                                                        
--------------- --- -----------------------------------------------------------------------------------------------------------------------------------------------------------
CREATE           74 CREATE ANALYTIC VIEW,CREATE ANY ANALYTIC VIEW,CREATE ANY ASSEMBLY,CREATE ANY ATTRIBUTE DIMENSION,CREATE ANY CLUSTER,CREATE ANY CONTEXT,CREATE ANY CREDENTIA
                    L,CREATE ANY CUBE,CREATE ANY CUBE BUILD PROCESS,CREATE ANY CUBE DIMENSION,CREATE ANY DIMENSION,CREATE ANY DIRECTORY,CREATE ANY EDITION,CREATE ANY EVALUATIO
                    N CONTEXT,CREATE ANY HIERARCHY,CREATE ANY INDEX,CREATE ANY INDEXTYPE,CREATE ANY JOB,CREATE ANY LIBRARY,CREATE ANY MATERIALIZED VIEW,CREATE ANY MEASURE FOLD
                    ER,CREATE ANY MINING MODEL,CREATE ANY OPERATOR,CREATE ANY OUTLINE,CREATE ANY PROCEDURE,CREATE ANY RULE,CREATE ANY RULE SET,CREATE ANY SEQUENCE,CREATE ANY S
                    QL PROFILE,CREATE ANY SQL TRANSLATION PROFILE,CREATE ANY SYNONYM,CREATE ANY TABLE,CREATE ANY TRIGGER,CREATE ANY TYPE,CREATE ANY VIEW,CREATE ASSEMBLY,CREATE
                     ATTRIBUTE DIMENSION,CREATE CLUSTER,CREATE CREDENTIAL,CREATE CUBE,CREATE CUBE BUILD PROCESS,CREATE CUBE DIMENSION,CREATE DATABASE LINK,CREATE DIMENSION,CRE
                    ATE EVALUATION CONTEXT,CREATE EXTERNAL JOB,CREATE HIERARCHY,CREATE INDEXTYPE,CREATE JOB,CREATE LIBRARY,CREATE LOCKDOWN PROFILE,CREATE MATERIALIZED VIEW,CRE
                    ATE MEASURE FOLDER,CREATE MINING MODEL,CREATE OPERATOR,CREATE PLUGGABLE DATABASE,CREATE PROCEDURE,CREATE PROFILE,CREATE PUBLIC DATABASE LINK,CREATE PUBLIC 
                    SYNONYM,CREATE ROLE,CREATE ROLLBACK SEGMENT,CREATE RULE,CREATE RULE SET,CREATE SEQUENCE,CREATE SESSION,CREATE SQL TRANSLATION PROFILE,CREATE SYNONYM,CREATE
                     TABLE,CREATE TABLESPACE,CREATE TRIGGER,CREATE TYPE,CREATE USER,CREATE VIEW                                                                                

DROP             40 DROP ANY ANALYTIC VIEW,DROP ANY ASSEMBLY,DROP ANY ATTRIBUTE DIMENSION,DROP ANY CLUSTER,DROP ANY CONTEXT,DROP ANY CUBE,DROP ANY CUBE BUILD PROCESS,DROP ANY 
                    CUBE DIMENSION,DROP ANY DIMENSION,DROP ANY DIRECTORY,DROP ANY EDITION,DROP ANY EVALUATION CONTEXT,DROP ANY HIERARCHY,DROP ANY INDEX,DROP ANY INDEXTYPE,DROP
                     ANY LIBRARY,DROP ANY MATERIALIZED VIEW,DROP ANY MEASURE FOLDER,DROP ANY MINING MODEL,DROP ANY OPERATOR,DROP ANY OUTLINE,DROP ANY PROCEDURE,DROP ANY ROLE,D
                    ROP ANY RULE,DROP ANY RULE SET,DROP ANY SEQUENCE,DROP ANY SQL PROFILE,DROP ANY SQL TRANSLATION PROFILE,DROP ANY SYNONYM,DROP ANY TABLE,DROP ANY TRIGGER,DRO
                    P ANY TYPE,DROP ANY VIEW,DROP LOCKDOWN PROFILE,DROP PROFILE,DROP PUBLIC DATABASE LINK,DROP PUBLIC SYNONYM,DROP ROLLBACK SEGMENT,DROP TABLESPACE,DROP USER  

ALTER            38 ALTER ANY ANALYTIC VIEW,ALTER ANY ASSEMBLY,ALTER ANY ATTRIBUTE DIMENSION,ALTER ANY CLUSTER,ALTER ANY CUBE,ALTER ANY CUBE BUILD PROCESS,ALTER ANY CUBE DIMEN
                    SION,ALTER ANY DIMENSION,ALTER ANY EDITION,ALTER ANY EVALUATION CONTEXT,ALTER ANY HIERARCHY,ALTER ANY INDEX,ALTER ANY INDEXTYPE,ALTER ANY LIBRARY,ALTER ANY
                     MATERIALIZED VIEW,ALTER ANY MEASURE FOLDER,ALTER ANY MINING MODEL,ALTER ANY OPERATOR,ALTER ANY OUTLINE,ALTER ANY PROCEDURE,ALTER ANY ROLE,ALTER ANY RULE,A
                    LTER ANY RULE SET,ALTER ANY SEQUENCE,ALTER ANY SQL PROFILE,ALTER ANY SQL TRANSLATION PROFILE,ALTER ANY TABLE,ALTER ANY TRIGGER,ALTER ANY TYPE,ALTER DATABAS
                    E,ALTER LOCKDOWN PROFILE,ALTER PROFILE,ALTER RESOURCE COST,ALTER ROLLBACK SEGMENT,ALTER SESSION,ALTER SYSTEM,ALTER TABLESPACE,ALTER USER                   

EXECUTE          12 EXECUTE ANY ASSEMBLY,EXECUTE ANY CLASS,EXECUTE ANY EVALUATION CONTEXT,EXECUTE ANY INDEXTYPE,EXECUTE ANY LIBRARY,EXECUTE ANY OPERATOR,EXECUTE ANY PROCEDURE,
                    EXECUTE ANY PROGRAM,EXECUTE ANY RULE,EXECUTE ANY RULE SET,EXECUTE ANY TYPE,EXECUTE ASSEMBLY                                                                

SELECT            9 SELECT ANY CUBE,SELECT ANY CUBE BUILD PROCESS,SELECT ANY CUBE DIMENSION,SELECT ANY DICTIONARY,SELECT ANY MEASURE FOLDER,SELECT ANY MINING MODEL,SELECT ANY 
                    SEQUENCE,SELECT ANY TABLE,SELECT ANY TRANSACTION                                                                                                           

ADMINISTER        5 ADMINISTER ANY SQL TUNING SET,ADMINISTER DATABASE TRIGGER,ADMINISTER RESOURCE MANAGER,ADMINISTER SQL MANAGEMENT OBJECT,ADMINISTER SQL TUNING SET           
MANAGE            5 MANAGE ANY FILE GROUP,MANAGE ANY QUEUE,MANAGE FILE GROUP,MANAGE SCHEDULER,MANAGE TABLESPACE                                                                
UPDATE            4 UPDATE ANY CUBE,UPDATE ANY CUBE BUILD PROCESS,UPDATE ANY CUBE DIMENSION,UPDATE ANY TABLE                                                                   
DEBUG             3 DEBUG ANY PROCEDURE,DEBUG CONNECT ANY,DEBUG CONNECT SESSION                                                                                                
DELETE            3 DELETE ANY CUBE DIMENSION,DELETE ANY MEASURE FOLDER,DELETE ANY TABLE                                                                                       
GRANT             3 GRANT ANY OBJECT PRIVILEGE,GRANT ANY PRIVILEGE,GRANT ANY ROLE                                                                                              
INSERT            3 INSERT ANY CUBE DIMENSION,INSERT ANY MEASURE FOLDER,INSERT ANY TABLE                                                                                       
UNDER             3 UNDER ANY TABLE,UNDER ANY TYPE,UNDER ANY VIEW                                                                                                              
ANALYZE           2 ANALYZE ANY,ANALYZE ANY DICTIONARY                                                                                                                         
AUDIT             2 AUDIT ANY,AUDIT SYSTEM                                                                                                                                     
COMMENT           2 COMMENT ANY MINING MODEL,COMMENT ANY TABLE                                                                                                                 
FLASHBACK         2 FLASHBACK ANY TABLE,FLASHBACK ARCHIVE ADMINISTER                                                                                                           
FORCE             2 FORCE ANY TRANSACTION,FORCE TRANSACTION                                                                                                                    
READ              2 READ ANY FILE GROUP,READ ANY TABLE                                                                                                                         
USE               2 USE ANY JOB RESOURCE,USE ANY SQL TRANSLATION PROFILE                                                                                                       
ADVISOR           1 ADVISOR                                                                                                                                                    
BACKUP            1 BACKUP ANY TABLE                                                                                                                                           
BECOME            1 BECOME USER                                                                                                                                                
CHANGE            1 CHANGE NOTIFICATION                                                                                                                                        
DEQUEUE           1 DEQUEUE ANY QUEUE                                                                                                                                          
EM                1 EM EXPRESS CONNECT                                                                                                                                         
ENQUEUE           1 ENQUEUE ANY QUEUE                                                                                                                                          
EXEMPT            1 EXEMPT REDACTION POLICY                                                                                                                                    
EXPORT            1 EXPORT FULL DATABASE                                                                                                                                       
GLOBAL            1 GLOBAL QUERY REWRITE                                                                                                                                       
IMPORT            1 IMPORT FULL DATABASE                                                                                                                                       
LOCK              1 LOCK ANY TABLE                                                                                                                                             
LOGMINING         1 LOGMINING                                                                                                                                                  
MERGE             1 MERGE ANY VIEW                                                                                                                                             
ON                1 ON COMMIT REFRESH                                                                                                                                          
QUERY             1 QUERY REWRITE                                                                                                                                              
REDEFINE          1 REDEFINE ANY TABLE                                                                                                                                         
RESTRICTED        1 RESTRICTED SESSION                                                                                                                                         
RESUMABLE         1 RESUMABLE                                                                                                                                                  
SET               1 SET CONTAINER                                                                                                                                              

40行が選択されました。 


test___role_prv_mst___の作成

コード表示

DROP TABLE test___role_prv_mst___ PURGE;
CREATE TABLE test___role_prv_mst___ AS
WITH liz___source___ AS(
SELECT
	SUM(CASE WHEN LEVEL <> 1 THEN 0 ELSE 1 END) OVER (ORDER BY ROWNUM) AS grp
	,LEVEL AS lv
	,CONNECT_BY_ROOT grantee AS root_role
	,grantee
	,granted_role
	,CONNECT_BY_ISLEAF AS isleaf
	,sys_connect_by_path(granted_role,',') AS liz
FROM
	dba_role_privs
START WITH
	grantee = 'DBA'
CONNECT BY
	PRIOR granted_role = grantee
)
,liz___create___ AS(
SELECT
	root_role
	,item_liz.ecl_dupli_liz(item_liz.segregate(substr(LISTAGG(liz)WITHIN GROUP (ORDER BY grp+grpseq),2))) AS liz
FROM
	(
	SELECT
		s1.grp
		,row_number()OVER(PARTITION BY s1.grp ORDER BY lv) AS grpseq
		,s1.lv
		,s1.root_role
		,s1.liz
	FROM
		liz___source___ s1
	WHERE
		s1.isleaf = 1
	)
GROUP BY
	root_role
)
,mst___role____ AS(
SELECT
	root_role AS root_role
	, column_value AS related_role
FROM
	liz___create___
	, TABLE ( liz )
)
,mst___role_prv____ AS(
SELECT
	grantee
	,RTRIM(XMLAGG(XMLELEMENT(E,PRIVILEGE,',').EXTRACT('//text()')).getclobval(),',') AS liz
FROM
	dba_sys_privs
GROUP BY
	grantee
)
,summury___role_prv___ AS (
SELECT
	s1.root_role
	,s1.related_role
	,s2.liz
FROM
	mst___role____ s1
		LEFT OUTER JOIN mst___role_prv____ s2
			ON
				s1.related_role = s2.grantee
),detail___role_prv___ AS(
	SELECT
		root_role
		,related_role
		,liz
		,COLUMN_VALUE AS related_prv
	FROM
		summury___role_prv___
		,TABLE(item_liz.segregate(liz))
)SELECT
	s1.root_role
	,s1.related_role
	,s1.related_prv
FROM
	detail___role_prv___ s1
ORDER BY
	s1.related_role
	,s1.related_prv
;
select * from test___role_prv_mst___;

test___ope_prv_mst___の作成

コード表示

DROP TABLE test___ope_prv_mst___ PURGE;
CREATE TABLE test___ope_prv_mst___ AS
SELECT DISTINCT
	ope
	,PRIVILEGE as prv
FROM
	(
		SELECT
			s1.*
			, row_number() OVER(
				PARTITION BY s1.ROLE, s1.PRIVILEGE
				ORDER BY
					ROWNUM
			) AS rn
			, COLUMN_VALUE AS ope
		FROM
			(
				SELECT
					ROLE
					, PRIVILEGE
					, item_liz.segregate(REPLACE(PRIVILEGE, ' ', ',')
					              || ',') AS liz
				FROM
					role_sys_privs
			) s1
			, TABLE ( liz ) s2
	)
WHERE
	rn = 1
ORDER BY
	ope
	,PRIVILEGE
;

select * from TEST___OPE_PRV_MST___;

Leave a Reply

Your email address will not be published. Required fields are marked *