Retrieving current username within table function

[Nigel Jones <ni...@cherrybyte.me.uk>]

Newbie warning ...

I'm trying to figure out how I could determine the current username when in
a table function (in fact I'm writing some java code that plugs into
someone else's table function ...)

I was originally thinking of something like the snippet below. However this
doesn't work (I get an SQL Transient Exception).

I don't know derby well but saw some discussion around not exposing the
language context in any case (due to security concerns)

Given this can anyone suggest the simplest way to retrieve the authorised
user?

ContextManager contextMgr = ((EmbedConnection)
DriverManager.getConnection("jdbc:default:connection")).getContextManager();

LanguageConnectionContext languageContext =
(LanguageConnectionContext)contextMgr.getContext("LanguageConnectionContext");
StatementContext derbyStatementContext = languageContext.getStatementContext();
String localDerbyContextCurrentUser =
derbyStatementContext.getSQLSessionContext().getCurrentUser();

Many thanks

Nigel.

Re: Retrieving current username within table function

[Nigel Jones <ni...@cherrybyte.me.uk>]

Many thanks for that suggestion.

I subsequently found another path through the code I'm using where the user
id had already been set and gives me what I need for now, but the above
fragment is really useful in case I need to do this somewhere else. Thanks
...

On Fri, 9 Feb 2018 at 02:42 Rick Hillegas <ri...@gmail.com> wrote:

> On 2/8/18 10:57 AM, Nigel Jones wrote:
>
> Newbie warning ...
>
> I'm trying to figure out how I could determine the current username when
> in a table function (in fact I'm writing some java code that plugs into
> someone else's table function ...)
>
> I was originally thinking of something like the snippet below. However
> this doesn't work (I get an SQL Transient Exception).
>
> I don't know derby well but saw some discussion around not exposing the
> language context in any case (due to security concerns)
>
> Given this can anyone suggest the simplest way to retrieve the authorised
> user?
>
> ContextManager contextMgr = ((EmbedConnection) DriverManager.getConnection("jdbc:default:connection")).getContextManager();
>
> LanguageConnectionContext languageContext = (LanguageConnectionContext)contextMgr.getContext("LanguageConnectionContext");
> StatementContext derbyStatementContext = languageContext.getStatementContext();
> String localDerbyContextCurrentUser = derbyStatementContext.getSQLSessionContext().getCurrentUser();
>
>
> Many thanks
>
> Nigel.
>
> Hi Nigel,
>
> You should be able to call current_user from inside a user-defined
> function. Here's an example of how to do this via a scalar function. Should
> work the same for a table function:
>
> Run the following ij script...
>
> connect 'jdbc:derby:memory:db1;create=true';
>
> create function findUser() returns varchar(128)
> language java
> parameter style java
> reads sql data
> external name 'UserFinder.findUser';
>
> values findUser();
>
> ...after compiling the following class...
>
> import java.sql.*;
>
> public class UserFinder
> {
>   public static String findUser() throws SQLException
>   {
>     try (Connection conn =
> DriverManager.getConnection("jdbc:default:connection"))
>     {
>       try (PreparedStatement ps = conn.prepareStatement("values
> current_user"))
>       {
>         try (ResultSet rs = ps.executeQuery())
>         {
>           rs.next();
>           return rs.getString(1);
>         }
>       }
>     }
>   }
> }
>

Re: Retrieving current username within table function

[Rick Hillegas <ri...@gmail.com>]

On 2/8/18 10:57 AM, Nigel Jones wrote:
> Newbie warning ...
>
> I'm trying to figure out how I could determine the current username 
> when in a table function (in fact I'm writing some java code that 
> plugs into someone else's table function ...)
>
> I was originally thinking of something like the snippet below. However 
> this doesn't work (I get an SQL Transient Exception).
>
> I don't know derby well but saw some discussion around not exposing 
> the language context in any case (due to security concerns)
>
> Given this can anyone suggest the simplest way to retrieve the 
> authorised user?
> ContextManager contextMgr = ((EmbedConnection) DriverManager.getConnection("jdbc:default:connection")).getContextManager();
>
> LanguageConnectionContext languageContext = (LanguageConnectionContext)contextMgr.getContext("LanguageConnectionContext");
> StatementContext derbyStatementContext = languageContext.getStatementContext();
> String localDerbyContextCurrentUser = derbyStatementContext.getSQLSessionContext().getCurrentUser();
>
> Many thanks
> Nigel.

Hi Nigel,

You should be able to call current_user from inside a user-defined 
function. Here's an example of how to do this via a scalar function. 
Should work the same for a table function:

Run the following ij script...

connect 'jdbc:derby:memory:db1;create=true';

create function findUser() returns varchar(128)
language java
parameter style java
reads sql data
external name 'UserFinder.findUser';

values findUser();

...after compiling the following class...

import java.sql.*;

public class UserFinder
{
   public static String findUser() throws SQLException
   {
     try (Connection conn = 
DriverManager.getConnection("jdbc:default:connection"))
     {
       try (PreparedStatement ps = conn.prepareStatement("values 
current_user"))
       {
         try (ResultSet rs = ps.executeQuery())
         {
           rs.next();
           return rs.getString(1);
         }
       }
     }
   }
}