호출자 권한 및 소유자 권한 저장 프로시저 이해하기

저장 프로시저는 호출자 의 권한 또는 소유자 의 권한 중 하나로 실행됩니다. 동시에 둘 다로 실행할 수 없습니다. 이 항목에서는 호출자 권한 저장 프로시저와 소유자 권한 저장 프로시저 간의 차이점에 대해 설명합니다.

이 항목의 내용:

소개

호출자 권한 저장 프로시저는 호출자의 권한으로 실행됩니다. 호출자 권한 저장 프로시저의 주요 이점은 호출자 또는 호출자의 현재 세션에 대한 정보에 액세스할 수 있다는 점입니다. 예를 들어, 호출자의 권한 저장 프로시저는 호출자의 세션 변수를 읽고 쿼리에서 사용할 수 있습니다.

소유자 권한 저장 프로시저는 주로 저장 프로시저 소유자의 권한으로 실행됩니다. 소유자 권한 저장 프로시저의 주요 이점은 소유자가 특정 테이블에서 모든 데이터를 삭제하는 권한과 같은 보다 일반적인 권한을 해당 역할에 부여하지 않고도 이전 데이터 정리와 같은 특정한 관리 작업을 다른 역할에 위임할 수 있다는 점입니다.

저장 프로시저 생성 시, 생성자는 소유자 권한 또는 호출자 권한으로 프로시저를 실행할지 여부를 지정합니다. 기본값은 소유자 권한입니다.

소유자는 ALTER PROCEDURE 명령을 실행하여 소유자 권한 저장 프로시저에서 호출자 권한 저장 프로시저로(또는 그 반대로) 프로시저를 변경할 수 있습니다.

데이터베이스 오브젝트에 대한 권한

호출자 권한 저장 프로시저는 저장 프로시저를 호출한 역할의 데이터베이스 권한으로 실행됩니다. 호출자가 저장 프로시저 외부에서 실행할 수 없는 문은 저장 프로시저 내부에서도 실행할 수 없습니다. 예를 들어, 《Nurse》로 명명된 역할에 medical_records 테이블에서 행을 삭제할 권한이 없는 경우에는 《Nurse》 역할을 가진 사용자가 해당 테이블에서 행을 삭제하려고 시도하는 호출자 권한 저장 프로시저를 호출하면 저장 프로시저가 실패합니다.

소유자 권한 프로시저는 프로시저 소유자의 권한으로 실행됩니다. 이는 소유자가 작업을 수행할 권한이 있는 경우에는 해당 작업을 직접 수행할 권한이 없는 역할이 호출한 저장 프로시저라도 해당 작업을 수행할 수 있다는 뜻입니다. 예를 들어, 《Doctor》로 명명된 역할에 medical_records 테이블에서 행을 삭제할 데이터베이스 권한이 있고 《Doctor》 역할이 해당 테이블에서 7년보다 오래된 행을 삭제하는 저장 프로시저를 만드는 경우에는 《Doctor》 역할이 《Nurse》 역할에 저장 프로시저에 대한 적절한 권한을 부여하면 《Nurse》 역할이 테이블에 대한 삭제 권한이 없더라도 저장 프로시저를 실행하고 그 저장 프로시저를 통해 테이블에서 이전 행을 삭제할 수 있습니다.

세션 상태 액세스 및 설정하기

다른 SQL 문과 마찬가지로, CALL 문은 세션 내에서 실행되고 세션 수준 변수, 현재 데이터베이스 등, 해당 세션의 컨텍스트를 상속합니다. 프로시저가 상속하는 정확한 컨텍스트는 저장 프로시저가 호출자 권한 저장 프로시저인지, 소유자 권한 저장 프로시저인지 여부에 따라 다릅니다.

호출자 권한 저장 프로시저가 세션을 변경할 경우 해당 변경 사항은 CALL 종료 이후에 지속될 수 있습니다. 소유자 권한 저장 프로시저는 세션 상태를 변경할 수 없습니다.

