了解调用方权限和所有者权限存储过程¶
存储过程使用 调用方 权限或 所有者 权限来运行。它不能同时运行两者。本主题介绍调用方权限存储过程和所有者权限存储过程之间的区别。
简介¶
调用方权限存储过程使用调用方的权限来运行。调用方权限存储过程的主要优点是它可以访问有关该调用方或调用方当前会话的信息。例如,调用方权限存储过程可以读取调用方的会话变量,并在查询中使用它们。
所有者权限存储过程主要使用存储过程所有者的权限来运行。所有者权限存储过程的主要优点是,所有者可以将特定的管理任务(如清理旧数据)委派给另一个角色,而无需授予该角色更多一般权限(例如从特定表中删除所有数据的权限)。
在创建存储过程时,创建者指定该过程是使用所有者权限还是调用方权限来运行。默认值为所有者权限。
所有者可以通过执行 ALTER PROCEDURE 命令将过程从所有者权限存储过程更改为调用方权限存储过程(反之亦然)。
小技巧
使用 引用 授权:即使存储过程以所有者权限运行,仍允许基于调用者权限对特定 SQL 对象或查询执行操作。
数据库对象的权限¶
调用方权限存储过程使用调用该存储过程的角色的数据库权限来运行。调用方无法在存储过程外部执行的任何语句也不能在存储过程内部执行。例如,如果名为“Nurse”的角色没有从 medical_records
表中删除行的权限,则如果角色为“Nurse”的用户调用尝试从该表中删除行的调用方权限存储过程,则存储过程将失败。
所有者权限过程使用过程所有者的权限来运行。这意味着,如果所有者具有执行任务的权限,则存储过程可以执行该任务,即使由没有直接执行该任务权限的角色调用也是如此。例如,如果名为“Doctor”的角色具有从 medical_records
表中删除行的数据库权限,并且“Doctor”角色创建一个存储过程,用于从该表中删除存在时间超过 7 年的行,则如果“Doctor”角色授予“Nurse”角色对存储过程的相应权限,然后,“Nurse”角色可以运行存储过程(并通过该存储过程从表中删除旧行),即使“Nurse”角色对表没有删除权限也是如此。
访问和设置会话状态¶
与其他 SQL 语句一样,CALL
语句在会话中运行,并从该会话继承上下文,例如会话级变量、当前数据库等。该过程继承的确切上下文取决于存储过程是调用方权限存储过程还是所有者权限存储过程。
如果调用方权限存储过程对会话进行更改,则这些更改可以在 CALL
的末尾保留。不允许所有者权限存储过程更改会话状态。
调用方权限存储过程¶
调用方权限存储过程在会话中遵循以下规则:
使用调用方的权限来运行,而不是所有者的权限。
继承调用方的当前仓库。
使用调用方当前使用的数据库和架构。
可以查看、设置和取消设置调用方的会话变量。
可以查看、设置和取消设置调用方的会话参数。
以调用方权限的子集运行¶
您可能希望存储过程以调用方权限运行,但仅限这些权限的子集。管理员可以使用调用方授权,以控制存储过程能以哪种调用方权限运行。有关更多信息,请参阅 受限制的调用方的权限。
调用方权限过程的会话变量¶
假设名为 MyProcedure
的存储过程执行读取和设置会话级变量的 SQL 语句。在此示例中,读取和设置命令的详细信息并不重要,因此语句表示为伪代码:
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 语言中将存储过程与其环境相隔离需要执行更多工作。
所有者权限存储过程¶
所有者权限存储过程在会话中遵循以下规则:
使用所有者的权限来运行,而不是调用方的权限。
继承调用方的当前仓库。
使用 在其中创建 存储过程的数据库和架构,而不是调用方当前使用的数据库和架构。
无法访问大多数调用方特定的信息。例如:
无法查看、设置或取消设置调用方的会话变量。
无法执行 SHOW PARAMETERS 来列出参数。
只能使用调用方设置的会话参数子集。例如,输出日期值的 SQL 命令可以使用为调用方会话设置的 DATE_OUTPUT_FORMAT 参数。
有关这些参数的列表,请参阅 了解调用方会话参数对所有者权限过程的影响。
无法设置或取消设置调用方的任何会话参数。
无法查询基于当前用户返回结果的 INFORMATION_SCHEMA 表函数(例如 AUTOMATIC_CLUSTERING_HISTORY)。
不允许非所有者从 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 语句的能力。
在执行时监视存储过程。
DESCRIBE 和 SHOW 命令。
LIST 命令。
可从存储过程内部调用的 SQL 语句的类型。
以下各部分将更详细地解释这些限制。
备注
对所有者权限存储过程的大多数限制适用于所有调用方,包括 所有者。
对内置函数的限制¶
如果将存储过程创建为所有者权限存储过程,则调用方(所有者除外)无法调用以下内置函数:
GET_DDL()
这样可以阻止存储过程所有者以外的用户查看存储过程的源代码。
SYSTEM$ENABLE_BEHAVIOR_CHANGE_BUNDLE()
SYSTEM$DISABLE_BEHAVIOR_CHANGE_BUNDLE()
ALTER USER¶
在所有者权限过程的处理程序中,您 不能 执行 隐式 使用当前用户进行会话的 ALTER USER 语句。
(但是,您 可以 执行 显式 识别用户的 ALTER USER 语句,只要该用户不是当前用户即可。)
在执行时监控存储过程¶
所有者权限存储过程的所有者和调用方都不一定具有监控存储过程执行的权限。
具有 WAREHOUSE MONITOR 权限的用户可以监控该存储过程中与各个仓库相关的 SQL 语句的执行。大多数查询和 DML 语句都是与仓库相关的语句。DDL 语句(如 CREATE、ALTER 等)不使用仓库,并且不能作为监控存储过程的一部分进行监视。
SHOW 和 DESCRIBE 命令¶
所有者权限存储过程没有足够的权限来读取调用方以外用户的信息。例如,运行 SHOW USERS LIKE <current_user>
将显示有关当前用户的信息,但除非当前用户是唯一的用户,否则更通用的 SHOW USERS
不能运行。
允许 使用以下 SHOW 命令:
SHOW DATABASES
SHOW SCHEMAS
SHOW WAREHOUSES
LIST 命令¶
无论存储过程的所有者和调用者是谁,在所有者权限存储过程的 Snowflake Scripting 或 :doc:`JavaScript </developer-guide/stored-procedure/stored-procedures-javascript>` 处理程序代码中,都不可使用 LIST 命令。如果过程处理程序是使用其他处理程序语言之一编写的,您 可以 使用 LIST。
对 SQL 语句的限制¶
尽管调用方权限存储过程可以执行调用方具有足够权限在存储过程外部执行的任何 SQL 语句,但所有者权限存储过程只能调用 SQL 语句的子集。
可以 从所有者权限存储过程内部调用以下 SQL 语句:
SELECT
DML
DDL(有关对 ALTER USER 语句的限制,请参阅以上所述)
GRANT/REVOKE
变量赋值
DESCRIBE 和 SHOW(请参阅上面记录的限制)
LIST(请参阅上面记录的限制)
不能从所有者权限存储过程内部调用其他 SQL 语句。
具有不同权限的嵌套存储过程¶
如果所有者权限存储过程由调用方权限存储过程调用,或调用方权限存储过程由所有者权限存储过程调用,则适用以下规则:
当且仅当该过程及其之上的整个调用层次结构是调用方权限存储过程时,存储过程才充当调用方权限存储过程。
所有者权限存储过程始终表现为所有者权限存储过程,无论它从何处调用。
直接或间接从所有者权限存储过程调用的任何存储过程都表现为所有者权限存储过程。
在所有者权限和调用方权限间做出选择¶
如果满足以下 所有 条件,则将存储过程创建为所有者权限存储过程:
您想将任务委派给另一个用户,该用户将使用所有者的权限(而不是调用方自己的权限)来运行。
例如,如果希望对表没有 DELETE 权限的用户能够调用删除旧数据但不删除当前数据的存储过程,则可能需要使用所有者权限存储过程。该过程将包含一个 DELETE 语句,该语句包含一个筛选器(一个 WHERE 子句),用于控制可以通过筛选器删除哪些数据。
所有者权限存储过程中的限制不会阻止存储过程正常工作。
如果满足以下条件,则将存储过程创建为调用方权限存储过程:
存储过程仅对调用方拥有的对象或对其具有所需权限的对象进行操作。
所有者权限存储过程中的限制将阻止存储过程工作。例如,如果存储过程的调用方需要使用调用方的环境(例如会话变量或账户参数),则使用调用方权限过程。
如果特定过程可以使用调用方权限或所有者权限正常工作,则以下规则可能会帮助您选择要使用的权限:
如果过程是所有者权限过程,则调用方无权查看存储过程中的代码(除非调用方也是所有者)。如果要阻止调用方查看过程的源代码,请将过程创建为所有者权限过程。相反,如果希望调用方能够读取源代码,则将过程创建为调用方权限程序。