実行者権限(authid current_user)と即時実行(execute immediate)のコラボがいいんじゃないか??

参考文献

なんども読み返している

で結局、BEQUEATH VIEWってなんなのさ?(Oracle)  
定義者権限および実行者権限のセキュリティの管理  

ユーザー作成

POSSESSORユーザーとINVOKERユーザーのアカウントを作成。

コード表示

--sys session
drop user POSSESSOR cascade;
drop user INVOKER cascade;
create user POSSESSOR identified by test default tablespace users quota unlimited on users;
create user INVOKER identified by test default tablespace users quota unlimited on users; 

実行例

コード表示

[oracle@centos7 ~]$ docker exec -it orcl_18cr3_1 bash
[oracle@f285aba0589a ~]$ sqlplus sys/ORACLE_PWD@pdb1 as sysdba

SQL*Plus: Release 18.0.0.0.0 - Production on Fri Mar 29 18:46:22 2019
Version 18.3.0.0.0

Copyright (c) 1982, 2018, Oracle.  All rights reserved.


Connected to:
Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production
Version 18.3.0.0.0

SYS@pdb1> drop user POSSESSOR cascade;
drop user INVOKER cascade;
drop user POSSESSOR cascade
          *
ERROR at line 1:
ORA-01918: user 'POSSESSOR' does not exist


Elapsed: 00:00:00.02
SYS@pdb1> create user POSSESSOR identified by test default tablespace users quota unlimited on users;
create user INVOKER identified by test default tablespace users quota unlimited on users;

User dropped.

Elapsed: 00:00:00.06
SYS@pdb1>
User created.

Elapsed: 00:00:00.02
SYS@pdb1>
User created.

Elapsed: 00:00:00.02

オブジェクト作成

SYSスキーマにdual以外のオブジェクトを作成

コード表示

--sys session
drop table tbl___privilege___ purge;
create table tbl___privilege___ as select level as rn from dual connect by level <= 1; 

実行例

コード表示

SYS@pdb1> drop table tbl___privilege___ purge;

Table dropped.

Elapsed: 00:00:00.04
SYS@pdb1> create table tbl___privilege___ as select level as rn from dual connect by level <= 1;

Table created.

Elapsed: 00:00:00.03

コンパイルするファンクション

dual表とdual表以外。所有者権限と実行者権限。即時実行と即時実行以外。これらの組み合わせ。

コード表示

--possessor session
drop function func___current_user_dual_execute_immediate___;
drop function func___current_user_dual___;
drop function func___current_user_except_dual_execute_immediate___;
drop function func___current_user_except_dual___;
drop function func___definer_dual_execute_immediate___;
drop function func___definer_dual___;
drop function func___definer_except_dual_execute_immediate___;
drop function func___definer_except_dual___;

create or replace function func___current_user_dual_execute_immediate___ return varchar2
authid current_user
as
    rt varchar2(30);
begin
	execute immediate 'select' || ''' hello world ''' || 'from dual' into rt;
    return rt;
end;
/
create or replace function func___current_user_dual___ return varchar2
authid current_user
as
    rt varchar2(30);
begin
	select 'hello world' into rt from dual;
    return rt;
end;
/
create or replace function func___current_user_except_dual_execute_immediate___ return varchar2
authid current_user
as
    rt varchar2(30);
begin
	execute immediate 'select rn from sys.tbl___privilege___' into rt;
    return rt;
end;
/
create or replace function func___current_user_except_dual___ return varchar2
authid current_user
as
    rt varchar2(30);
begin
    select rn into rt
    from sys.tbl___privilege___;
    return rt;
end;
/
create or replace function func___definer_dual_execute_immediate___ return varchar2
authid definer
as
    rt varchar2(30);
begin
	execute immediate 'select' || ''' hello world ''' || 'from dual' into rt;
    return rt;
end;
/
create or replace function func___definer_dual___ return varchar2
authid definer
as
    rt varchar2(30);
begin
	select 'hello world' into rt from dual;
    return rt;
end;
/
create or replace function func___definer_except_dual_execute_immediate___ return varchar2
authid definer
as
    rt varchar2(30);
begin
	execute immediate 'select rn from sys.tbl___privilege___' into rt;
    return rt;
end;
/
create or replace function func___definer_except_dual___ return varchar2
authid definer
as
    rt varchar2(30);
begin
    select rn into rt
    from sys.tbl___privilege___;
    return rt;
end;
/
select authid,object_name from user_procedures order by authid,object_name;

実行するファンクションクエリ

クエリ

コード表示

col rt for a30
select possessor.FUNC___CURRENT_USER_DUAL_EXECUTE_IMMEDIATE___ as rt from dual;
select possessor.FUNC___CURRENT_USER_DUAL___ as rt from dual;
select possessor.FUNC___CURRENT_USER_EXCEPT_DUAL_EXECUTE_IMMEDIATE___ as rt from dual;
select possessor.FUNC___CURRENT_USER_EXCEPT_DUAL___ as rt from dual;
select possessor.FUNC___DEFINER_DUAL_EXECUTE_IMMEDIATE___ as rt from dual;
select possessor.FUNC___DEFINER_DUAL___ as rt from dual;
select possessor.FUNC___DEFINER_EXCEPT_DUAL_EXECUTE_IMMEDIATE___ as rt from dual;
select possessor.FUNC___DEFINER_EXCEPT_DUAL___ as rt from dual;

コンパイル時(possessorユーザー)

他人の持ち物を使って自分の持ち物を作成することはできないよって感じだな。

直接権限付与

クエリ

コード表示

--sys session
grant create session to POSSESSOR;
grant create table to POSSESSOR;
grant create view to POSSESSOR;
grant create procedure to POSSESSOR;
grant select on sys.tbl___privilege___ to POSSESSOR;

実行例

コード表示

[oracle@f285aba0589a ~]$ sqlplus sys/ORACLE_PWD@pdb1 as sysdba
SYS@pdb1> grant create session to POSSESSOR;
grant create table to POSSESSOR;

Grant succeeded.

Elapsed: 00:00:00.03
SYS@pdb1> grant create view to POSSESSOR;

Grant succeeded.

Elapsed: 00:00:00.00
SYS@pdb1>
Grant succeeded.

Elapsed: 00:00:00.01
SYS@pdb1> grant create procedure to POSSESSOR;

Grant succeeded.

Elapsed: 00:00:00.01
SYS@pdb1> grant select on sys.tbl___privilege___ to POSSESSOR;

Grant succeeded.

Elapsed: 00:00:00.02

[oracle@f285aba0589a ~]$ sqlplus possessor/test@pdb1

SQL*Plus: Release 18.0.0.0.0 - Production on Fri Mar 29 18:49:31 2019
Version 18.3.0.0.0