호출자 권한 저장 프로시저

호출자 권한 저장 프로시저는 세션 내에서 다음 규칙을 따릅니다.

  • 소유자의 권한이 아닌 호출자의 권한으로 실행됩니다.

  • 호출자의 현재 웨어하우스를 상속합니다.

  • 호출자가 현재 사용 중인 데이터베이스와 스키마를 사용합니다.

  • 호출자의 세션 변수를 보고 설정하고 설정 해제할 수 있습니다.

  • 호출자의 세션 매개 변수를 보고 설정하고 설정 해제할 수 있습니다.

아래 섹션에서는 호출자 권한 저장 프로시저가 호출자의 세션 수준 변수를 읽고 쓰는 방법에 대해 자세히 설명합니다.

세션 변수

MyProcedure 로 명명된 저장 프로시저가 세션 수준 변수를 읽고 설정하는 SQL 문을 실행한다고 가정합니다. 이 예에서 read 및 set 명령의 세부 사항은 중요하지 않으므로, 문은 의사 코드로 표시됩니다.

  • READ SESSION_VAR1

  • SET SESSION_VAR2

저장 프로시저는 다음 의사 코드와 비슷한 형태입니다.

CREATE PROCEDURE MyProcedure()
...
$$
   READ SESSION_VAR1;
   SET SESSION_VAR2;
$$
;

같은 세션에서 다음과 같은 문 시퀀스를 실행한다고 가정합니다.

SET SESSION_VAR1 = 'some interesting value';
CALL MyProcedure();
SELECT *
    FROM table1
    WHERE column1 = $SESSION_VAR2;

이것은 다음 시퀀스를 실행하는 것과 같습니다.

SET SESSION_VAR1 = 'some interesting value';
READ SESSION_VAR1;
SET SESSION_VAR2;
SELECT *
    FROM table1
    WHERE column1 = $SESSION_VAR2;

즉:

  • 저장 프로시저는 프로시저가 호출되기 전에 문에 의해 설정된 변수를 볼 수 있습니다.

  • 저장 프로시저 뒤의 문은 프로시저 내부에서 설정된 변수를 볼 수 있습니다.

의사 코드에 의존하지 않는 완전한 예는 (이 항목에 있는) 호출자 권한 및 소유자 권한 저장 프로시저에서 세션 변수 사용하기 를 참조하십시오.

많은 저장 프로시저에서 현재 데이터베이스 및 현재 세션 수준 변수와 같은 컨텍스트 정보를 상속하려고 합니다.

하지만 어떤 경우에는 저장 프로시저를 더욱 확실히 분리하고 싶을 수도 있습니다. 예를 들어, 저장 프로시저가 세션 수준 변수를 설정하는 경우 세션 수준 변수가 저장 프로시저 외부의 향후 문에 영향을 주지 않기를 바랄 수도 있습니다.

세션의 나머지 부분에서 저장 프로시저를 더 잘 분리하려면 다음과 같이 하십시오.

  • 세션 수준 변수를 직접 사용하지 마십시오. 대신에 이런 변수를 명시적 매개 변수로 전달하십시오. 그러면 호출자가 저장 프로시저가 정확히 어떤 세션 수준 변수를 사용할지 생각할 수밖에 없게 됩니다.

  • 저장 프로시저 내부에 설정한 세션 수준 변수를 모두 정리하고, 다른 곳에서는 사용하지 않을 이름을 사용해 저장 프로시저 호출 이전부터 존재했던 세션 변수를 실수로 정리하지 않도록 합니다.

다음 저장 프로시저는 세션 변수를 직접 사용하는 게 아니라 매개 변수로 수신하여 세션 변수의 값을 사용합니다.

