Fully managed cloud. 30-day FREE full featured trial. Start Now
cancel
Showing results for 
Search instead for 
Did you mean: 

Granting access to connection info

drumcircle
Rising Star

My call to exa.get_connection('connection_name') in a LUA script function s failing when not running as super user.

The error is:

 

4300:"attempt to call a nil value (field 'get_connection')" caught in script...

 

I've given grants to to the script that uses the connection and upstream scripts.

 

    grant CONNECTION X to public;
    grant ACCESS ON CONNECTION X for SCRIPT Y to public;
    grant ACCESS ON CONNECTION X for SCRIPT Z to public;
    grant ACCESS ON CONNECTION X for SCRIPT A to public;

 

Ideas? 

7 REPLIES 7

mwellbro
Xpert

exa.get_connection('connection_name') reads like something from a UDF, I´ll look into the doc´s , one second...
this doesn´t seem to be a privilege issue, Exa would tell you something along the lines of "has no privilege to access..."

Then again, when running it as SYS it works ?

exa-Nico
Community Manager
Community Manager

@drumcircle is this running in a UDF or a Lua script? The below example reads it correctly for me. 

CREATE CONNECTION TEST_CONN TO 'testhost' user 'user' identified by 'pw';

--/
CREATE OR REPLACE LUA SCALAR SCRIPT test.read_conn (CONNECTION_NAME varchar(20000)) 
EMITS(field VARCHAR(2000000), val VARCHAR(2000000)) AS 

function run(ctx)
 ctx.emit('host',exa.get_connection(ctx.CONNECTION_NAME).address)
 ctx.emit('username',exa.get_connection(ctx.CONNECTION_NAME).user)
 ctx.emit('password',exa.get_connection(ctx.CONNECTION_NAME).password)
end
/

select test.read_conn('TEST_CONN');
Sports Enthusiast. Database Guy. Member of Team Exasol.
Having trouble? Just let me know!

Please Give Kudos if you find this post useful and also mark this as Accepted Solution if this is the required solution

drumcircle
Rising Star
It's a lua script not a UDF.

exa-Aleksandr
Team Exasol
Team Exasol

 

Hi @drumcircle ,

To my knowledge Lua scripts can't access connection, but Lua UDFs can.

If you think that it makes sense for Lua scripts to support this functionality (I do think) you can post an Idea in our Ideation portal: How to use the Exasol Ideation Board.

mwellbro
Xpert

Hi all,

"To my knowledge Lua scripts can't access connection, but Lua UDFs can." => thought so too, but that kind of contradicts @drumcircles statement that when running his script as a privileged user it works, i.e. "LUA script function s failing when not running as super user.".

And as promised I took a peek in the doc´s:
https://docs.exasol.com/db/latest/database_concepts/udf_scripts/lua.htm

mwellbro_0-1645085985982.png


The standard LUA script also has an "exa" interface for "meta" informations, but as @exa-Aleksandr pointed out, that won´t let us access connection infos.

exa-Nico
Community Manager
Community Manager

Hi @drumcircle, I'm also looking at the "privileged user thing", and I haven't been able to simply access the values of the connection in the Lua script, even as SYS. See this example:

 

--/
CREATE OR REPLACE LUA SCRIPT "TEST"."GET_CONNECTION_INFO" (CONNECTION_NAME) RETURNS ROWCOUNT AS
output(query("SELECT CURRENT_USER")[1].CURRENT_USER)
output(exa.get_connection(CONNECTION_NAME).address)
output(exa.get_connection(CONNECTION_NAME).user)
output(exa.get_connection(CONNECTION_NAME).password)
return 1
/

execute script TEST.GET_CONNECTION_INFO ('TEST_CONN') WITH OUTPUT;

 

returns:

OUTPUT
SYS
43000:"attempt to call a nil value (field 'get_connection')" caught in script "TEST"."GET_CONNECTION_INFO" at line 2

 

So even as SYS it doesn't work in a Lua script (at least for me). I could however call the UDF in the Lua script and then it would work:

 

--/
CREATE OR REPLACE LUA SCRIPT "TEST"."GET_CONNECTION_INFO" (CONNECTION_NAME) RETURNS ROWCOUNT AS
output(query("SELECT CURRENT_USER")[1].CURRENT_USER)

UDF_results = query([[SELECT TEST.READ_CONN('TEST_CONN')]])

output([[host: ]]..UDF_results[1].VAL)
output([[user: ]]..UDF_results[2].VAL) 
output([[pw: ]]..UDF_results[3].VAL) 
/

execute script TEST.GET_CONNECTION_INFO ('TEST_CONN') WITH OUTPUT;

 

which returns:

OUTPUT
SYS
host: testhost
user: user
pw: pw

When running as a non-privileged user, I am only able to get the results if the user has the ACCESS ON CONNECTION privilege (ie GRANT ACCESS ON CONNECTION TEST_CONN FOR TEST.READ_CONN TO USER;)

Could you share the behavior that you're getting? 

Sports Enthusiast. Database Guy. Member of Team Exasol.
Having trouble? Just let me know!

Please Give Kudos if you find this post useful and also mark this as Accepted Solution if this is the required solution

drumcircle
Rising Star

If you create a UDF and call it from a script using SELECT <UDF> FROM DUAL; it also times out, even if the user has permissions to the UDF (but it works for DBA).  Is there another work-around to inject a connection address into a script, for non-super user?