Copyright (c) 1982, 2018, Oracle.  All rights reserved.


Connected to:
Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production
Version 18.3.0.0.0

POSSESSOR@pdb1>
POSSESSOR@pdb1> --possessor session
POSSESSOR@pdb1> drop function func___current_user_dual_execute_immediate___;
drop function func___current_user_dual_execute_immediate___
*
ERROR at line 1:
ORA-04043: object FUNC___CURRENT_USER_DUAL_EXECUTE_IMMEDIATE___ does not exist


Elapsed: 00:00:00.00
POSSESSOR@pdb1> drop function func___current_user_dual___;
drop function func___current_user_dual___
*
ERROR at line 1:
ORA-04043: object FUNC___CURRENT_USER_DUAL___ does not exist


Elapsed: 00:00:00.00
POSSESSOR@pdb1> drop function func___current_user_except_dual_execute_immediate___;
drop function func___current_user_except_dual_execute_immediate___
*
ERROR at line 1:
ORA-04043: object FUNC___CURRENT_USER_EXCEPT_DUAL_EXECUTE_IMMEDIATE___ does not exist


Elapsed: 00:00:00.00
POSSESSOR@pdb1> drop function func___current_user_except_dual___;
drop function func___current_user_except_dual___
*
ERROR at line 1:
ORA-04043: object FUNC___CURRENT_USER_EXCEPT_DUAL___ does not exist


Elapsed: 00:00:00.00
POSSESSOR@pdb1> drop function func___definer_dual_execute_immediate___;
drop function func___definer_dual_execute_immediate___
*
ERROR at line 1:
ORA-04043: object FUNC___DEFINER_DUAL_EXECUTE_IMMEDIATE___ does not exist


Elapsed: 00:00:00.00
POSSESSOR@pdb1> drop function func___definer_dual___;
drop function func___definer_dual___
*
ERROR at line 1:
ORA-04043: object FUNC___DEFINER_DUAL___ does not exist


Elapsed: 00:00:00.00
POSSESSOR@pdb1> drop function func___definer_except_dual_execute_immediate___;
drop function func___definer_except_dual_execute_immediate___
*
ERROR at line 1:
ORA-04043: object FUNC___DEFINER_EXCEPT_DUAL_EXECUTE_IMMEDIATE___ does not exist


Elapsed: 00:00:00.01
POSSESSOR@pdb1> drop function func___definer_except_dual___;
drop function func___definer_except_dual___
*
ERROR at line 1:
ORA-04043: object FUNC___DEFINER_EXCEPT_DUAL___ does not exist


Elapsed: 00:00:00.00
POSSESSOR@pdb1>
POSSESSOR@pdb1> create or replace function func___current_user_dual_execute_immediate___ return varchar2
  2  authid current_user
  3  as
  4      rt varchar2(30);
  5  begin
  6  execute immediate 'select' || ''' hello world ''' || 'from dual' into rt;
  7      return rt;
end;
/
create or replace function func___current_user_dual___ return varchar2
authid current_user
as
    rt varchar2(30);
begin

Function created.

Elapsed: 00:00:00.05
POSSESSOR@pdb1>   2    3    4    5    6  select 'hello world' into rt from dual;
  7      return rt;
  8  end;
  9  /

Function created.

Elapsed: 00:00:00.01
POSSESSOR@pdb1> create or replace function func___current_user_except_dual_execute_immediate___ return varchar2
  2  authid current_user
as
    rt varchar2(30);
begin
execute immediate 'select rn from sys.tbl___privilege___' into rt;
    return rt;
  8  end;
  9  /

Function created.

Elapsed: 00:00:00.01
POSSESSOR@pdb1> create or replace function func___current_user_except_dual___ return varchar2
  2  authid current_user
  3  as
  4      rt varchar2(30);
  5  begin
    select rn into rt
    from sys.tbl___privilege___;
    return rt;
end;
/
create or replace function func___definer_dual_execute_immediate___ return varchar2
authid definer
as

Function created.

Elapsed: 00:00:00.03
POSSESSOR@pdb1>   2    3    4      rt varchar2(30);
  5  begin
  6  execute immediate 'select' || ''' hello world ''' || 'from dual' into rt;
  7      return rt;
  8  end;
  9  /
create or replace function func___definer_dual___ return varchar2
authid definer
as

Function created.

Elapsed: 00:00:00.03
    rt varchar2(30);
begin
  6  select 'hello world' into rt from dual;
  7      return rt;
  8  end;
  9  /

Function created.

Elapsed: 00:00:00.01
POSSESSOR@pdb1> create or replace function func___definer_except_dual_execute_immediate___ return varchar2
  2  authid definer
  3  as
    rt varchar2(30);
begin
execute immediate 'select rn from sys.tbl___privilege___' into rt;
    return rt;
end;
/
create or replace function func___definer_except_dual___ return varchar2

Function created.

Elapsed: 00:00:00.01
POSSESSOR@pdb1>   2  authid definer
  3  as
  4      rt varchar2(30);
  5  begin
  6      select rn into rt
    from sys.tbl___privilege___;
    return rt;
end;
/
select authid,object_name from user_procedures order by authid,object_name;

Function created.

Elapsed: 00:00:00.01
POSSESSOR@pdb1>
AUTHID       OBJECT_NAME
------------ --------------------------------------------------------------------------------------------------------------------------------
CURRENT_USER FUNC___CURRENT_USER_DUAL_EXECUTE_IMMEDIATE___
CURRENT_USER FUNC___CURRENT_USER_DUAL___
CURRENT_USER FUNC___CURRENT_USER_EXCEPT_DUAL_EXECUTE_IMMEDIATE___
CURRENT_USER FUNC___CURRENT_USER_EXCEPT_DUAL___
DEFINER      FUNC___DEFINER_DUAL_EXECUTE_IMMEDIATE___
DEFINER      FUNC___DEFINER_DUAL___
DEFINER      FUNC___DEFINER_EXCEPT_DUAL_EXECUTE_IMMEDIATE___
DEFINER      FUNC___DEFINER_EXCEPT_DUAL___

8 rows selected.

Elapsed: 00:00:00.12

ロール経由権限付与

4つの権限(システム権限)+1つの権限(オブジェクト権限)をロールにくるんで付与してみる。

コード表示

--sys session
revoke create session from POSSESSOR;
revoke create table from POSSESSOR;
revoke create view from POSSESSOR;
revoke create procedure from POSSESSOR;
revoke select on sys.tbl___privilege___ from POSSESSOR;
drop role role_test;
create role role_test;
grant create session to role_test;
grant create table to role_test;
grant create view to role_test;
grant create procedure to role_test;
grant select on sys.tbl___privilege___ to role_test;
grant role_test to possessor;

