뷰에서 Bequeath 절 사용

• BEQUEATH CURRENT_USER : 해당 뷰를 호출한(사용하는) 계정의 권한으로 뷰를 실행
• BEQUEATH DEFINER : 해당 뷰를 생성한 계정의 권한으로 뷰를 실행

PL/SQL 서브프로그램이 허가된 리스트

create or replace function helper (v_input number)
return number
accessible by (api) is    -- 해당 FUNCTION은 API라는 PL/SQL에서만 호출가능
begin
    return v_input + 1;
end;


JIGI@2016-11-22 11:32:45> select helper(1) from dual;
select helper(1) from dual
       *
ERROR at line 1:
ORA-06552: PL/SQL: Statement ignored
ORA-06553: PLS-904: insufficient privilege to access object HELPER



create or replace function api (v_input number)
return number
is
    v_return number;
begin
    v_return := helper(v_input);
    return v_return;
end;



JIGI@2016-11-22 11:33:08> select api(1) from dual;

    API(1)
----------
         2

JIGI@2016-11-22 11:35:54>

PL/SQL에서 SQL 인터페이스를 통한 PL/SQL 지정 데이터 타입 사용

• 11g까지는 PL/SQL 전용 데이터타입(boolean, 연관배열, record 타입 등)을 바인딩 할 수 없었으나, 12c에서 사용가능해짐
• 일부 제한사항이 존재함
• PL/SQL 전용데이터 타입이 미리 정의되어 있거나 PACKAGE SPEC에 선언되어 있어야 함
• 연관배열을 사용시 배열은 PLS_INTEGER 데이터 타입에 인덱스화가 되어 있어야함
• PL/SQL 함수 사용 시 PL/SQL 전용 데이터 타입은 SQL로 리턴되지 못함
• BOOLEAN 값은 STATICS SQL 쿼리에서 호출되는 PL/SQL 함수의 인수가 되지 못함
• SQL 문장 내에서는 PACKAGE SPEC에 선언되는 RETURN 타입을 가진 함수는 사용 할 수 없음

CREATE OR REPLACE PROCEDURE ANSWER_ME (P_R BOOLEAN)
IS
BEGIN
    IF P_R THEN
        DBMS_OUTPUT.PUT_LINE('ANSWER IS TRUE');
    ELSE
        DBMS_OUTPUT.PUT_LINE('ANSWER IS FALSE');
    END IF;
END;


SET SERVEROUTPUT ON
DECLARE
    V_SQL VARCHAR2(1000);
    V_BOOLEAN BOOLEAN := TRUE;
BEGIN
    V_SQL := 'BEGIN ANSWER_ME(:X); END;';
    EXECUTE IMMEDIATE V_SQL USING V_BOOLEAN;
END;


ANSWER IS TRUE
PL/SQL procedure successfully completed.

PL/SQL 프로그램 단위에서 호출자 권한의 보안 권한 변경

• PL/SQL 프로그램에서 프로시저를 생성 시 호출자 권한을 정의하려는 경우, PL/SQL 프로그램 유닛의 소유자가 INHERIT ANY PRIVILEGE나 INHERIT PRIVILEGE 권한을 가져야 함.
• 12c에서는 호환성을 위해 기본적으로 grant inherit any privileges to public가 모든 계정에 적용되어 있다.

grant inherit privileges on user scott to public;
grant inherit privileges on user to tiger;
grant inherit any privileges to tiger;

PL/SQL 패키지와 독립 서브프로그램에 롤 부여

-- 테이블 생성
CREATE TABLE need_privs (id NUMBER);

INSERT INTO need_privs VALUES (1);

COMMIT;

-- function 생성
CREATE OR REPLACE FUNCTION get_new_value(v_input IN NUMBER)
    RETURN NUMBER
--    AUTHID definer           -- 해당 function을 만든 사용자 권한으로 실행한다는 의미
    AUTHID current_user    -- 해당 function을 호출자(실행자) 권한으로 실행한다는 의미
AS
    v_number   NUMBER := 0;
BEGIN
    SELECT id + v_input INTO v_number FROM scott.need_privs;

    RETURN v_number;
END;


SCOTT@2016-11-22 14:51:11> select get_new_value(1) from dual;

GET_NEW_VALUE(1)
----------------
               2


-- 실행 권한 부여
SCOTT@2016-11-22 15:01:35> grant execute on get_new_value to hr;

Grant succeeded.

SCOTT@2016-11-22 15:01:45> conn hr/hr
Connected.

-- 권한이 없어서 오류발생
HR@2016-11-22 15:01:48> select scott.get_new_value(1) from dual;
select scott.get_new_value(1) from dual
       *
ERROR at line 1:
ORA-00942: table or view does not exist
ORA-06512: at "SCOTT.GET_NEW_VALUE", line 7


-- Role을 만들어본다.
HR@2016-11-22 15:02:04> conn scott/tiger
Connected.
SCOTT@2016-11-22 15:05:56> create role run_get_new_value;
create role run_get_new_value
*
ERROR at line 1:
ORA-01031: insufficient privileges


SCOTT@2016-11-22 15:06:11> conn /as sysdba
Connected.
SYS@2016-11-22 15:06:42> grant create role to scott;

Grant succeeded.

SYS@2016-11-22 15:07:14> conn scott/tiger
Connected.
SCOTT@2016-11-22 15:07:19> create role run_get_new_value;

Role created.

SCOTT@2016-11-22 15:07:32> grant select on need_privs to run_get_new_value;

Grant succeeded.

-- 12c에 새로생긴 기능
SCOTT@2016-11-22 15:07:57> grant run_get_new_value to function get_new_value;

Grant succeeded.


SCOTT@2016-11-22 15:08:25> conn hr/hr
Connected.

-- 결과가 잘 나옴
HR@2016-11-22 15:10:20> select scott.get_new_value(1) from dual;

SCOTT.GET_NEW_VALUE(1)
----------------------
                     2

HR@2016-11-22 15:10:27>

PL/SQL의 기타 새로운 기능

• function에 result_cache 절 지원 : Result Cache가 가능해짐
• Pluggable 데이터베이스 트리거 지원
• before unplug : 데이터베이스가 언플러그 되기 전에 실행됨
• after clone : PDB가 복제되고 나서 실행됨, 이후 트리거는 제거됨.
• 디버깅을 위한 PL/SQL 지시어 추가

-- 디버깅을 위한 새로운 파라미터 제공
CREATE OR REPLACE procedure test_procedure
is
begin
   dbms_output.put_line('$$plsql_unit_owner : ' || $$plsql_unit_owner);
   dbms_output.put_line('$$plsql_unit_type : ' || $$plsql_unit_type);
end test_procedure;
/

HR@2016-11-22 15:39:00> exec test_procedure;
$$plsql_unit_owner : HR
$$plsql_unit_type : PROCEDURE