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-deployment azure-function-app azure-functions azure-openai azure-sign-in azure-site-recovery azure-sql-database azure-subscription azure-vm base64 certificate change-data-capture change-tracking chrome clr container cte data-api-builder data-conversion data-gateway database-mail 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 excel-online-for-business 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 office-script 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-2025 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 terraform tips troubleshooting unicode view visual-studio visual-studio-code vmware wait-statistics wi-fi-connection-issue windows-settings