col ROLE for a30
col PRIVILEGE for a30
col ADMIN_OPTION for a30
col COMMON for a30
col INHERITED for a30
select * from role_sys_privs where role = 'ROLE_TEST';

col ROLE for a20
col OWNER for a20
col TABLE_NAME for a20
col COLUMN_NAME for a20
col PRIVILEGE for a20
col GRANTABLE for a20
col COMMON for a20
col INHERITED for a20
select * from role_tab_privs where role = 'ROLE_TEST';

実行例

コード表示

[oracle@f285aba0589a ~]$ sqlplus sys/ORACLE_PWD@pdb1 as sysdba

SQL*Plus: Release 18.0.0.0.0 - Production on Fri Mar 29 18:52:22 2019
Version 18.3.0.0.0

Copyright (c) 1982, 2018, Oracle.  All rights reserved.


Connected to:
Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production
Version 18.3.0.0.0

SYS@pdb1> --sys session
SYS@pdb1> revoke create session from POSSESSOR;
revoke create table from POSSESSOR;

Revoke succeeded.

Elapsed: 00:00:00.01
SYS@pdb1>
Revoke succeeded.

Elapsed: 00:00:00.00
SYS@pdb1> revoke create view from POSSESSOR;

Revoke succeeded.

Elapsed: 00:00:00.00
SYS@pdb1> revoke create procedure from POSSESSOR;

Revoke succeeded.

Elapsed: 00:00:00.01
SYS@pdb1> revoke select on sys.tbl___privilege___ from POSSESSOR;

Revoke succeeded.

Elapsed: 00:00:00.02
SYS@pdb1> drop role role_test;
create role role_test;
grant create session to role_test;

Role dropped.

Elapsed: 00:00:00.03
SYS@pdb1>
Role created.

Elapsed: 00:00:00.01
SYS@pdb1>
Grant succeeded.

Elapsed: 00:00:00.00
SYS@pdb1> grant create table to role_test;

Grant succeeded.

Elapsed: 00:00:00.00
SYS@pdb1> grant create view to role_test;

Grant succeeded.

Elapsed: 00:00:00.01
SYS@pdb1> grant create procedure to role_test;

Grant succeeded.

Elapsed: 00:00:00.00
SYS@pdb1> grant select on sys.tbl___privilege___ to role_test;

Grant succeeded.

Elapsed: 00:00:00.01
SYS@pdb1> grant role_test to possessor;

Grant succeeded.

Elapsed: 00:00:00.00
SYS@pdb1>
SYS@pdb1> col ROLE for a30
SYS@pdb1> col PRIVILEGE for a30
SYS@pdb1> col ADMIN_OPTION for a30
SYS@pdb1> col COMMON for a30
SYS@pdb1> col INHERITED for a30
SYS@pdb1> select * from role_sys_privs where role = 'ROLE_TEST';

ROLE                           PRIVILEGE                      ADMIN_OPTION                   COMMON                         INHERITED
------------------------------ ------------------------------ ------------------------------ ------------------------------ ------------------------------
ROLE_TEST                      CREATE SESSION                 NO                             NO                             NO
ROLE_TEST                      CREATE PROCEDURE               NO                             NO                             NO
ROLE_TEST                      CREATE VIEW                    NO                             NO                             NO
ROLE_TEST                      CREATE TABLE                   NO                             NO                             NO

4 rows selected.

Elapsed: 00:00:00.02
SYS@pdb1>
SYS@pdb1> col ROLE for a20
SYS@pdb1> col OWNER for a20
SYS@pdb1> col TABLE_NAME for a20
SYS@pdb1> col COLUMN_NAME for a20
SYS@pdb1> col PRIVILEGE for a20
SYS@pdb1> col GRANTABLE for a20
SYS@pdb1> col COMMON for a20
SYS@pdb1> col INHERITED for a20
SYS@pdb1> select * from role_tab_privs where role = 'ROLE_TEST';

ROLE                 OWNER                TABLE_NAME           COLUMN_NAME          PRIVILEGE            GRANTABLE            COMMON               INHERITED
-------------------- -------------------- -------------------- -------------------- -------------------- -------------------- -------------------- --------------------
ROLE_TEST            SYS                  TBL___PRIVILEGE___                        SELECT               NO                   NO                   NO

1 row selected.

Elapsed: 00:00:00.09

[oracle@f285aba0589a ~]$ sqlplus possessor/test@pdb1

SQL*Plus: Release 18.0.0.0.0 - Production on Fri Mar 29 18:53:21 2019
Version 18.3.0.0.0

Copyright (c) 1982, 2018, Oracle.  All rights reserved.

Last Successful login time: Fri Mar 29 2019 18:49:31 +09:00

Connected to:
Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production
Version 18.3.0.0.0

POSSESSOR@pdb1>
POSSESSOR@pdb1> --possessor session
POSSESSOR@pdb1> drop function func___current_user_dual_execute_immediate___;
drop function func___current_user_dual___;

Function dropped.

Elapsed: 00:00:00.03
POSSESSOR@pdb1> drop function func___current_user_except_dual_execute_immediate___;
drop function func___current_user_except_dual___;

Function dropped.

Elapsed: 00:00:00.02
POSSESSOR@pdb1> drop function func___definer_dual_execute_immediate___;

Function dropped.

Elapsed: 00:00:00.01
POSSESSOR@pdb1> drop function func___definer_dual___;

Function dropped.

Elapsed: 00:00:00.02
POSSESSOR@pdb1>
Function dropped.

Elapsed: 00:00:00.01
POSSESSOR@pdb1> drop function func___definer_except_dual_execute_immediate___;

Function dropped.

Elapsed: 00:00:00.01
POSSESSOR@pdb1>
Function dropped.

Elapsed: 00:00:00.00
POSSESSOR@pdb1> drop function func___definer_except_dual___;

Function dropped.

Elapsed: 00:00:00.01
POSSESSOR@pdb1>
POSSESSOR@pdb1> create or replace function func___current_user_dual_execute_immediate___ return varchar2
  2  authid current_user
  3  as
  4      rt varchar2(30);
  5  begin
  6  execute immediate 'select' || ''' hello world ''' || 'from dual' into rt;
  7      return rt;
  8  end;
  9  /
create or replace function func___current_user_dual___ return varchar2
authid current_user

Function created.

Elapsed: 00:00:00.03
POSSESSOR@pdb1>   2    3  as
  4      rt varchar2(30);
  5  begin
  6  select 'hello world' into rt from dual;
  7      return rt;
  8  end;
  9  /

Function created.

Elapsed: 00:00:00.01
POSSESSOR@pdb1> create or replace function func___current_user_except_dual_execute_immediate___ return varchar2
  2  authid current_user
  3  as
  4      rt varchar2(30);
  5  begin
  6  execute immediate 'select rn from sys.tbl___privilege___' into rt;
  7      return rt;
  8  end;
  9  /

