SNOW-844371: Add support for SECONDARY_ROLE in the Python SDK

[poudrouxj]

What is the current behavior?

If a user can have access to multiple secondary roles (f.i. when working on different levels of PII-related data) any user of the snowflake-python-connector would need to run a primary query beforehand to select the secondary role:

with snowflake.connector.connect(**connection_params) as con:
    cur = con.cursor()
    cur.execute('USE SECONDARY ROLE <ROLE>')
    cur.execute(<MY_QUERY>)

What is the desired behavior?

Include a session parameter in the connection_params:

connection_params['SECONDARY_ROLE']=<MY_SECONDARY_ROLE>

with snowflake.connector.connect(**connection_params) as con:
    cur = con.cursor()
    cur.execute(<MY_QUERY>)

How would this improve snowflake-connector-python?

Simplify the snowflake api

References and other background

It seems that SECONDARY_ROLE is a session oriented parameter, but I'm not seeing any support for it besides running a separate query:
https://docs.snowflake.com/en/user-guide/security-access-control-overview#enforcement-model-the-primary-role-and-secondary-roles

Feel free to give feedback if its intentional to include this or not.

[tempoxylophone]

This would be helpful, especially for asynchronous queries in the Cursor's execute_async function.

Best lead I found for async queries was to use execute_string from the Connection object, from this community post thead.

[tempoxylophone]

It looks like Cursor supports a num_statements parameter in both execute and execute_async. Main branch has this here, see line 726 in cursor.py. According to the SQL API doc page here, it seems like this is the only way to support secondary roles:

The following commands and statements are supported only within a [request that specifies multiple statements]
(https://docs.snowflake.com/en/developer-guide/sql-api/submitting-multiple-statements.html):

Commands that perform explicit transactions, including:

• BEGIN

• COMMIT

• ROLLBACK

Commands that change the context of the session, including:

• USE

[sfc-gh-yqiu]

Hi, I looked into this and unfortunately, the backend only supports activating the primary role when establishing a connection. An exception is when one is activating an account, during which all secondary roles are activated (with no finer control). If by chance enabling all secondary roles automatically is what you want, consider using ALTER USER <user> SET DEFAULT_SECONDARY_ROLES = ( 'ALL' );

Otherwise, it does sound like your best bet is, as @tempoxylophone mentioned, executing USE SECONDARY ROLE <role> in either execute_string or execute with the num_statements parameter set.