SET Variable_1 = 49;
CREATE PROCEDURE sv_proc2(PARAMETER_1 FLOAT)
    RETURNS VARCHAR
    LANGUAGE JAVASCRIPT
    AS
    $$
        var rs = snowflake.execute( {sqlText: "SELECT 2 * " + PARAMETER_1} );
        rs.next();
        var MyString = rs.getColumnValue(1);
        return MyString;
    $$
    ;
  CALL sv_proc2($Variable_1);

다음 저장 프로시저는 이름이 특이한 임시 세션 변수를 만들고 저장 프로시저가 완료되기 전에 그 변수를 정리합니다. 다음과 같이 프로시저 호출 이후의 문이 정리된 세션 변수를 사용하려고 하면 그 문이 실패합니다.

CREATE PROCEDURE sv_proc1()
    RETURNS VARCHAR
    LANGUAGE JAVASCRIPT
    EXECUTE AS CALLER
    AS
    $$
        var rs = snowflake.execute( {sqlText: "SET SESSION_VAR_ZYXW = 51"} );

        var rs = snowflake.execute( {sqlText: "SELECT 2 * $SESSION_VAR_ZYXW"} );
        rs.next();
        var MyString = rs.getColumnValue(1);

        rs = snowflake.execute( {sqlText: "UNSET SESSION_VAR_ZYXW"} );

        return MyString;
    $$
    ;

CALL sv_proc1();
-- This fails because SESSION_VAR_ZYXW is no longer defined.
SELECT $SESSION_VAR_ZYXW;

참고

C 언어(또는 Java와 같은 유사 언어)로 프로그래밍하는 경우, 저장 프로시저 내에서 설정하는 세션 변수는 C 함수가 실행을 마칠 때 사라지는 C의 로컬 변수와는 다릅니다. SQL에서 저장 프로시저를 환경에서 분리하려면 C에서 분리할 때보다 더 많은 노력이 필요합니다.

소유자 권한 저장 프로시저

소유자 권한 저장 프로시저는 세션 내에서 다음 규칙을 따릅니다.

  • 호출자의 권한이 아닌 소유자의 권한으로 실행됩니다.

  • 호출자의 현재 웨어하우스를 상속합니다.

  • 호출자가 현재 사용 중인 데이터베이스와 스키마가 아니라, 저장 프로시저가 생성된 데이터베이스와 스키마를 사용합니다.

  • 대부분의 호출자 관련 정보에 액세스할 수 없습니다. 예:

    • 호출자의 세션 변수를 보거나 설정하거나 설정 해제할 수 없습니다.

    • /sql-reference/sql/show-parameters 를 실행하여 매개 변수를 나열할 수 없습니다.

    • 호출자가 설정한 세션 매개 변수의 하위 세트만 사용할 수 있습니다. 예를 들어, 날짜 값을 출력하는 SQL 명령은 호출자의 세션에 대해 설정된 DATE_OUTPUT_FORMAT 매개 변수를 사용할 수 있습니다.

      이러한 매개 변수의 목록은 호출자의 세션 매개 변수가 소유자의 권한 프로시저에 미치는 영향 이해하기 섹션을 참조하십시오.

    • 호출자의 세션 매개 변수를 설정하거나 설정 해제할 수 없습니다.

    • 현재 사용자를 기반으로 결과를 반환하는 AUTOMATIC_CLUSTERING_HISTORY 와 같은 INFORMATION_SCHEMA 테이블 함수를 쿼리할 수 없습니다.

  • 소유자가 아닌 사용자가 PROCEDURES 뷰에서 프로시저에 대한 정보를 볼 수 있도록 허용하지 마십시오.

세션 변수와 세션 매개 변수에 대한 제한 사항은 아래에 더 자세히 설명되어 있습니다.

세션 변수

저장 프로시저는 저장 프로시저 외부에서 만든 SQL 변수 에 대한 액세스 권한이 없습니다. 이러한 제한으로 인해 한 사용자가 작성하거나 소유한 저장 프로시저가 다른 사용자(저장 프로시저 호출자)가 만든 SQL 변수를 읽지 못합니다.

