[Real MySQL] 14-2. 스토어드 프로그램: 참고사항

백은빈, 이성욱 님의 "Real MySQL" 책을 정리한 포스팅 입니다.

3. 보안 옵션

DEFINIER / SQL SECURITY

• 각 스토어드 프로그램을 생성하고 실행하는 권한

 

DEFINIER

CREATE DEFINER='admin'@'%' PROCEDURE sp_test()
BEGIN
    SELECT 'Hello, world!';
END;

• 소유권과 같은 의미
• 기본적으로 설정됨

 

SQL SECURITY

CREATE DEFINER='admin'@'%' PROCEDURE sp_test()
SQL SECURITY DEFINER
BEGIN
    SELECT * FROM sensitive_data;
END;

• 스토어드 프로그램을 실행할 때 누구의 권한으로 실행할지 결정하는 옵션
  • DEFINIER: 생성한 사용자의 권한으로 실행
  • INVOKER: 호출한 사용자의 권한으로 실행
• 사용 예
  • 보안에 민감한 정보가 있는 테이블의 일부 컬럼만 접근 및 작업 허용하기
    • 호출한 사용자는 해당 테이블의 권한이 없어도 작업 가능

 

DETERMINISTIC / NOT DETERMINISTIC

• 성능과 관련된 옵션

 

DETERMINISTIC

• 스토어드 프로그램의 입력이 같다면 시점이나 상황에 상관없이 결과가 항상 같음
• 스토어드 프로그램의 결과값을 상수화

 

NOT DETERMINISTIC

• 스토어드 프로그램의 입력이 같더라도 시점이나 상황에 따라 결과가 달라질 수 있음
  • 호출되는 시점에 따라 결과는 달라질 수 있음
• 스토어드 프로그램의 결과값을 상수화하지 않고, 호출되는 시점마다 재평가함
  • 풀 테이블 스캔을 기본으로 설정함
• 명시하지 않으면 기본적으로 적용됨

 

4. 참고 및 주의사항

한글 처리

• 스토어드 프로그램을 생성하는 클라이언트 프로그램이 어떤 문자집합으로 MySQL 서버에 접속되어 있는지가 중요함

해결책

CHARSET utfmb4; -- 명령어로 관련 시스템 변수를 셋팅할 수 있음

• 클라이언트 문자집합 시스템 변수 값이 한글을 지원하는 문자 집합으로 사용하기
  • character_set_connection, character_set_client, character_set_result

 

create function sf_getstring()
  return varachar(20) charater set utf8mb4
begin
  return '한글 테스트'
end;;

• 스토어드 프로그램에서 값을 넘겨받을 때도 별도로 캐릭터셋 지정하기

 

세션 변수

사용자 변수 사용

create function sf_getsum(p_arg1 INT, p_arg2 INT)
  returns int
begin
  declare v_sum int default 0;
  set v_sum = p_arg1 + p_arg2;
  set @v_sum = v_sum;
  
  return v_sum;
end;;

• '@'
• 타입 지정 X
• 커넥션에서는 그 값을 유지함

 

재귀 호출

create procedure sp_get_factorial(IN p_max INT, OUT p_sum INT)
begin
  set max_sp_recursion_depth=50;
  set p_sum=1;
  
  if p_max > 1 then call sp_decreasemultiply(p_max, p_sum);
  end if;
end;;

• 스토어드 프로시저에만 사용 가능
• max_sp_recursion_depth: 재귀호출 횟수 제한

 

중첩된 커서 사용

create procedure sp_updateemployeehiredate()
begin
  declare v_dept_no char(4);
  declare v_emp_no INT;
  declare v_no_more_rows BOOLEAN DEFAULT FALSE;
  declare v_dept_list CURSOR FOR SELECT dept_no FROM departments;
  declare v_emp_list CURSOR FOR SELECT emp_no FROM dept_emp WHERE dept_no=v_dept_no LIMIT 1;
  
  ...
  
  OPEN v_dept_list;
  LOOP_OUTER: LOOP
    FETCH v_dept_list INTO v_dept_no;
    IF v_no_more_rows THEN
      CLOSE v_dept_list;
      LEAVE: LOOP_OUTER;
    END IF;
    
    OPEN v_emp_list;
    LOOP_INNER: LOOP
      FETCH v_emp_list INTO v_emp_no;
      IF v_no_more_rows THEN
        SET v_no_more_rows := FALSE:
        CLOSE v_emp_list;
        LEAVE loop_inner;
      END IF;
    END LOOP loop_inner;
  END LOOP loop_outer;  
end;;

• 중첩된 루프 안에서 두 개의 커서를 동시에 열어 사용해야 할 수 있음