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:
- Grant Type: Authorization Code
- Callback URL: <exactly as what’s set in the integration API created>
- Auth URL: get from DESC SECURITY INTEGRATION
- Access Token URL: get from DESC SECURITY INTEGRATION
- Client ID: get from DESC SECURITY INTEGRATION
- Client Secret: get from SELECT SYSTEM$SHOW_OAUTH_CLIENT_SECRETS
- 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
}