This article explains how to hide sensitive information in UDF scripts.
UDF scripts can contain hard-coded sensitive information such as access keys and passwords, which are visible in the audit tables, profiling tables, and in the EXA_ALL_SCRITPS table. All users who have the privileges to execute UDF scripts can view these access keys and passwords.
To avoid exposing access keys and passwords, you can use connection objects to hide the information. A connection consists of a type (currently always password
), an address, a user, and a password/key. These details are available by using the get_connection method.
In Python, you can retrieve these details with exa.get_connection('<connection_name>'). For the exact syntax used in the different script languages, see the following sections:
-
Access connection definitions (Python 3)
Examples
--/
CREATE OR REPLACE PYTHON3 SCALAR SCRIPT udfs.ip_to_city (ip varchar(20000)) RETURNS VARCHAR(2000000) AS
import json
import requests
ACCESS_KEY = exa.get_connection('IPSTACK_API').password
def run (ctx):
s = requests.get('http://api.ipstack.com/'+ctx.ip+'?access_key='+ACCESS_KEY).json()
return s['city']
/
CREATE or replace CONNECTION ipstack_api TO '' IDENTIFIED BY '<key>';The user in this example has the privileges to EXECUTE SCRIPT, CREATE SCRIPT, and USE ANY CONNECTION. Executing the following script returns an insufficient privilege for using connection IPSTACK_API message, since by default, the user is not allowed to access the connection details.
You can solve this by providing GRANT ACCESS ANY CONNECTION privileges to the user. However, this is not the most secure method. Providing GRANT ACCESS ANY CONNECTION privileges allows users to create new scripts that will use the connection details to find out the access keys or passwords.
A more secure solution is to limit the access to connection details to a UDF script. Users can then only access the connection details through the script. For example:
Additionally, you can specify the schema name:
This allows the users to access connection details via all scripts within the schema. If the user does not have the CREATE SCRIPT privilege within the schema or is not the schema owner, they will not be able to retrieve the access keys or passwords.