Function created.

Elapsed: 00:00:00.02
POSSESSOR@pdb1> create or replace function func___current_user_except_dual___ return varchar2
  2  authid current_user
  3  as
  4      rt varchar2(30);
  5  begin
  6      select rn into rt
  7      from sys.tbl___privilege___;
  8      return rt;
  9  end;
 10  /

Warning: Function created with compilation errors.

Elapsed: 00:00:00.02
POSSESSOR@pdb1> create or replace function func___definer_dual_execute_immediate___ return varchar2
  2  authid definer
  3  as
  4      rt varchar2(30);
  5  begin
  6  execute immediate 'select' || ''' hello world ''' || 'from dual' into rt;
  7      return rt;
  8  end;
  9  /
create or replace function func___definer_dual___ return varchar2

Function created.

Elapsed: 00:00:00.01
POSSESSOR@pdb1>   2  authid definer
  3  as
  4      rt varchar2(30);
  5  begin
  6  select 'hello world' into rt from dual;
  7      return rt;
  8  end;
  9  /

Function created.

Elapsed: 00:00:00.02
POSSESSOR@pdb1> create or replace function func___definer_except_dual_execute_immediate___ return varchar2
  2  authid definer
  3  as
  4      rt varchar2(30);
  5  begin
  6  execute immediate 'select rn from sys.tbl___privilege___' into rt;
  7      return rt;
  8  end;
  9  /

Function created.

Elapsed: 00:00:00.02
POSSESSOR@pdb1> create or replace function func___definer_except_dual___ return varchar2
  2  authid definer
  3  as
  4      rt varchar2(30);
  5  begin
  6      select rn into rt
  7      from sys.tbl___privilege___;
  8      return rt;
  9  end;
 10  /
select a
uthid,object_name from user_procedures ordeWarning: Function created with compilation errors.r by auth
id,ob
ject_name;
Elapsed: 00:00:00.01
POSSESSOR@pdb1>
AUTHID       OBJECT_NAME
------------ --------------------------------------------------------------------------------------------------------------------------------
CURRENT_USER FUNC___CURRENT_USER_DUAL_EXECUTE_IMMEDIATE___
CURRENT_USER FUNC___CURRENT_USER_DUAL___
CURRENT_USER FUNC___CURRENT_USER_EXCEPT_DUAL_EXECUTE_IMMEDIATE___
DEFINER      FUNC___DEFINER_DUAL_EXECUTE_IMMEDIATE___
DEFINER      FUNC___DEFINER_DUAL___
DEFINER      FUNC___DEFINER_EXCEPT_DUAL_EXECUTE_IMMEDIATE___

6 rows selected.

Elapsed: 00:00:00.02

実行時(invokerユーザー)

あたりまえだけど、コンパイルできたものが実行できる。

直接権限付与

クエリ

コード表示

--sys session
drop role role_test;
grant create session to POSSESSOR;
grant create table to POSSESSOR;
grant create view to POSSESSOR;
grant create procedure to POSSESSOR;
grant select on sys.tbl___privilege___ to POSSESSOR;
grant create session to INVOKER;

--possessor session
grant execute on FUNC___CURRENT_USER_DUAL___ to invoker;
grant execute on FUNC___CURRENT_USER_EXCEPT_DUAL_EXECUTE_IMMEDIATE___ to invoker;
grant execute on FUNC___CURRENT_USER_DUAL_EXECUTE_IMMEDIATE___ to invoker;
grant execute on FUNC___CURRENT_USER_EXCEPT_DUAL___ to invoker;
grant execute on FUNC___DEFINER_DUAL___ to invoker;
grant execute on FUNC___DEFINER_EXCEPT_DUAL___ to invoker;
grant execute on FUNC___DEFINER_DUAL_EXECUTE_IMMEDIATE___ to invoker;
grant execute on FUNC___DEFINER_EXCEPT_DUAL_EXECUTE_IMMEDIATE___ to invoker;

col GRANTEE for a10
col TABLE_NAME for a55
col GRANTOR for a10
col PRIVILEGE for a25
col GRANTABLE for a10
col HIERARCHY for a10
col COMMON for a10
col TYPE for a10
col INHERITED for a10
select * from USER_TAB_PRIVS_MADE;


--invoker session
col OWNER for a10
col TABLE_NAME for a55
col GRANTOR for a10
col PRIVILEGE for a25
col GRANTABLE for a10
col HIERARCHY for a10
col COMMON for a10
col TYPE for a10
col INHERITED for a10
select * from USER_TAB_PRIVS_RECD ;

実行例

コード表示

[oracle@f285aba0589a ~]$ sqlplus sys/ORACLE_PWD@pdb1 as sysdba

SQL*Plus: Release 18.0.0.0.0 - Production on Fri Mar 29 18:55:22 2019
Version 18.3.0.0.0

Copyright (c) 1982, 2018, Oracle.  All rights reserved.


Connected to:
Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production
Version 18.3.0.0.0

SYS@pdb1> drop role role_test;
grant create session to POSSESSOR;

Role dropped.

Elapsed: 00:00:00.02
SYS@pdb1> grant create table to POSSESSOR;

Grant succeeded.

Elapsed: 00:00:00.01
SYS@pdb1>
Grant succeeded.

Elapsed: 00:00:00.00
SYS@pdb1> grant create view to POSSESSOR;

Grant succeeded.

Elapsed: 00:00:00.00
SYS@pdb1> grant create procedure to POSSESSOR;

Grant succeeded.

Elapsed: 00:00:00.00
SYS@pdb1> grant select on sys.tbl___privilege___ to POSSESSOR;

Grant succeeded.

Elapsed: 00:00:00.01
SYS@pdb1> grant create session to INVOKER;

Grant succeeded.

Elapsed: 00:00:00.02

[oracle@f285aba0589a ~]$ sqlplus possessor/test@pdb1

SQL*Plus: Release 18.0.0.0.0 - Production on Fri Mar 29 18:55:52 2019
Version 18.3.0.0.0

Copyright (c) 1982, 2018, Oracle.  All rights reserved.

Last Successful login time: Fri Mar 29 2019 18:53:21 +09:00

Connected to:
Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production
Version 18.3.0.0.0

POSSESSOR@pdb1> grant execute on FUNC___CURRENT_USER_DUAL___ to invoker;
grant execute on FUNC___CURRENT_USER_EXCEPT_DUAL_EXECUTE_IMMEDIATE___ to invoker;

Grant succeeded.

Elapsed: 00:00:00.03
POSSESSOR@pdb1>
Grant succeeded.

