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

実行者権限(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#';

Leave a Reply

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