저장 프로시저에 현재 세션의 SQL 변수에 저장된 값이 필요한 경우 그와 같은 변수의 값을 명시적 인자로 저장 프로시저에 전달해야 합니다. 예:

SET PROVINCE = 'Manitoba';
CALL MyProcedure($PROVINCE);

호출자의 세션 매개 변수가 소유자의 권한 프로시저에 미치는 영향 이해하기

참고

이 섹션은 SHOW PARAMETERS 명령에 적용되지 않습니다. SHOW PARAMETERS 명령은 소유자의 권한 저장 프로시저에서 허용되지 않습니다.

세션 매개 변수 의 값은 명령과 함수의 동작에 영향을 줄 수 있습니다. 예를 들어, 날짜 값을 출력하는 명령은 DATE_OUTPUT_FORMAT 세션 매개 변수로 지정된 형식을 사용합니다.

호출자의 세션에서, 호출자는 세션 매개 변수를 설정하거나 재정의할 수 있습니다. 호출자의 권한 저장 프로시저에서 세션 매개 변수는 프로시저 내에서 실행되는 쿼리와 식의 실행에 영향을 줄 수 있습니다. 예를 들어, TIMESTAMP_OUTPUT_FORMAT 매개 변수는 select current_timestamp::string 과 같은 하위 쿼리의 출력 형식에 영향을 줍니다.

하지만 소유자의 권한 저장 프로시저의 경우 호출자 세션의 값이 오직 다음 매개 변수에만 사용됩니다.

  • AUTOCOMMIT

  • BINARY_INPUT_FORMAT

  • BINARY_OUTPUT_FORMAT

  • DATE_INPUT_FORMAT

  • DATE_OUTPUT_FORMAT

  • ENABLE_UNLOAD_PHYSICAL_TYPE_OPTIMIZATION

  • ERROR_ON_NONDETERMINISTIC_MERGE

  • ERROR_ON_NONDETERMINISTIC_UPDATE

  • JDBC_TREAT_DECIMAL_AS_INT

  • JSON_INDENT

  • LOCK_TIMEOUT

  • MAX_CONCURRENCY_LEVEL

  • ODBC_USE_CUSTOM_SQL_DATA_TYPES

  • PERIODIC_DATA_REKEYING

  • QUERY_TAG

  • QUERY_WAREHOUSE_NAME

  • ROWS_PER_RESULTSET

  • STATEMENT_QUEUED_TIMEOUT_IN_SECONDS

  • STATEMENT_TIMEOUT_IN_SECONDS

  • STRICT_JSON_OUTPUT

  • TIMESTAMP_DAY_IS_ALWAYS_24H

  • TIMESTAMP_INPUT_FORMAT

  • TIMESTAMP_LTZ_OUTPUT_FORMAT

  • TIMESTAMP_NTZ_OUTPUT_FORMAT

  • TIMESTAMP_OUTPUT_FORMAT

  • TIMESTAMP_TYPE_MAPPING

  • TIMESTAMP_TZ_OUTPUT_FORMAT

  • TIMEZONE

  • TIME_INPUT_FORMAT

  • TIME_OUTPUT_FORMAT

  • TRANSACTION_ABORT_ON_ERROR

  • TRANSACTION_DEFAULT_ISOLATION_LEVEL

  • TWO_DIGIT_CENTURY_START

  • UNSUPPORTED_DDL_ACTION

  • USE_CACHED_RESULT

  • WEEK_OF_YEAR_POLICY

  • WEEK_START

참고

이 목록은 시간의 경과에 따라 변경될 수 있습니다.

(위에 나열되지 않은) 다른 매개 변수의 경우:

  • 소유자의 계정 수준 매개 변수 값이 사용됩니다.

  • 계정 수준 매개 변수가 소유자의 계정에 대해 설정되지 않은 경우에는 계정 매개 변수의 기본값이 사용됩니다.

