Prerequisites for Creating a Connection

Know the Parameter Values

Know the instance URL.
Know the warehouse name.
Know the database name.
Know the schema name.
Know the client ID and client secret of the integration. The client secret is retrieved using the SYSTEM$SHOW_OAUTH_CLIENT_SECRETS function.
Know the user name.
Know the account ID.
Know your public key, private key, and private key passphrase (required only if you are using an encrypted private key).
Ensure that the integration user (custom role) has all privileges on the warehouse, database, and schema.

When you create your Snowflake Adapter connection in Oracle Integration, you must specify the following details on the Connections page. Therefore, you must know or obtain the following values before creating a connection:

Warehouse name
Database name
Schema name
Client ID and client secret
User name
Account ID
Public key, private key, and private key passphrase (required only if you are using an encrypted private key)

Perform the following steps to obtain these values:

Note:

The following command examples are provided to give you an idea of what to enter.

Log in to your Snowflake admin account.
Create a warehouse using the following command. For example:
```
create or replace warehouse ORACLE_WH;
```
See Create Warehouse.
Create a database using the following command. For example:
```
create or replace database snowflake_db_oracle;
```
See Create Database.
Create a schema. See Create Schema. You receive the following schema by default if you used the commands mentioned in steps 2 and 3.
```
use schema Public;
```
Create a new table in the current/specified schema. See Create Table.
Create a user name using the following command. For example:
```
create user oracle_user password='01March#2022' default_role = SYSADMIN
```
You need this user name (for this example, oracle_user) when configuring a connection using Snowflake Key Pair Authentication.

See Create User.
Create an account using the following command:
```
Select Create_Account();
```
An account is created. You need the account ID to configure a connection using the Snowflake Key Pair Authentication security policy.

See Create Account.
Create a new Snowflake OAuth security integration using the following command in Snowflake. For example:
```
create or replace security integration ORACLE_OAUTH
type=oauth
enabled=true
oauth_client=CUSTOM
oauth_client_type='CONFIDENTIAL'
oauth_redirect_uri='https://my-development-instance.integration.us-region-1.domain.com/icsapis/agent/oauth/callback’
oauth_issue_refresh_tokens=true
oauth_refresh_token_validity=86400; 
```
If you need to enable a single-use refresh token, you can set the parameter oauth_single_ use_refresh_tokens_required to true.

See Create Security Integration (Snowflake Oauth).

Note:
For the OAuth refresh token value, the maximum value can be set to 7776000 (90 days).

This step generates the client ID, client secret, and access tokens (and optionally, refresh tokens) for access to Snowflake.
- To see the client ID and client secret of security integration, use the following command. For example:
```
SELECT SYSTEM$SHOW_OAUTH_CLIENT_SECRETS('ORACLE_OAUTH');
```
- To see the properties of security integration, use the following command. For example:
```
desc integration ORACLE_OAUTH;
```
Assign a role (other than admin) to the user. See Grant Role and Alter User.
Provide all privileges on the warehouse, database, and schema to the custom role.
- To provide the privileges of the database to the default role, use the following command. For example:
```
GRANT all PRIVILEGES on DATABASE snowflake_db_oracle to role sysadmin;
```
- To provide the privileges of security integration to the default role, use the following command. For example:
```
grant all on integration ORACLE_OAUTH to role sysadmin;
```
- To provide the privileges of predefined roles to the user, use the following command. For example:
```
grant role SYSADMIN to user oracle_user;
```
- To provide the privileges of the warehouse to the default role, use the following command. For example:
```
grant usage on warehouse oracle_wh to role sysadmin;
```
- To provide the privileges of the database to the default role, use the following command. For example:
```
grant usage on database snowflake_db_oracle to role sysadmin;
```
- To provide the usage privileges of the schema to the default role, use the following command. For example:
```
grant usage on schema public to role sysadmin;
```
See GRANT <privileges> … TO ROLE.
Generate the private key.
You can either generate an unencrypted or encrypted private key.
- To generate an unencrypted private key:
```
openssl genrsa 2048 | openssl pkcs8 -topk8 -inform PEM -out rsa_key.p8 -nocrypt
```
- To generate an encrypted private key:
```
openssl genrsa 2048 | openssl pkcs8 -topk8 -v2 des3 -inform PEM -out rsa_key.p8
```
This command prompts you for a passphrase. Enter a paraphrase value that complies with the PCI DSS standard. Ensure that you store this passphrase value securely. You need this value to configure a connection with Snowflake using an encrypted private key.
Generate the public key by referencing the private key.
```
openssl rsa -in rsa_key.p8 -pubout -out rsa_key.pub 
```
See Key-pair authentication and key-pair rotation.

Configure External Identity Providers for OAuth 2.0 Client Credentials Authentication

To use the OAuth 2.0 Client Credentials security policy with the Snowflake Adapter, you must configure an external identity provider (IDP) such as Azure AD, Okta, or PingFederate to enable token-based access. Register an application in your chosen IDP and retrieve the following values required for authentication:

Access token URI
Client ID
Client secret
Scope

You use these credentials to obtain an access token that the Snowflake Adapter uses to connect securely to Snowflake without requiring user-interactive (three-legged OAuth) flows.

Note:

These details apply only to security configuration. Connection properties and runtime behavior remain the same as existing OAuth-based integrations. If invalid credentials are provided, descriptive error messages are returned to help with troubleshooting.

For more information, see the provider-specific setup instructions:

To learn more about how external OAuth works with Snowflake in general, see External OAuth Overview in the Snowflake Documentation.

Create User and Security Integration In Snowflake

To allow secure, token-based access to Snowflake using an external identity provider, follow these steps to create a user:

Obtain an access token using the following credentials from your IDP:
- Client ID
- Client secret
- Access token URI
- Scope
Decode the access token and extract the sub value. See Decode the OAuth Access Token. This sub value represents the user ID issued by the identity provider.
Create an external OAuth security integration in Snowflake. Provide the following information:
- JWS key URL
- OAuth issuer
- Audience list
Create the user in Snowflake by using the extracted sub value as the value for the LOGIN_NAME property.
Grant appropriate roles and permissions to the user.
In the Security section of the Connections page in Oracle Integration, enter the same sub value in the User Id field.