Elapsed: 00:00:00.00
POSSESSOR@pdb1> grant execute on FUNC___CURRENT_USER_DUAL_EXECUTE_IMMEDIATE___ to invoker;

Grant succeeded.

Elapsed: 00:00:00.01
POSSESSOR@pdb1> grant execute on FUNC___CURRENT_USER_EXCEPT_DUAL___ to invoker;
grant execute on FUNC___DEFINER_DUAL___ to invoker;
grant execute on FUNC___DEFINER_EXCEPT_DUAL___ to invoker;
grant execute on FUNC___DEFINER_DUAL_EXECUTE_IMMEDIATE___ to invoker;

Grant succeeded.

Elapsed: 00:00:00.05
POSSESSOR@pdb1>
Grant succeeded.

Elapsed: 00:00:00.00
POSSESSOR@pdb1> grant execute on FUNC___DEFINER_EXCEPT_DUAL_EXECUTE_IMMEDIATE___ to invoker;

Grant succeeded.

Elapsed: 00:00:00.02
POSSESSOR@pdb1>
Grant succeeded.

Elapsed: 00:00:00.00
POSSESSOR@pdb1>
Grant succeeded.

Elapsed: 00:00:00.00

POSSESSOR@pdb1> col GRANTEE for a10
POSSESSOR@pdb1> col TABLE_NAME for a55
POSSESSOR@pdb1> col GRANTOR for a10
POSSESSOR@pdb1> col PRIVILEGE for a25
POSSESSOR@pdb1> col GRANTABLE for a10
POSSESSOR@pdb1> col HIERARCHY for a10
POSSESSOR@pdb1> col COMMON for a10
POSSESSOR@pdb1> col TYPE for a10
POSSESSOR@pdb1> col INHERITED for a10
POSSESSOR@pdb1> select * from USER_TAB_PRIVS_MADE;

GRANTEE    TABLE_NAME                                              GRANTOR    PRIVILEGE                 GRANTABLE  HIERARCHY  COMMON     TYPE       INHERITED
---------- ------------------------------------------------------- ---------- ------------------------- ---------- ---------- ---------- ---------- ----------
INVOKER    FUNC___CURRENT_USER_DUAL_EXECUTE_IMMEDIATE___           POSSESSOR  EXECUTE                   NO         NO         NO         FUNCTION   NO
INVOKER    FUNC___CURRENT_USER_DUAL___                             POSSESSOR  EXECUTE                   NO         NO         NO         FUNCTION   NO
INVOKER    FUNC___CURRENT_USER_EXCEPT_DUAL_EXECUTE_IMMEDIATE___    POSSESSOR  EXECUTE                   NO         NO         NO         FUNCTION   NO
INVOKER    FUNC___CURRENT_USER_EXCEPT_DUAL___                      POSSESSOR  EXECUTE                   NO         NO         NO         FUNCTION   NO
INVOKER    FUNC___DEFINER_DUAL_EXECUTE_IMMEDIATE___                POSSESSOR  EXECUTE                   NO         NO         NO         FUNCTION   NO
INVOKER    FUNC___DEFINER_DUAL___                                  POSSESSOR  EXECUTE                   NO         NO         NO         FUNCTION   NO
INVOKER    FUNC___DEFINER_EXCEPT_DUAL_EXECUTE_IMMEDIATE___         POSSESSOR  EXECUTE                   NO         NO         NO         FUNCTION   NO
INVOKER    FUNC___DEFINER_EXCEPT_DUAL___                           POSSESSOR  EXECUTE                   NO         NO         NO         FUNCTION   NO
PUBLIC     POSSESSOR                                               POSSESSOR  INHERIT PRIVILEGES        NO         NO         NO         USER       NO

9 rows selected.

Elapsed: 00:00:00.01

[oracle@f285aba0589a ~]$ sqlplus invoker/test@pdb1

SQL*Plus: Release 18.0.0.0.0 - Production on Fri Mar 29 19:09:52 2019
Version 18.3.0.0.0

Copyright (c) 1982, 2018, Oracle.  All rights reserved.

Last Successful login time: Fri Mar 29 2019 19:08:35 +09:00

Connected to:
Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production
Version 18.3.0.0.0

INVOKER@pdb1> col OWNER for a10
INVOKER@pdb1> col TABLE_NAME for a55
INVOKER@pdb1> col GRANTOR for a10
INVOKER@pdb1> col PRIVILEGE for a25
INVOKER@pdb1> col GRANTABLE for a10
INVOKER@pdb1> col HIERARCHY for a10
INVOKER@pdb1> col COMMON for a10
INVOKER@pdb1> col TYPE for a10
INVOKER@pdb1> col INHERITED for a10
INVOKER@pdb1> select * from USER_TAB_PRIVS_RECD ;

OWNER      TABLE_NAME                                              GRANTOR    PRIVILEGE                 GRANTABLE  HIERARCHY  COMMON     TYPE       INHERITED
---------- ------------------------------------------------------- ---------- ------------------------- ---------- ---------- ---------- ---------- ----------
POSSESSOR  FUNC___CURRENT_USER_DUAL_EXECUTE_IMMEDIATE___           POSSESSOR  EXECUTE                   NO         NO         NO         FUNCTION   NO
POSSESSOR  FUNC___CURRENT_USER_DUAL___                             POSSESSOR  EXECUTE                   NO         NO         NO         FUNCTION   NO
POSSESSOR  FUNC___CURRENT_USER_EXCEPT_DUAL_EXECUTE_IMMEDIATE___    POSSESSOR  EXECUTE                   NO         NO         NO         FUNCTION   NO
POSSESSOR  FUNC___CURRENT_USER_EXCEPT_DUAL___                      POSSESSOR  EXECUTE                   NO         NO         NO         FUNCTION   NO
POSSESSOR  FUNC___DEFINER_DUAL_EXECUTE_IMMEDIATE___                POSSESSOR  EXECUTE                   NO         NO         NO         FUNCTION   NO
POSSESSOR  FUNC___DEFINER_DUAL___                                  POSSESSOR  EXECUTE                   NO         NO         NO         FUNCTION   NO
POSSESSOR  FUNC___DEFINER_EXCEPT_DUAL_EXECUTE_IMMEDIATE___         POSSESSOR  EXECUTE                   NO         NO         NO         FUNCTION   NO
POSSESSOR  FUNC___DEFINER_EXCEPT_DUAL___                           POSSESSOR  EXECUTE                   NO         NO         NO         FUNCTION   NO

8 rows selected.

Elapsed: 00:00:00.00

INVOKER@pdb1> col rt for a30
INVOKER@pdb1> select possessor.FUNC___CURRENT_USER_DUAL_EXECUTE_IMMEDIATE___ as rt from dual;

RT
------------------------------
 hello world

1 row selected.