이 제한은 소유자의 권한 저장 프로시저가 호출자의 세션 매개 변수를 사용한 경우에 발생할 수 있는 잠재적 문제를 예방하기 위한 조치입니다. 예:

  • 저장 프로시저의 작성자(소유자)가 특정 세션 매개 변수를 설정했지만 저장 프로시저의 호출자가 그 매개 변수를 설정하지 않은 경우, 작성자 이외의 사용자가 호출할 때 저장 프로시저가 실패하거나 다르게 동작할 수 있습니다.

  • 저장 프로시저가 호출자가 설정한 세션 매개 변수의 값을 사용할 수 있는 경우 저장 프로시저의 소유자가 호출자 모르게 해당 값을 결정할 수 있습니다.

소유자 권한 저장 프로시저에 대한 추가 제한 사항

소유자 권한 저장 프로시저는 세션 변수 및 세션 매개 변수와 관련된 제한 사항 외에 여러 가지 추가적인 제한 사항이 있습니다. 이러한 제한 사항은 다음 사항에 영향을 미칩니다.

  • 저장 프로시저 내부에서 호출할 수 있는 기본 제공 함수.

  • ALTER USER 문 실행 능력.

  • 실행 시 저장 프로시저 모니터링.

  • SHOW 및 DESCRIBE 명령.

  • 저장 프로시저 내부에서 호출할 수 있는 SQL 문의 유형.

다음 섹션에서는 이러한 제한 사항을 더 자세히 설명합니다.

참고

소유자 권한 저장 프로시저에 대한 대부분의 제한 사항은 소유자를 포함한 모든 호출자에게 적용됩니다.

기본 제공 함수에 대한 제한 사항

저장 프로시저가 소유자 권한 저장 프로시저로 생성된 경우에는 (소유자 이외의) 호출자는 다음 기본 제공 함수를 호출할 수 없습니다.

  • GET_DDL()

    이것은 저장 프로시저 소유자 이외의 사용자가 저장 프로시저의 소스 코드를 보지 못하게 합니다.

  • SYSTEM$ENABLE_BEHAVIOR_CHANGE_BUNDLE()

  • SYSTEM$DISABLE_BEHAVIOR_CHANGE_BUNDLE()

ALTER USER

소유자 권한 저장 프로시저의 ALTER USER 문에는 다음 제한 사항이 적용됩니다.

  • 소유자 권한 저장 프로시저는 세션에 대해 현재 사용자를 암시적으로 사용하는 ALTER USER 문을 실행할 수 없습니다. (하지만 소유자 권한 저장 프로시저는 사용자가 현재 사용자가 아닌 한 명시적으로 사용자를 식별하는 ALTER USER 문을 실행할 수 있습니다.)

실행 시 저장 프로시저 모니터링

소유자 권한 저장 프로시저의 소유자나 호출자 중 누구도 저장 프로시저의 실행을 모니터링할 권한이 반드시 있어야 하는 것은 아닙니다.

WAREHOUSE MONITOR 권한이 있는 사용자는 해당 저장 프로시저 내에서 개별 웨어하우스 관련 SQL 문의 실행을 모니터링할 수 있습니다. 대부분의 쿼리 및 DML 문은 웨어하우스 관련 문입니다. CREATE, ALTER 등과 같은 DDL 문은 웨어하우스를 사용하지 않으며 저장 프로시저 모니터링의 일부로 모니터링할 수 없습니다.

SHOW 및 DESCRIBE 명령

소유자 권한 저장 프로시저는 호출자 이외의 사용자에 대한 정보를 읽기에 충분한 권한이 없습니다. 예를 들어, SHOW USERS LIKE <현재_사용자> 를 실행하면 현재 사용자에 대한 정보가 표시되지만, 현재 사용자가 유일한 사용자가 아니면 더 일반적인 SHOW USERS 가 작동하지 않습니다.

