Standard for Database Development – Getting rid of USER from PL/SQL and SQL – no longer is USER equivalent to End User

Many applications built in the Client/Server era made use of the fact that every end user had his or her own Database User Account and every Database Connection and Session were for the use of a single end user at any time. No proxy users or connection pooling were invented. So there was a direct relation at any time between the actual end user typing away at the keyboard and the database user that is available through the USER pseudo-function.

USER can be used – and is very frequently used – in SQL as well as PL/SQL. In this article I will discuss the merits of removing USER from all application code. And also how to go about it. It is based on very recent – yesterday – experience with the migration of a WebForms 6.0 application to the 10g toolstack, with a further migration to Java/J2EE lying around the corner.

Prior to Oracle 10g, performance could also be a reason for trying to avoid excessive use of USER in PL/SQL code. That has largely been resolved in 10g, as you can see below in the note on FAST DUAL.

Far more important is the fact that in an increasing number of applications, for starters almost all HTML and Java based web applications, the end user has not direct relationship with the database user. The Client/Server days with each end-users corresponding with a database user account are long gone. That means that references to USER have come to have a different meaning. A typical Java/J2EE application will have a connection pool with up to several tens of database connections, usually all to the same database user. That means that no matter who is the end user, the same database user account is used. USER does not tell us anything anymore! Of course this also means that the meaning of Database Roles is very limited when it comes to application security.

Note that I am not suggesting we should banish all user related logic and authorization from our applications and databases! What I am saying though is that we must make sure that we refer to the user we intend to refer to. And typically that is the end user, who may or may not relate directly to a database user.

Getting down to business

Many applications I have come across have their own table with application users. Typical columns in an application users table for a Client/Server applications are USERNAME (corresponding with a Database User) and ID or USER_ID, a primary key that is typically referenced from other tables. Such applications frequently contain queries – either in Views or Client Side filter expressions such as Form Block Where Clauses – that restrict record access based on record-ownership, something like:

select *
from   some_table tbl
,      app_users  usr
where  usr.username = USER
and    tbl.usr_id = usr.id
/

A more advanced approach to the similar authorization requirement is implementation using VPD (Virtual Private Database) – which requires an Oracle Enterprise Edition database. Note: for an introduction to VPD, see this article Virtual Private Database, securing your data by my colleague Marcos Claver. Using VPD, predicates returned by the Policy Function could be something like:

function TBL_FUN_AUT
(  p_schema_name   in   varchar2
,  p_table_name    in   varchar2
) return varchar2
is
  l_usr_id number(10);
begin
  select usr.id
  into   l_usr_id
  from   app_users usr
  where  usr.username = USER
  ;
  return ' tbl.usr_id = '||l_usr_id;
end TBL_FUN_AUT;

As this policy function is invoked only once in each session, the select against app_users is performed less often than in the previous case.

It is all good and well to take out references to USER, but what do we replace it with? We still need to filter and authorize data based on the current end user. So how do we know server side who the current end user is.

The best solution I can see is to make the filter logic and authorization code refer to our own Application Context in which we make sure that the current user is set up. There can be several ways to set the end user in this context: the Java middle tier of a Web Application can do it, a Database LOGON trigger can do it, a Client/Server LOGON or (RE)CONNECT event can do it etc.

First our Application Context:

create or replace
context pks_context
using pks_context_editor
/

The using clause in the creation of the Application Context refers to a database package that is to be used to set values in this particular context. The package is defined as follows:

create or replace
package pks_context_editor
as
c_username constant varchar2(8):= 'USERNAME';
c_userid   constant varchar2(8):= 'USERID';
c_module   constant varchar2(6):= 'MODULE';
procedure set_user
( p_username in varchar2
);
procedure set_module
( p_module in varchar2
);
end pks_context_editor;
/
create or replace
package body pks_context_editor
as
procedure set_user
( p_username in varchar2
) is
  l_user_id number;