Elapsed: 00:00:00.00
INVOKER@pdb1> select possessor.FUNC___CURRENT_USER_DUAL___ as rt from dual;

RT
------------------------------
hello world

1 row selected.

Elapsed: 00:00:00.00
INVOKER@pdb1> select possessor.FUNC___CURRENT_USER_EXCEPT_DUAL_EXECUTE_IMMEDIATE___ as rt from dual;
select possessor.FUNC___CURRENT_USER_EXCEPT_DUAL_EXECUTE_IMMEDIATE___ as rt from dual
       *
ERROR at line 1:
ORA-00942: table or view does not exist
ORA-06512: at "POSSESSOR.FUNC___CURRENT_USER_EXCEPT_DUAL_EXECUTE_IMMEDIATE___", line 6


Elapsed: 00:00:00.00
INVOKER@pdb1> select possessor.FUNC___CURRENT_USER_EXCEPT_DUAL___ as rt from dual;
select possessor.FUNC___CURRENT_USER_EXCEPT_DUAL___ as rt from dual
       *
ERROR at line 1:
ORA-00942: table or view does not exist
ORA-06512: at "POSSESSOR.FUNC___CURRENT_USER_EXCEPT_DUAL___", line 6


Elapsed: 00:00:00.00
INVOKER@pdb1> select possessor.FUNC___DEFINER_DUAL_EXECUTE_IMMEDIATE___ as rt from dual;

RT
------------------------------
 hello world

1 row selected.

Elapsed: 00:00:00.01
INVOKER@pdb1> select possessor.FUNC___DEFINER_DUAL___ as rt from dual;

RT
------------------------------
hello world

1 row selected.

Elapsed: 00:00:00.01
INVOKER@pdb1> select possessor.FUNC___DEFINER_EXCEPT_DUAL_EXECUTE_IMMEDIATE___ as rt from dual;

RT
------------------------------
1

1 row selected.

Elapsed: 00:00:00.00
INVOKER@pdb1> select possessor.FUNC___DEFINER_EXCEPT_DUAL___ as rt from dual;

RT
------------------------------
1

1 row selected.

Elapsed: 00:00:00.01

ロール経由権限付与

4つの権限(システム権限)+1つの権限(オブジェクト権限)をロールにくるんで付与してみる。

コード表示

--sys session
revoke create session from POSSESSOR;
revoke create table from POSSESSOR;
revoke create view from POSSESSOR;
revoke create procedure from POSSESSOR;
revoke select on sys.tbl___privilege___ from POSSESSOR;
drop role role_test;
create role role_test;
grant create session to role_test;
grant create table to role_test;
grant create view to role_test;
grant create procedure to role_test;
grant select on sys.tbl___privilege___ to role_test;
grant role_test to possessor;
grant role_test to invoker;

col ROLE for a30
col PRIVILEGE for a30
col ADMIN_OPTION for a30
col COMMON for a30
col INHERITED for a30
select * from role_sys_privs where role = 'ROLE_TEST';

col ROLE for a20
col OWNER for a20
col TABLE_NAME for a20
col COLUMN_NAME for a20
col PRIVILEGE for a20
col GRANTABLE for a20
col COMMON for a20
col INHERITED for a20
select * from role_tab_privs where role = 'ROLE_TEST';

--possessor session
grant execute on FUNC___CURRENT_USER_DUAL___ to invoker;
grant execute on FUNC___CURRENT_USER_EXCEPT_DUAL_EXECUTE_IMMEDIATE___ to invoker;
grant execute on FUNC___CURRENT_USER_DUAL_EXECUTE_IMMEDIATE___ to invoker;
grant execute on FUNC___CURRENT_USER_EXCEPT_DUAL___ to invoker;
grant execute on FUNC___DEFINER_DUAL___ to invoker;
grant execute on FUNC___DEFINER_EXCEPT_DUAL___ to invoker;
grant execute on FUNC___DEFINER_DUAL_EXECUTE_IMMEDIATE___ to invoker;
grant execute on FUNC___DEFINER_EXCEPT_DUAL_EXECUTE_IMMEDIATE___ to invoker;

実行例

コード表示

[oracle@f285aba0589a ~]$ sqlplus sys/ORACLE_PWD@pdb1 as sysdba

SQL*Plus: Release 18.0.0.0.0 - Production on Fri Mar 29 19:12:17 2019
Version 18.3.0.0.0

Copyright (c) 1982, 2018, Oracle.  All rights reserved.


Connected to:
Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production
Version 18.3.0.0.0

SYS@pdb1> revoke create session from POSSESSOR;
revoke create table from POSSESSOR;
revoke create view from POSSESSOR;

Revoke succeeded.

Elapsed: 00:00:00.02
SYS@pdb1>
Revoke succeeded.

Elapsed: 00:00:00.00
SYS@pdb1>
Revoke succeeded.

Elapsed: 00:00:00.01
SYS@pdb1> revoke create procedure from POSSESSOR;

Revoke succeeded.

Elapsed: 00:00:00.01
SYS@pdb1> revoke select on sys.tbl___privilege___ from POSSESSOR;

Revoke succeeded.

Elapsed: 00:00:00.01
SYS@pdb1> drop role role_test;
drop role role_test
          *
ERROR at line 1:
ORA-01919: role 'ROLE_TEST' does not exist


Elapsed: 00:00:00.01
SYS@pdb1> create role role_test;

Role created.

Elapsed: 00:00:00.02
SYS@pdb1> grant create session to role_test;

Grant succeeded.

Elapsed: 00:00:00.00
SYS@pdb1> grant create table to role_test;

Grant succeeded.

Elapsed: 00:00:00.00
SYS@pdb1> grant create view to role_test;

Grant succeeded.

Elapsed: 00:00:00.00
SYS@pdb1> grant create procedure to role_test;

Grant succeeded.

Elapsed: 00:00:00.00
SYS@pdb1> grant select on sys.tbl___privilege___ to role_test;

Grant succeeded.

Elapsed: 00:00:00.01
SYS@pdb1> grant role_test to possessor;

Grant succeeded.

Elapsed: 00:00:00.01
SYS@pdb1> grant role_test to invoker;

Grant succeeded.

Elapsed: 00:00:00.00
SYS@pdb1>
SYS@pdb1> col ROLE for a30
SYS@pdb1> col PRIVILEGE for a30
SYS@pdb1> col ADMIN_OPTION for a30
SYS@pdb1> col COMMON for a30
SYS@pdb1> col INHERITED for a30
SYS@pdb1> select * from role_sys_privs where role = 'ROLE_TEST';


