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.
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
Refresh token

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 using the following command. For example:
```
create user oracle_user password='01March#2022' default_role = SYSADMIN
```
See Create User.
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; 
```
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.

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.