Hide Access Keys and Passwords

UDF scripts can contain hard-coded information such as access keys and passwords, and these are visible in the audit tables, profiling tables, and in EXA_ALL_SCRITPS table. All users who have the privileges to execute UDF scripts can view these access keys and passwords. To avoid exposing such information, you can use connection objects to hide information. A Connection consists of a type (currently always "password"), an address, a user, and a password. These details are available by using the get_connection method. For example, in Python, you can retrieve these details with exa.get_connection('<connection_name>'). Refer to Accessing Connection Definitions section for the various languages for the exact syntax. Consider the following examples:

--/
CREATE OR REPLACE PYTHON SCALAR SCRIPT ip_to_city (ip char(15)) RETURNS VARCHAR(2000000) AS
import json
import urllib
ACCESS_KEY = exa.get_connection('IPSTACK_API').password
def run (ctx):
        s = urllib.urlopen('http://api.ipstack.com/'+ctx.ip+'?access_key='+ACCESS_KEY).read()
        data=json.loads(s)
        return data["city"]
/
SELECT ip_to_city ('213.95.129.161');
CREATE or replace CONNECTION ipstack_api TO '' IDENTIFIED BY '3ea0e75f8fbd815a86516aa783648d4a'

The user has the privileges to EXECUTE SCRIPT, CREATE SCRIPT, and USE ANY CONNECTION. Executing the below script returns an insufficient privilege for using connection IPSTACK_API message.

SELECT udfs.ip_to_city (‘213.95.129.161’);

The insufficient privileges message indicates that by default, the user is not allowed to access the connection details. You can easily solve this by providing GRANT CONNECTION privileges or GRANT ACCESS ANY CONNECTION privileges to the user. However, this is not the most secure method. Providing GRANT CONNECTION privileges allows users to create new scripts that will use the connection details to find out the access keys or passwords.

In order to make this secure, limit the access to connection details to a UDF script. This way, users can access the connection details only by the script. For example,

GRANT ACCESS ON CONNECTION ipstack_api FOR SCRIPT udfs.ip_to_city to Jim;

Additionally, you can also specify the schema name as shown below. This allows the users to access connection details via all scripts within the schema. This way, if the user does not have the CREATE SCRIPT privilege within the schema or is not the schema owner, the user will not be able to retrieve the access keys or passwords.

GRANT ACCESS ON CONNECTION ipstack_api FOR SCHEMA udfs to Jim;