ROLE                           PRIVILEGE                      ADMIN_OPTION                   COMMON                         INHERITED
------------------------------ ------------------------------ ------------------------------ ------------------------------ ------------------------------
ROLE_TEST                      CREATE SESSION                 NO                             NO                             NO
ROLE_TEST                      CREATE PROCEDURE               NO                             NO                             NO
ROLE_TEST                      CREATE VIEW                    NO                             NO                             NO
ROLE_TEST                      CREATE TABLE                   NO                             NO                             NO

4 rows selected.

Elapsed: 00:00:00.01
SYS@pdb1> SYS@pdb1> col ROLE for a20
SYS@pdb1> col OWNER for a20
SYS@pdb1> col TABLE_NAME for a20
SYS@pdb1> col COLUMN_NAME for a20
SYS@pdb1> col PRIVILEGE for a20
SYS@pdb1> col GRANTABLE for a20
SYS@pdb1> col COMMON for a20
SYS@pdb1> col INHERITED for a20
SYS@pdb1> select * from role_tab_privs where role = 'ROLE_TEST';

ROLE                 OWNER                TABLE_NAME           COLUMN_NAME          PRIVILEGE            GRANTABLE            COMMON               INHERITED
-------------------- -------------------- -------------------- -------------------- -------------------- -------------------- -------------------- --------------------
ROLE_TEST            SYS                  TBL___PRIVILEGE___                        SELECT               NO                   NO                   NO

1 row selected.

Elapsed: 00:00:00.00

[oracle@f285aba0589a ~]$ sqlplus possessor/test@pdb1

SQL*Plus: Release 18.0.0.0.0 - Production on Fri Mar 29 19:14:10 2019
Version 18.3.0.0.0

Copyright (c) 1982, 2018, Oracle.  All rights reserved.

Last Successful login time: Fri Mar 29 2019 19:09:33 +09:00

Connected to:
Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production
Version 18.3.0.0.0

POSSESSOR@pdb1>
POSSESSOR@pdb1> --possessor session
POSSESSOR@pdb1> drop function func___current_user_dual_execute_immediate___;
drop function func___current_user_dual___;
drop function func___current_user_except_dual_execute_immediate___;

Function dropped.

Elapsed: 00:00:00.04
POSSESSOR@pdb1>
Function dropped.

Elapsed: 00:00:00.01
POSSESSOR@pdb1> drop function func___current_user_except_dual___;

Function dropped.

Elapsed: 00:00:00.01
POSSESSOR@pdb1> drop function func___definer_dual_execute_immediate___;

Function dropped.

Elapsed: 00:00:00.01
POSSESSOR@pdb1>
Function dropped.

Elapsed: 00:00:00.00
POSSESSOR@pdb1> drop function func___definer_dual___;

Function dropped.

Elapsed: 00:00:00.00
POSSESSOR@pdb1> drop function func___definer_except_dual_execute_immediate___;

Function dropped.

Elapsed: 00:00:00.00
POSSESSOR@pdb1> drop function func___definer_except_dual___;

Function dropped.

Elapsed: 00:00:00.00
POSSESSOR@pdb1>
POSSESSOR@pdb1> create or replace function func___current_user_dual_execute_immediate___ return varchar2
  2  authid current_user
  3  as
  4      rt varchar2(30);
  5  begin
  6  execute immediate 'select' || ''' hello world ''' || 'from dual' into rt;
  7      return rt;
  8  end;
  9  /
create or replace function func___current_user_dual___ return varchar2

Function created.

Elapsed: 00:00:00.03
POSSESSOR@pdb1>   2  authid current_user
  3  as
  4      rt varchar2(30);
  5  begin
  6  select 'hello world' into rt from dual;
  7      return rt;
  8  end;
  9  /
create or replace function func___current_user_except_dual_execute_immediate___ return varchar2

Function created.

Elapsed: 00:00:00.02
POSSESSOR@pdb1>   2  authid current_user
  3  as
  4      rt varchar2(30);
  5  begin
  6  execute immediate 'select rn from sys.tbl___privilege___' into rt;
  7      return rt;
  8  end;
  9  /

Function created.

Elapsed: 00:00:00.01
POSSESSOR@pdb1> create or replace function func___current_user_except_dual___ return varchar2
  2  authid current_user
  3  as
  4      rt varchar2(30);
  5  begin
  6      select rn into rt
  7      from sys.tbl___privilege___;
  8      return rt;
  9  end;
 10  /

Warning: Function created with compilation errors.

Elapsed: 00:00:00.02
POSSESSOR@pdb1> create or replace function func___definer_dual_execute_immediate___ return varchar2
  2  authid definer
  3  as
  4      rt varchar2(30);
  5  begin
  6  execute immediate 'select' || ''' hello world ''' || 'from dual' into rt;
  7      return rt;
  8  end;
  9  /

Function created.

Elapsed: 00:00:00.01
POSSESSOR@pdb1> create or replace function func___definer_dual___ return varchar2
  2  authid definer
  3  as
  4      rt varchar2(30);
  5  begin
  6  select 'hello world' into rt from dual;
  7      return rt;
  8  end;
  9  /

Function created.

Elapsed: 00:00:00.02
POSSESSOR@pdb1> create or replace function func___definer_except_dual_execute_immediate___ return varchar2
  2  authid definer
  3  as
  4      rt varchar2(30);
  5  begin
  6  execute immediate 'select rn from sys.tbl___privilege___' into rt;
  7      return rt;
  8  end;
  9  /

Function created.

Elapsed: 00:00:00.02
POSSESSOR@pdb1> create or replace function func___definer_except_dual___ return varchar2
  2  authid definer
  3  as
  4      rt varchar2(30);
  5  begin
  6      select rn into rt
  7      from sys.tbl___privilege___;
  8      return rt;
  9  end;
 10  /

Warning: Function created with compilation errors.

Elapsed: 00:00:00.02
POSSESSOR@pdb1> select authid,object_name from user_procedures order by authid,object_name;

AUTHID       OBJECT_NAME
------------ --------------------------------------------------------------------------------------------------------------------------------
CURRENT_USER FUNC___CURRENT_USER_DUAL_EXECUTE_IMMEDIATE___
CURRENT_USER FUNC___CURRENT_USER_DUAL___
CURRENT_USER FUNC___CURRENT_USER_EXCEPT_DUAL_EXECUTE_IMMEDIATE___
DEFINER      FUNC___DEFINER_DUAL_EXECUTE_IMMEDIATE___
DEFINER      FUNC___DEFINER_DUAL___
DEFINER      FUNC___DEFINER_EXCEPT_DUAL_EXECUTE_IMMEDIATE___

6 rows selected.

Elapsed: 00:00:00.04

[oracle@f285aba0589a ~]$ sqlplus invoker/test@pdb1

SQL*Plus: Release 18.0.0.0.0 - Production on Fri Mar 29 19:19:14 2019
Version 18.3.0.0.0