다음 SHOW 명령이 허용됩니다.

  • SHOW DATABASES.

  • SHOW SCHEMAS.

  • SHOW WAREHOUSES.

SQL 문에 대한 제한 사항

호출자 권한 저장 프로시저는 호출자가 저장 프로시저 외부에서 실행하기에 충분한 권한이 있는 SQL 문을 실행할 수 있지만, 소유자 권한 저장 프로시저는 SQL 문의 서브세트만 호출할 수 있습니다.

소유자 권한 저장 프로시저 내부에서 다음 SQL 문을 호출할 수 있습니다.

  • SELECT.

  • DML.

  • DDL. (ALTER USER 문에 대한 제한 사항은 위를 참조하십시오.)

  • GRANT/REVOKE.

  • 변수 할당.

  • DESCRIBE 및 SHOW. (위에서 설명한 제한 사항을 참조하십시오.)

소유자 권한 저장 프로시저 내부에서 다른 SQL 문을 호출할 수 없습니다.

다양한 권한을 가진 중첩 저장 프로시저

호출자 권한 저장 프로시저가 소유자 권한 저장 프로시저를 호출하거나 그 반대의 경우 다음 규칙이 적용됩니다.

  • 저장 프로시저는 프로시저와 그 위의 전체 호출 계층 구조가 호출자 권한 저장 프로시저인 경우에만 호출자 권한 저장 프로시저로 동작합니다.

  • 소유자 권한 저장 프로시저는 호출된 위치와 관계없이 항상 소유자 권한 저장 프로시저처럼 동작합니다.

  • 소유자 권한 저장 프로시저에서 직접 또는 간접적으로 호출된 모든 저장 프로시저는 소유자 권한 저장 프로시저처럼 동작합니다.

소유자 권한과 호출자 권한 중에서 선택하기

다음 모든 사항이 참인 경우 저장 프로시저를 소유자 권한 저장 프로시저로 만듭니다.

  • 호출자 자신의 권한이 아닌 소유자 권한으로 실행할 다른 사용자에게 작업을 위임하려고 합니다. 예를 들어, 테이블에 대한 DELETE 권한이 없는 사용자가 이전 데이터를 삭제하되 현재 데이터는 삭제하지 않는 저장 프로시저를 호출할 수 있게 하려는 경우 어쩌면 소유자 권한 저장 프로시저를 사용하고 싶을 것입니다. 해당 프로시저는 필터를 통해 삭제할 수 있는 데이터를 제어하는 필터(WHERE 절)를 포함한 DELETE 문을 포함합니다.

  • 소유자 권한 저장 프로시저의 제한 사항이 저장 프로시저의 올바른 작동을 막지 않습니다.

다음 사항이 참인 경우 저장 프로시저를 호출자 권한 저장 프로시저로 만듭니다.

  • 저장 프로시저는 호출자가 소유하거나 필요한 권한이 있는 오브젝트에서만 작동합니다.

  • 소유자 권한 저장 프로시저의 제한 사항이 저장 프로시저의 작동을 막습니다. 예를 들어, 저장 프로시저의 호출자가 호출자의 환경(예: 세션 변수 또는 계정 매개 변수)을 사용해야 하는 경우 호출자 권한 프로시저를 사용합니다.

특정 프로시저가 호출자 권한 또는 소유자 권한으로 올바르게 작동할 수 있는 경우에는 다음 규칙이 사용할 권한을 선택하는 데 도움이 될 수 있습니다.

  • 프로시저가 소유자 권한 프로시저인 경우 호출자는 저장 프로시저의 코드를 볼 권한이 없습니다(호출자가 소유자이기도 한 경우는 제외). 호출자가 프로시저의 소스 코드를 볼 수 없도록 하려는 경우에는 프로시저를 소유자 권한 프로시저로 만드십시오. 반대로, 호출자가 소스 코드를 읽을 수 있게 하려면 프로시저를 호출자 권한 프로시저로 만드십시오.

맨 위로 이동