begin
  dbms_session.set_context
  ( 'PKS_CONTEXT'
  , c_username
  , p_username
  );
  select usr_id
  into   l_usr_id
  from   app_users usr
  where  usr.USERNAME = p_username
  ;
  -- this next statement ensures that for all authorization filters, we can directly use the APP_USERS.ID value
  -- instead of deriving it over and over again from the USER(NAME)
  dbms_session.set_context
  ( 'PKS_CONTEXT'
  , c_userid
  , l_usr_id
  );
end set_user;
procedure set_module
( p_module in varchar2
) is
begin
  dbms_session.set_context
  ( 'PKS_CONTEXT'
  , c_module
  , p_module
  );
end set_module;
end pks_context_editor;
/

In case of Client/Server applications or WebForms applications – where too each user has his own database connection and user account – it is easiest to set the current user from a Database Logon trigger, such as this one:

CREATE OR REPLACE
TRIGGER PKS_LOGON_TRG
AFTER LOGON ON DATABASE
declare
  l_plsql_call varchar2(2000):= 'begin
                                   pks_context_editor.set_user( p_username => sys_context( ''USERENV'', ''SESSION_USER''));
                                 end;';
BEGIN
  -- we make use of dynamic sql to have changes in the pks_context_editor package not invalidate this logon trigger
  execute immediate l_plsql_call;
exception
WHEN OTHERS
THEN
  dbms_output.put_line( 'Error in database logon trigger '||sqlerrm);
END;
/

In Java Web applications, we have to build in logic that will set the End User (aka Web User) immediately after retrieving a connection from the connection pool.

Thus using our own application context to contain the current end user, for the moment regardless of how the application made sure that the end sure gets set in this context, we can rewrite all logic – SQL and PL/SQL – that so far made use of USER, to now make use of SYS_CONTEXT(‘PKS_CONTEXT’,’USERNAME’) or SYS_CONTEXT(‘PKS_CONTEXT’,’USERID’).

For example:

select *
from   some_table tbl
,      app_users  usr
where  usr.username = USER
and    tbl.usr_id = usr.id
/

gets rewritten as:

select *
from   some_table tbl
where  tbl.usr_id = SYS_CONTEXT('PKS_CONTEXT','USERID')
/

Proposed standard for SQL and PL/SQL development

It is a continuous struggle for us at AMIS to keep our standards for SQL and PL/SQL development up to date. I propose a very clear standard:

Thou shalt not use USER in either SQL or PL/SQL code.

Instead, use references to an Application Context variable, such as sys_context(‘APP_CONTEXT’,’USERID’) and ensure that either in a database LOGON trigger or from the application the current end user is set in this context.

Rationale: USER is the database user and often does not correspond to the actual end user. To ensure that authorization and filtering based on user preferences is done correctly, the code should refer to the representation of the end user and not just the database user. In addition, this approach yields a small performance benefit, especially in pre 10g databases where USER is translated to select USER from dual.

A note to the side: FAST DUAL

It is perhaps noteworthy to know that a reference to USER is translated to this select statement:

select USER [ into  ] from DUAL

Prior to 10g, this meant that frequent reference to USER in PL/SQL meant frequent queries against DUAL. Each call to the DUAL creates logical I/Os, which the database can do without. In some cases the call to DUAL is inevitable as in the line ause Oracle code treats DUAL as a special table, some ideas for tuning tables may not apply or be relevant. Oracle Database 10g makes all that worry simply disappear: Because DUAL is a special table, the consistent gets are considerably reduced and the optimization plan is different as seen from the event 10046 trace.

In Oracle9i

Rows     Execution Plan
-------  ---------------------------------------------------
      0  SELECT STATEMENT   GOAL: CHOOSE
      1   TABLE ACCESS (FULL) OF 'DUAL'

In 10g

Rows     Execution Plan
-------  ---------------------------------------------------
      0  SELECT STATEMENT   MODE: ALL_ROWS
      0   FAST DUAL

Notice the use of the new FAST DUAL optimization plan, as opposed to the FULL TABLE SCAN of DUAL in Oracle9i. This improvement reduces the consistent reads significantly, benefiting applications that use the DUAL table frequently.