Copyright (c) 1982, 2018, Oracle.  All rights reserved.

Last Successful login time: Fri Mar 29 2019 19:17:42 +09:00

Connected to:
Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production
Version 18.3.0.0.0

INVOKER@pdb1>
INVOKER@pdb1>
INVOKER@pdb1> col rt for a30
INVOKER@pdb1> select possessor.FUNC___CURRENT_USER_DUAL_EXECUTE_IMMEDIATE___ as rt from dual;

RT
------------------------------
 hello world

1 row selected.

Elapsed: 00:00:00.01
INVOKER@pdb1> select possessor.FUNC___CURRENT_USER_DUAL___ as rt from dual;

RT
------------------------------
hello world

1 row selected.

Elapsed: 00:00:00.00
INVOKER@pdb1> select possessor.FUNC___CURRENT_USER_EXCEPT_DUAL_EXECUTE_IMMEDIATE___ as rt from dual;

RT
------------------------------
1

1 row selected.

Elapsed: 00:00:00.01
INVOKER@pdb1> select possessor.FUNC___CURRENT_USER_EXCEPT_DUAL___ as rt from dual;
select possessor.FUNC___CURRENT_USER_EXCEPT_DUAL___ as rt from dual
                 *
ERROR at line 1:
ORA-06575: Package or function FUNC___CURRENT_USER_EXCEPT_DUAL___ is in an invalid state


Elapsed: 00:00:00.01
INVOKER@pdb1> select possessor.FUNC___DEFINER_DUAL_EXECUTE_IMMEDIATE___ as rt from dual;

RT
------------------------------
 hello world

1 row selected.

Elapsed: 00:00:00.00
INVOKER@pdb1> select possessor.FUNC___DEFINER_DUAL___ as rt from dual;

RT
------------------------------
hello world

1 row selected.

Elapsed: 00:00:00.01
INVOKER@pdb1> select possessor.FUNC___DEFINER_EXCEPT_DUAL_EXECUTE_IMMEDIATE___ as rt from dual;
select possessor.FUNC___DEFINER_EXCEPT_DUAL_EXECUTE_IMMEDIATE___ as rt from dual
       *
ERROR at line 1:
ORA-00942: table or view does not exist
ORA-06512: at "POSSESSOR.FUNC___DEFINER_EXCEPT_DUAL_EXECUTE_IMMEDIATE___", line 6


Elapsed: 00:00:00.01
INVOKER@pdb1> select possessor.FUNC___DEFINER_EXCEPT_DUAL___ as rt from dual;

select possessor.FUNC___DEFINER_EXCEPT_DUAL___ as rt from dual
                 *
ERROR at line 1:
ORA-06575: Package or function FUNC___DEFINER_EXCEPT_DUAL___ is in an invalid state


Elapsed: 00:00:00.01

結果

たぶんこんな感じ

考察

パターン3はロール経由権限はコンパイル時無効となるオラクルの仕様の為、コンパイルができない。しかし、コンパイル時に直接権限があれば、コンパイルは可能となり、実行時に直接権限を有していれば実行可能となるため、△。ロール経由権限が今回の場合、存在していないので、実行できなかったが、実行者権限ではロール経由権限が有効になるので、実行できる。よって△。パターン4は直接権限を有していれば、実行できたので、△。パターン7はパターン3とほぼ同じだが、所有者権限の場合、実行時、ロール経由権限が無効となるので、直接権限を与えるほか実行の術はない。パターン8も所有者権限の場合で、実行時、ロール経由権限が無効となるので、直接権限を与えるほか実行の術はない。

まとめ

ハンディなのはパターン2と4かなー。実行者権限にして、即時実行すれば、コンパイルエラーも回避できて、権限もロール管理できるから。実行者権限プログラムであれば、ログイン時に権限チェックが済んでいるので、プログラム実行時に所有者権限プログラムのように毎回権限チェックしなくて済む。パフォーマンスUPするぽい。スカラ値返すだけのファンクションとかも基本は実行者権限でいい気がする。

権限の弱いユーザーが作成した実行者権限ストアドプログラムを権限の強いユーザーが実行すると、実行するユーザーのすべての権限を一時的に権限の弱いユーザーが継承するそうw。ということは弱い権限のユーザーが一時的に最強の権限になるので、その強い権限のまま、もともと弱い権限であったはずのユーザーが実行者権限ストアドプログラムをコールしたりするとよからぬことが起こるリスクが発生する。ということで、INHERIT PRIVILEGES権限なるものができた。これはもともと権限の弱かったユーザーが強い権限になすまして、本来アクセスできないはずのオブジェクトに対して処理をしようとしたときに、権限チェックするもの。実行者権限ストアドプログラムの所有者にこのINHERIT PRIVILEGES権限を付与しておくと、信頼できる人というお墨付きをもらえるみたいな仮札みたいな感じかな。職権乱用はしなさそうだから、権限強いユーザーによって自分の実行者権限プログラムの実行をさせてもいいよね見たいな感覚。自分の強い権限を与える代償に。。とんち効いている。。。

ストアドプログラムを所有しているユーザの権限を持って実行するのが、所有者権限実行プログラム。ストアドプログラムを実行するユーザの権限を持って実行するのが、実行者権限実行プログラム。ストアド作成時はデフォで定義者権限(所有者権限)で実行される。

たとえば、dual表以外のテーブルを参照するような所有者権限ストアドプログラムと実行者権限ストアドプログラムがあったとする。ともにテーブルの値を取得して何かしらの処理をおこなうプログラム。ストアドプログラムを実行するユーザに対して、自スキーマのテーブルへのオブジェクト権限を付与しない限りは、実行者権限ストアドプログラムは実行されることはない。ストアドプログラム実行者へ自分の持ち物であるオブジェクトを不必要に公開されることがない。データセキュリティの側面でのメリット。所有者権限プログラムに関しては、所有者が自スキーマのテーブルに対するオブジェクト権限を保持していれば、プログラムは実行できる。ユーザはストアドプログラムを通してでしか、他人の持ち物であるオブジェクトにアクセスする術がない。入力値が限定さているみたいな感覚かな。干渉されたくない感じが伝わる。所有者がストアドプログラムに記載されているオブジェクトへのオブジェクト権限を保有しているかどうかストアドプログラムが実行されるたびに確認する。所有者権限はパフォーマンスの面でチェック処理時間が懸念される。dual表ならその心配はない。オブジェクト管理されていないから。

メモ

セッションしくったら、これでいい感じにする。

コード表示

select sid, serial#, username, program from v$session where username = 'POSSESSOR';
select sid, serial#, username, program from v$session where username = 'INVOKER';

alter system kill session 'sid, serial#';

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

まえがき

私は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___;