Snowflake OAUTH using Python

Snowflake Supports various techniques for authentication but if we want to run queries using python then we need to use Snowflake's Python Connector. While creating a connection in python we can either use Basic auth (username and password) or we can use Oauth.

Authentication in Snowflake

Basic AUTH

In Basic AUTH we need to provide username password in code itself so that Snowflake can create a connection using those credentials and we can run SQL queries on Snowflake. But it is not a good practice to give your credentials in code like this.

OAUTH

Oauth doesn't use password for authentication, instead it uses access token to run queries on Snowflake. In order to generate access token we need to call Snowflake's API. We need to follow certain steps in order to get access token.

Snowflake Configuration

Creating Security Integration Object

In order to get all necessary URLs and tokens, we need to create a Security Intergration Object in Snowflake.

CREATE OR REPLACE SECURITY INTEGRATION PYTHON_OAUTH

TYPE = OAUTH

ENABLED = TRUE

OAUTH_CLIENT = CUSTOM

OAUTH_CLIENT_TYPE = 'CONFIDENTIAL'

OAUTH_ALLOW_NON_TLS_REDIRECT_URI = TRUE

OAUTH_REDIRECT_URI = 'http://localhost/'

OAUTH_ISSUE_REFRESH_TOKENS = TRUE

OAUTH_REFRESH_TOKEN_VALIDITY = 7776000;

-- FOR CUSTOM CLIENTS OAUTH_REFRESH_TOKEN_VALIDITY

-- MIN = 3600 (1HR)

-- MAX(DEFAULT) = 7776000 (90 DAYS)

-- AUTH_URL = OAUTH_AUTHORIZATION_ENDPOINT

-- ACCESS_TOKEN_URL = OAUTH_TOKEN_ENDPOINT

Note - Refresh Token validity is Min 1 Hr. and Max 90 Days. After this time we need to authenticate again to get a new Refresh Token.

Please make sure:

User must have permission to create Security Integration Object.
User who is going to access snowflake using OAUTH must not be an ACCOUNTADMIN, ORGADMIN or SECURITYADMIN. These roles are by default added to BLOCKED_ROLES_LIST.
User who is going to access Snowflake using OAUTH must have required GRANTS to this Security Integration. For Example if Sysadmin is going to access OAUTH then:

-- GRANTING ACCESS TO SYSAMIN ROLE

GRANT ALL ON INTEGRATION PYTHON_OAUTH TO ROLE SYSADMIN;

Getting URLs and parameters

To get all Basic URLs and parameters we need to describe this object.

-- AUTH_URL = OAUTH_AUTHORIZATION_ENDPOINT

-- ACCESS_TOKEN_URL = OAUTH_TOKEN_ENDPOINT

DESCRIBE SECURITY INTEGRATION PYTHON_OAUTH;

After running describe statement we will get output like this.

We need following properties:

OAUTH_CLIENT_ID
OAUTH_AUTHORIZATION_ENDPOINT
OAUTH_TOKEN_ENDPOINT

For the sake of simplicity, we will be using more generic terms like CLIENT_ID, AUTH_URL and ACCESS_TOKEN_URL.

We also need CLIENT_SECRET that we can get by using following statement.

-- CLIENT_ID, CLIENT_SECRET

SELECT SYSTEM$SHOW_OAUTH_CLIENT_SECRETS('PYTHON_OAUTH');

Please keep all these parameters hidden from Public. If anyone gets access to them, they can authenticate themselves and may result in a data breach.

Here you will get 3 parameters:

OAUTH_CLIENT_SECRET_2
OAUTH_CLIENT_SECRET
OAUTH_CLIENT_ID

We can use either of the two client secrets, I will be using OAUTH_CLIENT_SECRET.

Getting Refresh Token

Refresh Token

NOTE: Getting this refresh token is a one time process (depends on expiry of refresh token). We will need to repeat the same process if our refresh token expires.

We just need last piece of puzzle i.e. Refresh Token. Please follow these steps carefully.

Step : 1

We need to put a specific URL in our browser, authenticate using Username and password and finally copy the code in the URL.

URL format will be like this:

<AUTH_URL>?client_id=<CLIENT_ID(with URL encoding)>&response_type=code&redirect_uri=http%3A%2F%2Flocalhost%2F

For me URL will be like this:

https://XXXXX.ap-southeast-1.snowflakecomputing.com/oauth/authorize?client_id=XXXXXXLBQh8%3D&response_type=code&redirect_uri=http%3A%2F%2Flocalhost%2F

Note: We need to encode all the characters which are not suitable with URLs. For example, we can't put "=" as is, upon encoding it becomes "%3D". Similarly, we need to encode other unsuitable characters. You can find characters along with their encoding here : https://www.w3schools.com/tags/ref_urlencode.ASP

Step 2

After submitting the username and password, a blank page with a code in our URL will be opened

