CREATE SECRET

在当前/指定架构中创建新密钥或替换现有密钥。

See also:

ALTER SECRET , DESCRIBE SECRET , DROP SECRET , SHOW SECRETS

语法

OAuth （使用客户端凭据流程）：

CREATE [ OR REPLACE ] SECRET [ IF NOT EXISTS ] <name>
  TYPE = OAUTH2
  API_AUTHENTICATION = <security_integration_name>
  OAUTH_SCOPES = ( '<scope_1>' [ , '<scope_2>' ... ] )
  [ COMMENT = '<string_literal>' ]

OAuth （使用授权代码授予流程：）

CREATE [ OR REPLACE ] SECRET [ IF NOT EXISTS ] <name>
  TYPE = OAUTH2
  OAUTH_REFRESH_TOKEN = '<string_literal>'
  OAUTH_REFRESH_TOKEN_EXPIRY_TIME = '<string_literal>'
  API_AUTHENTICATION = <security_integration_name>;
  [ COMMENT = '<string_literal>' ]

云提供商：

CREATE [ OR REPLACE ] SECRET [ IF NOT EXISTS ] <name>
  TYPE = CLOUD_PROVIDER_TOKEN
  API_AUTHENTICATION = '<cloud_provider_security_integration>'
  ENABLED = { TRUE | FALSE }
  [ COMMENT = '<string_literal>' ]

基本身份验证：

CREATE [ OR REPLACE ] SECRET [ IF NOT EXISTS ] <name>
  TYPE = PASSWORD
  USERNAME = '<username>'
  PASSWORD = '<password>'
  [ COMMENT = '<string_literal>' ]

通用字符串：

CREATE [ OR REPLACE ] SECRET [ IF NOT EXISTS ] <name>
  TYPE = GENERIC_STRING
  SECRET_STRING = '<string_literal>'
  [ COMMENT = '<string_literal>' ]

对称密钥：

CREATE [ OR REPLACE ] SECRET [ IF NOT EXISTS ] <name>
TYPE = SYMMETRIC_KEY
ALGORITHM = GENERIC

OAuth 客户端凭据流程的必填参数

name

指定密钥的标识符（即名称）的字符串；在架构中必须唯一。

In addition, the identifier must start with an alphabetic character and cannot contain spaces or special characters unless the entire identifier string is enclosed in double quotes (e.g. "My object"). Identifiers enclosed in double quotes are also case-sensitive.

For more details, see Identifier requirements.

TYPE = OAUTH2

指定要用于 OAuth 授予流程的密钥。

API_AUTHENTICATION = security_integration_name

Specifies the name value of the Snowflake security integration that connects Snowflake to an external service.

OAUTH_SCOPES = ( 'scope_1' [ , 'scope_2' ... ] )

指定在以下情况下使用的以逗号分隔的范围列表：在 OAuth 客户端凭据流程期间，对集成具有 USAGE 权限的角色从 OAuth 服务器发出请求。

This list must be a subset of the scopes defined in the OAUTH_ALLOWED_SCOPES property of the security integration. If the OAUTH_SCOPES property values are not specified, the secret inherits all of the scopes that are specified in the security integration.

For the ServiceNow connector, the only possible scope value is 'useraccount'.

OAuth 授权代码授予流程的必填参数

name

指定密钥的标识符（即名称）的字符串；在架构中必须唯一。

In addition, the identifier must start with an alphabetic character and cannot contain spaces or special characters unless the entire identifier string is enclosed in double quotes (e.g. "My object"). Identifiers enclosed in double quotes are also case-sensitive.

For more details, see Identifier requirements.

TYPE = OAUTH2

指定要用于 OAuth 授予流程的密钥。

OAUTH_REFRESH_TOKEN = 'string_literal'

指定字符串形式的令牌，它用于在访问令牌过期时从 OAuth 授权服务器获取新的访问令牌。

OAUTH_REFRESH_TOKEN_EXPIRY_TIME = 'string_literal'

当 OAuth 刷新令牌过期时，指定字符串形式的时间戳。

API_AUTHENTICATION = security_integration_name

Specifies the name value of the Snowflake security integration that connects Snowflake to an external service.

AWS IAM 必填参数

TYPE = CLOUD_PROVIDER_TOKEN

指定这是用于云提供商（例如 Amazon Web Services AWS）的密钥。

