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

호출자 권한 프로시저의 세션 변수¶

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;

소유자 권한 프로시저의 세션 변수¶

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

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

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

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

세션 매개 변수 의 값은 명령과 함수의 동작에 영향을 줄 수 있습니다. 예를 들어, 날짜 값을 출력하는 명령은 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

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

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

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

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

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

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

내장 함수에 대한 제한 사항¶

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

• 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 문을 호출할 수 없습니다.