CREATE EXTERNAL ACCESS INTEGRATION | Snowflake Documentation (original) (raw)

Creates an external access integration for access to external network locations from a UDF or procedure handler.

See also:

ALTER EXTERNAL ACCESS INTEGRATION , DROP INTEGRATION ,SHOW INTEGRATIONS , DESCRIBE INTEGRATION

Syntax¶

CREATE [ OR REPLACE ] EXTERNAL ACCESS INTEGRATION ALLOWED_NETWORK_RULES = ( [, , ... ] ) [ ALLOWED_API_AUTHENTICATION_INTEGRATIONS = ( { [, , ... ] | none } ) ] [ ALLOWED_AUTHENTICATION_SECRETS = ( { [, , ... ] | all | none } ) ] ENABLED = { TRUE | FALSE } [ COMMENT = '' ]

Required parameters¶

_name_

Identifier for the external access integration.

The identifier value 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 case-sensitive.

For more details, see Identifier requirements.

ALLOWED_NETWORK_RULES = (_rulename_ [ , _rulename_ ... ])

Specifies the allowed network rules. Only egress rules may be specified.

ENABLED = { TRUE | FALSE }

Specifies whether this integration is enabled or disabled. If the integration is disabled, any handler code that relies on it will be unable to reach the external network location.

The value is case-insensitive.

The default is TRUE.

Optional parameters¶

ALLOWED_API_AUTHENTICATION_INTEGRATIONS = ( _integrationname1_ [, _integrationname2_, ... ] | none )

Specifies the security integrations whose OAuth authorization server issued the secret used by the UDF or procedure. The security integration must be the type used for external API integration.

This parameter’s value must be one of the following:

• One or more Snowflake security integration names to allow any of the listed integrations.
• none to allow no integrations.

Security integrations specified by this parameter – as well as secrets specified by the ALLOWED_AUTHENTICATION_SECRETS parameter – are ways to allow secrets for use in a UDF or procedure that uses this external access integration. For more information, seeUsage notes.

For reference information about security integrations, refer to CREATE SECURITY INTEGRATION (External API Authentication).

ALLOWED_AUTHENTICATION_SECRETS = ( _secretname_ [, _secretname_ ... ] | all | none )

Specifies the secrets that UDF or procedure handler code can use when accessing the external network locations referenced in allowed network rules.

This parameter’s value must be one of the following:

• One or more Snowflake secret names to allow any of the listed secrets.
• all to allow any secret.
• none to allow no secrets.

The ALLOWED_API_AUTHENTICATION_INTEGRATIONS parameter can also specify allowed secrets. For more information, seeUsage notes.

For reference information about secrets, refer to CREATE SECRET.

COMMENT = '_stringliteral_'

Specifies a comment for the external access integration.

Default: No value

Access control requirements¶

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

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 onsecurable objects, see Overview of Access Control.

Usage notes¶

• You can allow secrets for use by a UDF or procedure by using two external access integration parameters, as described below.
  • With the ALLOWED_AUTHENTICATION_SECRETS parameter. You can specify secrets as parameter values or set the parameter’s value toall, allowing handler code to use any secret.
  • With the ALLOWED_API_AUTHENTICATION_INTEGRATIONS parameter. A secret is allowed for use when the secret itself specifies a security integration whose name is also specified by this parameter. The secret specifies the security integration with its API_AUTHENTICATION parameter. In other words, when both the secret and the external access integration specify the security integration, the secret is allowed for use in functions and procedures that specify the external access integration.
    Note that these two alternatives function independently of one another. A secret is allowed if either (or both) of the parameters allows it, regardless of the value specified for the other parameter. For example, setting one of the parameters to none does not prevent a secret specified by the other parameter from being used in handler code.
• While you can specify network rules using a hostname, Snowflake enforces the rules at the IP level of granularity. Snowflake will not inspect your application’s traffic, so it is your responsibility to ensure that the external location’s host has the authentic service and that it is not possible to connect to other services on the same host. Whenever possible, you should use secure protocols such as HTTPS and TLS when communicating with internet endpoints.
• Regarding metadata:
  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.
• CREATE OR REPLACE 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.