API_AUTHENTICATION = 'cloud_provider_security_integration'

Specifies the name value of the Snowflake security integration that connects Snowflake to a cloud provider.

基本身份验证的必填参数

name

指定密钥的标识符（即名称）的字符串；在架构中必须唯一。

In addition, the identifier must start with an alphabetic character and cannot contain spaces or special characters unless the entire identifier string is enclosed in double quotes (e.g. "My object"). Identifiers enclosed in double quotes are also case-sensitive.

For more details, see Identifier requirements.

TYPE = PASSWORD

指定用于基本身份验证的密钥。

指定此类型时，必须指定用户名和密码属性的值。

USERNAME = 'username'

指定要存储在密钥中的用户名值。

Specify this value when setting the TYPE value to PASSWORD for use with basic authentication.

PASSWORD = 'password'

指定要存储在密钥中的密码值。

Specify this value when setting the TYPE value to PASSWORD for use with basic authentication.

通用字符串参数

name

指定密钥的标识符（即名称）的字符串；在架构中必须唯一。

In addition, the identifier must start with an alphabetic character and cannot contain spaces or special characters unless the entire identifier string is enclosed in double quotes (e.g. "My object"). Identifiers enclosed in double quotes are also case-sensitive.

For more details, see Identifier requirements.

TYPE = GENERIC_STRING

指定用于存储敏感字符串值的密钥。

SECRET_STRING = 'string_literal'

指定要存储在密钥中的字符串。

The string can be an API token or a string of sensitive value that can be used in the handler code of a UDF or stored procedure. For details, see Creating and using an external access integration.

不应使用此属性来存储任何类型的 OAuth 令牌；对于您的 OAuth 用例，请使用其他密钥类型之一。

对称密钥参数

Symmetric key secrets generate a cryptographic key that can be used for cryptographic operations. Currently only used to generate synthetic data.

ALGORITHM

Specifies which algorithm to use to generate the symmetric key. The only value supported is GENERIC, which generates a 256-bit key.

可选参数

COMMENT = 'string_literal'

用于指定密钥注释的字符串（字面量）。

默认：无值

访问控制要求

A role used to execute this operation must have the following privileges at a minimum:

权限对象备注
CREATE SECRET架构
USAGE数据库或架构

Operating on an object in a schema requires at least one privilege on the parent database and at least one privilege on the parent schema.

For instructions on creating a custom role with a specified set of privileges, see Creating custom roles.

For general information about roles and privilege grants for performing SQL actions on securable objects, see Overview of Access Control.

使用说明

  • The OR REPLACE and IF NOT EXISTS clauses are mutually exclusive. They can’t both be used in the same statement.

  • CREATE OR REPLACE <object> statements are atomic. That is, when an object is replaced, the old object is deleted and the new object is created in a single transaction.

  • 关于元数据：

    Attention

    Customers should ensure that no personal data (other than for a User object), sensitive data, export-controlled data, or other regulated data is entered as metadata when using the Snowflake service. For more information, see Metadata fields in Snowflake.

示例

使用客户端凭据的 OAuth

创建用于 OAuth 客户端凭据流程的密钥：

CREATE OR REPLACE SECRET mysecret
  TYPE = OAUTH2
  API_AUTHENTICATION = mysecurityintegration
  OAUTH_SCOPES = ('useraccount')
  COMMENT = 'secret for the service now connector'

使用授权码授予的 OAuth

创建用于 OAuth 代码授予流程的密钥：

CREATE SECRET service_now_creds_oauth_code
  TYPE = OAUTH2
  OAUTH_REFRESH_TOKEN = '34n;vods4nQsdg09wee4qnfvadH'
  OAUTH_REFRESH_TOKEN_EXPIRY_TIME = '2022-01-06 20:00:00'
  API_AUTHENTICATION = sn_oauth;

AWS IAM

为身份验证包含 AWS IAM ARN，创建用于 Amazon Web Services (AWS) 的密钥：

CREATE SECRET aws_secret
  TYPE = CLOUD_PROVIDER_TOKEN
  API_AUTHENTICATION = myawsiamintegration
  ENABLED = TRUE;

基本身份验证

创建一个密钥，以指定用于访问 ServiceNow 的用户名和密码：

CREATE SECRET service_now_creds_pw
  TYPE = password
  USERNAME = 'jsmith1'
  PASSWORD = 'W3dr@fg*7B1c4j';