Is there a way to associate an application context with a database connection on servers without Sybase DB (similar to set_appcontext in Sybase)?

Question

Sybase has the ability for an application to transfer “contextual” data — for example, the end-user name of an application, etc. - to a session connecting to the database. The context data is basically just a set of key-value pairs that are stored / retrieved through the set_appcontext / get_appcontext stored procedures.

Question

Do other main database servers (MSSQL / Oracle / MySQL) have a means to associate the application context with a session similar to Sybase set_appcontext ?

More details

One specific practical use of the application context is when you have a mid-tier application that connects to the database as a very specific general database user (examples include "webuser" / "http" for a web application running on an Internet server or myappserver "for the application server).

If this happens, we still want the database session to know who the END user is (for example, the actual user using the application client), either for access control, or (more importantly for my interest) for the audit / history trigger so that Determine which end user made the changes and register this end user information in the audit table.

Please note that the information is set at the session level, which means that any inserts / updates / deletions made in this session can use context data without passing them to each individual SQL statement - this is VERY important for, say, a trigger.

As a very concrete example of why this is useful, let's say you have an application server that starts a database session on behalf of a client, inside which you insert / update / delete rows in 5 different tables. You want to have audit tables for each of these 5 tables, which include information about what information was done with each user.

Using contextual data, you can simply get the "end user" data from the application context using a trigger and save it as part of the audit table entry. Without using the application context, you will need to (1) add an “end user” column to each of these 5 tables (and not just to audit the tables) and (2) change the application server to insert or install the update value of this column in the EVERY SQL statement, which the application server sets. Oh, and it’s not even related to how this can be done if you delete the line.

Answer 1

Oracle has several different solutions to this problem. First, you have a DBMS_APPLICATION_INFO package . Although you can use this to set arbitrary contextual information, it is usually used to track an application. Usually you install the module as the name of the application, and the action should be a description of a specific business process. You can then link to this information from V $ SESSION and track long-running operations through V $ SESSION_LONGOPS.

Oracle also has the ability to create a database object called a context . This is a more flexible way to populate the session level context. You can create a new context, and then create any attributes that you need in this context. And all your code can just refer to the context. for instance

 SQL> create context my_ctx 2 using pkg_ctx; Context created. SQL> create package pkg_ctx 2 as 3 procedure set_context; 4 end; 5 / Package created. SQL> create or replace package body pkg_ctx 2 as 3 procedure set_context 4 as 5 begin 6 dbms_session.set_context( 'MY_CTX', 'USERNAME', 'Justin Cave' ); 7 end; 8 end; 9 / Package body created. SQL> exec pkg_ctx.set_context; PL/SQL procedure successfully completed. SQL> select sys_context( 'MY_CTX', 'USERNAME' ) 2 from dual; SYS_CONTEXT('MY_CTX','USERNAME') ------------------------------------------------------------------------------- Justin Cave 

Answer 2

For PostgreSQL, you can create your own class of variables, which is the configuration setting in postgresql.conf. Something like that:

  custom_variable_classes = 'myvars'

(This requires a server reboot if I'm not mistaken)

Now with SQL, you can read and write this as follows:

  set myvars.some_flag = 'true';
 select current_setting ('myvars.some_flag');

Note that you can "dynamically" define new "variables", all with the myvars prefix. Individual values ​​should not be declared in postgresql.conf

Initially, this was intended for additional modules that allow you to define custom configuration parameters so that it slightly abuses the function, but should work nonetheless.