Snowflake unable to add integration without BLOCKED_ROLES_LIST

Question

When i execute the following command:

use role accountadmin;
CREATE SECURITY INTEGRATION cr_integration_10
   TYPE=OAUTH
   ENABLED=TRUE
   OAUTH_CLIENT = CUSTOM
   OAUTH_CLIENT_TYPE='CONFIDENTIAL'
   OAUTH_REDIRECT_URI='https://oauth.pstmn.io/v1/browser-callback'
   OAUTH_ISSUE_REFRESH_TOKENS = TRUE
   OAUTH_REFRESH_TOKEN_VALIDITY = 86400
   BLOCKED_ROLES_LIST = ('ORGADMIN');]

describe integration cr_integration_10;

I get following error:

003629 (42501): Roles [ACCOUNTADMIN, ORGADMIN, SECURITYADMIN] are blocked since parameter OAUTH_ADD_PRIVILEGED_ROLES_TO_BLOCKED_LIST is enabled.
002003 (02000): SQL compilation error:                                          
Integration 'CR_INTEGRATION_10' does not exist or not authorized.

So i ran the following command:

use role accountadmin;
alter account ENQQEAI.DH46924 set EXTERNAL_OAUTH_ADD_PRIVILEGED_ROLES_TO_BLOCKED_LIST = false;

still issue persist.

Answer 1

The first issue is because you need to set "OAUTH_ADD_PRIVILEGED_ROLES_TO_BLOCKED_LIST" to False if one of the privileged roles have to be added to the BLOCKED_LIST.

So, run the following SQL and review the results:

alter account set OAUTH_ADD_PRIVILEGED_ROLES_TO_BLOCKED_LIST = false;

There is a difference in the use for the aforementioned parameter and the one which you are running viz EXTERNAL_OAUTH_ADD_PRIVILEGED_ROLES_TO_BLOCKED_LIST

and the details for it are here:

https://docs.snowflake.com/en/sql-reference/parameters#external-oauth-add-privileged-roles-to-blocked-list

https://docs.snowflake.com/en/sql-reference/parameters#oauth-add-privileged-roles-to-blocked-list