클라우드 데이터베이스 Oracle 12c 가이드 (2016년)
오라클 데이터베이스 12c PL/SQL의 새로운 기능 0 0 12,666

by 구루비스터디 12c PL/SQL [2023.09.03]


뷰에서 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
"구루비 데이터베이스 스터디모임" 에서 2016년에 "클라우드 데이터베이스 Oracle 12c 가이드" 도서를 스터디하면서 정리한 내용 입니다.

- 강좌 URL : http://www.gurubee.net/lecture/4259

- 구루비 강좌는 개인의 학습용으로만 사용 할 수 있으며, 다른 웹 페이지에 게재할 경우에는 출처를 꼭 밝혀 주시면 고맙겠습니다.~^^

- 구루비 강좌는 서비스 제공을 위한 목적이나, 학원 홍보, 수익을 얻기 위한 용도로 사용 할 수 없습니다.

댓글등록
SQL문을 포맷에 맞게(깔끔하게) 등록하려면 code() 버튼을 클릭하여 작성 하시면 됩니다.
로그인 사용자만 댓글을 작성 할 수 있습니다. 로그인, 회원가입