Invoking a stored procedure as part of a unit of work

(ORACLE 11/12)

The documentation on stored procedures seems to assume a result set is being returned by Lightspeed. My task is different. I need to invoke a stored procedure on connect to set the database role for the connection for the duration of the unit of work. The internal connecting user is defined as low privilege, but acquires a proper role with elevated privileges based on the external userid that is connected to the application. So for example, the internal login is internal-db-user with minimal database object privileges (there is indeed a bare minimum), and based on the connecting web app's user, say editing-privileges-app-user, a stored procedure (sp) is fired with the role name DATABASE-EDITOR and a role password. The database session is then running as a DATABASE-EDITOR and Lightspeed can be used to do fun things like update, delete and insert rows to tables where those privileges have been granted to that role. However, this sp invocation needs to happen right after connect before LightSpeed does any work with the database.

So the fundamental question is: how to intervene in the Lightspeed unit of work's connection stream and call this non-query non-result set sp (with parameters, of course, like the role to be granted)?

Usage:
SET_ROLE(<rolename>,<optional-password>)

There is no "hook" as such to achieve this but you can execute the procedure after creating the UnitOfWork by creating a new ADO.NET command object and then calling UnitOfWork.PrepareCommand passing in that object as an argument which will attach the command to the active connection associated with the UnitOfWork and you can then call cmd.ExecuteNonQuery()

If you are doing this consistently then you could develop a simple helper function to enscapulate this, e.g.

public IUnitOfWork GetUnitOfWork(LightSpeedContext context)
{
    var unitOfWork = context.CreateUnitOfWork();
    var cmd = new OracleCommand("sp_setpermissions");
    unitOfWork.PrepareCommand(cmd);
    cmd.ExecuteNonQuery();
    return unitOfWork;
}

using (var unitOfWork = GetUnitOfWork(context))
{
   // perform normal data-access
}

Does this sound viable for your scenario?

Jeremy

This appears to fulfill the need. I will try it and report back!