Hi

For us, this would be a security game changer, and I believe it wouldn't be difficult for you to implement — similar to how GRANT ROLE or SET ROLE works.

Please consider implementing something like:

SET SESSION ROLE XXX; or grant XXX to LIVIUS SCOPE SESSION;

The format above is just an example — you can come up with a better one. Internally, this could be written into a temporary structure (table, variable, etc.) that is discarded automatically when the session ends.

The script below would be created by an administrative user (e.g., SYSDBA), so the SECURITY DEFINER would be SYSDBA.

CREATE ROLE XXX;

CREATE TABLE TEST
(ID INTEGER, 
SECURE_INFO VARCHAR(100)
);

...other tables 

GRANT ALL ON TEST TO XXX;

CREATE PROCEDURE SETUP_ROLE_XXX (TOCKEN VARCHAR(200)) SQL SECURITY DEFINER
AS 
BEGIN
  IF (TOKEN<>'') THEN /* here should be some validation of the tocken, e.g by udr ,not simple comparision as <>'' ;-) */
     SET SESSION ROLE XXX; -- or grant XXX to LIVIUS SCOPE SESSION;
END

GRAN EXECUTE ON SETUP_ROLE_XXX  TO LIVIUS;

Now, I connect as user LIVIUS from the application, and the application calls:

EXECUTE PROCEDURE SETUP_ROLE_XXX('some_secure_tocken')

From that point on, the user LIVIUS has all the privileges granted to that role (XXX) — but only for the duration of the current session (connection).

If the connection breaks, the session-specific role is gone.

If the user connects to the database using ISQL, FlameRobin, etc., they won’t have any special privileges at all.