Prerequisites for Creating a Connection

You must satisfy the following prerequisites to create a connection with the Snowflake Adapter:

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.