Passing references for objects and queries to stored procedures¶

Background: The problem with passing objects and queries to stored procedures¶

소유자의 권한 저장 프로시저가 입력 인자로 지정된 테이블에 행을 삽입하도록 설계되었다고 가정합니다. 다음은 Snowflake Scripting과 JavaScript로 작성된 예제입니다.

USE ROLE stored_proc_owner;

CREATE OR REPLACE PROCEDURE insert_row(table_identifier VARCHAR)
RETURNS TABLE()
LANGUAGE SQL
AS
$$
BEGIN
  LET stmt VARCHAR := 'INSERT INTO ' || table_identifier || ' VALUES (10)';
  LET res RESULTSET := (EXECUTE IMMEDIATE stmt);
  RETURN TABLE(res);
END;
$$;

USE ROLE stored_proc_owner;

CREATE OR REPLACE PROCEDURE insert_row(table_identifier VARCHAR)
RETURNS FLOAT
LANGUAGE JAVASCRIPT
AS
$$
  let res = snowflake.execute({
    sqlText: "INSERT INTO IDENTIFIER(?) VALUES (10);",
    binds : [TABLE_IDENTIFIER]
  });
  res.next()
  return res.getColumnValue(1);
$$;

다른 역할이 소유한 테이블에 대해 이 프로시저를 호출해야 한다고 가정합니다.

USE ROLE table_owner;

CREATE OR REPLACE TABLE table_with_different_owner (x NUMBER) AS SELECT 42;

저장 프로시저를 호출하고 테이블 이름을 전달하면 저장 프로시저의 소유자에게 테이블에 액세스할 충분한 권한이 없으므로 저장 프로시저가 실패합니다.

USE ROLE table_owner;

CALL insert_row('table_with_different_owner');

002003 (42S02): Uncaught exception of type 'STATEMENT_ERROR' on line 4 at position 25 : SQL compilation error:
Table 'TABLE_WITH_DIFFERENT_OWNER' does not exist or not authorized.

저장 프로시저가 테이블에서 호출자로 SQL 작업을 수행할 수 있도록 하려면 테이블에 대한 참조를 만들고 테이블 이름이 아닌 해당 참조를 전달합니다.

참조 만들기¶

참조를 생성하려면 SYSTEM$REFERENCE 함수를 호출하십시오. 예:

USE ROLE table_owner;

CALL insert_row(SYSTEM$REFERENCE('TABLE', 'table_with_different_owner', 'SESSION', 'INSERT'));

위의 예에서는 SYSTEM$REFERENCE 함수에 다음 인자를 전달합니다.

• 오브젝트 유형에 대한 'TABLE'.

• 테이블 이름에 대한 'table_with_different_owner'.

• 참조의 범위를 세션으로 지정 해야 함을 나타내는 'SESSION'.

• 오브젝트에 대한 작업을 수행하는 데 필요한 권한 으로 'INSERT'.

참조 범위 지정하기¶

참조는 참조가 전달되는 호출 기간 또는 세션 기간 동안 유효합니다. 참조가 생성되는 컨텍스트에 따라 범위가 결정됩니다.

