Snowflake SQL Script API

Snowflake notes

Posted by John Liu on Saturday, February 15, 2025

To grant access to a role

grant usage on warehouse compute_wh to role mytestrole;
grant usage on database test to role mytestrole;
grant usage on schema study to role mytestrole;

--select on individual table
grant select on table test.study.sales to role mytestrole;

--full access to all existing tables
grant select,insert,update,delete on all tables in schema test.study to role mytestrole;

--automatically grant access to future tables
grant select on future tables in schema test.study to role mytestrole;

--varify permission grant
show grants on table test.study.sales;

show grants to role mytestrole;

Setup Snowflake API access:

--create an OAuth security integration
CREATE SECURITY INTEGRATION my_oauth_integration
    TYPE = OAUTH
    ENABLED = TRUE
    OAUTH_CLIENT = CUSTOM
    OAUTH_CLIENT_TYPE = CONFIDENTIAL
    OAUTH_REDIRECT_URI = 'http://localhost:5000'
    OAUTH_ALLOW_NON_TLS_REDIRECT_URI = true
    OAUTH_ISSUE_REFRESH_TOKENS = TRUE
    OAUTH_REFRESH_TOKEN_VALIDITY = 86400;

--Verify the integration
SHOW SECURITY INTEGRATIONS;

--Get OAuth configuration details
DESC SECURITY INTEGRATION my_oauth_integration;

--Get Client ID/Client Secret
SELECT SYSTEM$SHOW_OAUTH_CLIENT_SECRETS('MY_OAUTH_INTEGRATION');
--the name here is case sensitive
--note that even though we used lowercase for the integration name without quote, Snowflake auto convert it to upercase.

--set roles allowed to use the API
ALTER SECURITY INTEGRATION my_oauth_integration 
SET ALLOWED_AUTHORIZATION_ROLES = ('PUBLIC', 'MyTestRole');

SELECT CURRENT_ACCOUNT(), CURRENT_REGION(), CURRENT_ROLE();

For HTTPListener to work, the above Redirect URI needs to be http and not https. Using option OAUTH_ALLOW_NON_TLS_REDIRECT_URI = true.

To access the API in Postman:

Set Authorization type to OAuth 2.0, then set follow in the right panel configure new token section and then click Get New Access Token:

  1. Grant Type: Authorization Code
  2. Callback URL: <exactly as what’s set in the integration API created>
  3. Auth URL: get from DESC SECURITY INTEGRATION
  4. Access Token URL: get from DESC SECURITY INTEGRATION
  5. Client ID: get from DESC SECURITY INTEGRATION
  6. Client Secret: get from SELECT SYSTEM$SHOW_OAUTH_CLIENT_SECRETS
  7. Scope: session:role:RoeToBeUsed

In the body, set to raw and JSON format. Use POST instead of GET.

POST: https://<your account>.snowflakecomputing.com/api/v2/statements

for the <your account> address, refer to Auth URL.

Body:

{
  "statement": "SELECT * FROM TEST.STUDY.SALES LIMIT 10;",
  "timeout": 60
}
FEATURED TAGS
ai api automation availability availability sets availability zones aws vm azure azure automation runbook azure blob azure cosmos db azure data lake azure function app azure openai azure sign-in azure site recovery azure sql database azure sql db azure subscription azure vm base64 certificate change data capture change tracking chrome clr container cte data api builder data conversion data gateway database role database size date table dax db config derived table diagram direct query disk management disk space docker downtime dtc dynamic m parameter embedding encrypted connection excel excel online execution plan extended events external data fabric fabric capacity failover cluster fk geometry hierarchy httpwebrequest hugo hyper-v incognito mode index infrastructure inline tvf json kql lakehouse linked server live query statistics locking m machine learning machine learning model machine learning services master key mcp mdx memory memory grant mermaid mirrored sql server network network card network category onedrive onnx runtime openrowset p2v parquet performance polybase power automate power bi power bi report tricks power platform power query powershell printer public ip address pyspark python qgis qt designer query performance query plan query troubleshooting r regex replication route s3 schema design scripting self-signed certificate server role sharepoint snowflake software development sofware development spark sql sql agent sql availability group sql error sql failover cluster instance sql index sql openjson sql permission sql recovery sql script sql security sql server sql server admin sql server config sql statistics ssis ssisdb ssl ssl/tls error ssms table expression tempdb tips troubleshooting unicode view visual studio visual studio code vmware wait statistics wi-fi connection issue windows settings