Examples¶

Create an external access integration that provides access to the Google Translation API.

For a more complete example, refer to Creating and using an external access integration.

1. Create a secret representing credentials.
   To create a secret, you must have been assigned a role with the CREATE SECRET privilege on the current schema. For other kinds of secret supported by this command, refer to CREATE SECRET. In this example, google_translate_oauthrefers to a security integration. For more information, refer to CREATE SECURITY INTEGRATION (External API Authentication).
   CREATE OR REPLACE SECRET oauth_token
   TYPE = OAUTH2
   API_AUTHENTICATION = google_translate_oauth
   OAUTH_REFRESH_TOKEN = 'my-refresh-token';
2. Grant READ privileges on the secret to the developer role so that UDF developers can use it.
   Create the role that will be required for developers needing to use the secret.
   USE ROLE USERADMIN;
   CREATE OR REPLACE ROLE developer;
   Grant the READ privilege to the developer role.
   USE ROLE SECURITYADMIN;
   GRANT READ ON SECRET oauth_token TO ROLE developer;
3. Create a network rule representing the external network location. Use a role with the privileges described inCREATE NETWORK RULE.
   USE ROLE SYSADMIN;
   CREATE OR REPLACE NETWORK RULE google_apis_network_rule
   MODE = EGRESS
   TYPE = HOST_PORT
   VALUE_LIST = ('translation.googleapis.com');
4. Create an external access integration using the secret and network rule.
   USE ROLE ACCOUNTADMIN;
   CREATE OR REPLACE EXTERNAL ACCESS INTEGRATION google_apis_access_integration
   ALLOWED_NETWORK_RULES = (google_apis_network_rule)
   ALLOWED_AUTHENTICATION_SECRETS = (oauth_token)
   ENABLED = true;
5. Grant USAGE privileges on the integration to the developer role so that UDF developers can use it.
   GRANT USAGE ON INTEGRATION google_apis_access_integration TO ROLE developer;
6. Create a UDF google_translate_python that translates the specified text into a phrase in the specified language. For more information, refer to Using the external access integration in a function or procedure.
   USE ROLE developer;
   CREATE OR REPLACE FUNCTION google_translate_python(sentence STRING, language STRING)
   RETURNS STRING
   LANGUAGE PYTHON
   RUNTIME_VERSION = 3.10
   HANDLER = 'get_translation'
   EXTERNAL_ACCESS_INTEGRATIONS = (google_apis_access_integration)
   PACKAGES = ('snowflake-snowpark-python','requests')
   SECRETS = ('cred' = oauth_token )
   AS
   importsnowflakeimportrequestsimportjsonsession=requests.Session()defgettranslation(sentence,language):token=snowflake.getoauthaccesstoken(′cred′)url="https://translation.googleapis.com/language/translate/v2"data=′q′:sentence,′target′:languageresponse=session.post(url,json=data,headers="Authorization":"Bearer"+token)returnresponse.json()[′data′][′translations′][0][′translatedText′]import _snowflake
   import requests
   import json
   session = requests.Session()
   def get_translation(sentence, language):
   token = _snowflake.get_oauth_access_token('cred')
   url = "https://translation.googleapis.com/language/translate/v2"
   data = {'q': sentence,'target': language}
   response = session.post(url, json = data, headers = {"Authorization": "Bearer " + token})
   return response.json()['data']['translations'][0]['translatedText']imports​nowflakeimportrequestsimportjsonsession=requests.Session()defgett​ranslation(sentence,language):token=s​nowflake.geto​autha​ccesst​oken(′cred′)url="https://translation.googleapis.com/language/translate/v2"data=′q′:sentence,′target′:languageresponse=session.post(url,json=data,headers="Authorization":"Bearer"+token)returnresponse.json()[′data′][′translations′][0][′translatedText′];
7. Grant the USAGE privilege on the google_translate_python function so that those with the user role can call it.
   GRANT USAGE ON FUNCTION google_translate_python(string, string) TO ROLE user;
8. Execute the google_translate_python function to translate a phrase.
   USE ROLE user;
   SELECT google_translate_python('Happy Thursday!', 'zh-CN');
   This generates the following output.