This code is after http://localhost/? is very important for next step. Note it down somewhere secure.

Step 3

Paste the copied code in the python program along with ACCES_TOKEN_URL, CLIENT_ID and CLEINT_SECRET

import requests

from requests.auth import HTTPBasicAuth

ACCESS_TOKEN_URL = 'https://XXXXX.ap-southeast-1.snowflakecomputing.com/oauth/token-request'

CLIENT_ID = 'XXXXXXXXXXXXXXXLBQh8='

CLEINT_SECRET = 'XXXXXXXXXXXXXXXXXXXXXXXXXXXT7+Yw='

CODE = "XXXXXXXXXXXXXXXXXXXXXXXXXXXXXAD134BD0", # PASTE THAT CODE HERE

URL = ACCESS_TOKEN_URL

AUTH = HTTPBasicAuth(CLIENT_ID, CLEINT_SECRET)

HEADER = {

"Content-Type" : "application/x-www-form-urlencoded",

"Charset" : "UTF-8"

}

BODY = {

"grant_type" : "authorization_code",

"code" : CODE

"redirect_uri" : "http://localhost/"

}

# Main Request

with requests.session() as r:

response = r.post(url=URL, headers=HEADER, auth=AUTH, data=BODY)

response_json = response.json()

print(response_json)

We will get Output like This:

{'access_token': 'ver:1-hint:1111XXXXXXX', 'refresh_token': 'ver:2-hint:111XXXX VERY LONG REFRESH TOKEN HERE', 'token_type': 'Bearer', 'username': 'TOMAR', 'scope': 'refresh_token session:role:SYSADMIN', 'refresh_token_expires_in': 7775999, 'expires_in': 600, 'idpInitiated': False}

Creating JSON file

Gathering all puzzle pieces into one place

We can now collect all parameters in a single json for ease of access for further process.

{

"auth_url" : "https://XXXXX.ap-southeast-1.snowflakecomputing.com/oauth/authorize",

"access_token_url" : "https://XXXXX.ap-southeast-1.snowflakecomputing.com/oauth/token-request",

"client_id" : "XXXXXXXXXXXXXXXXLBQh8=",

"client_secret" : "XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXtaT7+Yw=",

"refresh_token" : "ver:2-hint:111XXXXXX VERY LONG REFRESH TOKEN HERE"

}

Interacting with Snowflake

We are ready to run SQL queries.

We can import this json file in our python code. We need to make sure that either this json file is in the same directory as our main code file or we need to give the absolute path of json file to import it successfully.

Pre-requisites

We need to Install the following libraries:

pip install requests

pip install pandas

pip install "snowflake-connector-python[pandas]"

pip install snowflake-connector-python

More information on snowflake python connector installation can be found here : https://docs.snowflake.com/en/user-guide/python-connector-install.html

Necessary Imports

json -> to interact with json file

requests -> to make custom api call

pandas -> to handle dataframe

snowflake.connector -> to connect with snowflake

import json

import requests

import pandas as pd

import snowflake.connector as snow

from requests.auth import HTTPBasicAuth

Global Variables

auth_url = ''

access_token_url = ''

client_id = ''

client_secret = ''

refresh_token = ''

Reading JSON file for its values

Make sure that if JSON file is not in same folder as this code file then you will be required to give fully qualified path of the file.

with open(r'C:\\Users\\USERNAME\\Desktop\\python\\client.json', 'r') as f:

data = json.load(f)

auth_url = data['auth_url']

access_token_url= data['access_token_url']

client_id= data['client_id']

client_secret= data['client_secret']

refresh_token= data['refresh_token']

Preparing attributes for API Call

URL = access_token_url

AUTH = HTTPBasicAuth(client_id, client_secret)

HEADER = {

"Content-Type" : "application/x-www-form-urlencoded"

}

BODY = {

"grant_type" : "refresh_token",

"refresh_token" : refresh_token

}

Main API call to get Access Token

# Main Request

def get_access_token():

with requests.session() as r:

response = r.post(url=URL, headers=HEADER, auth=AUTH, data=BODY)

response_json = response.json()

return response_json['access_token']

Method to create connection with Snowflake

def connection():

conn = snow.connect(

user='USER',

authenticator="oauth",

token=get_access_token(),

account='ACCOUNT-IDENTIFIER',

warehouse='WAREHOUSE',

database='DATABASE',

schema='SCHEMA'

)

return conn

Running SQL query

check = 'select * from customer limit 10'

cur = connection().cursor().execute(check)

# Getting output of query into dataframe

df = pd.DataFrame()

df = cur.fetch_pandas_all()

print('Printing Dataframe Information :')

print(df.info(memory_usage='deep'))

Jupyter Notebook

To access the code along with the documentation in one place, refer to my Jupyter Notebook.Click on the "Google Colaboratory" Tab below to access it.

Google Colaboratory

Google Sites

Report abuse