• 단일 문에서 저장 프로시저에 대한 참조를 생성하고 전달하는 경우, 참조는 저장 프로시저의 가장 바깥쪽 블록에서 선언된 변수와 동일한 표시 유형을 갖습니다.

  CALL select_from_table(SYSTEM$REFERENCE('TABLE', 'my_table');
  

  Copy

• 참조를 생성하고 세션 변수 에 할당하면 세션 변수를 설정 해제하더라도 참조는 세션 기간 동안 유효합니다.

  SET tableRef = (SELECT SYSTEM$REFERENCE('TABLE', 'my_table'));
  
  SELECT * FROM IDENTIFIER($tableRef);
  

  Copy

참조가 생성된 컨텍스트에 관계없이 참조 범위가 세션의 지속 기간이어야 하도록 지정하려면 SYSTEM$REFERENCE 함수의 세 번째 인자(session_scope)에 'SESSION' 를 전달하십시오.

CALL insert_row(SYSTEM$REFERENCE('TABLE', 'table_with_different_owner', 'SESSION', 'INSERT'));

참조에 추가 권한 부여하기¶

기본적으로, 참조는 참조되는 오브젝트의 유형을 기반으로 권한의 하위 세트를 부여합니다. 예를 들어, 테이블에 대한 참조는 참조를 생성한 사용자의 활성 역할 또는 보조 역할에 대해 해당 테이블에 대한 SELECT 권한을 부여합니다. 기본 권한은 오브젝트 유형에 따라 다릅니다. 지원되는 오브젝트, 권한, 기본 권한의 목록은 참조의 지원 오브젝트 유형과 권한 섹션을 참조하십시오.

추가 권한을 부여하려면 해당 권한을 SYSTEM$REFERENCE 함수에 대한 추가 인자로 지정하십시오. 예를 들어 테이블에 대한 INSERT, UPDATE 및 TRUNCATE 권한을 부여하려면 다음을 수행하십시오.

SELECT SYSTEM$REFERENCE('TABLE', 'table_with_different_owner', 'SESSION', 'INSERT', 'UPDATE', 'TRUNCATE');

OWNERSHIP 또는 ALL을 권한으로 지정할 수 없습니다.

참조가 생성된 후 참조 생성자의 권한에 대한 변경 사항은 참조와 연결된 권한에 반영됩니다. 예를 들어 참조 생성자에 대해 INSERT 권한이 취소되는 경우 INSERT 권한은 참조와 더 이상 연결되지 않습니다.

마스킹 정책과 함께 테이블 및 뷰에 대한 참조 사용하기¶

마스킹 정책이 있는 테이블 또는 뷰에 대한 참조를 사용하는 경우, 참조가 쿼리, 저장 프로시저 또는 사용자 정의 함수에서 사용되는지 여부에 관계없이 참조 역할은 호출자 역할(INVOKER_ROLE 에서 반환되는 역할)입니다.

참조를 사용해도 현재 역할(CURRENT_ROLE 에서 반환되는 역할)은 변경되지 않습니다.

저장 프로시저에서 참조 만들기¶

소유자의 권한 저장 프로시저 를 작성하는 경우 저장 프로시저 본문 내에 참조를 만들지 마십시오.

소유자의 권한 저장 프로시저에서 생성된 참조는 저장 프로시저의 소유자 역할을 사용합니다. 참조는 저장 프로시저를 호출하는 사용자의 역할을 사용해야 합니다. 소유자 권한 저장 프로시저의 경우 저장 프로시저를 호출하는 사용자는 참조를 만들어 저장 프로시저에 전달해야 합니다.

호출자의 권한 저장 프로시저를 작성하는 경우 저장 프로시저 본문 내에 참조를 만들 수 있습니다.

쿼리 참조 사용하기¶

저장 프로시저에서 SELECT 문의 FROM 절에 사용되는 쿼리를 전달해야 하는 경우 쿼리 참조를 만들어 전달하십시오.

예를 들어 저장 프로시저가 다른 SELECT 문의 FROM 절에서 사용하려는 SELECT 문을 전달한다고 가정합니다. 아래 예에서 쿼리 인자는 SELECT 문이 됩니다. 이 예제는 Snowflake Scripting과 JavaScript로 작성됩니다.

USE ROLE stored_proc_owner;

CREATE OR REPLACE PROCEDURE get_num_results(query VARCHAR)
  RETURNS INTEGER
  LANGUAGE SQL
  AS
  DECLARE
    row_count INTEGER DEFAULT 0;
    stmt VARCHAR DEFAULT 'SELECT COUNT(*) FROM (' || query || ')';
    res RESULTSET DEFAULT (EXECUTE IMMEDIATE :stmt);
    cur CURSOR FOR res;
  BEGIN
    OPEN cur;
    FETCH cur INTO row_count;
    RETURN row_count;
  END;

USE ROLE stored_proc_owner;

CREATE OR REPLACE PROCEDURE get_num_results(query VARCHAR)
RETURNS FLOAT
LANGUAGE JAVASCRIPT
AS
$$
  let res = snowflake.execute({
    sqlText: "SELECT COUNT(*) FROM (" + QUERY + ");",
  });
  res.next()
  return res.getColumnValue(1);
$$;

저장 프로시저는 소유자의 권한을 사용합니다. 저장 프로시저 소유자에게 SELECT 문의 테이블을 쿼리할 권한이 없으면 저장 프로시저 호출이 실패합니다.

USE ROLE table_owner;
CREATE OR REPLACE TABLE table_with_different_owner (x NUMBER) AS SELECT 42;

CALL get_num_results('SELECT x FROM table_with_different_owner');

002003 (42S02): Uncaught exception of type 'STATEMENT_ERROR' on line 4 at position 29 : SQL compilation error:
Object 'TABLE_WITH_DIFFERENT_OWNER' does not exist or not authorized.

저장 프로시저가 호출자로서 쿼리를 실행할 수 있도록 하려면 SELECT 문에 대한 쿼리 참조를 만들고 SELECT 문이 아닌 해당 참조를 전달하십시오.

쿼리 참조를 생성하려면 SYSTEM$QUERY_REFERENCE 함수를 호출하면 됩니다.

SYSTEM$QUERY_REFERENCE 함수를 호출하는 경우, 다음에서 전달됩니다.

• 쿼리로 'SELECT x FROM table_with_different_owner' 를 사용합니다.

  SELECT 문에 작은따옴표나 기타 특수 문자(예: 줄 바꿈)가 포함된 경우 백슬래시로 해당 문자를 이스케이프 해야 합니다.

• 쿼리 참조의 범위를 세션으로 지정해야 함을 나타내는 true.

예:

USE ROLE table_owner;

CALL get_num_results(
  SYSTEM$QUERY_REFERENCE('SELECT x FROM table_with_different_owner', true)
);

+-----------------+
| GET_NUM_RESULTS |
|-----------------|
|               1 |
+-----------------+

저장 프로시저 내에서 쿼리의 FROM 절에 쿼리 참조를 추가할 수 있습니다. 예:

snowflake.execute({
  sqlText: "SELECT COUNT(*) FROM (" + QUERY + ");"
});

이 함수에 대한 자세한 내용은 SYSTEM$QUERY_REFERENCE 섹션을 참조하십시오.

쿼리 참조 생성 및 사용과 관련된 제한 사항은 현재 제한 사항 섹션을 참조하십시오.

TABLE 키워드를 사용하여 테이블, 뷰 또는 쿼리에 대한 참조를 만들기¶

저장 프로시저가 쿼리해야 하는 수정하지 않는 테이블, 뷰 또는 보안 뷰에 대한 참조를 만들어야 하고, 전체 세션이 아닌 호출 범위에 대해 참조가 유효하도록 하려면 다음 구문과 함께 TABLE 키워드를 사용합니다.

TABLE( [[<database_name>.]<schema_name>.]<object_name> )

TABLE("<object_name_that_requires_double_quotes>")

TABLE(IDENTIFIER('string_literal_for_object_name'))

TABLE 키워드는 오브젝트 유형에 대한 인자를 지정하지 않고도 테이블 또는 뷰에 대한 SYSTEM$REFERENCE 함수를 호출할 수 있는 더 간단한 구문을 제공합니다. TABLE 키워드를 사용하는 경우 참조는 SELECT 권한만 부여하며 참조의 범위는 세션이 아닌 호출입니다.

다음 예제에서는 저장 프로시저 my_procedure 를 호출하고 테이블 및 뷰에 대한 참조를 전달합니다.

CALL my_procedure(TABLE(my_table));

CALL my_procedure(TABLE(my_database.my_schema.my_view));

CALL my_procedure(TABLE("My Table Name"));

CALL my_procedure(TABLE(IDENTIFIER('my_view')));

쿼리에 대한 참조를 생성하려는 경우 참조가 전체 세션이 아닌 호출 범위에 대해서만 유효해야 하는 경우 SYSTEM$QUERY_REFERENCE 함수를 호출하는 대신 TABLE 키워드를 사용할 수 있습니다. TABLE 키워드를 사용하려면 다음 구문을 사용합니다.

TABLE(<select_statement>)

예:

CALL my_procedure(TABLE(SELECT * FROM my_view));

CALL my_procedure(TABLE(WITH c(s) as (SELECT $1 FROM VALUES (1), (2)) SELECT a, count(*) FROM T, C WHERE s = a GROUP BY a));

다음 사항을 참고하십시오.

• 오브젝트 이름이나 쿼리에 바인드 변수를 사용할 수 없습니다.

• TABLE 키워드로 생성된 참조는 호출의 지속 시간 동안 유효합니다. 참조에 다른 범위를 지정할 수 없습니다.

• 참조에는 오브젝트 유형에 대해 부여된 기본 권한 이 있습니다.

현재 제한 사항¶

현재, 참조에는 다음과 같은 제한 사항이 있습니다.

• GET_DDL 및 SYSTEM$GET_TAG 는 입력 인자로 참조를 지원하지 않습니다.

• You can only create references to tables, views, functions, and procedures.

• 참조를 포함하는 쿼리에서는 계획 캐시와 결과 캐싱이 사용되지 않습니다.

• 쿼리 참조의 경우:

  • 인라인 뷰 역할을 하는 SELECT 문에 대한 쿼리 참조만 만들 수 있습니다.

  • 쿼리 참조를 생성할 때 바인드 변수 또는 세션 변수를 지정할 수 없습니다.

  • 저장 프로시저에서는 SELECT 문의 FROM 절에서만 쿼리 참조를 사용할 수 있습니다.